SQL ZOO窗口滞后#8

Question

问:对于每天至少有1000例新病例的每个国家，显示新病例数量高峰期的日期。

Here is a few sample data of the covid table。我写的：

SELECT name,date,MAX(confirmed-lag) AS PeakNew 
FROM(
    SELECT name, DATE_FORMAT(whn,'%Y-%m-%d') date, confirmed,
    LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn) lag  
    FROM covid 
    ORDER BY  confirmed
) temp
GROUP BY name
HAVING PeakNew>=1000
ORDER BY PeakNew DESC;

我得到的结果很奇怪，PeakNew看起来是正确的，但相关的日期却不是。

My answer

The right answer

任何人都可以帮助得到正确的答案吗？谢谢!

Answer 1

下面的查询非常适合我。尽管日期和值是正确的，但由于顺序不同，输出将显示不同的结果。这里的顺序是先按日期，然后按名称。

SELECT z1.name, DATE_FORMAT(c.dt,'%Y-%m-%d'), z1.nc
FROM 

(
SELECT z.name, MAX(z.nc) AS 'mx'
FROM (
SELECT DATE(whn) AS 'dt', name, confirmed - LAG(confirmed,1) OVER(PARTITION BY name ORDER BY DATE(whn) ASC) AS 'nc'
FROM covid ) z
WHERE z.nc >= 1000
GROUP BY z.name
) z1

INNER JOIN

(
SELECT DATE(whn) AS 'dt', name, confirmed - LAG(confirmed,1) OVER(PARTITION BY name ORDER BY DATE(whn) ASC) AS 'nc'
FROM covid
) c

ON c.nc = z1.mx
AND c.name = z1.name

ORDER BY 2 ASC

Answer 2

外部查询中的date值并不对应于找到MAX(confirmed-lag)的行-它只是该组中的一个随机日期值。请查看这篇博客文章中标题为“ONLY_FULL_GROUP_BY问题”的部分：https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/以获取更多信息。

我使用ROW_NUMBER()函数来获取与最大新案例相对应的整行。然而，我的最终结果并没有按照答案的方式排序，也没有具体说明它应该如何排序，所以我仍然没有得到令人满意的快乐表情符号。

Answer 3

您需要自行加入才能获取最大计数发生的日期：

WITH CTE1 as
(SELECT name,DATE_FORMAT(whn, "%Y-%m-%d") as date,
   confirmed - LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY DATE(whn)) as increase
 FROM covid
ORDER BY whn),
CTE2 AS
(SELECT name, MAX(increase) as max_increase
FROM CTE1
WHERE increase >999
GROUP BY name
ORDER BY date)
SELECT c1.name,c1.date,c2.max_increase as peakNewCases
FROM CTE1 as c1
JOIN CTE2 as c2
ON c1.name=c2.name AND c1.increase=c2.max_increase