PostgreSQL/Oracle层级值对分组计数查询：关联值重复次数统计

嘿，我来帮你搞定这个连续值对统计的问题！你需要统计每个KEY下按ID顺序排列的连续VALUE对的重复次数，还要排除只有单条记录的KEY（比如Dia-001）对吧？针对Oracle和PostgreSQL分别给你写了解决方案，刚好适配你的测试场景：

核心思路 #

要实现需求，我们分三步走：

1. 过滤无效KEY：先筛选出包含至少2条记录的KEY，因为单条记录无法形成连续值对
2. 生成连续值对：用窗口函数LEAD()，按每个KEY分组、ID升序排序，获取每条记录的下一个VALUE，组成当前值-下一个值的配对
3. 统计重复次数：对所有生成的有效值对（排除每个KEY的最后一条记录，因为它没有下一个值）分组计数

Oracle 实现方案 #

建表与插入数据（你的测试用例） #

CREATE TABLE MY_TABLE ( KEY VARCHAR2(100 BYTE), ID NUMBER, VALUE VARCHAR2(2000 BYTE) );

INSERT INTO my_table VALUES ('Dia-001', 1, 'welcome');
INSERT INTO my_table VALUES ('Dia-002', 1, 'welcome');
INSERT INTO my_table VALUES ('Dia-002', 2, 'condizioni_app1');
INSERT INTO my_table VALUES ('Dia-002', 3, 'condizioni');
INSERT INTO my_table VALUES ('Dia-003', 1, 'welcome');
INSERT INTO my_table VALUES ('Dia-003', 2, 'condizioni_app1');
INSERT INTO my_table VALUES ('Dia-003', 3, 'condizioni_app2');

统计查询语句 #

WITH key_with_multiple_rows AS (
    -- 筛选出有至少2条记录的KEY
    SELECT KEY
    FROM MY_TABLE
    GROUP BY KEY
    HAVING COUNT(*) > 1
),
value_pairs AS (
    -- 生成每个KEY下的连续值对
    SELECT 
        t.VALUE,
        LEAD(t.VALUE) OVER (PARTITION BY t.KEY ORDER BY t.ID) AS NEXT_VALUE
    FROM MY_TABLE t
    JOIN key_with_multiple_rows k ON t.KEY = k.KEY
)
-- 统计值对的重复次数
SELECT 
    VALUE AS "VALUE",
    NEXT_VALUE AS "NEXT",
    COUNT(*) AS "COUNT"
FROM value_pairs
WHERE NEXT_VALUE IS NOT NULL  -- 排除每个KEY的最后一条无后续值的记录
GROUP BY VALUE, NEXT_VALUE
ORDER BY VALUE, NEXT_VALUE;

执行结果 #

运行后会得到你期望的输出：

VALUE           NEXT               COUNT
welcome         condizioni_app1    2
condizioni_app1 condizioni         1
condizioni_app1 condizioni_app2    1

PostgreSQL 实现方案 #

PostgreSQL的逻辑和Oracle基本一致，仅需调整数据类型（PostgreSQL用VARCHAR而非VARCHAR2，INTEGER替代NUMBER）：

建表与插入数据 #

CREATE TABLE MY_TABLE ( 
    KEY VARCHAR(100), 
    ID INTEGER, 
    VALUE VARCHAR(2000) 
);

INSERT INTO my_table VALUES ('Dia-001', 1, 'welcome');
INSERT INTO my_table VALUES ('Dia-002', 1, 'welcome');
INSERT INTO my_table VALUES ('Dia-002', 2, 'condizioni_app1');
INSERT INTO my_table VALUES ('Dia-002', 3, 'condizioni');
INSERT INTO my_table VALUES ('Dia-003', 1, 'welcome');
INSERT INTO my_table VALUES ('Dia-003', 2, 'condizioni_app1');
INSERT INTO my_table VALUES ('Dia-003', 3, 'condizioni_app2');

统计查询语句 #

WITH key_with_multiple_rows AS (
    SELECT KEY
    FROM MY_TABLE
    GROUP BY KEY
    HAVING COUNT(*) > 1
),
value_pairs AS (
    SELECT 
        t.VALUE,
        LEAD(t.VALUE) OVER (PARTITION BY t.KEY ORDER BY t.ID) AS next_value
    FROM MY_TABLE t
    JOIN key_with_multiple_rows k ON t.KEY = k.KEY
)
SELECT 
    VALUE AS "VALUE",
    next_value AS "NEXT",
    COUNT(*) AS "COUNT"
FROM value_pairs
WHERE next_value IS NOT NULL
GROUP BY VALUE, next_value
ORDER BY VALUE, next_value;

执行后同样会得到你想要的统计结果。

内容的提问来源于stack exchange，提问作者Giuseppe PopEax