时间戳

首页 > 解决方案 > Sqlite / 填充对现有行进行排名的新列

sqlite - Sqlite / 填充对现有行进行排名的新列

问题描述

我有一个包含以下列的 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 |

现在我想介绍一个列“排名”，它表示每天访问者的排名。预期的表格如下所示：

| 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'
        )

标签： sqlitesql-updatesubquerywindow-functionsrank

解决方案

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

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

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

请参阅演示。

或者，您可以在 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+，您可以使用类似连接的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);

推荐阅读