主页 / dba / 问题 / 212713
Accepted
Jon of All Trades
Asked: 2018-07-20 13:25:08 +0800 CST

我可以只计算连续记录的 ROW_NUMBER() 吗？

  • 772

我需要计算连续值的序列号。这听起来像是一份工作ROW_NUMBER()！

DECLARE @Data TABLE
    (
    Sequence    TINYINT NOT NULL PRIMARY KEY,
    Subset  CHAR(1) NOT NULL
    )
INSERT INTO @Data (Sequence, Subset) VALUES
    (1, 'A'),
    (2, 'A'),
    (3, 'A'),
    (4, 'B'),  -- New subset
    (5, 'B'),
    (6, 'A')   -- New subset

SELECT
    Sequence, Subset,
    ROW_NUMBER() OVER (PARTITION BY Subset ORDER BY Sequence) AS SeqWithinGroup
FROM
    @Data

我希望该PARTITION子句在每次更改时重置计数Subset，但 SQL Server 会收集给定Subset值的所有值并对它们进行编号。这是我所期望的，也是我得到的：

Sequence Subset Expected Actual
-------- ------ -------- -----
1        A      1        1
2        A      2        2
3        A      3        3
4        B      1        1
5        B      2        2
6        A      *1*      *4*

当 SQL 到达第 6 行时，它继续对子集“A”进行编号，而我将其视为恰好也被命名为“A”的新子集的第一行。

有没有办法ROW_NUMBER()严格地进行分区，而不是默认行为？

这里和其他地方有很多关于用 SQL 计算连续值的问题。但是，我还没有看到解决PARTITION BY字段中重复值的问题。大多数只处理增加的值，通常是日期。

sql-server sql-server-2017

3 个回答

  1. Best Answer

    我能够使用以下方法解决这个问题LAG()：

    SELECT
    Sequence, Subset,
    CASE WHEN Sequence = 1 OR Subset <> LAG(Subset, 1) OVER (ORDER BY Sequence)
        THEN 'New subset'
        ELSE 'Continuation'
        END
FROM
    @Data

    这将为记录 #1、#4 和 #6 返回“新子集”。显然LAG()分区与ROW_NUMBER().

    显然，这不提供行号，但它帮助我达到了识别连续数字序列的目标，当子集标识符可以重复时。

    • 3

  2. 我们在这里所做的是，

    1. 计算重置（rst代码中的列）
    2. sum()获取组（grp代码中的列）
    3. row_number()从分组中获取。

    代码，

    SELECT row_number() OVER (PARTITION BY grp ORDER BY sequence) AS number,
  sequence,
  subset
FROM (
  SELECT count(rst) OVER (ORDER BY sequence) AS grp, *
  FROM (
    SELECT CASE WHEN subset != lag(subset) OVER (ORDER BY sequence) THEN 1 END AS rst, *
    FROM foo
  ) AS t1
) AS t2;

    您可以在此处查看带有结果的 DBFiddle

    • 3

  3. 我使用了这篇文章中的答案并针对您的问题对其进行了修改。

    --Demo setup

;DECLARE @Data TABLE
    (
    Sequence    TINYINT NOT NULL PRIMARY KEY,
    Subset  CHAR(1) NOT NULL
    )
;INSERT INTO @Data (Sequence, Subset) VALUES
    (1, 'A'),
    (2, 'A'),
    (3, 'A'),
    (4, 'B'),  -- New subset
    (5, 'B'),
    (6, 'A')   -- New subset

--The solution
--Create a grouping for consecutive values called grp
;WITH _cte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY Subset ORDER BY [Sequence]
                ), [Sequence]) AS grp
    FROM @Data
    )
   ,AddedRn    --add a row number for each entry in the group
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY grp ORDER BY sequence
            ) AS rn
    FROM _cte
    )
--Select result ordering by Sequence
SELECT Sequence, Subset, grp, rn as SeqWithinGroup    
FROM AddedRn
order by Sequence

    | Sequence | Subset | grp                     | SeqWithinGroup |
|----------|--------|-------------------------|----------------|
| 1        | A      | 1900-01-01 00:00:00.000 | 1              |
| 2        | A      | 1900-01-01 00:00:00.000 | 2              |
| 3        | A      | 1900-01-01 00:00:00.000 | 3              |
| 4        | B      | 1900-01-04 00:00:00.000 | 1              |
| 5        | B      | 1900-01-04 00:00:00.000 | 2              |
| 6        | A      | 1900-01-03 00:00:00.000 | 1              |
    • 1

相关问题