【MySQL】LeetCode 511&512
2020-02-18 本文已影响0人
每天要读书的Claire
Activity表，主键(player_id, event_date)
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
511 游戏玩法分析I
找出每个玩家第一次登录的日期
mysql> select player_id,min(event_date) as first_event_date
-> from activity
-> group by player_id;
+-----------+------------------+
| player_id | first_event_date |
+-----------+------------------+
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
+-----------+------------------+
3 rows in set (0.00 sec)
512 游戏玩法分析II
找出每个玩家第一次登录的日期
mysql> select player_id,device_id from ( select player_id,device_id,row_number()over(partition by player_id order by event_date ) as rn from activity ) t where rn =1;
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
+-----------+-----------+
3 rows in set (0.00 sec)
