sqlzoo 9+.COVID 19 答案

有什么问题，欢迎评论或私聊。转载请私聊博主，谢谢。

原题链接：https://sqlzoo.net/wiki/Window_LAG

其他题解连接：https://blog.csdn.net/aiqiyizz/article/details/109057732

题解对应的是英文版题目。

窗口函数的应用，用的是新冠的数据。。。

9+ COVID 19

9+.1 Introducing the covid table

SELECT name, DAY(whn),
 confirmed, deaths, recovered
 FROM covid
WHERE name = 'Spain'
AND MONTH(whn) = 3
ORDER BY whn

9+.2 Introducing the LAG function

LAG窗口函数例子

SELECT name, DAY(whn), confirmed,
   LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn)
 FROM covid
WHERE name = 'Italy'
AND MONTH(whn) = 3
ORDER BY whn

9+.3 Number of new cases

统计新增病例。。

SELECT name, DAY(whn), confirmed - 
   LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn) AS 'new cases'
 FROM covid
WHERE name = 'Italy'
AND MONTH(whn) = 3
ORDER BY whn

9+.4 Weekly changes

统计每周新增病例

SELECT name, DATE_FORMAT(whn,'%Y-%m-%d'), confirmed - LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn) AS 'new cases'
 FROM covid
WHERE name = 'Italy'
AND WEEKDAY(whn) = 0
ORDER BY whn

9+.5 LAG using a JOIN

使用JOIN的方法实现LAG

SELECT tw.name, DATE_FORMAT(tw.whn,'%Y-%m-%d'), 
 tw.confirmed - lw.confirmed AS 'new cases'
 FROM covid tw LEFT JOIN covid lw ON 
  DATE_ADD(lw.whn, INTERVAL 1 WEEK) = tw.whn
   AND tw.name=lw.name
WHERE tw.name = 'Italy'
  AND WEEKDAY(tw.whn) = 0
ORDER BY tw.whn

9+.6 RANK()

rank的使用

SELECT 
   name,
   confirmed,
   RANK() OVER (ORDER BY confirmed DESC) rc,
   deaths,
   RANK() OVER (ORDER BY deaths DESC) rc
  FROM covid
WHERE whn = '2020-04-20'
ORDER BY confirmed DESC

9+.7 Infection rate

这题有个坑点，不能直接用它给定的感染率来排序，给定的已经四舍五入过的。

SELECT 
   world.name,
   ROUND(100000*confirmed/population,0) AS infect,
   rank() OVER(ORDER BY confirmed/population) AS rank
  FROM covid JOIN world ON covid.name=world.name
WHERE whn = '2020-04-20' AND population > 10000000
ORDER BY population DESC

9+.8 Turning the corner

输出格式要求：名字，日期（精确到日），新增峰值。
排序没尝试出来，正解也是错的，放弃了。非常诡异。
步骤：

1. 找出每天的日期以及新增病例
2. 找出新增病例最大的那天，这个方法在前面学习了很多，此处使用的是找rank1的方法。附加一个条件大于1000即可。
3. 排序方面，只看出第一优先度是日期。

SELECT
  name,
  dates,
  cases
FROM (
  SELECT
    *,
    RANK() OVER(
      PARTITION BY temp1.name
      ORDER BY
        temp1.cases DESC
    ) AS rank
  FROM
    (
      SELECT
        name,
        DATE_FORMAT(whn, '%Y-%m-%d') AS dates,
        confirmed - LAG(confirmed, 1) OVER (
          PARTITION BY name
          ORDER BY
            whn
        ) AS cases
      FROM
        covid
    ) temp1
) temp2
WHERE
  cases >= 1000
  AND rank = 1
ORDER BY 
  dates