sql - SQL查询/根据日期和ID计算值的等级(Sql Query / Calculate rank for values based on date and id)

Question

For each date and id we have to give the rank to values in decreasing order.

(对于每个日期和ID，我们必须按降序对值进行排名。)

Below is the Input Table.

(下面是输入表。)

>input data
+------------+----+-------+
|    date    | id | value |
+------------+----+-------+
| 01-01-2018 | A  |    20 |
| 01-01-2018 | A  |    50 |
| 01-01-2018 | C  |    40 |
| 01-01-2018 | B  |    40 |
| 02-01-2018 | A  |    30 |
| 03-01-2018 | C  |    20 |
| 03-01-2018 | C  |    40 |
| 04-01-2018 | B  |     0 |
| 04-01-2018 | B  |    40 |
| 05-01-2018 | B  |    70 |
+------------+----+-------+

Output should look like below:

(输出应如下所示：)

>output data
+------------+----+-------+------+
|    date    | id | value | rank |
+------------+----+-------+------+
| 01-01-2018 | A  |    50 |    1 |
| 01-01-2018 | A  |    20 |    2 |
| 01-01-2018 | B  |    40 |    1 |
| 01-01-2018 | C  |    40 |    1 |
| 02-01-2018 | A  |    30 |    1 |
| 03-01-2018 | C  |    40 |    1 |
| 03-01-2018 | C  |    20 |    2 |
| 04-01-2018 | B  |    40 |    1 |
| 04-01-2018 | B  |     0 |    2 |
| 05-01-2018 | B  |    70 |    1 |
+------------+----+-------+------+

  ask by Akash Jain translate from so

Answer 1

You can use RANK() , partitioning on date and id and ordering by value descending:

(您可以使用RANK() ，对date和id分区，并按value降序排序：)

SELECT *,
       RANK() OVER (PARTITION BY date, id ORDER BY value DESC) AS ranking
FROM data

Output:

(输出：)

date        id  value   ranking
01-01-2018  A   50      1
01-01-2018  A   20      2
01-01-2018  B   40      1
01-01-2018  C   40      1
02-01-2018  A   30      1
03-01-2018  C   40      1
03-01-2018  C   20      2
04-01-2018  B   40      1
04-01-2018  B   0       2
05-01-2018  B   70      1

Demo on SQLFiddle

(关于SQLFiddle的演示)

This query will run on all the DBMS you have tagged your question with.

(该查询将在您标记了问题的所有DBMS上运行。)