CODEWITHSUNDEEP
sqlitesql-updatesubquerywindow-functionsrank
Freude
Freude

Reputation: 1039

Sqlite / populate new column that ranks the existing rows

I've a SQLite database table with the following columns:

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

Now I would like to introduce a column 'rank' which indicates the rank according to the visitors each day. Expected table would look like:

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

Populating the data for the new column Rank can be done with a program like (Pseudocode).

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'

Since this line by line update is quite inefficient, I'm searching how to optimize this with a SQL sub query in combination with the UPDATE.

(does not work so far...)

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

Upvotes: 1

Views: 511

Answers (1)

forpas
forpas

Reputation: 164089

Typically, this can be done with a subquery that counts the number of rows with visitors greater than the value of visitors of the current row:

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

Note that the result is actually what RANK() would return, if there are ties in the values of visitors.

See the demo.

Or, you could calculate the rankings with ROW_NUMBER() in a CTE and use it in a subquery:

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

See the demo.

Or, if your versipn of SQLite is 3.33.0+ you can use the join-like UPDATE...FROM... syntax:

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

Upvotes: 2

Related Questions