SQL排名仅连续行

Question

我有一个查询，在其中基于3列对行进行排名。 除了如果任何一行在这3列中包含相同的数据，它会给出下一个排名，即使它在输出中不是连续的，我已经成功地做到了。如果任何一行匹配这些列中的数据，则只有在它在连续的行中时，才应该给它下一个排名，如果不是，则应再次将其排名为1。 我尝试了以下代码：

  SELECT DISTINCT DENSE_RANK () OVER (PARTITION BY Patient_ID, 
                                                 Opnametype, 
                                                 afdelingscode ORDER BY  Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd) AS rnk, 
                *
  FROM t_opnames
  ORDER BY Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd

它的输出结果如下：

rnk Opnamenummer Patient_ID afdelingscode     Opnametype   Specialismen  OntslagDatumTijd ...
1   2983800      100006     RD8-GH MAU        Inpatient-E  GM            2014-09-01 14:50:00.000
2   2983800      100006     RD8-GH MAU        Inpatient-E  GM            2014-09-02 19:32:00.000
1   2983800      100006     RD8-GH Ward 08    Inpatient-E  GM            2014-09-03 17:12:00.000  
1   2983800      100006     RD8-GH Endo       Inpatient-E  GM            2014-09-09 09:06:00.000
2   2983800      100006     RD8-GH Ward 08    Inpatient-E  GM            2014-09-17 17:00:00.000
3   2983800      100006     RD8-GH Ward 08    Inpatient-E  GM            2014-10-01 17:15:00.000

所以，除了最后两行外，所有行都是正确的。我希望它们的排名为1和2，而不是2和3，因为它们之间有一个名为“RD8-GH Endo”的行。

那么我该怎么做呢？

- Hemant Sisodia

---

你可以编辑你的问题，并添加所有在OVER()子句中包含的列的示例数据吗？ - gotqn

我已经按照您的要求添加了这三列的数据。 - Hemant Sisodia

请同时添加 SPECIALISMEN 和 OntslagDatumTijd。 - gotqn

好的，已完成，你能否现在看一下？谢谢。 - Hemant Sisodia

你正在使用哪个版本的 SQL Server？ - gotqn

Answer 1

你可以使用相关子查询来实现这个目标。使用类似于以下的语句：

DECLARE @t_opnames TABLE
(
    Opnamenummer INT,
    Patient_ID INT,
    afdelingscode     VARCHAR(100),
    Opnametype   VARCHAR(100),
    Specialismen  CHAR(2),
    OntslagDatumTijd DATETIME
)

Insert into @t_opnames
SELECT  2983800      ,100006,     'RD8-GH MAU',        'Inpatient-E',  'GM',            '2014-09-01 14:50:00.000'
UNION ALL SELECT 2983800      ,100006,     'RD8-GH MAU',        'Inpatient-E',  'GM',            '2014-09-02 19:32:00.000'
UNION ALL SELECT 2983800      ,100006,     'RD8-GH Ward 08',    'Inpatient-E',  'GM',            '2014-09-03 17:12:00.000'  
UNION ALL SELECT 2983800      ,100006,     'RD8-GH Endo',       'Inpatient-E',  'GM',            '2014-09-09 09:06:00.000'
UNION ALL SELECT 2983800      ,100006,     'RD8-GH Ward 08',    'Inpatient-E',  'GM',            '2014-09-17 17:00:00.000'
UNION ALL SELECT 2983800      ,100006,     'RD8-GH Ward 08',    'Inpatient-E',  'GM',            '2014-10-01 17:15:00.000'


;WITH CTE as 
(
SELECT DENSE_RANK() OVER(ORDER BY  Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd) rnk,* 
  FROM @t_opnames
)
SELECT rnk-ISNULL((
            SELECT MAX(rnk) 
            FROM CTE c2 
            WHERE c2.Opnamenummer <= c1.Opnamenummer
            AND c2.SPECIALISMEN <= c1.SPECIALISMEN
            AND c2.OntslagDatumTijd <= c1.OntslagDatumTijd
            AND c2.rnk < c1.rnk
            AND (c2.Patient_ID <> c1.Patient_ID 
                OR   c2.Opnametype <> c1.Opnametype 
                OR c2.afdelingscode <> c1.afdelingscode)),0) rnk,Patient_ID, Opnametype,afdelingscode,Opnamenummer, SPECIALISMEN, OntslagDatumTijd
