亲宝软件园·资讯

展开

[SQL]511+512+534+550+569

whiky 人气:0
# 511. 游戏玩法分析 I ![](https://img2020.cnblogs.com/blog/1948911/202003/1948911-20200331152457514-1749315417.png) **solution** ```mysql SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id; ``` # 512. 游戏玩法分析 II ![](https://img2020.cnblogs.com/blog/1948911/202003/1948911-20200331153257375-725029509.png) ```mysql 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 ![](https://img2020.cnblogs.com/blog/1948911/202003/1948911-20200331154108685-1064583385.png) ```mysql 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; ``` **另一种方法:** ```mysql 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 ![](https://img2020.cnblogs.com/blog/1948911/202003/1948911-20200331213418606-838097580.png) **方法一** ```mysql 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); ``` **方法二** ```mysql 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. 员工薪水中位数 ![](https://img2020.cnblogs.com/blog/1948911/202003/1948911-20200331224427091-1345209532.png) ![](https://img2020.cnblogs.com/blog/1948911/202003/1948911-20200331224436737-751336580.png) ```mysql SELECT b.id,b.company,b.salary -- 3. 连接结果 FROM ( -- 1. 按 company 分组排序,记为 `rk` SELECT id,company,salary, CASE @com WHEN company THEN @rk:=@rk+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); ```

加载全部内容

相关教程
猜你喜欢
用户评论