根据条件为记录分配等级

debugcn 发表于 Dev

阿比舍克·吉尼亚尼

我有一张包含以下记录的表：

=======================
| 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的排名功能dense，rank等，但不能在此顺序分配级别。

乔治·贝索斯（Giorgos Betsos）

尝试这个：

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

我做出的假设是：