Learn MySQL Window functions.
- Posted on
- Category：MySQL
- Tags：MySQL
The Window function is now available in MySQL version 8 or later, try the Window function.
Contents
- Window function
- basic format
- Named Windows
- Table for Window function execution.
- COUNT
- SUM
- AVG
- LAG / LEAD
- ROW_NUMBER
- RANK
- NTILE
- DENSE_RANK
- PERCENT_RANK
- CUME_DIST
- FIRST_VALUE / LAST_VALUE / NTH_VALUE
- JSON_ARRAYAGG
- JSON_OBJECTAGG
Window function
The Window function is a function that can perform calculations for aggregation and analysis without aggregation of result rows.
A window function performs an aggregate-like operation on a set of query rows. However, whereas an aggregate operation groups query rows into a single result row, a window function produces a result for each query row
For example, the aggregate function `GROUP BY` aggregates the result rows, but when using a Window function, the result rows are not aggregated, but each row is assigned a result. The same is true when an aggregate function is processed as a Window function.
# Aggregate with `GROUP BY`
SELECT
user_id,
AVG(score) AS average
FROM scores
GROUP BY user_id;
+---------+---------+
| user_id | average |
+---------+---------+
| 1 | 76.7204 | <-- Rows are aggregated by user_id.
| 2 | 73.7097 |
| 3 | 74.1505 |
| 4 | 74.5591 |
| 5 | 75.2688 |
+---------+---------+
# Aggregate with window function
SELECT
user_id,
score,
AVG(score) OVER (PARTITION BY user_id) AS average
FROM scores;
+---------+-------+---------+
| user_id | score | average |
+---------+-------+---------+
| 1 | 85 | 76.7204 | <-- Each record is given an average
| 1 | 83 | 76.7204 |
| 1 | 88 | 76.7204 |
| 1 | 90 | 76.7204 |
| 1 | 58 | 76.7204 |
+---------+-------+---------+
(Aggregate functions such as AVG() can be processed as Window functions.)
basic format
FUNCTION_NAME(expr) OVER ([PARTITION BY expr, expr,..] [ORDER BY expr, expr,..] [ROWS|RANGE ...])
- PARTITION BY clause: Which columns to aggregate.
- ORDER BY clause: sort order.
- frame clause: Scope of target line.
Specifying these as necessary, the desired value is assigned to the line.
Named Windows
Named Windows can be defined to group the same queries together.
SELECT
subject,
user_id,
average,
MIN(average) OVER w1 as min_avg,
MAX(average) OVER w1 as max_avg
FROM user_avg_scores_by_subject
Window w1 AS (PARTITION BY subject) # <- Define Named Window
;
Table for Window function execution.
Create a score table to execute the queries.
create table scores
(
id bigint unsigned auto_increment primary key,
user_id int unsigned not null,
subject varchar(255) not null,
score int unsigned not null,
implementation_date date not null,
created_at timestamp null,
updated_at timestamp null
);
Five users took the test in three subjects per day for one month. This table contains the test scores for one month.
mysql> SELECT id, user_id, subject, score, implementation_date FROM scores;
+-----+---------+---------+-------+---------------------+
| id | user_id | subject | score | implementation_date |
+-----+---------+---------+-------+---------------------+
| 1 | 1 | sub01 | 85 | 2021-12-01 |
| 2 | 1 | sub02 | 83 | 2021-12-01 |
| 3 | 1 | sub03 | 88 | 2021-12-01 |
| 4 | 1 | sub01 | 90 | 2021-12-02 |
| 5 | 1 | sub02 | 58 | 2021-12-02 |
| 6 | 1 | sub03 | 84 | 2021-12-02 |
| 7 | 1 | sub01 | 76 | 2021-12-03 |
| 8 | 1 | sub02 | 72 | 2021-12-03 |
.
.
.
We will start writing queries in the next section, but we will also include aggregate functions such as COUNT() and SUM() since they can be processed as Window functions.
COUNT
Count the number of records.
SELECT
implementation_date,
subject,
user_id,
score,
COUNT(user_id) OVER (PARTITION BY user_id) as total_tests
FROM scores
ORDER BY implementation_date, subject, user_id;
The total number of records per user(= total number of tests performed) is given by specifying user_id in the `PARTITION BY` clause.
+---------------------+---------+---------+-------+-------------+
| implementation_date | subject | user_id | score | total_tests |
+---------------------+---------+---------+-------+-------------+
| 2021-12-01 | sub01 | 1 | 85 | 93 |
| 2021-12-01 | sub01 | 2 | 85 | 93 |
| 2021-12-01 | sub01 | 3 | 78 | 93 |
| 2021-12-01 | sub01 | 4 | 93 | 93 |
| 2021-12-01 | sub01 | 5 | 78 | 93 |
.
.
.
| 2021-12-31 | sub03 | 5 | 61 | 93 |
+---------------------+---------+---------+-------+-------------+
465 rows in set (0.00 sec)
Five users took three tests over a period of one month (31 days).
The total number of tests administered per user is 93.
You can see that this number, 93, has been added to each record. (Total number of records in this table is 93 * 5 = 465 records)
SUM
Calculate the total value.
SELECT
user_id,
subject,
score,
implementation_date,
SUM(score) OVER (PARTITION BY user_id, subject) as total_by_subject
FROM scores
ORDER BY user_id, implementation_date, subject;
Aggregate using the `user_id` and `subject` columns and add to each user's record "the total score per subject obtained by each user".
+---------+---------+-------+---------------------+------------------+
| user_id | subject | score | implementation_date | total_by_subject |
+---------+---------+-------+---------------------+------------------+
| 1 | sub01 | 85 | 2021-12-01 | 2417 |
| 1 | sub02 | 83 | 2021-12-01 | 2405 |
| 1 | sub03 | 88 | 2021-12-01 | 2313 |
.
.
.
| 5 | sub03 | 61 | 2021-12-31 | 2438 |
+---------+---------+-------+---------------------+------------------+
465 rows in set (0.00 sec)
AVG
Calculate the average value.
SELECT
user_id,
implementation_date,
subject,
score,
AVG(score) OVER (PARTITION BY user_id, subject) AS average
FROM scores
ORDER BY user_id, implementation_date, subject;
Add the average score for each subject for each user.
+---------+---------------------+---------+-------+---------+
| user_id | implementation_date | subject | score | average |
+---------+---------------------+---------+-------+---------+
| 1 | 2021-12-01 | sub01 | 85 | 77.9677 |
| 1 | 2021-12-01 | sub02 | 83 | 77.5806 |
| 1 | 2021-12-01 | sub03 | 88 | 74.6129 |
.
.
.
| 5 | 2021-12-31 | sub03 | 61 | 78.6452 |
+---------+---------------------+---------+-------+---------+
465 rows in set (0.01 sec)
If a range of values is specified `frame` clause, the average value for that interval can also be calculated. This is useful for obtaining moving averages.
SELECT
user_id,
implementation_date,
subject,
score,
AVG(score) OVER (PARTITION BY user_id, subject) AS average,
AVG(score) OVER (PARTITION BY user_id, subject ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_average_3,
AVG(score) OVER (PARTITION BY user_id, subject ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS moving_average_5
FROM scores
ORDER BY user_id, implementation_date, subject;
Add moving averages for 3 and 5 intervals.
+---------+---------------------+---------+-------+---------+------------------+------------------+
| user_id | implementation_date | subject | score | average | moving_average_3 | moving_average_5 |
+---------+---------------------+---------+-------+---------+------------------+------------------+
| 1 | 2021-12-01 | sub01 | 85 | 77.9677 | 87.5000 | 83.6667 |
| 1 | 2021-12-01 | sub02 | 83 | 77.5806 | 70.5000 | 71.0000 |
| 1 | 2021-12-01 | sub03 | 88 | 74.6129 | 86.0000 | 80.3333 |
| 1 | 2021-12-02 | sub01 | 90 | 77.9677 | 83.6667 | 79.5000 |
| 1 | 2021-12-02 | sub02 | 58 | 77.5806 | 71.0000 | 71.2500 |
| 1 | 2021-12-02 | sub03 | 84 | 74.6129 | 80.3333 | 75.7500 |
| 1 | 2021-12-03 | sub01 | 76 | 77.9677 | 77.6667 | 81.8000 |
| 1 | 2021-12-03 | sub02 | 72 | 77.5806 | 67.3333 | 72.6000 |
| 1 | 2021-12-03 | sub03 | 69 | 74.6129 | 71.6667 | 72.8000 |
.
.
.
| 5 | 2021-12-31 | sub03 | 61 | 78.6452 | 63.5000 | 60.3333 |
+---------+---------------------+---------+-------+---------+------------------+------------------+
465 rows in set (0.00 sec)
LAG / LEAD
The previous value (LAG) and the next value (LEAD) are given for the specified number of lines.
SELECT
user_id,
implementation_date,
subject,
score,
LAG(score, 1) over (PARTITION BY user_id, subject) as last_score,
LEAD(score, 1) over (PARTITION BY user_id, subject) as next_score
FROM scores
ORDER BY user_id, subject, implementation_date;
After grouping by `user_id` and `subject` columns, give each line the score of the previous and next lines.
+---------+---------------------+---------+-------+------------+------------+
| user_id | implementation_date | subject | score | last_score | next_score |
+---------+---------------------+---------+-------+------------+------------+
| 1 | 2021-12-01 | sub01 | 85 | NULL | 90 |
| 1 | 2021-12-02 | sub01 | 90 | 85 | 76 |
| 1 | 2021-12-03 | sub01 | 76 | 90 | 67 |
| 1 | 2021-12-04 | sub01 | 67 | 76 | 91 |
| 1 | 2021-12-05 | sub01 | 91 | 67 | 98 |
| 1 | 2021-12-06 | sub01 | 98 | 91 | 58 |
| 1 | 2021-12-07 | sub01 | 58 | 98 | 83 |
.
.
.
| 1 | 2021-12-30 | sub01 | 93 | 56 | 76 |
| 1 | 2021-12-31 | sub01 | 76 | 93 | NULL |
| 1 | 2021-12-01 | sub02 | 83 | NULL | 58 |
| 1 | 2021-12-02 | sub02 | 58 | 83 | 72 |
| 1 | 2021-12-03 | sub02 | 72 | 58 | 72 |
.
.
.
| 5 | 2021-12-31 | sub03 | 61 | 66 | NULL |
+---------+---------------------+---------+-------+------------+------------+
465 rows in set (0.00 sec)
The first and the last of the grouped rows are null because last and next do not exist, respectively.
ROW_NUMBER
Assign ordinal numbers within row groups.
SELECT
implementation_date,
user_id,
ROW_NUMBER() over (PARTITION BY user_id, subject) as times,
subject,
score
FROM scores;
Group by user and subject and assign the number of times each subject is conducted.
+---------------------+---------+-------+---------+-------+
| implementation_date | user_id | times | subject | score |
+---------------------+---------+-------+---------+-------+
| 2021-12-01 | 1 | 1 | sub01 | 85 |
| 2021-12-02 | 1 | 2 | sub01 | 90 |
| 2021-12-03 | 1 | 3 | sub01 | 76 |
| 2021-12-04 | 1 | 4 | sub01 | 67 |
| 2021-12-05 | 1 | 5 | sub01 | 91 |
| 2021-12-06 | 1 | 6 | sub01 | 98 |
.
.
.
| 2021-12-29 | 1 | 29 | sub01 | 56 |
| 2021-12-30 | 1 | 30 | sub01 | 93 |
| 2021-12-31 | 1 | 31 | sub01 | 76 |
| 2021-12-01 | 1 | 1 | sub02 | 83 |
| 2021-12-02 | 1 | 2 | sub02 | 58 |
| 2021-12-03 | 1 | 3 | sub02 | 72 |
.
.
.
| 2021-12-29 | 5 | 29 | sub03 | 54 |
| 2021-12-30 | 5 | 30 | sub03 | 66 |
| 2021-12-31 | 5 | 31 | sub03 | 61 |
+---------------------+---------+-------+---------+-------+
465 rows in set (0.01 sec)
RANK
Ranking.
SELECT
implementation_date,
subject,
user_id,
score,
RANK() OVER (PARTITION BY subject, implementation_date ORDER BY score DESC) as ranking
FROM scores
ORDER BY implementation_date, subject, ranking;
Grouping by SUBJECT and IMPLEMENTATION_DATE columns and ranking the SCORE column in descending order allows us to assign a rank to each subject for each test date.
+---------------------+---------+---------+-------+---------+
| implementation_date | subject | user_id | score | ranking |
+---------------------+---------+---------+-------+---------+
| 2021-12-01 | sub01 | 4 | 93 | 1 |
| 2021-12-01 | sub01 | 1 | 85 | 2 |
| 2021-12-01 | sub01 | 2 | 85 | 2 |
| 2021-12-01 | sub01 | 3 | 78 | 4 |
| 2021-12-01 | sub01 | 5 | 78 | 4 |
| 2021-12-01 | sub02 | 1 | 83 | 1 |
| 2021-12-01 | sub02 | 2 | 81 | 2 |
| 2021-12-01 | sub02 | 5 | 76 | 3 |
| 2021-12-01 | sub02 | 3 | 70 | 4 |
| 2021-12-01 | sub02 | 4 | 50 | 5 |
| 2021-12-01 | sub03 | 4 | 97 | 1 |
.
.
.
| 2021-12-31 | sub03 | 5 | 61 | 5 |
+---------------------+---------+---------+-------+---------+
465 rows in set (0.01 sec)
NTILE
Classifications are grouped and ranked by the number of groups specified.
Classifications are grouped by close values (i.e., as close as possible to the same size).
SELECT
user_id,
total,
NTILE(3) OVER (ORDER BY total desc) as g_rank
FROM user_total_scores;
The total score of the five is ranked into three groups.
+---------+-------+--------+
| user_id | total | g_rank |
+---------+-------+--------+
| 1 | 7135 | 1 |
| 5 | 7000 | 1 |
| 4 | 6934 | 2 |
| 3 | 6896 | 2 |
| 2 | 6855 | 3 |
+---------+-------+--------+
5 rows in set (0.00 sec)
You can see that the five are ranked in three groups.
DENSE_RANK
Calculate the rank in the group.
The difference between `dense_rank()` and `rank()` is that ranks are not carried forward even if there is a tie.
SELECT
subject,
user_id,
average,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY average desc) AS d_rnk,
RANK() OVER (PARTITION BY subject ORDER BY average desc) as rnk
FROM user_avg_scores_by_subject;
Rank users against their average score in each subject area.
+---------+---------+---------+-------+-----+
| subject | user_id | average | d_rnk | rnk |
+---------+---------+---------+-------+-----+
| sub01 | 1 | 78 | 1 | 1 |
| sub01 | 4 | 76 | 2 | 2 |
| sub01 | 3 | 75 | 3 | 3 |
| sub01 | 5 | 74 | 4 | 4 |
| sub01 | 2 | 73 | 5 | 5 |
| sub02 | 1 | 78 | 1 | 1 |
| sub02 | 4 | 76 | 2 | 2 |
| sub02 | 2 | 73 | 3 | 3 |
| sub02 | 3 | 73 | 3 | 3 |
| sub02 | 5 | 73 | 3 | 3 |
| sub03 | 5 | 79 | 1 | 1 |
| sub03 | 1 | 75 | 2 | 2 |
| sub03 | 2 | 75 | 2 | 2 |
| sub03 | 3 | 74 | 3 | 4 |
| sub03 | 4 | 71 | 4 | 5 |
+---------+---------+---------+-------+-----+
If you look at the rankings for `sub03`, you will see that when there is a tie, the subsequent rankings are not carried forward.
PERCENT_RANK
Calculate the percentile rank.
- The rank is calculated as a percentage with the highest rank being 0.
- Rank ranges from 0 to 1
- The formula for calculating percentile rank is (rank - 1)/(number of rows in window or partition - 1)
SELECT
subject,
user_id,
average,
RANK() OVER (PARTITION BY subject ORDER BY average desc) as rnk,
PERCENT_RANK() OVER (PARTITION BY subject ORDER BY average desc) as p_rnk
FROM user_avg_scores_by_subject;
Percentile ranks are calculated for the average score for each user subject respectively.
+---------+---------+---------+-----+-------+
| subject | user_id | average | rnk | p_rnk |
+---------+---------+---------+-----+-------+
| sub01 | 1 | 78 | 1 | 0 |
| sub01 | 4 | 76 | 2 | 0.25 |
| sub01 | 3 | 75 | 3 | 0.5 |
| sub01 | 5 | 74 | 4 | 0.75 |
| sub01 | 2 | 73 | 5 | 1 |
| sub02 | 1 | 78 | 1 | 0 |
| sub02 | 4 | 76 | 2 | 0.25 |
| sub02 | 2 | 73 | 3 | 0.5 |
| sub02 | 3 | 73 | 3 | 0.5 |
| sub02 | 5 | 73 | 3 | 0.5 |
| sub03 | 5 | 79 | 1 | 0 |
| sub03 | 1 | 75 | 2 | 0.25 |
| sub03 | 2 | 75 | 2 | 0.25 |
| sub03 | 3 | 74 | 4 | 0.75 |
| sub03 | 4 | 71 | 5 | 1 |
+---------+---------+---------+-----+-------+
CUME_DIST
Calculate the cumulative distribution within the row group.
The last row of the specified group is 1, and the rows are stacked from 0 toward it. You can see the relative position.
SELECT
user_id,
subject,
average,
CUME_DIST() OVER (PARTITION BY subject ORDER BY average) as cume_dist_by_avg
FROM user_avg_scores_by_subject
ORDER BY user_id, subject;
The cumulative distribution is grouped by subject and calculated using the user's average score by subject.
+---------+---------+---------+------------------+
| user_id | subject | average | cume_dist_by_avg |
+---------+---------+---------+------------------+
| 1 | sub01 | 78 | 1 |
| 1 | sub02 | 78 | 1 |
| 1 | sub03 | 75 | 0.8 |
| 2 | sub01 | 73 | 0.2 |
| 2 | sub02 | 73 | 0.6 |
| 2 | sub03 | 75 | 0.8 |
| 3 | sub01 | 75 | 0.6 |
| 3 | sub02 | 73 | 0.6 |
| 3 | sub03 | 74 | 0.4 |
| 4 | sub01 | 76 | 0.8 |
| 4 | sub02 | 76 | 0.8 |
| 4 | sub03 | 71 | 0.2 |
| 5 | sub01 | 74 | 0.4 |
| 5 | sub02 | 73 | 0.6 |
| 5 | sub03 | 79 | 1 |
+---------+---------+---------+------------------+
The results show that `user_id: 1` is in the 80% position with respect to the grade of subject sub03.
FIRST_VALUE / LAST_VALUE / NTH_VALUE
The first, last, and specified line values are assigned in the group, respectively.
SELECT
subject,
user_id,
average,
FIRST_VALUE(average) OVER (PARTITION BY subject ORDER BY average) as first,
LAST_VALUE(average) OVER (PARTITION BY subject ORDER BY average ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last,
NTH_VALUE(average ,3) OVER (PARTITION BY subject ORDER BY average DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as third_value
FROM user_avg_scores_by_subject;
Assigns values for the first, last, and third rows in the average score for each subject.
+---------+---------+---------+-------+------+-------------+
| subject | user_id | average | first | last | third_value |
+---------+---------+---------+-------+------+-------------+
| sub01 | 1 | 78 | 73 | 78 | 75 |
| sub01 | 4 | 76 | 73 | 78 | 75 |
| sub01 | 3 | 75 | 73 | 78 | 75 |
| sub01 | 5 | 74 | 73 | 78 | 75 |
| sub01 | 2 | 73 | 73 | 78 | 75 |
| sub02 | 1 | 78 | 73 | 78 | 73 |
| sub02 | 4 | 76 | 73 | 78 | 73 |
| sub02 | 2 | 73 | 73 | 78 | 73 |
| sub02 | 3 | 73 | 73 | 78 | 73 |
| sub02 | 5 | 73 | 73 | 78 | 73 |
| sub03 | 5 | 79 | 71 | 79 | 75 |
| sub03 | 1 | 75 | 71 | 79 | 75 |
| sub03 | 2 | 75 | 71 | 79 | 75 |
| sub03 | 3 | 74 | 71 | 79 | 75 |
| sub03 | 4 | 71 | 71 | 79 | 75 |
+---------+---------+---------+-------+------+-------------+
When retrieving the last row with LAST_VALUE(), a frame clause must be specified, as in the above query, because by default it only reads up to its own row.
In this case, it is simpler to use FIRST_VALUE() to get the last row.
SELECT
subject,
user_id,
average,
FIRST_VALUE(average) OVER (PARTITION BY subject ORDER BY average) as first,
LAST_VALUE(average) OVER (PARTITION BY subject ORDER BY average ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last,
FIRST_VALUE(average) OVER (PARTITION BY subject ORDER BY average DESC) as last_by_first_value, -- <- Take the first row of the `average` column in descending order.
NTH_VALUE(average ,3) OVER (PARTITION BY subject ORDER BY average DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as third_value
FROM user_avg_scores_by_subject;
In the above query, the calculated values for LAST_VALUE() and FIRST_VALUE() are the same.
+---------+---------+---------+-------+------+---------------------+-------------+
| subject | user_id | average | first | last | last_by_first_value | third_value |
+---------+---------+---------+-------+------+---------------------+-------------+
| sub01 | 1 | 78 | 73 | 78 | 78 | 75 |
| sub01 | 4 | 76 | 73 | 78 | 78 | 75 |
| sub01 | 3 | 75 | 73 | 78 | 78 | 75 |
| sub01 | 5 | 74 | 73 | 78 | 78 | 75 |
| sub01 | 2 | 73 | 73 | 78 | 78 | 75 |
| sub02 | 1 | 78 | 73 | 78 | 78 | 73 |
| sub02 | 4 | 76 | 73 | 78 | 78 | 73 |
| sub02 | 2 | 73 | 73 | 78 | 78 | 73 |
| sub02 | 3 | 73 | 73 | 78 | 78 | 73 |
| sub02 | 5 | 73 | 73 | 78 | 78 | 73 |
| sub03 | 5 | 79 | 71 | 79 | 79 | 75 |
| sub03 | 1 | 75 | 71 | 79 | 79 | 75 |
| sub03 | 2 | 75 | 71 | 79 | 79 | 75 |
| sub03 | 3 | 74 | 71 | 79 | 79 | 75 |
| sub03 | 4 | 71 | 71 | 79 | 79 | 75 |
+---------+---------+---------+-------+------+---------------------+-------------+
JSON_ARRAYAGG
Combines the values in a group into one by JSON format.
SELECT
user_id,
subject,
implementation_date,
score,
JSON_ARRAYAGG(score) OVER (PARTITION BY user_id, subject ORDER BY user_id, subject) as all_scores_for_subject
FROM scores
ORDER BY user_id, subject;
Combine each user's subject scores in one place.
+---------+---------+---------------------+-------+--------------------------------------------------------------------------------------------------------------------------------+
| user_id | subject | implementation_date | score | all_scores_for_subject |
+---------+---------+---------------------+-------+--------------------------------------------------------------------------------------------------------------------------------+
| 1 | sub01 | 2021-12-01 | 85 | [85, 90, 76, 67, 91, 98, 58, 83, 69, 87, 98, 91, 70, 91, 60, 55, 95, 92, 93, 78, 66, 94, 50, 84, 63, 56, 69, 83, 56, 93, 76] |
| 1 | sub01 | 2021-12-02 | 90 | [85, 90, 76, 67, 91, 98, 58, 83, 69, 87, 98, 91, 70, 91, 60, 55, 95, 92, 93, 78, 66, 94, 50, 84, 63, 56, 69, 83, 56, 93, 76] |
| 1 | sub01 | 2021-12-03 | 76 | [85, 90, 76, 67, 91, 98, 58, 83, 69, 87, 98, 91, 70, 91, 60, 55, 95, 92, 93, 78, 66, 94, 50, 84, 63, 56, 69, 83, 56, 93, 76] |
.
.
.
We were able to confirm that each user's subject scores were summarized.
JSON_OBJECTAGG
Combines the values in a group into one by JSON format.
The output can be in key:value format.
SELECT
implementation_date,
subject,
user_id,
score,
JSON_OBJECTAGG(user_id, score) OVER (PARTITION BY implementation_date, subject ORDER BY user_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as scores_for_everyone
FROM scores
ORDER BY implementation_date, subject, user_id;
Combines the scores of all users in one place for each subject and implementation date.
+---------------------+---------+---------+-------+------------------------------------------------+
| implementation_date | subject | user_id | score | scores_for_everyone |
+---------------------+---------+---------+-------+------------------------------------------------+
| 2021-12-01 | sub01 | 1 | 85 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78} |
| 2021-12-01 | sub01 | 2 | 85 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78} |
| 2021-12-01 | sub01 | 3 | 78 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78} |
| 2021-12-01 | sub01 | 4 | 93 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78} |
| 2021-12-01 | sub01 | 5 | 78 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78} |
| 2021-12-01 | sub02 | 1 | 83 | {"1": 83, "2": 81, "3": 70, "4": 50, "5": 76} |
| 2021-12-01 | sub02 | 2 | 81 | {"1": 83, "2": 81, "3": 70, "4": 50, "5": 76} |
.
.
.
| 2021-12-31 | sub03 | 5 | 61 | {"1": 79, "2": 96, "3": 67, "4": 100, "5": 61} |
+---------------------+---------+---------+-------+------------------------------------------------+
