时间戳

首页 > 解决方案 > SQL 序列中组中的行数

sql-server - SQL 序列中组中的行数

问题描述

我为这个问题的标题道歉，它不是很清楚，但我想不出更好的语言来描述它，数据应该自己说话。

我有一个数据表，我需要根据一个值知道相同的行数，但还要考虑它们当前所处的顺序。该表比这大得多，但这些列是相关的。

Id    | MinCode      | MaxCode      | ExpectedResult
----------------------------------------------------
1     | 00001.000001 | 00001.000001 | 2
2     | 00001.000001 | 00001.000002 | 2
3     | 00002.00001a | 00002.00001a | 3
4     | 00002.00001a | 00002.00001b | 3
5     | 00002.00001a | 00002.00001c | 3
6     | 00002.000002 | 00002.000002 | 1
7     | 00002.00003a | 00002.00003a | 2
8     | 00002.00003a | 00002.00003b | 2
9     | 00002.000002 | 00002.000004 | 1
10    | 00003.000001 | 00003.000001 | 1

注意：id也是这个例子中的顺序，我只是没有看到有相同数据的额外列的点。

我已经使用COUNT,和尝试了几个版本ROW_NUMBER/RANK，但没有获得 ExpectedResult 值。问题出在 ID 6 和 9 上，因为我的 ExpectedResult 值总是将它们结合起来产生一个 2，我部分理解它，因为这些函数没有考虑数据的顺序。我相信我已经接近了，但是这些天我的 T-SQL 已经很生锈了！PARTITIONGROUP BY

我知道我可以通过 a 处理这个数据集来获得这个值CURSOR，但我想避免这种情况。

标签： sql-servertsql

解决方案

这里的关键是创建一个序列 ID，您可以在窗口函数中使用来获取计数。您将无法在一个查询中执行此操作，因为无法组合窗口函数，但您可以使用子查询或 CTE 将其完成。

要确定一行的序列号，您需要计算前面行中组键更改的次数。因此，要确定更改，请创建一个内部查询，使用滞后窗口函数检查当前组键是否与前一个不同。根据滞后值是否与当前值不同，使用导致 1 或 0 的case语句。然后，外部查询只需总结当前之前所有行的值。

获得序列号后，您可以使用计数窗口函数来计算具有匹配数字的所有行。

WITH src AS ( -- cte to mimic table.
    SELECT * 
    FROM (VALUES 
        (1, N'00001.000001', N'00001.000001', 2),
        /* ... test data ... */
        (10, N'00003.000001', N'00003.000001', 1)
    ) [src] ( [Id],[MinCode],[MaxCode],[ExpectedResult] )
)
SELECT src.Id, MinCode, MaxCode, ExpectedResult
    , COUNT(1) OVER (PARTITION BY seq.SequenceId) [Result]
FROM src 
INNER JOIN (
    SELECT x.Id, SUM(x.IsNew) OVER (ORDER BY Id ROWS UNBOUNDED PRECEDING) [SequenceId]
    FROM (
        SELECT Id, CASE WHEN LAG(MinCode) OVER (ORDER BY Id) <> MinCode THEN 1 ELSE 0 END [IsNew]
        FROM src 
    ) x
) seq ON seq.Id = src.Id
ORDER BY Id

推荐阅读