FROM CTE c1
  ORDER BY Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd

- ughai

---

实际上，由于编辑问题时我忘记添加更多的列到“按照”中。现在我已经添加了所有的列到“按照”中，但这样做会搞乱所有的排名。请帮忙解决。 - Hemant Sisodia

您是否想要根据 Patient_ID、Opnamenummer、SPECIALISMEN、Opnametype、OntslagDatumTijd 来排序集合？ - ughai

我不知道问题出在哪里，但如果我运行你给的确切查询，它可以完美地运行。但在我的情况下，有更多的列（这就是为什么我使用“*”），并且有75k+行，它只是挂起并继续运行而没有任何输出。如果我将行数限制为前50行，它会显示排名为-35、-34、...-42、9、7等。 - Hemant Sisodia

如果您计划在没有任何筛选器的情况下运行超过75k+行的相关查询，由于使用了“OR”和“<>”进行检查，这将需要一些时间。 - ughai

请看我的查询输出，输出结果非常混乱，与我期望的结果相差甚远：https://i.imgur.com/a9yD7ii.png - Hemant Sisodia

让我们在聊天中继续讨论。 - ughai

Answer 2

最终我找到了我的问题的解决方案，现在我可以获得我想要的输出，并且只需要在超过75k行的情况下运行3秒钟。我使用的代码是：

SELECT DISTINCT ROW_NUMBER () OVER (ORDER BY Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd) AS rownum, 
            * INTO #temp
FROM t_opnames
ORDER BY Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd;

