我有一个具有以下 struct 的PostgreSQL表:
CREATE TABLE cte1 (
entity_id INT,
assignedtogroup INT,
time BIGINT
);
INSERT INTO cte1 (entity_id, assignedtogroup, time)
VALUES
(1, 435198, 1687863949740),
(1, 435198, 1687863949741),
(1, NULL, 1687863949742),
(1, NULL, 1687863949743),
(1, 435224, 1687863949744),
(1, 435224, 1687863949745),
(1, 435143, 1687863949746),
(1, 435143, 1687863949747),
(1, 435191, 1687863949748),
(1, NULL, 1687863949749),
(2, 435143, 1690452125291),
(2, 435143, 1690452125292),
(2, 435191, 1690452125293),
(2, NULL, 1690452125294);
我希望使用前一行的非空值(当前行之前的时间和相同的实体ID)来填充assignedtogroup列中的空值.预期结果应该是:
entity_id | assignedtogroup | time |
---|---|---|
1 | 435198 | 1687863949740 |
1 | 435198 | 1687863949741 |
1 | 435198 | 1687863949742 |
1 | 435198 | 1687863949743 |
1 | 435224 | 1687863949744 |
1 | 435224 | 1687863949745 |
1 | 435143 | 1687863949746 |
1 | 435143 | 1687863949747 |
1 | 435191 | 1687863949748 |
1 | 435191 | 1687863949749 |
2 | 435143 | 1690452125291 |
2 | 435143 | 1690452125292 |
2 | 435191 | 1690452125293 |
2 | 435191 | 1690452125294 |
有没有办法只使用一条SELECT语句来实现这一点呢?
我try 使用LAG函数:
SELECT
entity_id,
COALESCE(
assignedtogroup,
LAG(assignedtogroup) OVER (PARTITION BY entity_id ORDER BY time)
) AS filled_assignedtogroup
FROM cte1;
但是,我仍然有一个空值,并且对于实体id 2,值完全是混合的.
你可以找到DB小提琴:https://www.db-fiddle.com/f/m52Rgq8jtK85g9yvaDMJqz/3