sql server—返回结果集有序分区中最后一个值之前的值

7gcisfzg  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(267)

我想把一个数据放到一个新的列中(上一年末的人数),在对该年进行分区之后,从该分区中获取最新的人数值,并在下一年为新列设置它。
创建表并插入数据脚本:

  1. CREATE TABLE #test1(
  2. [DateTime] [date] NULL,
  3. [HeadCount] [int] NULL
  4. )
  5. GO
  6. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2020-06-02' AS Date), 2)
  7. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2020-05-02' AS Date), 2)
  8. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2020-02-20' AS Date), 3)
  9. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2020-01-20' AS Date), 3)
  10. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2019-06-05' AS Date), 7)
  11. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2019-05-01' AS Date), 7)
  12. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2019-04-20' AS Date), 5)
  13. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2019-01-02' AS Date), 5)
  14. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-12-15' AS Date), 8)
  15. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-10-02' AS Date), 9)
  16. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-09-12' AS Date), 6)
  17. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-08-06' AS Date), 3)
  18. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-07-10' AS Date), 2)
  19. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-06-05' AS Date), 3)
  20. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-03-20' AS Date), 3)
  21. INSERT #test1 ([DateTime], [HeadCount]) VALUES (CAST(N'2018-02-10' AS Date), 7)

我尝试过的解决方案:

  1. SELECT
  2. datetime,headcount,
  3. LAST_VALUE(headcount) OVER(
  4. PARTITION BY year(datetime)
  5. ORDER BY datetime desc
  6. RANGE BETWEEN
  7. UNBOUNDED PRECEDING AND
  8. UNBOUNDED FOLLOWING
  9. ) PrevYearEndHeadCnt
  10. FROM
  11. #test1 order by datetime desc

我得到的结果与预期相比:

  1. datetime headcount PrevYearEndHeadCnt Expected
  2. 02-06-2020 2 3 7
  3. 02-05-2020 2 3 7
  4. 20-02-2020 3 3 7
  5. 20-01-2020 3 3 7
  6. 05-06-2019 7 5 8
  7. 01-05-2019 7 5 8
  8. 20-04-2019 5 5 8
  9. 02-01-2019 5 5 8
  10. 15-12-2018 8 7 0
  11. 02-10-2018 9 7 0
  12. 12-09-2018 6 7 0
  13. 06-08-2018 3 7 0
  14. 10-07-2018 2 7 0
  15. 05-06-2018 3 7 0
  16. 20-03-2018 3 7 0
  17. 10-02-2018 7 7 0
6xfqseft

6xfqseft1#

这将帮助您获得预期的结果集。我只是使用self-join来比较今年和去年,并使用rank函数得到去年的最新员工人数。

  1. SELECT curr.datetime,curr.headcount,
  2. ISNULL(prev.HeadCount,0) as PrevYearEndHeadCnt
  3. FROM #test1 curr
  4. LEFT JOIN (Select datetime,HeadCount,
  5. RANK() OVER (PARTITION BY year(datetime) ORDER BY datetime desc) as rn_headcount
  6. FROM #test1) prev ON YEAR(prev.DateTime) = YEAR(curr.DateTime) - 1
  7. AND rn_headcount = 1
  8. ORDER BY curr.datetime DESC
thtygnil

thtygnil2#

  1. with x as (
  2. select *,
  3. cnt = count(*) over (partition by year([DateTime])),
  4. rownum = row_number() over (partition by year([DateTime])
  5. order by [DateTime] desc)
  6. from #test1
  7. )
  8. select [DateTime],
  9. HeadCount,
  10. PrevYearEndHeadCnt = isnull(lead(HeadCount, cnt - rownum + 1)
  11. over(order by [DateTime] desc), 0)
  12. from x order by [DateTime] desc;

相关问题