logo标签列表

如何在PostgreSQL中移动整个分组？

sqlpostgresqldistinctwindow-functionslag
3
我有一个按如下结构设置的表格（对于数据的举例说明可能有点冗长，但是无法通过更少的行数复制问题）：
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。

非常出色的提问！唯一缺失的部分是你的Postgres版本，这总是相关的。 - Erwin Brandstetter
3个回答
3

PostgreSQL 11的一个主要新功能在发布说明中是这样宣传的：

  • 窗口函数现在支持SQL:2011标准中显示的所有框架选项，包括RANGE 距离 PRECEDING/FOLLOWING、GROUPS模式和框架排除选项。

利用此功能，可以在单个SELECT语句中只用一个窗口函数就能解决问题。

SELECT *, first_value(id) OVER (PARTITION BY tid
                                ORDER BY CASE pid
                                            WHEN 157 THEN 1
                                            WHEN 598 THEN 2
                                            WHEN 118 THEN 3
                                            WHEN 783 THEN 4
                                         END, sid
                                GROUPS BETWEEN 1 PRECEDING AND 1 PRECEDING
                                ) AS prev_id
FROM   example_tbl;

db<>fiddle 这里

在Postgres 11及更高版本中，应该表现良好，也适用于您的扩展测试设置，但不适用于早于版本11的Postgres。

手册:

在GROUPS模式下，偏移量必须产生一个非空的非负整数，并且该选项意味着帧从当前行的对等组之前或之后的指定数量的对等组开始或结束，其中对等组是按ORDER BY排序等效的一组行。

所选择的框架定义GROUPS BETWEEN 1 PRECEDING AND 1 PRECEDING恰好符合您的要求。

您的描述和测试数据似乎暗示sid和id以相同的步长变化。因此，窗口框架仅包含单个id值。min()或max()也可以使用，但我选择了最便宜的first_value()。而且，它适用于任何数据类型，甚至在min()或max()失败的情况下也可以使用（例如json）。

此外，请使用更短（稍微便宜一些）的“简单”或“交换”的CASE。请参见：

对于Postgres 9.5

这适用于您过时的Postgres版本9.5：

SELECT e.*, p.prev_id
FROM   example_tbl e
JOIN  (
   SELECT *
        , lag(id) OVER (PARTITION BY tid
                        ORDER BY CASE pid
                                    WHEN 157 THEN 1
                                    WHEN 598 THEN 2
                                    WHEN 118 THEN 3
                                    WHEN 783 THEN 4
                                 END, sid) AS prev_id
   FROM  (
      SELECT DISTINCT ON (tid, pid, sid)
             tid, pid, sid, id
      FROM   example_tbl
      ) dist
   ) p USING (tid, pid, sid);

db<>fiddle 这里

假设tid、pid、sid被定义为NOT NULL，否则需要进行更多操作。

  1. 导出一个包含不同组合的(tid, pid, sid)的表。其中，id只是有效负载列。关于DISTINCT ON：

  2. 现在使用带有默认窗口框架的lag()来获取“前一”行的id。

  3. 连接到完整的表格。

哇，这太棒了，非常有用！不过我有一个问题：该命令在我的本地游乐场数据库上运行良好，但在其他外部数据库上返回“ERROR: syntax error at or near "GROUPS"”，这是否意味着该外部数据库与适当版本的PostgreSQL（>=11）不兼容，因此不识别GROUPS语法？我该如何检查我连接到的外部数据库正在运行哪个版本的PostgreSQL？我想知道这是否是问题的根源还是我确实有一些语法错误。 - jakes
你可以运行 SELECT version(); 并获取它。 - Tarun Lalwani
谢谢@TarunLalwani。不幸的是，我的Postgres版本是9.5.10。我会相应地更新主题。 - jakes
@jakes：我为Postgres 9.5添加了一个解决方案。请记得在所有问题中披露主要软件版本。关于Postgres 9.5.10有两点需要注意的地方 - 如果您对此有任何影响力：1.：始终升级到最新的点发布版本，目前是9.5.24。2.：但考虑升级到当前版本，因为Postgres 9.5将于2021年2月到达EOL。 - Erwin Brandstetter
非常感谢！也许要求太多了（我已经接受了你的答案），但如果你有时间，能否看一下并可能解释一下为什么我的初始查询出错了？我无法弄清楚。 - jakes
@jakes：在你的CTE example_tbl_with_offset 中，你计算每个组中的ID数量，以便稍后在 LAG(id, lag_offset) 中用作偏移量。但这只适用于同行中最新的一行。您必须相应地为每个较早的行减少该偏移量。（更复杂，总体上可能更昂贵。）此外，没有断言列 id 是唯一的。计数可能会汇总多个对等组。如果是这样，您必须按 (tid、pid、sid) 计数... - Erwin Brandstetter
1
相似的解决方案：
with 
  a as (
 select distinct *, 
 case
    WHEN pid = 157 THEN 1
    WHEN pid = 598 THEN 2
    WHEN pid = 118 THEN 3
    WHEN pid = 783 THEN 4
  end as cat
 from example_tbl
),
 b as (
select id, cat,
  lag(id, 1) over(partition by tid order by cat, sid) prev_id
from a
)
select c.*, b.prev_id
from example_tbl c
left join b using (id)
order by b.cat,c.sid
谢谢你的回复。不幸的是，我不能使用distinct + lag(id, 1)，因为我的原始表有更多的列，而且select distinct不能返回每个pid x sid x tid组合的唯一行。 - jakes
当然，我认为Erwin的解决方案也是完美的。 - timothyzhang
“left join b using (id)” 可能会失败，因为在问题中未定义“id”为唯一标识符。（列名似乎具有误导性。） - Erwin Brandstetter
1

这个可以工作，希望有人能找到更好的解决方案。

select m.* from (
select *, lag(id,1) 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
    )
) from ( select distinct * from example_tbl et )a )
m,
example_tbl n
where m.id = n.id and m.pid = n.pid and m.sid = n.sid and m.tid = n.tid
order by (CASE 
                                WHEN m.pid = 157 THEN 1
                                WHEN m.pid = 598 THEN 2
                                WHEN m.pid = 118 THEN 3
                                WHEN m.pid = 783 THEN 4
                              END, m.sid
    )
谢谢你的回复。不幸的是，我不能使用distinct + lag(id, 1)，因为我的原始表有更多的列，而且select distinct不能返回每个pid x sid x tid组合的唯一行。 - jakes
没问题，可以在这4个列上使用group by，而不是使用select distinct * from example_tbl，改为使用select id, pid, sid, tid from example_tbl group by id, pid, sid, tid 。 - detzu
值得注意的是，这对于原始问题是有效的。 - Erwin Brandstetter
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接