从下表中，我try 创建另一列(last_k2X)，它将显示last key2 where 101 was set to 102 over time(ti).
如果同时为多个key2设置了X(ts)(在相同的key1分区中)，则新列last_k2X将具有该分区/时间中所有key2的key2.
Input:个
| key1 | key2 | ts | type |
|---|---|---|---|
| 1 | A | t0 | |
| 1 | B | t1 | a |
| 1 | C | t1 | X |
| 1 | D | t2 | b |
| 1 | E | t3 | |
| 1 | F | t4 | c |
| 1 | G | t5 | X |
| 1 | H | t5 | |
| 1 | I | t6 | d |
我try 使用First_Value()和Lag()等窗口函数，但无法获得正确的结果. 我预料到的结果是:
Expected output:个
| key1 | key2 | ts | type | last_k2X |
|---|---|---|---|---|
| 1 | A | t0 | ||
| 1 | B | t1 | a | C |
| 1 | C | t1 | X | C |
| 1 | D | t2 | b | C |
| 1 | E | t3 | C | |
| 1 | F | t4 | c | C |
| 1 | G | t5 | X | G |
| 1 | H | t5 | G | |
| 1 | I | t6 | d | G |
因为您没有指定您正在使用的数据库系统，所以这个解决方案是在MySQL中实现的，但可以简单地转换为其他数据库系统.
这基本上是一个缺口和孤岛问题，但需要获得正确的分区
CREATE TABLE data
(key1 int, key2 varchar(1), ts varchar(2), type varchar(4))
;
INSERT INTO data
(key1, key2, ts, type)
VALUES
(1, 'A', 't0', NULL),
(1, 'B', 't1', 'a'),
(1, 'C', 't1', 'X'),
(1, 'D', 't2', 'b'),
(1, 'E', 't3', NULL),
(1, 'F', 't4', 'c'),
(1, 'G', 't5', 'X'),
(1, 'H', 't5', NULL),
(1, 'I', 't6', 'd')
;
WITH x_sel AS (SELECT key1, key2, ts,type, CASE WHEN type = 'X' then 1 ELSE 0 END rk
FROM data
), CTE2 as
(SELECT
x_sel.key1, x_sel.key2, x_sel.ts,x_sel.type,x_sel2.key2 k2X, SUM(rk) OVER (PARTITION BY x_sel.key1 ORDER BY x_sel.key2) s_rk FROM x_sel
LEFT JOIN (SELECT key1, key2, ts FROM data WHERE type = 'X') x_sel2
ON x_sel.key1 = x_sel2.key1 ANd x_sel.ts = x_sel2.ts)
SELECT key1, key2, ts,type,CASE WHEN s_rk = 0 THEn k2x ELSE COALESCE(k2x,MAX(k2X) OVER(PARTITION BY s_rk ORDER BY S_rk)) END k2x
FROM CTE2
| key1 | key2 | ts | type | k2x |
|---|---|---|---|---|
| 1 | A | t0 | null | null |
| 1 | B | t1 | a | C |
| 1 | C | t1 | X | C |
| 1 | D | t2 | b | C |
| 1 | E | t3 | null | C |
| 1 | F | t4 | c | C |
| 1 | G | t5 | X | G |
| 1 | H | t5 | null | G |
| 1 | I | t6 | d | G |