WITH CTE
AS (SELECT *, 
           ROW_NUMBER () OVER (ORDER BY rownum) - ROW_NUMBER () OVER (PARTITION BY Patient_ID, 
                                                                                   Opnametype, 
                                                                                   afdelingscode ORDER BY rownum) AS RowGroup
      FROM #temp) 
SELECT ROW_NUMBER () OVER (PARTITION BY RowGroup, 
                                        Patient_ID, 
                                        Opnametype, 
                                        afdelingscode ORDER BY rownum) AS GroupSequence, 
       *
  FROM CTE
  ORDER BY rownum;

DROP TABLE #temp;

我参考了一个发布在页面上的例子。

- Hemant Sisodia

Answer 3

这并不是直接回答问题，但我想解释一下为什么你尝试的方法没有按照你的期望工作。

你的问题是由于PARTITION引起的。如果你从PARTITION子句中删除非唯一列，那么你只剩下afdelingscode。因此，简单来说，你的PARTITION将数据分组如下：

RD8-GH Endo
RD8-GH MAU
RD8-GH MAU
RD8-GH Ward 08
RD8-GH Ward 08
RD8-GH Ward 08

ORDER BY子句确定了你的PARTITION内的顺序，因此再次删除非唯一列，给出ORDER BY OntslagDatumTijd，这将产生以下结果，其中按日期列排序，注意分区仍由afdelingscode分隔：

afdelingscode   OntslagDatumTijd
RD8-GH Endo     2014-09-09 09:06:00.000
RD8-GH MAU      2014-09-01 14:50:00.000
RD8-GH MAU      2014-09-02 19:32:00.000
RD8-GH Ward 08  2014-09-03 17:12:00.000
RD8-GH Ward 08  2014-09-17 17:00:00.000
RD8-GH Ward 08  2014-10-01 17:15:00.000

排名然后应用于这些分区。其输出如下：

rnk afdelingscode   OntslagDatumTijd
1   RD8-GH Endo     2014-09-09 09:06:00.000
1   RD8-GH MAU      2014-09-01 14:50:00.000
2   RD8-GH MAU      2014-09-02 19:32:00.000
1   RD8-GH Ward 08  2014-09-03 17:12:00.000
2   RD8-GH Ward 08  2014-09-17 17:00:00.000
3   RD8-GH Ward 08  2014-10-01 17:15:00.000

所以它是按照您指定的方式进行排名的，您输出的问题在于在选择结束时（删除非唯一列）按日期列 OntslagDatumTijd 进行排序，这样会得到：

rnk afdelingscode   OntslagDatumTijd
1   RD8-GH MAU      2014-09-01 14:50:00.000
2   RD8-GH MAU      2014-09-02 19:32:00.000
1   RD8-GH Ward 08  2014-09-03 17:12:00.000
1   RD8-GH Endo     2014-09-09 09:06:00.000
2   RD8-GH Ward 08  2014-09-17 17:00:00.000
3   RD8-GH Ward 08  2014-10-01 17:15:00.000

如果其他回答不符合您的要求，我会继续查看此问题。

参考：

OVER Clause

PARTITION BY 将查询结果集分成分区。窗口函数将分别应用于每个分区，并且每个分区都重新开始计算。

ORDER BY 子句 定义结果集中每个分区内行的逻辑顺序。也就是说，它指定了执行窗口函数计算的逻辑顺序。

这里有一个潜在的解决方案，可能会因为您使用的数据量而出现性能问题，但您可以进行测试：

-- sets up your dummy data
CREATE TABLE #t_opnames
    (
      Opnamenummer INT ,
      Patient_ID INT ,
      afdelingscode NVARCHAR(20) ,
      Opnametype NVARCHAR(20) ,
      Specialismen NVARCHAR(20) ,
      OntslagDatumTijd DATETIME
    );

INSERT  INTO #t_opnames
        ( Opnamenummer, Patient_ID, afdelingscode, Opnametype, Specialismen,
          OntslagDatumTijd )
VALUES  ( 2983800, 100006, 'RD8-GH MAU', 'Inpatient-E', 'GM',
          '2014-09-01 14:50:00.000' ),
        ( 2983800, 100006, 'RD8-GH MAU', 'Inpatient-E', 'GM',
          '2014-09-02 19:32:00.000' ),
        ( 2983800, 100006, 'RD8-GH Ward 08', 'Inpatient-E', 'GM',
          '2014-09-03 17:12:00.000' ),
        ( 2983800, 100006, 'RD8-GH Endo', 'Inpatient-E', 'GM',
          '2014-09-09 09:06:00.000' ),
        ( 2983800, 100006, 'RD8-GH Ward 08', 'Inpatient-E', 'GM',
          '2014-09-17 17:00:00.000' ),
        ( 2983800, 100006, 'RD8-GH Ward 08', 'Inpatient-E', 'GM',
          '2014-10-01 17:15:00.000' )

-- I've added a row number to your data to enable iteration over the data
SELECT  ROW_NUMBER() OVER ( ORDER BY OntslagDatumTijd ) AS rn ,
        *
INTO #temp
FROM    #t_opnames
ORDER BY OntslagDatumTijd
-- this will iterate over the rows and apply the rankings
;WITH cte AS (
    SELECT *, 1 AS rnk 
    FROM #temp 
    WHERE rn = 1

    UNION ALL 

    SELECT t.*, CASE WHEN cte.afdelingscode = t.afdelingscode 
                     THEN cte.rnk + 1 
                     ELSE 1 
                END AS rnk 
    FROM #temp t
    INNER JOIN cte ON cte.rn +1 = t.rn
)
SELECT * FROM cte

DROP TABLE #t_opnames
DROP TABLE #temp

当你处理一个更大的数据集时，你会遇到MAXRECURSION限制，所以你需要在最后一个SELECT语句之后使用以下代码修改限制:

SELECT * FROM cte
OPTION (MAXRECURSION 0)

将此值设置为0不会强制实施任何限制，如果您事先知道数据集的大小，则可以将此数字设置为数据集的大小。

- Tanner

---

感谢您的回复。实际上，这三列中的数据都不是唯一的。在这些行中，Patient_Id相同，但在接下来的行中，这将会改变，Opnametype也会改变。之前发布的答案给出了我想要的输出结果，但速度太慢了，需要在4-5分钟后取消查询。 - Hemant Sisodia

@HemantSisodia，另一个答案没有为您提供解决方案吗？ - Tanner

是的，它解决了我的问题，但只有在我将行数限制在前500行左右时才有效。对于超过75k条记录的查询，它没有任何作用，而且我需要在4-5分钟后取消运行查询。因此，在我的情况下它非常慢，无法使用，因为我不能使用where子句，因为我需要处理表中所有的行。 - Hemant Sisodia

@HemantSisodia已经更新了我的答案，设置maxrecursion将解决这个问题。 - Tanner

我尝试了添加 OPTION (MAXRECURSION 0)，它也能够运行，但查询也太慢了。我的记录有75k+，在运行50秒后，仅显示了5000个记录，之后我不得不停止运行查询。但是还是感谢你，我已经找到了解决问题的方法并发布了它。 - Hemant Sisodia