In this article, Mysql 5.7 Window Functions Workaround, I put some examples of how we can solve problems if we need window functions in MySQL 5.7(which lacks such functionality).
In this post, we can achieve the same result, but with less code, and it looks more readable.
What we want to do: given we have a list of tweets, we want to find SUM
of the latest comments(only 3 latest items) for each user.
SELECT
user_id,
SUM(likes)
FROM (
SELECT
*,
ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY published_at DESC ) AS rank
FROM twitter_comments
ORDER BY user_id ASC, published_at DESC
) AS data
WHERE rank <= 3
GROUP BY user_id;
For each user's tweet, we generate a unique row number, which is later used to limit rows with aggregation functions(like SUM
or AVG
)
The intermediate result(with rank
for each tweet per user) looks like this:
id | user_id | likes | published_at | rank
----+---------+-------+---------------------+------
6 | 1 | 200 | 2021-04-05 14:01:00 | 1
5 | 1 | 100 | 2021-04-05 13:01:00 | 2
4 | 1 | 200 | 2021-04-05 12:01:00 | 3
3 | 1 | 300 | 2021-04-05 11:01:00 | 4
2 | 1 | 200 | 2021-04-05 10:01:00 | 5
1 | 1 | 100 | 2021-04-05 09:01:00 | 6
12 | 2 | 100 | 2021-05-05 14:01:00 | 1
11 | 2 | 200 | 2021-05-05 13:01:00 | 2
10 | 2 | 300 | 2021-05-05 12:01:00 | 3
9 | 2 | 100 | 2021-05-05 11:01:00 | 4
8 | 2 | 300 | 2021-05-05 10:01:00 | 5
7 | 2 | 200 | 2021-05-05 09:01:00 | 6
(12 rows)
Helpful links:
Top comments (0)