时间戳

首页 > 解决方案 > PostgreSQL / Oracle => 查询对分层值进行分组和计数

sql - PostgreSQL / Oracle => 查询对分层值进行分组和计数

问题描述

我在 PostgreSQL 上有这张表，但我在 Oracle DB 上对其进行了测试。我需要提取计数数据以检查起始值和结束值重复了多少次

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');

我想得到这个结果

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

注意：如果 KEY 字段中的值为单个，则不应计算（例如，对于 KEY 'Dia-001'）。
必须计算所有成对的值。例如，对于键“Dia-002”，我们有成对的welcome/condizioni_app1、condizioni_app1/condizioni（id 1 和2、2 和3）。

标签： sqloraclepostgresql

解决方案

一种选择是 Postgres 的lead窗口函数：

select  value
,       next_value
,       count(*)
from    (
        select  value
        ,       lead(value) over (partition by key order by id) as next_value
        from    MY_TABLE
        ) sub
where   next_value is not null
group by
        value
,       next_value

或者您可以使用自连接来查找同一键的下一个值：

select  t1.value
,       t2.value
,       count(*)
from    MY_TABLE t1
join    MY_TABLE t2
on      t1.KEY = t2.KEY
and     t1.ID = t2.ID - 1
group by
        t1.value
,       t2.value

SQL Fiddle 上的两个示例。

推荐阅读