SQLチートシート

迷った時にこれを見れば自分でSQLを組み立てることができる優れものです。

WHERE 節の使用例としてフランス France の人口 population を表示するSQLが書いてある。 注）文字列（短いテキストデータ）はこの様に'シングルクオート'で囲む。

SELECT population FROM world
WHERE name = 'Germany'

いずれかの値に当てはまるかIN,AまたはBまたはCのような(キーワード IN に続くリスト中のそれぞれのアイテムについて表示する。)

SELECT name, population FROM world
WHERE name IN ('Sweden', 'Norway', 'Denmark ');

範囲の指定BETWEEN(小さすぎも大きすぎもしない国は？ BETWEEN で範囲を指定する(境界値を範囲に含む)。

面積が 200,000 ～ 250,000 の国の国名と面積を表示するように修正。
SELECT name, area FROM world
WHERE area BETWEEN 250000 AND 300000

人口が２億人（200000000 ゼロが８個ある）以上の国の名前を表示

ELECT name FROM world
WHERE population >= 200000000

あいまい検索(NOT LIKE)

name NOT LIKE '%a%' で特定の文字を含む国を除外できる。
SELECT name
FROM world
WHERE name LIKE 'B%'
AND name NOT LIKE '%a%'

あいまい検索(LIKE)

国名に 'United'　を含む国の国名を特定する。
SELECT name FROM world where name LIKE 'United%'

あいまい検索(_のところには何か一文字入る)

SELECT name FROM world where name LIKE 'United'

ファーストネームが John の受賞者を表示

SELECT winner
FROM nobel
WHERE winner LIKE 'John%'

ロシア（Russia）よりも人口(population)が多い国の名前を表示する

SELECT name FROM world
WHERE population >
(SELECT population FROM world
WHERE name='Romania')

GROUP BYはWHEREとORDER BYの間に書きます。

GROUP BYのところには、グルーピングをしたいカラムを記述します。
SELECTには、グルーピングをしたいカラム,集計関数を記述します。
集計関数とは、合計やカウント、最大値、最小値などを求めることができる関数です。
集計関数のことは、集約関数といったりもします。
SELECT
商品名
, avg(売上金額)
, min(売上金額)
, max(売上金額)
FROM test_table
GROUP BY 商品名

SELECT グルーピングをするカラム,集計関数
FROM テーブル名
WHERE 条件式
GROUP BY グルーピングをするカラム
ORDER BY ソート条件

GROUP BYとORDER BYを併用することで、この問題を解決しましょう。

ORDER BYのあとに、集計している記述を書きましょう。
それでは実行します。
売上金額を昇順に並び替えることができました。
このように、GROUP BYとORDER BYを組み合わせて使いたい場合は、集計関数まで含めたカラムをORDER BYに指定しましょう。
SELECT 商品名, sum(売上金額)
FROM test_table
GROUP BY 商品名
ORDER BY sum(売上金額)

-LEFTとは左側、つまりFROMで指定した側のテーブルを指します。このテーブル側を軸に情報を表示するのがLEFT JOINです
SELECT * FROM team LEFT OUTER JOIN user ON team.team_id = user.team_id;

LEFT JOINで複数テーブルを結合する

• LEFT JOINの結合
  SELECT e.empno, e.empname, t.empno tokyo, o.empno osaka
  FROM emp e
  LEFT JOIN tokyo_aut t ON e.empno = t.empno
  LEFT JOIN osaka_aut o ON e.empno = o.empno
  WHERE e.empno < 'A100';

複数列の値が一致しているところを指定してJOINする場合

(empテーブルのempno列とname列、tokyo_autテーブルのempno列とname列が同じ箇所をJOINする)
SELECT e.empno, e.empname, t.empno tokyo, o.empno osaka
FROM emp e
LEFT JOIN tokyo_aut t ON e.empno = t.empno AND e.name = t.name;

昇順、降順ソート(並び順を変える)

• 昇順(1,2,3,・・・)
  SELECT *
  FROM scores
  ORDER BY Score

• 降順(10,9,8,・・・)
  SELECT *
  FROM scores
  ORDER BY Rank DESC

• orderで２つカラムを選択すると左のカラムから順番にソートする
  SELECT *
  FROM scores
  ORDER BY Rank, Score DESC

• グループごとにソート
  SELECT Rank
  , Score
  FROM scores
  GROUP BY Rank
  ORDER BY Score DESC

行の指定(LIMIT)

• 五行分取得する
  SELECT * FROM students LIMIT 5
• ORDER BY,WHEREと一緒に使う場合はLIMITより前に入れる
  SELECT * FROM students WHERE branch = '池袋' ORDER BY student_id LIMIT 5

数値演算

以下サイトを参考
https://style.potepan.com/articles/26162.html

NULL判定(IS NULL,IS NOT NULL)

• mobile_phoneがNULLの生徒を一覧表示
  SELECT * FROM students WHERE mobile_phone IS NULL;

• mobile_phoneがNULLではない生徒を一覧表示
  SELECT * FROM students WHERE mobile_phone IS NOT NULL;

##SQLの正規化表現
https://kino-code.com/sql19/
https://bigdata-tools.com/sql-like/

SELECT文が処理される順番

https://qiita.com/k_0120/items/a27ea1fc3b9bddc77fa1
FROM句
↓
JOIN句
↓
WHERE句
↓
GROUP BY句
↓
HAVING句
↓
WINDOW句
↓
SELECT句
↓
ORDER BY句
↓
LIMIT句

小計と合計をつける(ROLLUP)

参照元：https://qiita.com/q1701/items/2321b9a8674d9796af5b
KEY1, KEY2 で集計しつつ KEY2 の小計をとる
小計を取りたい列を ROLLUP() 演算子に指定する。

SELECT key1, key2, COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, ROLLUP(key2)
ORDER BY key1, key2

複数列を組み合わせた小計（１）
KEY1ごとのKEY2, KEY3の小計をとる。KEY2, KEY3の小計については更にKEY2ごとのKEY3の小計をとる。

SELECT key1, key2, key3, COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, ROLLUP(key2, key3)
ORDER BY key1, key2, key3

小計と合計をつける(ROLLUP)

• 試験の会と科目ごとの平均点を小数点付きで出力(postgreSQL)
  --
  SELECT exam_no, subject, AVG(score)
  FROM exams
  GROUP BY ROLLUP(exam_no,subject) --小計と合計をつける時の書き方
  ORDER BY exam_no, subject NULLS LAST --NULLの値を最後に表示するように書いている

列の一部の値で集計する(SUBSTRING())

• SUBSTRING(列名,取得するはじめの番号,取得終わりの番号)
• SUBSTRING(name,1,4):これはname列の1番目の桁から４番目桁までを取得する
• 例
  SELECT SUBSTRING(home_phone,1,2), COUNT(*) FROM students GROUP BY SUBSTRING(home_phone,1,2);

列ごとに異なる条件で集計する(CASE,GROUP BY)

• 例
  SELECT exam_no,subject,AVG(score) FROM exams GROUP BY exam_no,subject;
  実行例(試験番号と科目ごとの平均点)
  |exam_no | subject | AVG(score)|
  |1 |国語 |87.9 |
  |2 |数学 |83.9 |
  |3 |英語 |85.9 |

• 列ごとに異なる条件で集計する例
  SELECT
  exam_no,
  AVG(CASE subject WHEN '国語' THEN score ELSE NULL END) AS 国語,
  AVG(CASE subject WHEN '英語' THEN score ELSE NULL END) AS 英語,
  AVG(CASE subject WHEN '数学' THEN score ELSE NULL END) AS 数学,
  FROM exams
  GROUP BY exam_no;
  実行結果例
  |exam_no|国語 |英語 |数学|
  |1 |87.9|83.9|85.9|

集約関数

AVG：平均
SUM：合計
MAX：最大値
MIN：最小値
COUNT：件数
COUNTD:個別の件数(重複なし)

複数の列でグループ化する

• group byを使用する

• GROUP BYを使用するときはSELECTにかけるのはGROUP BYで指定した列と、集約関数だけ

集約結果で絞り込む(HAVING)

• 科目別の平均点が80点以上の生徒
  FROM students
  JOIN exams ON students.student_id = exams.student_id
  GROUP BY students.student_id, student_name, subject
  HAVING AVG(score) >= 80
  ORDER BY students.student_id;

テーブルを縦に繋げたい(UNION,UNION ALL)

UNIONでは「重複を除去するための処理」が行われる
UNION ALLは「重複があっても構わない、ないとわかっている」時に使うほうがいい
例

• UNIONの場合(重複が取り除かれる)
  SELECT name, tell from staff;
  UNION
  SELECT name, tell FROM mentor;

• UNION ALLの場合(重複あり)
  SELECT name, tell from staff;
  UNION ALL
  SELECT name, tell FROM mentor;

-列の数、肩の調整
列の数が違うときは''を間に入れて調整する
例

• UNION ALLの場合(重複あり)
  SELECT name, corce_subject AS subject, tell, '' AS number from staff;
  UNION ALL
  SELECT name, '', tell, number FROM mentor;

CASE式(単純case,検索case)

• 例
  SELECT exam_no,subject,AVG(score) FROM exams GROUP BY exam_no,subject;
  実行例(試験番号と科目ごとの平均点)
  |exam_no | subject | AVG(score)|
  |1 |国語 |87.9 |
  |2 |数学 |83.9 |
  |3 |英語 |85.9 |

• 列ごとに異なる条件で集計する例()
  SELECT
  exam_no,
  AVG(CASE subject WHEN '国語' THEN score ELSE NULL END) AS 国語,
  AVG(CASE subject WHEN '英語' THEN score ELSE NULL END) AS 英語,
  AVG(CASE subject WHEN '数学' THEN score ELSE NULL END) AS 数学,
  FROM exams
  GROUP BY exam_no;
  実行結果例
  |exam_no|国語 |英語 |数学|
  |1 |87.9|83.9|85.9|

• 単純case(tsukin_kubunの値によって場合分けする)
  SELECT
  id,
  name,
  CASE tsukin_kubun
  WHEN 0 THEN 'walk'
  WHEN 1 THEN 'train'
  WHEN 2 THEN 'car'
  ELSE 'others'
  END AS tsukin
  FROM members_tsukin.

• 検索case
  SELECT
  id,
  name,
  CASE
  WHEN tsukin_kubun=0 THEN 'walk'
  WHEN tsukin_kubun=1 THEN 'train'
  WHEN tsukin_kubun=2 THEN 'car'
  ELSE 'others'
  END AS tsukin
  FROM members_tsukin.

• WHERE句でCASEを使う
  --c1,c2,c3の優先順位でyellowが入っている人を表示
  --c1に値が入っていたらc1の値
  --c2に値が入っていたらc2の値
  --さもなくばc3の値とyellowを比較

SELECT * FROM members_color
WHERE
(CASE
WHEN c1 IS NOT NULL THEN c1
WHEN c2 IS NOT NULL THEN c2
ELSE c3
END) = 'yellow';

1980年の　化学 Chemistry と医学 Medicine 以外で、賞の年度、分野、名前を表示

SELECT
year AS 賞の年度,
category AS 分野,
laureate_name AS 受賞者の名前
FROM
nobel_prizes
WHERE
year = 1980
AND category NOT IN ('Chemistry', 'Medicine');

【ORDER BYで２つの要素で並べ替え】Userナイトの受賞者リストを表示する。 Sir. で始まる受賞者の 受賞者、年、分野　を表示する。 年が新しい順に、同年内では名前順に表示する

SELECT
laureate_name AS 受賞者,
year AS 年,
category AS 分野
FROM
nobel_prizes
WHERE
laureate_name LIKE 'Sir.%'
ORDER BY
year DESC,
CASE
WHEN year = year THEN laureate_name
END;

SELECT name, route, saved_date, price
FROM customer
ORDER BY route ASC, price DESC;

ブラジル'Brazil' と同じ大陸にある各国のリストを求める(()ないのSQLで取得される情報は一つだけ)

SELECT name FROM world WHERE continent =
(SELECT continent
FROM world WHERE name = 'Brazil')

実行結果が複数の値となる場合をサブクエリでとる

例：ブラジル'Brazil'　またはメキシコ'Mexico'　と同じ大陸にある各国とその大陸をリストアップする。
SELECT name, continent FROM world
WHERE continent IN
(SELECT continent
FROM world WHERE name='Brazil'
OR name='Mexico')

一つの値だけが求められるようなサブクエリは、SELECT 文のフィールで利用できる。

例：中国 China　の人口 population をイギリス United Kingdom の何倍あるかで表示する
SELECT
population/(SELECT population FROM world
WHERE name='United Kingdom')
FROM world
WHERE name = 'China'

指定された日付から1日前、1ヶ月前、1年前、および10日前の日付を取得するために、以下のようなSQLを使用できます。

このSQLでは、WITH 句を使用して一時的なテーブル dates を作成し、各日付を計算しています。その後、SELECT 文でこの一時的なテーブルから日付を取得しています。
WITH
dates AS
(
SELECT CURRENT_DATE AS today,
CURRENT_DATE - INTERVAL '1' day AS one_day_ago,CURRENT_DATE - INTERVAL '1' month AS one_month_ago,CURRENT_DATE - INTERVAL '1' year AS one_year_ago,CURRENT_DATE - INTERVAL '10' day AS ten_days_ago
)

SELECT * FROM dates

実行結果
today 2024-03-20
one_day_ago 2024-03-19
one_month_ago 2024-02-20
one_year_ago 2023-03-20
ten_days_ago 2024-03-10

前日日付を取得する

WITH
previous_dates AS --前日の日付を取得する
(
SELECT date_format(CURRENT_DATE - INTERVAL '1' day, '%Y%m%d') AS one_day_ago
)

#　自分の思う日付のフォーマットに変えて出力する
WITH
previous_dates AS --前日の日付を出す
(
SELECT date_format(CURRENT_DATE - INTERVAL '1' day, '%Y%m%d') AS one_day_ago
)

→たとえ元の列に入っている値が　YYYY-MM-DD hhssみたいな形でも
この場合出力はYYYYMMDDになる

Tableauディクショナリーを使用して1ヶ月で増加したデータソースのサイズを取得します。

WITH previous_month AS (
SELECT
SUM(size) AS previous_size
FROM
information_schema.tables
WHERE
table_schema = 'public'
AND DATE(created_at) >= DATE_FORMAT(NOW() - INTERVAL '1' MONTH, '%Y-%m-01')
AND DATE(created_at) < DATE_FORMAT(NOW(), '%Y-%m-01')
),
current_month AS (
SELECT
SUM(size) AS current_size
FROM
information_schema.tables
WHERE
table_schema = 'public'
AND DATE(created_at) >= DATE_FORMAT(NOW(), '%Y-%m-01')
)
SELECT
current_size - previous_size AS increase_in_size
FROM
current_month
JOIN
previous_month ON 1=1;

---

User
table_schema = 'public'
AND DATE(created_at) >= DATE_FORMAT(NOW() - INTERVAL '1' MONTH, '%Y-%m-01')
AND DATE(created_at) < DATE_FORMAT(NOW(), '%Y-%m-01')

上記SQLはどのような動作をしているのでしょうか

table_schema = 'public': データベース内のスキーマが 'public' であるテーブルに絞り込みます。
DATE(created_at) >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01'): テーブルの作成日が直近の1ヶ月間（前月の1日から今日の1日の前）以上であることを条件としています。NOW() は現在の日時を返し、それから INTERVAL 1 MONTH を引くことで前月の日時を求めます。DATE_FORMAT() 関数は日付を指定したフォーマットに変換します。ここでは、年月日（'%Y-%m-01'）の形式に変換しています。
DATE(created_at) < DATE_FORMAT(NOW(), '%Y-%m-01'): テーブルの作成日が今月の1日より前であることを条件としています。現在の日付（NOW()）から取得した年月日の形式（'%Y-%m-01'）より前の日付であるテーブルを選択します。

---

Tableauディクショナリーを使用して今日と前日のデータソースサイズを比較し、増減とその量を取得します。また、SQL内でDATE関数を使用しています。

WITH previous_day AS (
SELECT
SUM(size) AS previous_size
FROM
information_schema.tables
WHERE
table_schema = 'public'
AND DATE(created_at) = DATE(NOW() - INTERVAL '1' DAY)
),
current_day AS (
SELECT
SUM(size) AS current_size
FROM
information_schema.tables
WHERE
table_schema = 'public'
AND DATE(created_at) = DATE(NOW())
)
SELECT
CASE
WHEN current_size > previous_size THEN '増加'
WHEN current_size < previous_size THEN '減少'
ELSE '変化なし'
END AS size_change,
ABS(current_size - previous_size) AS change_amount
FROM
current_day
JOIN
previous_day ON 1=1;

このクエリでは、まず previous_day と current_day というCTE（Common Table Expression）を使用して、前日と今日のデータソースサイズをそれぞれ取得します。次に、これらの情報を結合し、増減とその量を計算して表示します。

byteをキロバイト、メガバイト、ギガバイトで出力する

SELECT
byte_column / 1024 AS kilobytes,
byte_column / (1024 * 1024) AS megabytes,
byte_column / (1024 * 1024 * 1024) AS gigabytes
FROM
your_table;

ワークブックごとに最新の更新日とワークブック名を取得するSQLを以下に示します。

SELECT
    workbook_name,
    MAX(updated_at) AS latest_update
FROM
    your_table_name -- ワークブックの情報が格納されているテーブル名を指定してください
GROUP BY
    workbook_name

)

各分野の異なる受賞者の人数を表示する（同一人物の複数回受賞は1名と数える）

SELECT subject, COUNT(DISTINCT winner)
FROM nobel
GROUP BY subject

OUNT関数を使用して各分野の異なる受賞者の数を数えます。DISTINCTキーワードを使用して、各分野で異なる受賞者のみをカウントします。それぞれの分野について、結果をグループ化するためにGROUP BY句を使用します。

各分野の初受賞の年を表示する

SELECT subject, MIN(yr)
FROM nobel
GROUP BY subject

nobelテーブルから各分野の最初の受賞年を検索します。それぞれの分野について、最小の年を見つけるためにMIN関数を使用し、GROUP BY句で分野ごとに結果をグループ化します。