Window LAG/ja

Language:Project:Language policy	English  • 日本語

COVID-19 データ

データに関する注記： このデータは、Rodrigo Pombo が行った作業をもとに、ジョンズ・ホプキンス大学 および 世界保健機関 の情報を基に構築されました。このデータは2020年4月21日に構築されたものであり、このデータセットを最新の状態に保つ計画はありません。

Window関数

SQLのWindow関数には、LAG、LEAD、RANK、NTILEが含まれます。これらの関数は行の「窓（ウィンドウ）」上で動作します。窓とは通常はテーブル内で何らかの意味で隣接する行です。

covid テーブルの紹介

この例ではWHERE節を2020年の3月のイタリア'Italy'の症例を表示するのに使用している

スペインSpainのデータを表示する様に修正する

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

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

LAG関数の紹介

MySQLに関する注意： MariaDBエンジンを使用している場合、https://jira.mariadb.org/browse/MDEV-23866 のバグに遭遇します。 [訳者注 この問題はバージョン 10.11.6-MariaDB-1:10.11.6+maria~ubu2204 で解決済み]

• 代わりにMicrosoft SQL Serverエンジンを使用できます。
• 各クエリの前に以下の行を含めることができます：

SET @@sql_mode='ANSI';

LAG関数は、テーブルで前の行のデータを表示するために使用されます。 行を並べる際、データは国名nameによってパーティション分割され、日付whnのデータによって順序付けられます。つまり、イタリアのデータのみが考慮されます。

クエリを修正して、前日の陽性confirmedを表示する。

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

SELECT name, DAY(whn), confirmed,
   LAG(confirmed, 1) OVER (partition by name ORDER BY whn) AS dbf
 FROM covid
WHERE name = 'Italy'
AND MONTH(whn) = 3 AND YEAR(whn) = 2020
ORDER BY whn

LAG(遅延)操作

以下は、前日の症例を示す正しいクエリです:

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

LAG列の値が、斜め左上の行の値と一致していることに注意してください。

name	DAY(whn)	confirmed	dbf
Italy	1	1694	null
Italy	2	2036	1694
Italy	3	2502	2036
Italy	4	3089	2502
Italy	5	3858	3089
Italy	6	4636	3858
Italy	7	5883	4636
Italy	8	7375	5883
Italy	9	9172	7375
Italy	10	10149	9172
...

新規感染者数

確認された症例数は累積値だが、LAGを使用して各日ごとに報告された新規症例数を復元できる。

イタリアItalyの3月における各日の新規症例数を表示する。

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

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

毎週の変化

収集されたデータは推定値でどうしても不正確になる。しかし、より長い間隔をあけることで、その影響をいくらか軽減できる。 データは、WEEKDAY(whn) = 0 で月曜日の数値のみを表示するようにフィルタリングできる。

2020年のイタリアの各週の新規症例数を表示する - 月曜日のみ表示。

SELECT name, DATE_FORMAT(whn,'%Y-%m-%d'), confirmed
 FROM covid
WHERE name = 'Italy'
AND WEEKDAY(whn) = 0 AND YEAR(whn) = 2020
ORDER BY whn

SELECT name,DATE_FORMAT(whn,'%Y-%m-%d'),
  confirmed-LAG(confirmed,1) OVER (ORDER BY whn) "new this week"
  FROM covid
 WHERE name='Italy' and WEEKDAY(whn) = 0 AND YEAR(whn) = 2020

JOINを利用した遅延処理

テーブルを日付演算を使用してJOINすることができる。データが欠落している場合、結果は異なる。

イタリアの各週の新規症例数を表示する - 月曜日のみを表示する。

サンプルクエリでは、DATE_ADD関数を使用して今週（tw）を先週（lw）とJOINする。

SELECT tw.name, DATE_FORMAT(tw.whn,'%Y-%m-%d'), 
 tw.confirmed, lw.confirmed
 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'
ORDER BY tw.whn

SELECT tw.name, DATE_FORMAT(tw.whn,'%Y-%m-%d'), 
 tw.confirmed - lw.confirmed
 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

RANK()関数

このクエリは、2020年4月20日における確認された症例数と世界の症例ランキングを表示する。COVIDによる死亡者数も表示される。 米国が最も多く、スペインが2位である… スペインが確認された症例数で2番目に多い一方で、イタリアはウイルスによる死亡者数で2番目に多いことに注意する。

COVIDによる死亡者数のランキングを表示する列を追加する。

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

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

感染率

このクエリは、ワールドテーブルをJOINすることで各国の総人口にアクセスし、感染率（10万人あたりの症例数）を計算する。

各国の感染率ランキングを表示する。人口が少なくとも1000万人以上の国のみを含む。

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

SELECT 
   world.name,
   ROUND(100000*confirmed/population,2),
   RANK() OVER (ORDER BY 100000*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

峠を越える

1日あたり少なくとも2万件の新規症例を記録した各国について、国名、新規症例のピーク日、ピーク値を表示する。

SELECT name,DATE_FORMAT(whn,'%Y-%m-%d'),
  newCases AS peakNewCases
FROM (
SELECT name,whn,newCases,
  RANK() OVER 
    (PARTITION BY name ORDER BY newCases DESC) rnc
FROM
(
  SELECT name, whn,
     confirmed -
     LAG(confirmed, 1) OVER
      (PARTITION BY name ORDER BY whn) as newCases
   FROM covid
) AS x
) AS y
WHERE rnc = 1 AND newCases>=20000
ORDER BY name