14 游戏玩法分析 II

[astak16]

题目

查找出每一个玩家首次登录的设备名称

create table activity (
	player_id int,
	device_id int,
	event_date date,
	games_played int
);

insert into activity values
(1, 2, '2016-03-01', 5),
(1, 2, '2016-05-02', 6),
(1, 3, '2017-06-25', 1),
(3, 1, '2016-03-02', 0),
(3, 4, '2018-07-03', 5);

SQL：方法一

select activity.player_id, activity.device_id from activity, (
	select player_id, min(event_date) as first_login
	from activity group by player_id
) as temp
where activity.player_id = temp.player_id
and activity.event_date = temp.first_login;

解析

• 先是用子查询查出每个 player_id 最小的 event_date 命名为 temp 临时表
• activity 和 temp 连查，筛选出 player_id 相等，并且 activity.event_date = temp.first_login 相等的数据

SQL：方法二

select player_id, device_id from (
	select
		player_id,
		device_id,
		dense_rank() over(partition by player_id order by event_date) as 排名
	from activity
) as temp where temp.排名 = 1;

解析

这个方法和 游戏玩法分析 I 中的方法二一样。

SQL：方法三

select player_id, device_id from activity a1 where event_date <= all(
	select a2.event_date from activity a2 where a1.player_id = a2.player_id
);

解析

使用 all 配合 <= 筛选出 a1.player_id = a2.player_id 的 event_date 数据

Tips

all 方法使用