对连续的空值进行排名

Question

我想为我的记录对连续的空值进行排名。每条记录的排名均为 1。对于仅出现一次的空值，排名也将为 1。但是对于以连续方式出现的 null 值，第一条记录的排名将为 1，第二条记录的排名为 2，依此类推。这是我的代码。

CREATE TABLE #my_table
(
    id BIGINT                      IDENTITY PRIMARY KEY
    ,fruit                          varchar(100)
);
INSERT INTO #my_table
          SELECT 'apple'
UNION ALL SELECT 'apple'
UNION ALL SELECT NULL
UNION ALL SELECT 'pineapple'
UNION ALL SELECT 'banana'
UNION ALL SELECT  NULL
UNION ALL SELECT  NULL
UNION ALL SELECT 'orange'
select * from #my_table

预期结果

+----+-----------+------+
| id | fruit     | rank |
+----+-----------+------+
|  1 | apple     | 1    |
|  2 | apple     | 1    |
|  3 | NULL      | 1    |
|  4 | pineapple | 1    |
|  5 | banana    | 1    |
|  6 | NULL      | 1    |
|  7 | NULL      | 2    |
|  8 | orange    | 1    |
+----+-----------+------+

我应该如何查询？

请帮忙！

Answer 1

您可以使用

ROW_NUMBER差来获取连续NULL值的分组：

WITH Cte AS(
    SELECT *,
        g = ROW_NUMBER() OVER(ORDER BY id)
                - ROW_NUMBER() OVER(PARTITION BY fruit ORDER BY id)
    FROM #my_table  
)
SELECT 
    id,
    fruit,
    CASE
        WHEN fruit IS NULL THEN ROW_NUMBER() OVER(PARTITION BY fruit, g ORDER BY id)
        ELSE 1
    END AS rank
FROM Cte
ORDER BY id;

在线演示

Answer 2

 CREATE TABLE #my_table
 (
     id BIGINT                      IDENTITY PRIMARY KEY
     ,fruit                          varchar(100)
 );
 INSERT INTO #my_table
           SELECT 'apple'
 UNION ALL SELECT 'apple'
 UNION ALL SELECT NULL
 UNION ALL SELECT 'pineapple'
 UNION ALL SELECT 'banana'
 UNION ALL SELECT  NULL
 UNION ALL SELECT  NULL
 UNION ALL SELECT 'orange'
 ;
 WITH REC_CTE (id,fruit,ranks)
     AS (
         -- Anchor definition
        SELECT  id,
                fruit,
                1 as ranks
        FROM #my_table
       WHERE fruit is not null
          -- Recursive definition
         UNION ALL
         SELECT son.id,
                son.fruit,
                case when  son.fruit is null AND father.fruit is null  then
                    father.ranks + 1
                    else
                    1
               end as ranks
         FROM #my_table son INNER JOIN
              REC_CTE father
         on son.id = father.id +1 
         WHERE son.fruit is null
          --AND father.fruit is null
     )
  SELECT * from REC_CTE  order by id
  DROP TABLE #my_table

Answer 3

以下解决方案不使用递归（限制为 32767 级别 = ~ 行，具体取决于解决方案），并且它仅使用两个聚合/排名函数（SUM 和 DENSE_RANK）：

;WITH Base
AS (
    SELECT  *, IIF(fruit IS NULL, SUM(IIF(fruit IS NOT NULL, 1, 0)) OVER(ORDER BY id), NULL) AS group_num
    FROM    @my_table t
)
SELECT  *, IIF(fruit IS NULL, DENSE_RANK() OVER(PARTITION BY group_num ORDER BY id), 1) rnk
FROM    Base b
ORDER BY id

结果：

id  fruit     group_num rnk
--- --------- --------- ---
100 apple     NULL      1
125 apple     NULL      1
150 NULL      2         1
175 pineapple NULL      1
200 banana    NULL      1
225 NULL      4         1
250 NULL      4         2
275 orange    NULL      1
300 NULL      5         1
325 NULL      5         2
350 NULL      5         3