sql - 分区后从每个组中获取特定值

Question

我有下表 -

ID | Type | Val0 | Val1 | Group | Creation_date
1  |  A   |  V1  | Val1 | G1    | 24sep, 2011
2  |  A   |  V1  | Val2 | G1    | 27oct, 2008
3  |  A   |  V2  | Val3 | G2    | 19oct, 2003
4  |  B   |  X1  | Xal1 | G3    | 15 dec, 2001
5  |  B   |  X2  | Xal2 | G4    | 15 dec, 2000
6  |  B   |  X2  | Xal3 | G4    | 15 dec, 1990
7  |  C   |  X3  | Xal4 | G5    | 23Dec, 2001

我只想保留特定类型的最新值，并提取出旧的但不属于与最新项目相关联的组的值。因此，对于上表，我希望提取这些项目 - Val3、Xal2、Xal3，我可以稍后删除它们。

由于 A 的最新值是属于 G1 组的 Val1，B 的最新值是 Xal1，C 的最新值是 Xal4。

有人可以建议我如何使用 SQL 做到这一点吗？

Answer 1

您可以使用 row_number() .不需要聚合:

select t.*
from (select t.*,
             row_number() over (partition by t.type order by creation_date desc) as seqnum
      from t
     ) t
where seqnum = 1;

但是，您需要不在该组中的值。也就是说，您想要来自 val1 的值哪里 seqnum不是 1。这将是:

select t.val1
from (select t.*, min(seqnum) over (partition by t.type, t.group) as min_seqnum -- DON'T USE group FOR COLUMN NAMES!
      from (select t.*,
                   row_number() over (partition by t.type order by creation_date desc) as seqnum
            from t
           ) t
     ) t
where min_seqnum <> 1;