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

7gcisfzg 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(205)

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

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

我尝试过的解决方案：

SELECT 
    datetime,headcount,
    LAST_VALUE(headcount) OVER(
                PARTITION BY year(datetime)
            ORDER BY datetime desc
            RANGE BETWEEN 
                UNBOUNDED PRECEDING AND 
                UNBOUNDED FOLLOWING
        ) PrevYearEndHeadCnt
    FROM 
        #test1 order by datetime desc

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

datetime    headcount   PrevYearEndHeadCnt  Expected
    02-06-2020  2           3                   7
    02-05-2020  2           3                   7
    20-02-2020  3           3                   7
    20-01-2020  3           3                   7
    05-06-2019  7           5                   8
    01-05-2019  7           5                   8
    20-04-2019  5           5                   8
    02-01-2019  5           5                   8
    15-12-2018  8           7                   0
    02-10-2018  9           7                   0
    12-09-2018  6           7                   0
    06-08-2018  3           7                   0
    10-07-2018  2           7                   0
    05-06-2018  3           7                   0
    20-03-2018  3           7                   0
    10-02-2018  7           7                   0

sql sql-server tsql

来源：https://stackoverflow.com/questions/62158239/sql-to-returns-the-value-before-last-value-in-an-ordered-partition-of-a-result-s

2条答案

按热度按时间

6xfqseft1#

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

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

赞(0）回复(0）举报 2021-07-26

thtygnil2#

with x as (
    select *,
           cnt = count(*) over (partition by year([DateTime])),
           rownum = row_number() over (partition by year([DateTime])
                                       order by [DateTime] desc)
    from #test1
)
select [DateTime],
       HeadCount,
       PrevYearEndHeadCnt = isnull(lead(HeadCount, cnt - rownum + 1)
                                   over(order by [DateTime] desc), 0)
from x order by [DateTime] desc;

赞(0）回复(0）举报 2021-07-26

我来回答

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

2条答案

相关问题

热门标签

最新问答