sql - 根据条件为记录分配排名

Question

我有一个包含以下记录的表:

=======================
| ID    |  device_num |
=======================
|  1    |      11     |
|  2    |      11     | 
|  3    |      NULL   |
|  4    |      11     |
|  5    |      11     |
|  6    |      NULL   |
|  7    |      11     |
|  8    |      12     |
|  9    |      12     |
|  10   |      13     |
|  11   |      NULL   | 
|  12   |      13     |
|  13   |      13     |
|  14   |      13     |
|  15   |      14     |
|  16   |      14     |
=======================

我想根据以下情况为每个设备编号分配一个等级:

1- 排名应从 1 开始。

2- 每条记录都应与其之前的记录进行比较，如果两者具有相同的设备编号，则为记录分配相同的等级。

3- 不要将等级分配给 NULL 记录。如果我们在 Null 记录后得到相同的设备编号，那么排名应该增加一个。

4- 如果设备编号与之前的记录不匹配，则将排名提高 1。

期望的输出:

===============================
| ID    | RANK  |  device_num |
===============================
|  1    |  1    |      11     |
|  2    |  1    |      11     | 
|  3    |       |      NULL   |
|  4    |  2    |      11     |
|  5    |  2    |      11     |
|  6    |       |      NULL   |
|  7    |  3    |      11     |
|  8    |  4    |      12     |
|  9    |  4    |      12     |
|  10   |  5    |      13     |
|  11   |       |      NULL   | 
|  12   |  6    |      13     |
|  13   |  6    |      13     |
|  14   |  6    |      13     |
|  15   |  7    |      14     |
|  16   |  7    |      14     |
===============================

我已经尝试使用 PostgreSQL Rank 函数 dense、rank 等，但无法按此顺序分配排名。

Answer 1

试试这个:

SELECT id, device_num, 
       CASE 
         WHEN device_num IS NOT NULL 
         THEN DENSE_RANK() OVER (ORDER BY CASE 
                                            WHEN device_num IS NOT NULL
                                            THEN min_id 
                                          END) 
       END AS RANK
FROM (
  SELECT id, device_num, 
         MIN(id) OVER (PARTITION BY device_num, grp) AS min_id
  FROM (
  SELECT id, device_num,
         ROW_NUMBER() OVER (ORDER BY id) - 
         ROW_NUMBER() OVER (PARTITION BY CASE 
                                           WHEN device_num IS NULL THEN -1 
                                           ELSE device_num
                                         END
                            ORDER BY id) AS grp
  FROM mytable) AS t) AS s
ORDER BY id

我的假设是:

1. 您的表中有一个自动递增的 id 字段，用于指定行顺序。
2. device_num 字段是 integer 类型，-1 不是该字段的有效值。

Demo here