SQL文まとめ(MySQL)

# 参考
[集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析](https://www.amazon.co.jp/%E9%9B%86%E4%B8%AD%E6%BC%94%E7%BF%92-SQL%E5%85%A5%E9%96%80-Google-BigQuery%E3%81%A7%E3%81%AF%E3%81%98%E3%82%81%E3%82%8B%E3%83%93%E3%82%B8%E3%83%8D%E3%82%B9%E3%83%87%E3%83%BC%E3%82%BF%E5%88%86%E6%9E%90-%E3%81%A7%E3%81%8D%E3%82%8BDigital/dp/429501074X)


# テーブルイメージ
`sports`
id|num|name|kind|referee
-|-|-|-|-
1|5|"basketball"|"ball"|true
2|9|"baseball"|"ball"|true
3|1|"tennis"|"racket"|true
4|11|"soccer"|"ball"|true



`players`
id|sports_id|name
-|-|-
1|2|"Otan"
2|1|"Jordan"
3|1|"Hachi"



`teams`
id|sports_id|name
-|-|-
1|4|"Barce"

# コメント
```
-- コメント
# コメント
/*
コメント
*/
```

# 基本
```
SELECT * From sports # 全てのフィールドを取得

SELECT name, num From sports # 特定のフィールドを取得

SELECT * EXCEPT(name, num) From sports # 特定のフィールドを除いて取得
```
# 並び替え `ORDER BY` `DESC`
```
SELECT * From sports 
ORDER BY id # idの昇順

SELECT * From sports 
ORDER BY id DESC # idの降順

SELECT * From sports 
ORDER BY id, name DESC # 複数指定

SELECT name, num From sports
ORDER BY 1,2 # 得られるテーブルの列番号で指定
```
# 四則演算 `+ - * / `
```
SELECT (num+1)*2 From sports
SELECT num * team_num From sports
```
# 別名 `AS`
```
SELECT num * team_num AS play_num From sports # フィールドに別名をつける
SELECT num * team_num  play_num From sports # ASは省略可能
SELECT name, num people From sports # ASは省略可能
```
# 一部を取得 `LIMIT`
```
SELECT * From sports LIMIT 30
SELECT * From sports LIMIT 5 OFFSET 70 # 70~75番目を取得
```
# 条件式
`=` : 等しい
`<>`,`!=` : 等しくない
`>`,`>=` : 不等号
`AND`
`OR`
```
SELECT * From sports
where num = 5

SELECT * From sports
where num in (5,9,11)

SELECT * From sports
where num not in (5,9,11)
```

# LIKE
`%` : 任意の文字
`_` : 任意の一文字
`\` : エスケープ
```
SELECT * From sports
where name LIKE '%ボール'

SELECT * From sports
where name NOT LIKE '%ボール'

SELECT * From sports # 4以上5以下
where num BETWEEN 4 and 5
```
# null,true
`IFNULL` : nullの時、代わりに入れる値を指定できる
`COALESCE` : `IFNULL`とほぼ同じだが、引数にカラム名を指定できる
```
SELECT * From sports
where referee IS TRUE # falseもある

SELECT * From sports
where referee IS NULL

SELECT * From sports
where referee IS NOT TRUE # falseとnull

SELECT name, IFNULL(kind,"特殊") From sports # kindがnullの時,"特殊"を入れる
where referee IS NULL

SELECT name, COALESCE(kind,name) From sports # kindがnullの時,nameを入れる
where referee IS NULL
```
# テーブル無しでも実行できる
```
SELECT CURRENT_DATE AS today # CURRENT_DATEは、日付を取得する。それにtodayという名前を付ける
```

# グループ化
`COUNT(*)` : 数を数える
`COUNT(フィールド名)` : nullでない個数を数える
`COUNT(DISTINCT フィールド名)` : フィールド名が固有な値の個数を数える
`SUM` : 合計
`AVG` : 平均
`MAX` : 最大値
`MIN` : 最小値

`GROUP BY`をつけないと、テーブル全体を集約

標準偏差とかを求められる関数もある
```
SELECT kind, AVG(num) From sports
GROUP BY kind

SELECT kind, COUNT(num) From sports # kindごとに、このkindにしか無いnumの値を持っていたら、その個数を数える
GROUP BY kind

SELECT COUNT(num) From sports # テーブル全体のレコード数

``` 

# HAVING
```
SELECT kind, AVG(num) as average From sports
GROUP BY kind
HAVING average >= 5

SELECT kind, AVG(num) as avearage From sports
where name like '%ボール'
GROUP BY kind
HAVING average >= 5
ORDER BY 2
LIMIT 10
```

# 条件式 `IF`,`CASE`
```
SELECT if(birth_year > 1024,true,false ) as is_millennium, COUNT(*) From sports GROUP BY is_millennium# エクセルのif関数と同じ使い方

SELECT if(num in (1,2,3,4,5), "5人以下", "5人より多い") as more_5,COUNT(*)   From sports GROUP BY  more_5

SELECT
CASE num
WHEN 1 THEN "1対1"
WHEN 2 THEN "2対2"
WHEN 3 THEN "3対3"
ELSE "4人以上" as team_num, count(*)
From sports GROUP BY team_num

SELECT
CASE
WHEN num=1 THEN "1対1"
WHEN num=2 THEN "2対2"
WHEN num=3 THEN "3対3"
ELSE "4人以上" as team_num, count(*)
From sports GROUP BY team_num
```

# テーブルの結合
横に繋ぐ:`JOIN`
縦に繋ぐ:`UNION`
`INNER JOIN` : 両方にデータがあるものだけ取得
`LEFT OUTER JOIN` : 先に指定したテーブルにデータがあるもの全てを取得。後に指定したテーブルにデータがない部分はnullになる
`RIGHT OUTER JOIN`
`FULL OUTER JOIN` : 両方のテーブルの全レコードを残す
`CROSS JOIN` : 総当たりで結合。レコード10のテーブルとレコード15のテーブルからレコード150のテーブルができる。特定のレコードと、それ以外の全てのレコードを比べたいみたいな時とか
同じテーブル同士でも結合できる。前年の結果と今年の結果を横に並べたいとか
```
SELECT sp.id, pl.name FROM sports AS sp
INNER JOIN players AS pl
ON sp.id = pl.sports_id

SELECT sp.id, pl.name FROM sports AS sp
INNER JOIN players AS pl
USING (id) # sp.id = pl.id という意味になる

SELECT sp.id, pl.name FROM sports AS sp
LEFT OUTER JOIN players AS pl
ON sp.id = pl.sports_id

SELECT sp.id, pl.name FROM sports AS sp
RIGHT OUTER JOIN players AS pl
ON sp.id = pl.sports_id

SELECT sp.id, pl.name FROM sports AS sp
FULL OUTER JOIN players AS pl
ON sp.id = pl.sports_id

# 3つのテーブル
SELECT sp.id, pl.name, te.name FROM sports AS sp
JOIN players AS pl
ON sp.id = pl.sports_id
JOIN teams AS te
ON sp.id = te.sports_id
```

# 集合演算
- ほとんど同じテーブルが2つある時とかに使う
`UNION` : 和集合
`INTERSECT` : 積集合
`EXCEPT` : 差集合

```
SELECT id,name FROM players
UNION DISTINCT # 重複を許さない 今回の場合、同じスポーツで、選手の名前とチーム名が同じものがあった場合、片方しか表示されない
SELECT id,name FROM teams

SELECT sports_id,name FROM players
UNION ALL # 重複を許す
SELECT sports_id,name FROM teams

SELECT sports_id,name FROM players
INTERSECT DISTINCT
SELECT sports_id,name FROM teams

SELECT sports_id,name FROM players
EXECPT DISTINCT
SELECT sports_id,name FROM teams
```

# 仮想テーブル
- このSQLでだけ使えるテーブルを作れるイメージ
- 仮想テーブルに対してSQLを実行すると、仮想テーブルを作成するSQLも再実行される
```
# sports_nameという名の仮想テーブル作成
WITH sports_names AS (
SELECT name FROM sporsts;
)

```
# サブクエリ
```
SELECT * FROM sports
WHERE num > (SELECT AVG(num) FROM sports)

SELECT * FROM players
WHERE sports_id in (SELECT id FROM sports where num >= 5)
```

# RECORD型
- カラムの型の1つ
- フィールドにテーブルを入れられるイメージ
- `UNNEST`で普通のテーブルのように操作できる

# 関数
```
FLOOR(x) # xよりも小さい最大の整数 3.4=>3
CEIL(x) # x以上の最小の整数 3.4=>4
ROUND(x,3) # 小数第4位を四捨五入し、第3位に丸める。 1.2345=>1.235
ROUND(x,-3) # 整数3桁目を四捨五入し、4桁に丸める。 1234.5=> 1000
ABS(x) # 絶対値
CAST(x AS INT64) # 型変換
CAST(x AS FLOAT64)
CAST(x AS STRING)
CAST(x AS BOOL)
CAST(x AS DATE)
CONCAT("a","b") #=>"ab" 文字列の結合
SUBSTRING("abcdefg",3) #=>"cdefg" 3文字目以降を抜き出す
SUBSTRING("abcdefg",3,4) #=>"cdef" 3文字目から4文字を抜き出す
SUBSTRING("abcdefg",-3,2) #=>"ef" 後ろから3文字目から2文字を抜き出す
LEFT("abcdefg",3) #=>"abc"
RIGHT("abcdefg",3) #=>"efg"
INSTR("abcdefg","cd") #=>3 出現開始位置を返す
INSTR("abcdabcd","cd",4) #=>7 4文字目以降から探す
INSTR("abcdabcd","cd",4, 1) #=>7 4文字目以降、1番目に出てくるものを探す
STRPOS("abcdabcd","cd") # INSTRの第1,2引数のみの関数
REPLACE("abcdabcd","ab","置き換え") #=> "置き換えcd置き換えcd"
LENGTH("abcdefg") #=>7
(LENGTH(x)-LENGTH(REPLACE(x,"ab","")))/LENGTH("ab") # "ab"が何回出てくるか調べられる
```

# 正規表現
```
REGEXP_CONTAINS("abcd", r"^a") # 正規表現の文字列が含まれるかをbool型で返す
REGEXP_EXTRACT("abcd", r"^a") # 正規表現に当てはまる文字列を返す
REGEXP_EXTRACT("abcd", r"^a", 2, 3) # 2文字目以降で3番目に出てくる文字列を返す
REGEXP_REPLACE("abcd", r"^a", "置き換え") # 正規表現に当てはまる文字列を置き換える
```

# 日付
```
DATE(2025,12,25,12,25,55)
DATE("2025-12-25")
DATE(DATETIME("2025-12-25 12:25:55"))
CURRENT_DATE # DATE型 2025-12-25
CURRENT_DATE("Asia/Tokyo")
CURRENT_DATE ("+9")

# YEAR QUARTER MONTH WEEK DAYが使える
DATE_ADD(a,INTERVAL 1 MONTH) # aに追加
DATE_DIFF(a,b,YEAR) # a-bを年で返す
DATE_TRUNC(a,MONTH) # 2025-12-01 00:00:00のように、月単位で丸まる。月毎に集計したい時とかに便利

# YEAR QUARTER MONTH WEEK DAYOFYEAR DAY DAYOFWEEKが使える
EXTRACT(YEAR FROM a) # int型の要素を取り出す。特定の曜日だけとかで集計したい時に便利 
FORMAT_DATE("%A",a)

a+1 # 日を足し引きできる
```
||||
|---|---|---|
|%A|Sunday|
|%a|Sun|
|%B|December|
|%b|Dec|
|%c|Sun Dec 25 06:25:55 2025|
|%d|09|日付|
|%e|9|日付|
|%F|2025-12-25|
|%H|06|時刻
|%M|25|分
|%m|12|月
|%S|55|秒
|%T|06:25:55|
|%X|06:25:55|%Tと同じ
|%Y|2025

```
DATETIME(2025,12,25,12,25,55)
DATETIME("2025-12-25 12:25:55")
DATETIME(DATE("2025-12-25"))
CURRENT_DATIME # DATETIME型 2025-12-25T12:25:55.123456
CURRENT_DATETIME("Asia/Tokyo")
CURRENT_DATETIME("+9")

DATETIME_ADD(a,INTERVAL 1 MONTH) # YEAR QUARTER MONTH WEEK DAY HOUR MINUTE SECOND MILLISECOND MICROSECONDが使える
DATETIME_DIFF(a,b,YEAR) # a-bを年で返す YEAR QUARTER MONTH WEEK DAY HOUR MINUTE SECOND MILLISECOND MICROSECONDが使える
DATETIME_TRUNC(a,MONTH) YEAR QUARTER MONTH WEEK DAY HOUR MINUTE SECOND MILLISECOND MICROSECONDが使える

CURRENT_TIME # TIME型 12:25:55.123456
CURRENT_TIMESTAMP # TIMESTAMP型 2025-12-25T12:25:55.123456 UTC
```

# ウィンドウ関数

|||
|---|---|
RANK|順位
ROW_NUMBER|行番号
NTILE|均等な数に分割
FIRST_VALUE|最初の値
LAST_VALUE|最後の値
NTH_VALUE|任意の順番の値
LEAD|直後の値
LAG|直前の値
PRRCENTILE_COUNT|五数要約の指数
SUM|合計
AVG|平均

ウィンドウフレーム
範囲を絞れるイメージ
ORDER BYで並び替えられたものに対して適用される
|||
|---|---|
UNBOUNDED PRECEGIND|バーティションの上限
3 PRECEDING|現在の行から3行上
CURRENTROW|現在の行
3 FOLLOWING|現在の行から3行下
UNBOUNDED FOLLOWING|パーティションの下限

```
SELECT name, num, SUM(num) OVER (
PARTITION BY kind # 同じkind内でのSUMを取得できる
ORDER BY num# パーティション内でどのように並べ替えるかを指定できる
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW # WINDOWフレームという。 2つ前の行~現在の行のSUMを出す。 
# ROWではなくRANGEを用いると、行ではなく値が基準になる
# (2つ前の値~現在の値のSUM。numが5のレコードに対して、numが3~5の合計求める。)
)
FROM sports

```

## RANK
- 順位
```
SELECT name, num, 
RANK() OVER(
PARTITION BY kind # kindごとのnumランキング
ORDER BY num
)
FROM sports
```

## RANK_NUMBER
- 連番をつける
```
SELECT name, num, ROW_NUMBER() OVER(
PARTITION BY kind # kindごとに連番を振る
ORDER BY num
)
FROM sports
```

## NTILE
- ABC分析のようなことができる関数
- フィールドの数が均等になるように等分し、それぞれのグループに整数を割り振る

```
SELECT name, num, 
NTILE(10) OVER ( # フィールドを10個に分けて1~10の番号を振る。
ORDER BY num
)
FROM sports
```

## FIRST_VALUE
- 最初の値を取得
- ウィンドウフレームを使える
```
SELECT name FIRST_VALUE(name) OVER ( # スポーツごとの、idが一番小さい選手名を取得する
PARTITION BY sports_id
ORDER BY id
)
FROM 
```

## NTH_VALUE
- n番目の値を取得
- ウィンドウフレームを使える
```
SELECT name NTH_VALUE(name, 2) OVER ( # スポーツごとの、idが2番目に小さい選手名を取得する
PARTITION BY sports_id
ORDER BY id
)
```

## LAST_VALUE
- 最後の値を取得
- ウィンドウフレームを使える
```
SELECT name LAST_VALUE(name) OVER ( # スポーツごとの、idが一番大きい選手名を取得する
PARTITION BY sports_id
ORDER BY id
)
```

## LEAD
- 1つ下のレコードの情報を取得できる
```
SELECT name LEAD(name) OVER ( # スポーツごとの、1下のidの選手名を取得する
PARTITION BY sports_id
ORDER BY id
)
```

## LAG
- 1つ上のレコードの情報を取得できる
```
SELECT name LAG(name) OVER ( # スポーツごとの、1個上のidの選手名を取得する
PARTITION BY sports_id
ORDER BY id
)
```

## PERCENTILE_COUNT
- 五数要約
    - 最小値 : 0
    - 第1四分位数 : 0.25
    - 中央値 : 0.5
    - 第3四分位数 : 0.75
    - 最大値 : 1
```
SELECT name PERCENTILE_COUNT(num, 0.5) OVER ( # 種類ごとの、人数の中央値
PARTITION BY kind
ORDER BY num
)
```

## SUM
- 合計値を出す
- ウィンドウフレームを使える
```
SELECT name, num, SUM(num) OVER (  # 同じkind内でのSUMを取得できる
PARTITION BY kind
)
FROM sports
```

## AVG
- 平均値を出す
- ウィンドウフレームを使える
```
SELECT name, num, AVG(num) OVER (  # 同じkind内でのAVGを取得できる
PARTITION BY kind
)
FROM sports
```