気象×SQL 〜SQLをはじめからていねいに〜

最近SQLにさわる機会が増えてきたんで、復習がてらSQLの基本的な文法をまとめておくっ
#1.　SQLとは
SQL（Structured Query Language）はデータベースを操作するときに用いられる言語である。また、データベースに対する問合せ・命令をクエリという。
##1-1.　データベースについて
▶リレーショナルデータベース（RDB）：データを複数のテーブルとして管理し、複雑なデータの関連性を扱えるようにした管理方式のこと。

▶データウェアハウス（DWH）：列指向データベースで大規模なビッグデータの集計や分析の際に用いられる。

▶データベースマネジメントシステム（DBMS）：データベースの構築・運用・管理を行うソフトフェアのこと。

##1-2.　SQLの基本的な文法
SQLはISO（国際標準化機構）で規格化が行われているため、どんな種類のデータベースに対しても基本的に同じ文法で操作可能。主な命令は以下の通り。

|命令|機能|
|:---:|:---:|:---:|
|SELECT|データ検索|
|INSERT,UPDATE,DELETE|データ登録・更新・削除|
|CREATE,ALTER,DROP,GRANT|テーブル作成・更新・削除・権限付与|
|DISTINCT|重複を除く|
|WHERE|フィルタをかける|
|ORDER BY|並び替え|
|JOIN|異なるテーブルの列を結合する|
|UNION,ABS,DATEDIFF|四則演算・集合演算・日付演算|
|COUNT,SUM,MIN,MAX|集計関数|

##1-3.　今回使用する気象データとRDB
テーブル作成のために使用したデータは気象庁のAMeDAS時別データ(2014/12/1 0:00 ~ 2015/1/31 23:00)とAMeDAS地点の位置情報のデータ

左側：amedasテーブル（基本的にはこのテーブルを扱う）　右側：locationテーブル
※temp→気温、rain→降水量、wspd→風速、wdir→風向、なお取得地点はつくばと東京。
そして今回はこちらの記事を参考にSQLite3とDBeaverでデータベースを操作していきます。

自分は多少のデータ整形をした後に以下の手順でcsvをインポートしてみました。（utf-8形式じゃないと文字化けする）

sqlite>create table テーブル名(カラム名 データ型,　カラム名 データ型,・・);
sqlite>.mode csv
sqlite>.separator , #区切り文字を変更
sqlite>.import csvデータ テーブル名

#2.　SELECT文 ##2.1　LIMIT句 ```sql --amedasテーブルからdatetime,station_name,tempのカラムに対して3レコード取得する SELECT datetime ,station_name ,"temp" FROM amedas LIMIT 3; ```

▶tempは予約語なので、それと区別するためにダブルクォテーションで囲んだほうが良い
##2.2　COUNT関数・DISTINCT文・AS句

/*
 amedasテーブルのstation_nameカラムに対して
 「重複を許した場合」と「重複を除いた場合」のレコードを取得する
  なお、AS句で別名にする
 */
SELECT
	COUNT(station_name) AS name
	,COUNT(DISTINCT station_name) AS distinct_name
FROM amedas;

▶今回は2014/12/1 0:00 ~ 2015/1/31 23:00の1488×2地点分（つくばと東京）のデータとなっている ##2.3　ORDER BY句 ```sql /* amedasテーブルの全てのカラムを5レコード取得する ただし、tempについて昇順に並び替える */ SELECT * FROM amedas ORDER BY "temp" LIMIT 5; ```

▶昇順がASC、降順がDESC（何も指定しない場合はASC）
▶複数列で並び替えたい場合は、 カラム名 ASC, カラム名 DESC・・のように指定する
#3.　WHERE句(条件検索)
##3.1　比較条件

--amedasテーブルでwdir(風向)が西のstation_name、temp、wdirを5レコード取得する
SELECT
    station_name
    ,"temp"
    ,wdir
FROM amedas 
WHERE wdir = '西'
LIMIT 5;

▶比較演算子を使うことで2つの値の間の論理的な関係を指定できる

|比較演算子|意味|
|:---:|:---:|:---:|
|=|等しい|
|<>, !=|等しくない|
|>=|以上|
|<=|以下|
|>|より大きい|
|<|より小さい|
##3.2　論理条件

