我有一个按如下结构设置的表格(对于数据的举例说明可能有点冗长,但是无法通过更少的行数复制问题):
我尝试使用预先计算的偏移量和
CREATE TABLE example_tbl (
id CHAR(1),
pid INT,
sid INT,
tid VARCHAR(10),
other_columns INT
)
INSERT INTO example_tbl (id, pid, sid, tid, other_columns)
VALUES
('c', 157, 85, 'A32', 1),
('c', 157, 85, 'A32', 2),
('c', 157, 85, 'A32', 3),
('c', 157, 85, 'A32', 4),
('c', 157, 85, 'A32', 5),
('c', 157, 85, 'A32', 6),
('c', 157, 85, 'A32', 7),
('c', 157, 85, 'A32', 8),
('c', 157, 85, 'A32', 9),
('c', 157, 85, 'A32', 10),
('c', 157, 85, 'A32', 11),
('Q', 157, 81, 'A62', 1),
('Q', 157, 81, 'A62', 2),
('Q', 157, 81, 'A62', 3),
('Q', 157, 81, 'A62', 4),
('Q', 157, 81, 'A62', 5),
('Q', 157, 81, 'A62', 6),
('Q', 157, 81, 'A62', 7),
('Q', 157, 81, 'A62', 8),
('Q', 157, 81, 'A62', 9),
('Q', 157, 81, 'A62', 10),
('Q', 157, 81, 'A62', 11),
('f', 598, 51, 'A62', NULL),
('w', 598, 49, 'A32', 9),
('Z', 598, 44, 'A62', NULL),
('r', 598, 16, 'A32', 10),
('O', 118, 93, 'A32', 1),
('G', 118, 38, 'A32', 4),
('U', 118, 90, 'A32', 1),
('U', 118, 90, 'A32', 2),
('U', 118, 90, 'A32', 3),
('U', 118, 90, 'A32', 4),
('U', 118, 90, 'A32', 5),
('U', 118, 90, 'A32', 6),
('U', 118, 90, 'A32', 7),
('U', 118, 90, 'A32', 8),
('U', 118, 90, 'A32', 9),
('U', 118, 90, 'A32', 10),
('U', 118, 90, 'A32', 11),
('m', 118, 37, 'A62', 1),
('J', 118, 54, 'A32', 20),
('a', 118, 59, 'A32', 11),
('s', 118, 18, 'A62', 8),
('y', 118, 33, 'A62', NULL),
('N', 118, 79, 'A62', NULL),
('l', 118, 35, 'A32', 9),
('n', 118, 63, 'A32', 5),
('R', 118, 86, 'A62', 1),
('R', 118, 86, 'A62', 2),
('R', 118, 86, 'A62', 3),
('R', 118, 86, 'A62', 4),
('R', 118, 86, 'A62', 5),
('R', 118, 86, 'A62', 6),
('R', 118, 86, 'A62', 7),
('R', 118, 86, 'A62', 8),
('R', 118, 86, 'A62', 9),
('R', 118, 86, 'A62', 10),
('R', 118, 86, 'A62', 11),
('H', 118, 23, 'A32', 1),
('H', 118, 23, 'A32', 2),
('H', 118, 23, 'A32', 3),
('H', 118, 23, 'A32', 4),
('H', 118, 23, 'A32', 5),
('H', 118, 23, 'A32', 6),
('H', 118, 23, 'A32', 7),
('H', 118, 23, 'A32', 8),
('H', 118, 23, 'A32', 9),
('H', 118, 23, 'A32', 10),
('H', 118, 23, 'A32', 11),
('B', 118, 43, 'A62', 39),
('h', 118, 60, 'A62', NULL),
('p', 118, 72, 'A32', 1),
('v', 118, 22, 'A32', 5),
('I', 118, 89, 'A62', 9),
('T', 118, 17, 'A62', 1),
('F', 118, 41, 'A32', 10),
('z', 118, 55, 'A32', 6),
('Y', 118, 75, 'A32', NULL),
('u', 118, 48, 'A62', 9),
('x', 783, 27, 'A32', 10),
('V', 783, 11, 'A62', 8),
('i', 783, 61, 'A62', 1),
('i', 783, 61, 'A62', 2),
('i', 783, 61, 'A62', 3),
('i', 783, 61, 'A62', 4),
('i', 783, 61, 'A62', 5),
('i', 783, 61, 'A62', 6),
('i', 783, 61, 'A62', 7),
('i', 783, 61, 'A62', 8),
('i', 783, 61, 'A62', 9),
('i', 783, 61, 'A62', 10),
('i', 783, 61, 'A62', 11);
id
列定义了事件。我想在由tid
定义的分组内添加一个带有前一事件的id
列。数据应按照pid
(自定义顺序:157 < 598 < 118 < 783)和sid
排序。棘手的部分是,有些事件有11行,而有些事件只有1行。other_columns
表示所有其他存在且应保留的列。我尝试使用预先计算的偏移量和
LAG()
窗口函数,如下所示:WITH example_tbl_with_offset AS (
SELECT * FROM example_tbl
LEFT JOIN (SELECT id, COUNT(id)::int AS lag_offset
FROM example_tbl
GROUP BY id) AS offset_tbl
USING (id)
)
SELECT
*,
LAG(id, lag_offset) OVER (PARTITION BY tid
ORDER BY (CASE
WHEN pid = 157 THEN 1
WHEN pid = 598 THEN 2
WHEN pid = 118 THEN 3
WHEN pid = 783 THEN 4
END, sid
)) AS prev_id
FROM example_tbl_with_offset;
然而,虽然prev_id
列在最初几个事件中似乎是有效的,但它最终会失去追踪。
预期输出如下所示:
CREATE TABLE expected_output (
id CHAR(1),
pid INT,
sid INT,
tid VARCHAR(10),
prev_id CHAR(1)
)
INSERT INTO expected_output (id, pid, sid, tid, prev_id)
VALUES
('Q', 157, 81, 'A62', NULL),
('Q', 157, 81, 'A62', NULL),
('Q', 157, 81, 'A62', NULL),
('Q', 157, 81, 'A62', NULL),
('Q', 157, 81, 'A62', NULL),
('Q', 157, 81, 'A62', NULL),
('Q', 157, 81, 'A62', NULL),
('Q', 157, 81, 'A62', NULL),
('Q', 157, 81, 'A62', NULL),
('Q', 157, 81, 'A62', NULL),
('Q', 157, 81, 'A62', NULL),
('c', 157, 85, 'A32', NULL),
('c', 157, 85, 'A32', NULL),
('c', 157, 85, 'A32', NULL),
('c', 157, 85, 'A32', NULL),
('c', 157, 85, 'A32', NULL),
('c', 157, 85, 'A32', NULL),
('c', 157, 85, 'A32', NULL),
('c', 157, 85, 'A32', NULL),
('c', 157, 85, 'A32', NULL),
('c', 157, 85, 'A32', NULL),
('c', 157, 85, 'A32', NULL),
('r', 598, 16, 'A32', 'c'),
('Z', 598, 44, 'A62', 'Q'),
('w', 598, 49, 'A32', 'r'),
('f', 598, 51, 'A62', 'Z'),
('T', 118, 17, 'A62', 'f'),
('s', 118, 18, 'A62', 'T'),
('v', 118, 22, 'A32', 'w'),
('H', 118, 23, 'A32', 'v'),
('H', 118, 23, 'A32', 'v'),
('H', 118, 23, 'A32', 'v'),
('H', 118, 23, 'A32', 'v'),
('H', 118, 23, 'A32', 'v'),
('H', 118, 23, 'A32', 'v'),
('H', 118, 23, 'A32', 'v'),
('H', 118, 23, 'A32', 'v'),
('H', 118, 23, 'A32', 'v'),
('H', 118, 23, 'A32', 'v'),
('H', 118, 23, 'A32', 'v'),
('y', 118, 33, 'A62', 's'),
('l', 118, 35, 'A32', 'H'),
('m', 118, 37, 'A62', 'y'),
('G', 118, 38, 'A32', 'I'),
('F', 118, 41, 'A32', 'G'),
('B', 118, 43, 'A62', 'm'),
('u', 118, 48, 'A62', 'B'),
('J', 118, 54, 'A32', 'F'),
('z', 118, 55, 'A32', 'J'),
('a', 118, 59, 'A32', 'z'),
('h', 118, 60, 'A62', 'u'),
('n', 118, 63, 'A32', 'a'),
('p', 118, 72, 'A32', 'n'),
('Y', 118, 75, 'A32', 'p'),
('N', 118, 79, 'A62', 'h'),
('R', 118, 86, 'A62', 'N'),
('R', 118, 86, 'A62', 'N'),
('R', 118, 86, 'A62', 'N'),
('R', 118, 86, 'A62', 'N'),
('R', 118, 86, 'A62', 'N'),
('R', 118, 86, 'A62', 'N'),
('R', 118, 86, 'A62', 'N'),
('R', 118, 86, 'A62', 'N'),
('R', 118, 86, 'A62', 'N'),
('R', 118, 86, 'A62', 'N'),
('R', 118, 86, 'A62', 'N'),
('I', 118, 89, 'A62', 'R'),
('U', 118, 90, 'A32', 'Y'),
('U', 118, 90, 'A32', 'Y'),
('U', 118, 90, 'A32', 'Y'),
('U', 118, 90, 'A32', 'Y'),
('U', 118, 90, 'A32', 'Y'),
('U', 118, 90, 'A32', 'Y'),
('U', 118, 90, 'A32', 'Y'),
('U', 118, 90, 'A32', 'Y'),
('U', 118, 90, 'A32', 'Y'),
('U', 118, 90, 'A32', 'Y'),
('U', 118, 90, 'A32', 'Y'),
('O', 118, 93, 'A32', 'U'),
('V', 783, 11, 'A62', 'I'),
('x', 783, 27, 'A32', 'O'),
('i', 783, 61, 'A62', 'V'),
('i', 783, 61, 'A62', 'V'),
('i', 783, 61, 'A62', 'V'),
('i', 783, 61, 'A62', 'V'),
('i', 783, 61, 'A62', 'V'),
('i', 783, 61, 'A62', 'V'),
('i', 783, 61, 'A62', 'V'),
('i', 783, 61, 'A62', 'V'),
('i', 783, 61, 'A62', 'V'),
('i', 783, 61, 'A62', 'V'),
('i', 783, 61, 'A62', 'V');
以下的顺序可能更容易理解实际逻辑:
SELECT * FROM expected_output
ORDER BY tid, CASE
WHEN pid = 157 THEN 1
WHEN pid = 598 THEN 2
WHEN pid = 118 THEN 3
WHEN pid = 783 THEN 4
END, sid;
编辑:我的PostgreSQL版本是9.5。