英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论