如何在查找行更改时避免重复

Question

当一个列在单个表中的用户ID之间发生变化时，我正在提取数据。我可以使用Select+Union查询提取更改以及前一行(ID(。对于前一行，由于参数的原因，我得到了不止一个。正在查找有关如何仅检索前一行(ID(的建议。下面的查询试图检索单行

| ID | Year | Event | ActivityDate | UserID
| 1  | 2020 | A     | 2020-12-01   | xxx
| 1  | 2021 | A     | 2021-03-01   | xyz
| 2  | 2020 | B     | 2021-01-01   | xxx
| 1  | 2022 | C     | 2021-10-01   | yyy
| 3  | 2021 | D     | 2021-12-01   | xxx
Select d.ID, d.Year, d.Event, d.ActivityDate, d.UserID
from tableA d
where
d.year in ('2020','2021','2022')
and d.event <>
(select f.event
from tableA f
where
f.year in ('2020','2021','2022')
and d.id = f.id
and d.activityDate < f.activityDate
order by f.activityDate desc
fetch first 1 row only
)
;

我希望检索以下

1, 2021, A, 2021-03-01, xyz

但我有

1, 2020, a, 2020-12-01, xxx
1, 2021, a, 2021-03-01, xyz

Answer 1

我认为分析函数将帮助您找到答案。

row_number()将为您获取一系列重复项中的最后一行。

count(id)将允许您将自己限制为具有多行的组合。

WITH
aset
AS
(SELECT d.id
, d.year
, d.event
, d.activitydate
, d.userid
, ROW_NUMBER ()
OVER (PARTITION BY id, event ORDER BY year DESC)    AS rn
, COUNT (id) OVER (PARTITION BY id, event)                AS n
FROM tablea d)
SELECT *
FROM aset
WHERE rn = 1 AND n > 1;