表格中返回的数据带有不同的分页断点以供重新开始编号。

huangapple go评论61阅读模式
英文:

Numbering Data returned in table with various break points for restart

问题

我有一个名为#TEST的表,其中列出了引用的人员值(2)、该人员的年份(1、2、3、4、5)以及每年的D值(100、100、150、100、100)。

Year(整数)、DValue(Numeric(18,2))、Person(整数)

我试图将其转换为人员值、起始年份、结束年份和值的最终输出。

人员 = 2,起始年份 = 1,结束年份 = 2,值 = 100
人员 = 2,起始年份 = 3,结束年份 = 3,值 = 150
人员 = 2,起始年份 = 4,结束年份 = 5,值 = 100

以上代码将填充表中的数据。我已经尝试了一些排序选项,包括下面的代码以及当前的输出。

SELECT * 
  ,DDENSERANK = DENSE_RANK () OVER (PARTITION BY PERSON , DVALUE  ORDER BY YEAR)
  ,DLAG = CASE WHEN LAG(DVALUE) OVER (PARTITION BY PERSON ORDER BY YEAR) IS NULL THEN 2 ELSE CASE WHEN A.DAMT = LAG(DVALUE) OVER (PARTITION BY PERSON ORDER BY YEAR) THEN 2 ELSE 1 END END
  ,DROWNUM = ROW_NUMBER() OVER (PARTITION BY PERSON ORDER BY YEAR)
INTO #TESTFINAL
FROM #TEST  A
ORDER BY YEAR

SELECT DISTINCT *
  ,DDENSERANKA = DENSE_RANK () OVER (PARTITION BY PERSON ,DLAG ,DVALUE ORDER BY YEAR)
  ,ROW_NUMBER() OVER (PARTITION BY PERSON , DLAG ORDER BY A.SYEAR)

FROM #TESTFINAL A

表格中返回的数据带有不同的分页断点以供重新开始编号。

我的Dense_Rank在年份3中断后继续编号。使用Lag时,我遇到了确定结束年份的问题。如何以不同的方式对这些进行编号或从中进行选择,以便我可以看到每个DVALUE更改的起始年份和结束年份,按照人员的输出如下所示?

人员,DVALUE,起始年份,结束年份
2,100,1,2
2,150,3,3
2,100,4,5

当前的输出显示:

SELECT 
PERSON 
, DVALUE
, MIN(YEAR)
, MAX(YEAR)
FROM #TESTFINAL
GROUP BY PERSON, DVALUE

人员,DVALUE,起始年份,结束年份
2,100,1,5
2,150,3,3
英文:

I have a table, #TEST, that lists a referenced Person Value (2), a Year for that person (1, 2, 3, 4, 5) and a Dvalue for each year (100,100,150,100,100).

Year (int), DValue (Numeric(18,2)), Person (int)

I am attempting to get this to a final output of the person value, start year, end year, and value.

Person = 2, startyear = 1, endyear = 2, value = 100
Person = 2, startyear = 3, endyear = 3, value = 150
Person = 2, startyear = 4, endyear = 5, value = 100



SELECT 1 YEAR, 100 DVALUE, 2 PERSON
INTO #TEST
UNION ALL
SELECT 2 YEAR, 100 DVALUE, 2 PERSON
UNION ALL
SELECT 3 YEAR, 150 DVALUE, 2 PERSON
UNION ALL
SELECT 4 YEAR, 100 DVALUE, 2 PERSON
UNION ALL
SELECT 5 YEAR, 100 DVALUE, 2 PERSON

The above code will populate the data in the table. I have attempted a few ordering options already which are included below with current output.

SELECT * 
  ,DDENSERANK = DENSE_RANK () OVER (PARTITION BY PERSON , DVALUE  ORDER BY YEAR)
  ,DLAG = CASE WHEN LAG(DVALUE) OVER (PARTITION BY PERSON ORDER BY YEAR) IS NULL THEN 2 ELSE CASE WHEN A.DAMT = LAG(DVALUE) OVER (PARTITION BY PERSON ORDER BY YEAR) THEN 2 ELSE 1 END END
  ,DROWNUM = ROW_NUMBER() OVER (PARTITION BY PERSON ORDER BY YEAR)
INTO #TESTFINAL
FROM #TEST  A
ORDER BY YEAR

SELECT DISTINCT *
  ,DDENSERANKA = DENSE_RANK () OVER (PARTITION BY PERSON ,DLAG ,DVALUE ORDER BY YEAR)
  ,ROW_NUMBER() OVER (PARTITION BY PERSON , DLAG ORDER BY A.SYEAR)

FROM #TESTFINAL A

表格中返回的数据带有不同的分页断点以供重新开始编号。

My Dense_Rank continues numbering after a break in Year 3. With Lag I run into issues with determining if the end year. How can I number these differently or select from these so that I could see the start year and stop year of each DVALUE change by PERSON with an output like the following?

PERSON, DVALUE, STARTYEAR, ENDYEAR
2,      100,    1,         2
2,      150,    3,         3
2,      100,    4,         5

Current Output shows:

SELECT 
PERSON 
, DVALUE
, MIN(YEAR)
, MAX(YEAR)
FROM #TESTFINAL
GROUP BY PERSON, DVALUE

PERSON, DVALUE, STARTYEAR, ENDYEAR
2,      100,    1,         5
2,      150,    3,         3

答案1

得分: 0

以下是翻译好的 SQL 代码部分:

drop table #test
SELECT 1 YEAR, 100 DVALUE, 2 PERSON
INTO #TEST
UNION ALL
SELECT 2 YEAR, 100 DVALUE, 2 PERSON
UNION ALL
SELECT 3 YEAR, 150 DVALUE, 2 PERSON
UNION ALL
SELECT 4 YEAR, 100 DVALUE, 2 PERSON
UNION ALL
SELECT 5 YEAR, 100 DVALUE, 2 PERSON

select person, dvalue, MIN(year) AS startyear, max(year) AS endyear
from (
	select count(case when prevvalue <> dvalue then 1 end) over(partition by person order by year) as cnt
	, *
	from (
		select *
		, LAG(DValue) OVER(PARTITION BY Person ORDER BY Year) AS prevValue
		from #TEST
		) x
	) x
group by person, dvalue, cnt
order by person, MIN(year)
英文:

Try something like this:

drop table #test
SELECT 1 YEAR, 100 DVALUE, 2 PERSON
INTO #TEST
UNION ALL
SELECT 2 YEAR, 100 DVALUE, 2 PERSON
UNION ALL
SELECT 3 YEAR, 150 DVALUE, 2 PERSON
UNION ALL
SELECT 4 YEAR, 100 DVALUE, 2 PERSON
UNION ALL
SELECT 5 YEAR, 100 DVALUE, 2 PERSON

select person, dvalue, MIN(year) AS startyear, max(year) AS endyear
from (
	select count(case when prevvalue <> dvalue then 1 end) over(partition by person order by year) as cnt
	, *
	from (
		select *
		, LAG(DValue) OVER(PARTITION BY Person ORDER BY Year) AS prevValue
		from #TEST
		) x
	) x
group by person, dvalue, cnt
order by person, MIN(year)

It's a very standard LAG / COUNT technique which finds "groups" of same values as previous ones. After you have that, it's easy to GROUP BY and get the edges

huangapple
  • 本文由 发表于 2023年2月16日 02:35:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/75464109.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定