如何通过排序、分区和分组进行行编号

我需要通过排序、分区和分组进行行编号。订购依据IdDocument, DateChange, 划分为IdDocument并分组依据IdRole。问题尤其出在分组上。从例子中可以看出（NumberingExpected) DENSE_RANK()必须是用于此目的的最佳函数，但仅当用于排序的值相同时才会重复编号。就我而言，用于排序的值（IdDocument, DateChange）总是不同的，重复编号必须通过IdRole.

当然，使用光标可以很容易地解决这个问题。但是有没有办法用编号/排名功能来实现呢？

测试数据：

declare @LogTest as table (
    Id INT
    ,IdRole INT
    ,DateChange DATETIME
    ,IdDocument INT
    ,NumberingExpected INT
)
insert into @LogTest
select 1 as Id, 7 as IdRole, GETDATE() as DateChange, 13 as IdDocument, 1 as NumberingExpected
union 
select 2, 3, DATEADD(HH, 1, GETDATE()), 13, 2
union 
select 3, 3, DATEADD(HH, 2, GETDATE()), 13, 2
union 
select 4, 3, DATEADD(HH, 3, GETDATE()), 13, 2
union 
select 5, 5, DATEADD(HH, 4, GETDATE()), 13, 3
union 
select 7, 3, DATEADD(HH, 6, GETDATE()), 13, 4
union 
select 6, 3, DATEADD(HH, 5, GETDATE()), 27, 1
union 
select 8, 3, DATEADD(HH, 7, GETDATE()), 27, 1
union 
select 9, 5, DATEADD(HH, 8, GETDATE()), 27, 2
union 
select 10, 3, DATEADD(HH, 9, GETDATE()), 27, 3


select * from @LogTest order by IdDocument, DateChange;

从函数式编程的角度解释：

1. 按 IdDocument、DateChange 排序数据
2. 将第一行号设置为 i=1 转到下一行
3. 如果 IdDocument 已更改 { 我=1; } 别的 { 如果 IdRow 已更改 { i++; } }
4. 设置行号为i；
5. 转到下一行；
6. 如果 EOF { 退出; } else { 转到步骤 3; }

自 2012 年起您可以使用LAG/LEAD，但在 2008 年还没有，所以我们将模仿它。性能可能很差，您应该检查您的实际数据。

这是最终的查询：

WITH
CTE_rn
AS
(
    SELECT
        Main.IdRole
        ,Main.IdDocument
        ,Main.DateChange
        ,ROW_NUMBER() OVER(PARTITION BY Main.IdDocument ORDER BY Main.DateChange) AS rn
    FROM
        @LogTest AS Main
        OUTER APPLY
        (
            SELECT TOP (1) T.IdRole
            FROM @LogTest AS T
            WHERE
                T.IdDocument = Main.IdDocument
                AND T.DateChange < Main.DateChange
            ORDER BY T.DateChange DESC
        ) AS Prev
    WHERE Main.IdRole <> Prev.IdRole OR Prev.IdRole IS NULL
)
SELECT *
FROM
    @LogTest AS LT
    CROSS APPLY
    (
        SELECT TOP(1) CTE_rn.rn
        FROM CTE_rn
        WHERE
            CTE_rn.IdDocument = LT.IdDocument
            AND CTE_rn.IdRole = LT.IdRole
            AND CTE_rn.DateChange <= LT.DateChange
        ORDER BY CTE_rn.DateChange DESC
    ) CA_rn
ORDER BY IdDocument, DateChange;

最终结果集：

Id    IdRole    DateChange                 IdDocument    NumberingExpected    rn
1     7         2015-01-26 20:00:41.210    13            1                    1
2     3         2015-01-26 21:00:41.210    13            2                    2
3     3         2015-01-26 22:00:41.210    13            2                    2
4     3         2015-01-26 23:00:41.210    13            2                    2
5     5         2015-01-27 00:00:41.210    13            3                    3
7     3         2015-01-27 02:00:41.210    13            4                    4
6     3         2015-01-27 01:00:41.210    27            1                    1
8     3         2015-01-27 03:00:41.210    27            1                    1
9     5         2015-01-27 04:00:41.210    27            2                    2
10    3         2015-01-27 05:00:41.210    27            3                    3

怎么运行的

1) 当表按 IdDocument 和 DateChange 排序时，我们需要上一行的 IdRole 值。为了得到它，我们使用OUTER APPLY（因为LAG不可用）：

SELECT *
FROM
    @LogTest AS Main
    OUTER APPLY
    (
        SELECT TOP (1) T.IdRole
        FROM @LogTest AS T
        WHERE
            T.IdDocument = Main.IdDocument
            AND T.DateChange < Main.DateChange
        ORDER BY T.DateChange DESC
    ) AS Prev
ORDER BY Main.IdDocument, Main.DateChange;

这是第一步的结果集：

Id    IdRole    DateChange                 IdDocument    NumberingExpected    IdRole
1     7         2015-01-26 20:50:32.560    13            1                    NULL
2     3         2015-01-26 21:50:32.560    13            2                    7
3     3         2015-01-26 22:50:32.560    13            2                    3
4     3         2015-01-26 23:50:32.560    13            2                    3
5     5         2015-01-27 00:50:32.560    13            3                    3
7     3         2015-01-27 02:50:32.560    13            4                    5
6     3         2015-01-27 01:50:32.560    27            1                    NULL
8     3         2015-01-27 03:50:32.560    27            1                    3
9     5         2015-01-27 04:50:32.560    27            2                    3
10    3         2015-01-27 05:50:32.560    27            3                    5

2）我们想要删除具有重复IdRole的行，所以我们添加一个WHERE并对行进行编号。您可以看到行号符合预期结果：

SELECT
    Main.IdRole
    ,Main.IdDocument
    ,Main.DateChange
    ,ROW_NUMBER() OVER(PARTITION BY Main.IdDocument ORDER BY Main.DateChange) AS rn
FROM
    @LogTest AS Main
    OUTER APPLY
    (
        SELECT TOP (1) T.IdRole
        FROM @LogTest AS T
        WHERE
            T.IdDocument = Main.IdDocument
            AND T.DateChange < Main.DateChange
        ORDER BY T.DateChange DESC
    ) AS Prev
WHERE Main.IdRole <> Prev.IdRole OR Prev.IdRole IS NULL
;

这是此步骤的结果集（它成为 CTE）：

IdRole    IdDocument    DateChange                 rn
7         13            2015-01-26 20:13:26.247    1
3         13            2015-01-26 21:13:26.247    2
5         13            2015-01-27 00:13:26.247    3
3         13            2015-01-27 02:13:26.247    4
3         27            2015-01-27 01:13:26.247    1
5         27            2015-01-27 04:13:26.247    2
3         27            2015-01-27 05:13:26.247    3

3) 最后，我们需要从 CTE 中获取原始表每一行的正确行号。我用CROSS APPLY从原始表的每一行的 CTE 中获取一行。