For example, "In a user's event log, I want to extract the last event the user performed." Sometimes you want to retrieve the last record of data divided by groups.
The window function can be used to obtain the following
WITH tbl AS ( SELECT * ,ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY event_time DESC ) AS row_num FROM event_log ) SELECT * FROM tbl WHERE row_num=1
The group unit is specified with "PARTITION BY", the order is specified with "ORDER BY", and a non-duplicate sequential number is assigned with "ROW_NUMBER()".
Set the group unit and sort order so that the sequential number is 1, i.e., the first record in the group is the record you want to seek, and extract only those with a sequential number of 1.
In the above example, the records are arranged in descending chronological order by user unit, so that the record with the number 1 is the last record in chronological order.
application
If you want to retrieve the first record made by the user, you can do so by arranging them in ascending order.
- 「ORDER BY event_time ASC」
If you want to retrieve the last event that a user accessed on that day, you can do so by grouping by day in addition to by user.
- 「PARTITION BY user_id, DATE(event_time)」
To retrieve the last user accessed that day, group by day and retrieve the last record in the time series.
- PARTITION BY DATE(event_time)"
- 「ORDER BY event_time DESC」
