获取几年中每周的最新条目

Question

我有下表来存储实体的历史记录：

    Date                   Id        State
    -------------------------------------
    2017-10-10              1        0
    2017-10-12              1        4
    2018-5-30               1        8
    2019-4-1                2        0
    2018-3-6                2        4
    2018-3-7                2        0

我想要获取每个Id在一周内的最后一个条目，例如

    Date                   Id        State
    -------------------------------------
    2017-10-12              1        4
    2018-5-30               1        8
    2019-4-1                2        0
    2018-3-7                2        0

我会尝试使用Partition by：

select
        ID
       ,Date
        ,State
        ,DatePart(week,Date) as weekNumber
        from TableA
where   Date = (
            select max(Date) over (Partition by Id Order by DatePart(week, Date) Desc)
        )
order by ID

但它每周给我的结果仍然不止一个。

Answer 1

您可以使用ROW_NUMBER()

SELECT a.*
FROM (SELECT a.*, ROW_NUMBER() OVER (PARTITION BY a.id, DATEPART(WK, a.Date) ORDER BY a.Date DESC) AS Seq
      FROM tablea a
     ) a
WHERE seq = 1
ORDER BY id, Date;