--amedasテーブルでstation_nameが東京かつwdir(風向)が西のデータ総数を取得する
SELECT
    COUNT(*)
FROM amedas 
WHERE station_name = '東京' AND wdir = '西';

▶AND→ORの順に処理される (A OR B) AND C　のようにすればORが優先される ##3.3　LIKE句 ```sql --amedasテーブルでwdir(風向)に「西」を含むデータを５レコード取得する SELECT * FROM amedas WHERE wdir LIKE '%西%' LIMIT 5; ```

▶文字列があるパターンに合致しているかをチェックする

|パターン文字|意味|
|:---:|:---:|:---:|
|%|任意の0文字以上の文字列|
|_(アンダースコア)|任意の1文字|
%○○○%：「○○○」を含む文字列
%○○○ ：「○○○」で終わる文字列
○○○% ：「○○○」で始まる文字列

▶%や_を含む文字列をLIKEで検索したい場合はESCAPE句を用いる
##3.4　BETWEEN演算子

/*
amedasテーブルでdatetimeが2015/1/1 0:00 ~ 2015/1/20 23:00までのデータについて
datetime,station_name,tempのカラムを取得する
*/
SELECT
    datetime
    ,station_name
    ,"temp"
FROM amedas 
WHERE datetime BETWEEN '2015/1/1 0:00' AND '2015/1/20 23:00';

▶WHERE datetime >= '2015/1/1 0:00' AND datetime <= '2015/1/20 23:00'と同義
##3.5　IN句

-- amedasテーブルでwdir(風向)が'西'または'南'である行をカウントする
SELECT
    COUNT(*)
FROM amedas 
WHERE wdir IN ('西','南')
ORDER BY date;

▶IN / NOT IN ある値が列挙した値のどれかと等しいかを判定 / ある値が列挙した値のどれとも一致しないことを判定 ▶ANY / ALL演算子 値リストとそれぞれ比較していずれかが真なら真 / 値リストとそれぞれ比較して、全て真なら真 #4.　GROUP BY句 ##4.1　集計関数 ```sql /* amedasテーブルでstaion_name毎にグループ化したレコードに対して レコード数とtemp(気温)の平均値、最大値、最小値を求める ただし、wdir(風向)が'西','南'かつtemp(気温)が空白でないデータを対象とし、 さらにレコード数の昇順に並び替える */ SELECT station_name ,COUNT(*) AS ct ,AVG("temp") AS avg_temp ,MAX("temp") AS max_temp ,MIN("temp") AS min_temp FROM amedas WHERE wdir IN ('西','南') AND "temp" NOT IN ('') GROUP BY station_name ORDER BY ct; ``` ▶グループ集計に置いてSELECTで指定できるもの 1.　GROUP BYで指定されている基準列 2.　集計関数の集計対象

|集計関数|意味|
|:---:|:---:|:---:|
|COUNT|レコード数|
|SUM|合計値|
|AVG|平均値|
|MAX|最大値|
|MIN|最小値|
|VARIANCE|分散|
|STDDEV|標準偏差|
##4.2　HAVING句

/*
amedasテーブルでstaion_name毎にグループ化し、
temp(気温)の平均値を求め、その平均値が5.0以下であるものを取得する
*/
SELECT 
    station_name
   	,ROUND(AVG("temp"),0) AS avg_temp
FROM amedas
WHERE "temp" NOT IN ('')
GROUP BY station_name
HAVING AVG("temp") < 5.0;

▶WHEREは元のテーブルに対する絞り込み、グループ化する前の検索条件 一方、HAVINGは集計結果に対する絞り込み、グループ化した後の抽出条件 ▶ROUND(値,桁数)で指定した桁数に数値を丸める（四捨五入） FLOOR（切り捨て）やCEILING（切り上げ）もある

以上ここまでをまとめると、SQLの実行順序は大まかに以下のような流れである。

|FROMで処理対象テーブルを選択|
|:---:|:---:|
|↓|
|WHEREによる絞り込み|
|↓|
|GROUP BYによるグループ化|
|↓|
|HAVINGによる絞り込み|
|↓|
|SELECT|
|↓|
|ORDER BYによるデータの並び替え|
|↓|
|LIMITによる絞り込み|
#5.　条件式・文字列関数・型変換
##5.1　条件式

