- solution
- SELECT player_id, MIN(event_date) AS first_login
- FROM Activity
- GROUP BY player_id;
512. 游戏玩法分析 II
- SELECT player_id, device_id
- FROM Activity
- WHERE (player_id, event_date) IN (SELECT player_id, MIN(event_date)
- FROM Activity
- GROUP BY player_id);
HAVING 不行的原因
having 子句执行在 select 之后, 因此 having 中的字段必须在 select 子句中, event_date 没有再 select 子句里, 所以不行
534. 游戏玩法分析 III
- SELECT a1.player_id, a1.event_date, SUM(a2.games_played) AS games_played_so_far
- FROM Activity a1, Activity a2
- WHERE a1.player_id = a2.player_id
- AND a1.event_date>= a2.event_date
GROUP BY a1.player_id, a1.event_date -- 这一定要有 a1.event_date, 否则 Result 会根据 player_id 自动合并
ORDER BY a1.player_id, a1.event_date;
另一种方法:
- SELECT player_id, event_date,
- CASE WHEN @prev = player_id THEN @cnt := @cnt + games_played
- WHEN @prev := player_id THEN @cnt := games_played
- END 'games_played_so_far'
- FROM (SELECT player_id, event_date, games_played
- FROM activity
- ORDER BY player_id, event_date) a,
- (SELECT @cnt := 0, @prev := null) t;
550. 游戏玩法分析 IV
方法一
- SELECT ROUND(COUNT(DISTINCT player_id)/(SELECT COUNT(DISTINCT player_id)
- FROM Activity), 2) AS fraction
- FROM Activity
- WHERE (player_id, event_date) IN(SELECT player_id, DATE(MIN(event_date)+1)
- FROM Activity
- GROUP BY player_id);
方法二
- SELECT ROUND(SUM(CASE WHEN DATEDIFF(a.event_date, b.first_date)=1 THEN 1 ELSE 0 END) / (SELECT COUNT(DISTINCT player_id)
- FROM Activity), 2) AS fraction
FROM Activity a, -- 千万不要漏掉这个逗号!!
- (SELECT player_id, MIN(event_date) AS first_date
- FROM Activity
- GROUP BY player_id) b
- WHERE a.player_id = b.player_id;
569. 员工薪水中位数
SELECT b.id,b.company,b.salary
-- 3. 连接结果
FROM (
-- 1. 按 company 分组排序, 记为 `rk`
- SELECT id,company,salary,
- CASE @com WHEN company THEN @rk:[email protected]+1 ELSE @rk:=1 END rk,
- @com:=company
- FROM employee,(SELECT @rk:=0, @com:='') a
- ORDER BY company,salary) b
- LEFT JOIN
(-- 2. 计算各 company 的记录数除以 2, 记为 `cnt`
- SELECT company,COUNT(1)/2 cnt FROM employee GROUP BY company) c
- ON b.company=c.company
-- 4. 找出符合中位数要求的记录
- WHERE b.rk in (cnt+0.5,cnt+1,cnt);
- [SQL]511+512+534+550+569
来源: http://www.bubuko.com/infodetail-3486387.html