mysql> select * from activity
+-----------+-----------+------------+--------------+
| 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 |
+-----------+-----------+------------+--------------+
5 rows in set (0.00 sec)
534 游戏玩法分析3
查询玩家在该日期之前所玩的游戏场数
mysql> select player_id,event_date,sum(games_played)over(partition by player_id order by event_date) as games_played_so_far
-> from activity
-> order by player_id,event_date;
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1 | 2016-03-01 | 5 |
| 1 | 2016-05-02 | 11 |
| 2 | 2017-06-25 | 1 |
| 3 | 2016-03-02 | 0 |
| 3 | 2018-07-03 | 5 |
+-----------+------------+---------------------+
5 rows in set (0.00 sec)
550 游戏玩法分析4
mysql> select * from activity
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
5 rows in set (0.00 sec)
mysql> select round(count(distinct player_id)/(select count(distinct player_id)from activity ),2) as fraction
-> from( select player_id,event_date,
-> lead(event_date) over(partition by player_id)as next_log_date
-> from activity ) T
-> where next_log_date is not null and datediff(next_log_date,event_date)=1;
+----------+
| fraction |
+----------+
| 0.33 |
+----------+
1 row in set (0.00 sec)
网友评论