/*
amedasテーブルでrain(降水量)を次の条件に従って天気フラグを作成する
①rain > 0.0：雨 or 雪　②それ以外：晴 or 曇
*/
SELECT 
    station_name
   	,rain
   	,CASE
   		WHEN rain == 0 OR rain == '--' THEN '晴 or 曇'
   		ELSE '雨 or 雪'
   	END AS '天気'
FROM amedas;

▶条件分岐で値を変換する CASE 　　　WHEN 条件1 THEN 返り値 　　　WHEN 条件2 THEN 返り値・・・ END (AS 別名) ##5.2　文字列関数・型変換 ```sql /* amedasテーブルでdatetimeの'/'→'-'に置換し、西暦部分を取り出す また、temp(気温)float型→int型に変換 */ SELECT datetime ,REPLACE(datetime,'/','-') AS re_datetime ,SUBSTR(datetime,1,4) AS yyyy ,station_name ,"temp" AS float_temp ,CAST("temp" AS int) AS int_temp FROM amedas

<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/354874/e6358059-2aa9-473e-7ed7-e8077b0ebc86.png width=80%>
▶REPLACE関数は文字列の一部を別の文字列に置換する
REPLACE(データ列,置換前の文字列,置換後の文字列)

▶SUBSTR関数は文字列の一部分だけを取り出せる
SUBSTR(データ列,開始位置,抽出文字数)

▶CAST関数はデータ型を変換する
CAST(データ列 AS 変換後のデータ型)

