按顺序从数据库中检索每个组中的最后一条记录

Question

有一个表ticket包含如下所示的数据：

Id   Impact   group        create_date
------------------------------------------
1     3        ABC       2020-07-28 00:42:00.0
1     2        ABC       2020-07-28 00:45:00.0
1     3        ABC       2020-07-28 00:48:00.0
1     3        ABC       2020-07-28 00:52:00.0
1     3        XYZ       2020-07-28 00:55:00.0
1     3        XYZ       2020-07-28 00:59:00.0

预期成果：

Id   Impact   group        create_date
------------------------------------------
1     3        ABC       2020-07-28 00:42:00.0
1     2        ABC       2020-07-28 00:45:00.0
1     3        ABC       2020-07-28 00:52:00.0
1     3        XYZ       2020-07-28 00:59:00.0

目前，这是我使用的查询：

WITH final AS (
SELECT p.*, 
ROW_NUMBER() OVER(PARTITION BY p.id,p.group,p.impact
ORDER BY p.create_date desc, p.impact) AS rk
FROM ticket p 
)
SELECT f.*
FROM final f 
WHERE f.rk = 1

结果，我得到的是：

Id   Impact    group         create_date
-----------------------------------------
1     2        ABC       2020-07-28 00:45:00.0
1     3        ABC       2020-07-28 00:52:00.0
1     3        XYZ       2020-07-28 00:59:00.0

似乎分区依据优先于按值排序。 有没有其他方法可以实现预期的结果。我正在亚马逊Redshift上运行这些查询。

Answer 1

您可以使用LEAD()来检查行之间的Impact是否更改，仅获取值将更改的行。

WITH
look_forward AS
(
SELECT
*,
LEAD(impact) OVER (PARTITION BY id, group ORDER BY create_date) AS lead_impact
FROM
ticket
)
SELECT
*
FROM
look_forward 
WHERE
lead_impact IS NULL
OR lead_impact <> impact

Answer 2

您似乎想要id/impact/group相对于下一行发生变化的行。 一个简单的方法是查看下一个create_date整体和小组的下一个create_date。 如果它们相同，则过滤：

select t.*
from (select t.*,
lead(create_date) over (order by create_date) as next_create_date,
lead(create_date) over (partition by id, impact, group order by create_date) as next_create_date_img
from ticket t
) t
where next_create_date_img is null or next_create_date_img <> next_create_date;