Sqlite /填充对现有行进行排序的新列

Question

我有一个SQLite数据库表，包含以下列:

| day         | place | visitors |
-------------------------------------
|  2021-05-01 | AAA   |   20 |
|  2021-05-01 | BBB   |   10 |
|  2021-05-01 | CCC   |    3 |
|  2021-05-02 | AAA   |    5 |
|  2021-05-02 | BBB   |    7 |
|  2021-05-02 | CCC   |    2 |

现在我想介绍一个列'rank'，它表示根据每天的访问者排名。期望的表看起来像:

| day         | place | visitors | Rank  |
------------------------------------------
|  2021-05-01 | AAA   |   20     |  1    |
|  2021-05-01 | BBB   |   10     |  2    |
|  2021-05-01 | CCC   |    3     |  3    |
|  2021-05-02 | AAA   |    5     |  2    |
|  2021-05-02 | BBB   |    7     |  1    |
|  2021-05-02 | CCC   |    2     |  3    |

为新列Rank填充数据可以用类似(伪代码)的程序来完成。

for each i_day in all_days:
SELECT
ROW_NUMBER () OVER (ORDER BY `visitors` DESC) Day_Rank, place
FROM mytable
WHERE `day` = 'i_day'

for each i_place in all_places:
UPDATE mytable 
SET rank= Day_Rank
WHERE `Day`='i_day'
AND place = 'i_place'

由于这种逐行更新效率非常低，因此我正在搜索如何使用SQL子查询结合update .

对其进行优化。

for each i_day in all_days:
UPDATE mytable
SET rank= (
SELECT
ROW_NUMBER () OVER (ORDER BY `visitors` DESC) Day_Rank
FROM mytable
WHERE `day` = 'i_day'
)

Answer 1

通常，这可以通过计算visitors大于当前行的visitors值的子查询来完成:

UPDATE mytable
SET Day_Rank = (
SELECT COUNT(*) + 1
FROM mytable m 
WHERE m.day = mytable.day AND m.visitors > mytable.visitors 
);

注意，如果visitors的值存在关联，则结果实际上是RANK()将返回的结果。

或者，您可以在CTE中使用ROW_NUMBER()计算排名，并在子查询中使用它:

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY day ORDER BY visitors DESC) rn
FROM mytable
)
UPDATE mytable
SET Day_Rank = (SELECT rn FROM cte c WHERE (c.day, c.place) = (mytable.day, mytable.place));

或者，如果你的SQLite版本是3.33.0+，你可以使用类似join的UPDATE...FROM...语法:

UPDATE mytable AS m
SET Day_Rank = t.rn
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY day ORDER BY visitors DESC) rn
FROM mytable
) t
WHERE (t.day, t.place) = (m.day, m.place);