|数値型|int,tinyint,bigint|符号付き整数|
|:---:|:---:|:---:|
|**数値型**|**float,real**|**浮動小数点数値**|
|**文字列型**|**char(n)**|**固定長の文字列(nはバイト数)**|
|**文字列型**|**varchar(n)**|**可変長の文字列(nはバイト数)**|
|**日付型**|**date**|**日付**|
|**日付型**|**datetime**|**日付時刻**|
|**論理値型**|**boolean**|**真(True)or偽(False)**|
#6.　サブクエリ
##6.1　単一行サブクエリ
```sql
/*
amedasテーブルでtemp(気温)が最も低いデータの
datetime,station_name,tempをサブクエリを用いて取得する
*/
SELECT 
    datetime
    ,station_name
    ,"temp"
FROM amedas
WHERE "temp"=(SELECT MIN("temp") FROM amedas)

▶SQL文の一部分にクエリそのものを条件として代入し、クエリが入れ子（ネスト）構造となったものを**サブクエリ・副問合せ**という ▶WHEREの他にSELECTの選択列リストにも記述可能 ##6.2　複数行サブクエリ ```sql /* amedasテーブルにおいてwspd(風速)が5.0m/sより 大きかった日時をstation_name別にカウントする */ SELECT station_name ,COUNT(*) AS ct FROM amedas WHERE wspd IN (SELECT wspd FROM amedas WHERE wspd > 5.0) GROUP BY station_name; ``` ▶検索結果がn行1列の複数の値となるサブクエリ ▶複数の値と比較する際にはIN演算子、ANY/ALL演算子を用いる ##6.3　表形式の結果となるサブクエリ ```sql /* amedasテーブルにおいてtemp(気温)が0度未満の日時を station_name毎にカウントし、それを平均する */ SELECT AVG(ct_temp) FROM (SELECT station_name ,COUNT("temp") AS ct_temp FROM amedas WHERE "temp" < 0 AND "temp" IS NOT '' GROUP BY station_name) AS t; ``` ▶検索結果がn行m列の表となるサブクエリ ##6.4　WITH句 ```sql /* 先ほどと同様にamedasテーブルにおいてtemp(気温)が0度未満の日時を station_name毎にカウントし、それを平均する ただし、サブクエリの代わりにWITH句を用いる */ WITH t AS( SELECT station_name ,COUNT("temp") AS ct_temp FROM amedas WHERE "temp" < 0 AND "temp" IS NOT '' GROUP BY station_name ) SELECT AVG(ct_temp) FROM t; ``` ▶WITH句（WITH問合せ）はサブクエリに相当する部分を元のSQLから切り離す ▶入れ子構造のサブクエリと比較すると、可読性が高い ▶WITH句はカンマで区切ることで複数続けて書くことも可能 exp) WITH t1 AS(・・・),t2 AS(・・・) SELECT ・・・ #7.　テーブル作成・結合 ##7.1　テーブル作成 ○テーブルを作る

|構文|意味|
|:---:|:---:|:---:|
|CREATE TABLE <テーブル名> ();|テーブルの作成|
|CREATE ALL ON <テーブル名> TO PUBLIC;|作成したテーブルに権限を付与|
○テーブルを消す

|構文|意味|
|:---:|:---:|:---:|
|DROP TABLE <テーブル名>;|テーブルの削除|
|DROP TABLE IF EXISTS <テーブル名>;|テーブルが存在している場合のみ削除|
○一時テーブルを作る

|構文|意味|
|:---:|:---:|:---:|
|CREATE TEMPORARY TABLE <テーブル名> ();|セッション中に一時的に作成|

CREATE TEMPORARY TABLE tmp_2015 AS
SELECT 
    *
FROM amedas
WHERE datetime >= '2015/1/1 0:00';

SELECT
	*
FROM tmp_2015;

##7.2　テーブル結合 複数のテーブルを共通項目（キー）を用いて1つにまとめることをテーブル結合といい、結合に用いる共通項目はER図から調べたりする

/*
amedas,locationテーブルを用いて
それぞれのpref,station_nameをキーにして内部結合する
*/
SELECT
	datetime
	,a.pref
	,a.station_name
	,l.address
	,l.lat
	,l.lng
FROM amedas AS a
INNER JOIN "location" AS l
ON a.pref=l.pref AND a.station_name=l.station_name
ORDER BY datetime;

▶INNER JOINは共通項目をキーとし、一致するレコードのみ取り出す FROM テーブルA INNER JOIN テーブルB ON 結合条件

▶内部結合はデータ件数の不一致が生じてしまう場合があるため、結合相手がいない場合にもデータを保持したいときには**外部結合(LEFT JOIN)**を用いる（対応するレコードがない場合は全てNULLとなる）

▶**.(ドット)は助詞「〜の」という意味があり、今回のような紐付けるキー名が同じである時は.**(ドット)が必要
#8.　ウィンドウ関数
ウィンドウ関数を用いることでより効率的に分析用のクエリを作成できる

カテゴリ	関数例	使用例
集計関数群	SUM,COUNT,AVG,MIN,MAX,MEDIANなど	累積集計を計算する
ランク付け関数群	ROW_NUMBER,DENSE_RANK,RANKなど	ウィンドウ内のランキングを計算する
ラグ・リード関数群	LAG,LEAD,FIRST_VALUE,LAST_VALUE,NTH_VALUEなど	前後のレコードを取得する
レポート関数群	RATIO_TO_REPORT,CUME_DIST,NTILE,PERCENTILE_DISCなど	ウィンドウ内の構成比を計算する
線形回帰関数群	STDDEV_SAMP,VAR_SAMP	統計量を計算する
##8.1　集計関数群

/*
amedasテーブルで、station_nameで区切ったパーティション毎に
AVG,MIN,MAXで集計を行う
*/
SELECT
	datetime
	,station_name
	,"temp"
	,AVG("temp") OVER(PARTITION BY station_name) AS avg_temp
	,MIN("temp") OVER(PARTITION BY station_name) AS min_temp
	,MAX("temp") OVER(PARTITION BY station_name) AS max_temp
FROM amedas
WHERE "temp" IS NOT '';

▶結果の行を集約するGROUP BY句と違って、テーブル式の全ての行が保持される。 ##8.2　ランク付け関数群 ```sql /* amedasテーブルで、station_nameおよび西暦で区切ったパーティション毎に temp(気温)の降順で連番を振る、ただし、ランク2までを表示する */ WITH t1 AS( SELECT datetime ,SUBSTR(datetime,1,4) AS yyyy ,station_name ,"temp" FROM amedas WHERE "temp" IS NOT '' ), t2 AS( SELECT datetime ,yyyy ,station_name ,"temp" ,ROW_NUMBER() OVER(PARTITION BY station_name, yyyy ORDER BY "temp" DESC) AS rk FROM t1 ) SELECT datetime ,station_name ,"temp" ,rk FROM t2 WHERE rk <= 2; ```

|ランク付け関数|処理内容|
|:---:|:---:|:---:|
|ROW_NUMBER|ORDER BYで重複しても気にせずに連番を振る|
|DENSE_RANK|ORDER BYで重複したレコードには同じ番号を振り、以降は連番が続く|
|RANK|ORDER BYで重複したレコードには同じ番号を振るが、以降の連番は重複した分番号がとぶ|
##8.3　ラグ・リード関数群

/*
amedasテーブルで、station_nameで区切ったパーティション毎に
temp(気温)について1つ置きにLAGをとる、さらにラグの差(気温の時間差)をとり、
1時間後の気温低下率が大きい上位5つを取得する
*/
WITH t1 AS(
	SELECT
		datetime
		,LAG(datetime,1) OVER(PARTITION BY station_name) AS lag1_datetime
		,station_name
		,"temp"
		,LAG("temp",1) OVER(PARTITION BY station_name) AS lag1_temp
	FROM amedas
),t2 AS(
	SELECT
		datetime
		,lag1_datetime
		,station_name
		,"temp"
		,lag1_temp
		,"temp" - lag1_temp AS temp_diff
	FROM t1
	WHERE "temp" - lag1_temp IS NOT NULL
),t3 AS(
	SELECT
		datetime
		,lag1_datetime
		,station_name
		,"temp"
		,lag1_temp
		,temp_diff
		,ROW_NUMBER() OVER(PARTITION BY station_name ORDER BY temp_diff) rk
	FROM t2
)
SELECT
	station_name
	,datetime
	,lag1_datetime
	,"temp"
	,lag1_temp
	,temp_diff
	,rk
FROM t3
WHERE rk<=5;

|ランク・リード関数|処理内容|
|:---:|:---:|:---:|
|LAG(カラム名,n)|ORDER BYの順でn行前のレコードの値を取得|
|LEAD(カラム名,n)|ORDER BYの順でn行後のレコードの値を取得|
|FIRST_VALUE(カラム名)|ORDER BYの順で最初のレコードの値を取得|
|LAST_VALUE(カラム名)|ORDER BYの順で最後のレコードの値を取得|

#9.　その他
##9.1　EXISTS句

-- locationテーブルで、station_nameがamedasテーブルにも存在しているものを抽出する
SELECT
	station_name
	,address
	,lat
	,lng
FROM "location" l
WHERE EXISTS(
		SELECT
			station_name
		FROM amedas a
		WHERE l.station_name=a.station_name)
/*
ちなみにIN句でも同じことが実現できる
SELECT
	station_name
	,address
	,lat
	,lng
FROM location l
WHERE l.station_name IN(
		SELECT
			a.station_name
		FROM amedas a)
*/

▶IN句よりも処理速度が速いことで知られている ▶NOT EXISTS句による非存在検査もある ##9.2　縦持ち・横持ち ○横持ち：一般的なデータ保持の仕方で可読性が高い ○縦持ち：データを行単位で保持、SQLは行に対する処理を得意とするため、列数が多い場合などは縦持ちの方が高速に処理できることがある

/* 横持ち　→　縦持ち変換
amedasテーブルのwspd(風速)・wdir(風向)を、
それぞれのカラム名をinfo、値をvalueとして縦持ちに変換する
*/
CREATE TEMPORARY TABLE tmp_tbl AS
	SELECT
		datetime
		,station_name
		,'wspd' AS info
		,wspd AS value
	FROM amedas
	UNION ALL
	SELECT
		datetime
		,station_name
		,'wdir' AS info
		,wdir AS value
	FROM amedas
	ORDER BY datetime,station_name;

▶UNION ALLは複数のテーブルから取得した結果セットを一つに結合する ▶重複する行を1つにまとめずに、そのまま抽出する ▶UNIONだけを指定すると重複行は削除される

/* 縦持ち　→　横持ち変換
上の一時テーブルtmp_tblを横持ちに変換する
*/
SELECT
	datetime
	,station_name
	,MAX(CASE WHEN info='wspd' THEN value END) AS wspd
	,MAX(CASE WHEN info='wdir' THEN value END) AS wdir
FROM tmp_tbl
GROUP BY datetime,station_name
ORDER BY datetime,station_name;

▶MAX()でNULLを対象から外している

#参考サイト
・https://www.dbonline.jp/sqlite/

日付関数とか使えなかったのでちょいちょい追記してくかもです