#534. 游戏玩法分析III

https://leetcode-cn.com/problems/game-play-analysis-iii/submissions/

  1. +--------------+---------+
  2. | Column Name  | Type    |
  3. +--------------+---------+
  4. | player_id    | int     |
  5. | device_id    | int     |
  6. | event_date   | date    |
  7. | games_played | int     |
  8. +--------------+---------+
  9. （player_id，event_date）是此表的主键。
  10. 这张表显示了某些游戏的玩家的活动情况。
  11. 每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0 ）。

编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。详细情况请查看示例。

SELECT player_id, event_date, 
  SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) games_played_so_far
FROM Activity
ORDER BY player_id, event_date

SUM() OVER () 与 SUM() GROUP BY 的区别

  1. sum() over () 能够在聚合时候保留原行数，不被删除。在这一题中，我们看到输出结果要根据以往的行数来做一个累加，同时不能删除数据。
  2. sum() group by 在聚合的时候，每一个类别只能保留一个最终的结果。无法做到累加的功能。

#550. 游戏玩法分析IV

https://leetcode-cn.com/problems/game-play-analysis-iv/

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）。

编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

# Write your MySQL query statement below

SELECT ROUND( COUNT( DISTINCT CASE WHEN a.times=1 and a.gap_day=1 THEN a.player_id END)
             /COUNT( DISTINCT a.player_id), 2 ) fraction
             FROM
             (
               SELECT player_id, event_date, games_played,
                 RANK() OVER (PARTITION BY player_id ORDER BY event_date) times,
                 DATEDIFF( LEAD(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date),
                        event_date) gap_day
               FROM Activity
             ) a

#569. 员工薪水中位数

https://leetcode-cn.com/problems/median-employee-salary/

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| company      | varchar |
| salary       | int     |
+--------------+---------+
Id是该表的主键列。
该表的每一行表示公司和一名员工的工资。

写一个SQL查询，找出每个公司的工资中位数。
以 任意顺序 返回结果表。

SELECT a.id, a.company, a.salary
FROM
(
  SELECT id, company, salary,
    ROW_NUMBER() OVER (PARTITION BY company ORDER BY salary) salary_rank,
  COUNT(salary) OVER (PARTITION BY company) cnt
  FROM Employee
) a
WHERE a.salary_rank IN (cnt/2, cnt/2+1, cnt/2+0.5)

#570. 至少有5名直接下属的经理

https://leetcode-cn.com/problems/managers-with-at-least-5-direct-reports/

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| department  | varchar |
| managerId   | int     |
+-------------+---------+
Id是该表的主键列。
该表的每一行都表示雇员的名字、他们的部门和他们的经理的id。
如果managerId为空，则该员工没有经理。
没有员工会成为自己的管理者。

编写一个SQL查询，查询至少有5名直接下属的经理 。
以 任意顺序 返回结果表。

SELECT e.name
FROM 
(
  SELECT id, name, department, managerId,
    ROW_NUMBER() OVER (PARTITION BY managerId) number_employee
  FROM Employee
) a JOIN Employee e
ON a.managerId=e.id
WHERE a.number_employee = 5

SELECT name
FROM Employee
WHERE id in(
  SELECT managerId
  FROM Employee
  GROUP BY managerId
  HAVING count(DISTINCT id)>=5
)

#571. 给定数字的频率查询中位数

https://leetcode-cn.com/problems/find-median-given-frequency-of-numbers/

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
| frequency   | int  |
+-------------+------+
num 是这张表的主键。这张表的每一行表示某个数字在该数据库中的出现频率。

中位数 是将数据样本中半数较高值和半数较低值分隔开的值。
编写一个 SQL 查询，解压 Numbers 表，报告数据库中所有数字的 中位数 。结果四舍五入至 一位小数 。

# Write your MySQL query statement below

SELECT avg(a.num) median
FROM
(
  SELECT num,
    SUM(frequency) OVER (ORDER BY num) sum_up,
    SUM(frequency) OVER (ORDER BY num DESC) sum_down
  FROM Numbers
) a
WHERE a.sum_up >= (SELECT sum(frequency)/2 FROM Numbers)
  AND a.sum_down >= (SELECT sum(frequency)/2 FROM Numbers)

当知道频率时，计算中位数的小技巧

原理

+----------+-------------+-------------+-------------+
| Number | Frequency | sum1(asc) | sum2(desc) |
+----------+-------------|-------------+-------------|
| 0            | 5        | 5         | 10        |
| 1        | 1        | 6         | 5         |
| 2        | 3        | 9         | 4         |
| 3        | 1        | 10        | 1         |
+----------+-------------|-------------+-------------|
当sum1和sum2都大于等于总数的1/2时，Number的平均数就是中位数。
  • 当sum1和sum2都大于等于总数的1/2时，Number的平均数就是中位数。

今日知识点

  1. sum() over () 函数
  2. rank / lead 窗口函数
  3. 中位数的概念
    1. 知道频率求中位数
    2. 知道数据求中位数
  4. having函数的应用
  5. datediff() 的使用
  6. row_number() 窗口函数的使用