时间戳

首页 > 解决方案 > 将行分组到范围内，同时也显示间隙

sql - 将行分组到范围内，同时也显示间隙

问题描述

我需要一个数据库选择查询解决方案，用于将一系列细分细节转换为它的汇总版本，可能在一个视图中。考虑下表。它有一个复合主键 ( PK_1, PK_2, PK_3, 和SEQUENCE_NO)。

PK_1     PK_2 PK_3 SEQUENCE_NO STATUS_CODE
======== ==== ==== =========== ===========
20200421 A    1    1           Y
20200421 A    1    2           Y
20200421 A    1    3           Y
20200421 A    1    4           N
20200421 A    1    5           Y
20200421 A    1    6           Y
20200421 A    2    7           Y
20200421 A    2    8           Y
20200421 B    3    9           Y
20200421 B    3    10          Y
20200421 B    3    11          Y
20200422 B    3    11          Y

仅包括带有STATUS_CODE“Y”的所有记录，如何以连续记录的方式呈现记录，分别对应于它们的复合主键，形成值范围（由SEQUENCE_FROMand表示SEQUENCE_TO，见下文），同时显示可能表明的间隙是缺失的行，还是带有STATUS_CODE非“Y”值的行？

PK_1     PK_2 PK_3 SEQUENCE_FROM SEQUENCE_TO
======== ==== ==== ============= ===========
20200421 A    1    1             3
20200421 A    1    5             6
20200421 A    2    7             8
20200421 B    3    9             11
20200422 B    3    11            11

我使用过MIN，MAX但显然它不适合显示范围之间的差距。

标签： sqloracleselect

解决方案

这是一个空白和孤岛问题。这是ROW_NUMBER使用行数差异法解决的一种方法：

WITH cte AS (
    SELECT t.*, SEQUENCE_NO -
        ROW_NUMBER() OVER (PARTITION BY PK_1, PK_2, PK_3 ORDER BY SEQUENCE_NO) AS diff
    FROM yourTable t
    WHERE STATUS_CODE = 'Y'
)


SELECT
    PK_1,
    PK_2,
    PK_3,
    MIN(SEQUENCE_NO) AS SEQUENCE_FROM,
    MAX(SEQUENCE_NO) AS SEQUENCE_TO
FROM cte
GROUP BY
    PK_1,
    PK_2,
    PK_3,
    (rn1 - rn2)
ORDER BY
    PK_1,
    PK_2,
    PK_3;

下面演示的屏幕截图

演示

这里使用的逻辑的关键是，我们通过获取序列号和 a 之间的差异，在每个PK_1, PK_2,分区内为每个岛形成一个动态组号。这种差异保证对于每个岛屿始终是唯一的。PK_3ROW_NUMBER

推荐阅读