Zenn
Open3

SQLを学んでいく

SQL
Database
DB
query
UgoUgo

selectでconcat

SELECT CONCAT(Name, '(', LEFT(Occupation, 1), ')')
FROM OCCUPATIONS
ORDER BY Name;

このように取得したデータを加工することができる。
LEFTは文字列の左側から指定した数の文字を取り出す。

  • Occupationが"Doctor"の場合 → "D"を返す
  • Occupationが"Actor"の場合 → "A"を返す

case

SELECT 
    MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor,
    MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor,
    MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer,
    MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor
FROM (
    SELECT Name, Occupation,
           ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn
    FROM OCCUPATIONS
) AS ranked
GROUP BY rn;

このようにcaseとwhenで条件を書き、thenで結果を出すことができる。
WHERE句やHAVING句でも使用可能。
ELSEでデフォルトを書くこともできる。

MAXを使用する理由は、GROUP BY句を使用してデータをグループ化するときに、各グループから1つの値を選ぶ必要があるため。

サブクエリ

FROMの中のクエリのことをサブクエリと呼ぶ。
サブクエリにASとしてエイリアス（別名）をつけることは、SQLでは必須。

PARTITION BY

PARTITION BYは「グループ分け」をする機能だが、GROUP BYとは少し異なる。
データベースでよく使う「窓関数（Window Function）」の一部。
以下のようなデータがあるとする。

店舗 商品 売上
東京 りんご 1000
東京 みかん 2000
大阪 りんご 1500
大阪 みかん 1800

PARTITION BY を使うことで各店舗ごとにグループ分けされ、その中で計算がされる。

SELECT 
    店舗,
    商品,
    売上,
    ROW_NUMBER() OVER (PARTITION BY 店舗 ORDER BY 売上) as 順位

結果は以下のようになる。

店舗 商品 売上 順位
東京 りんご 1000 1
東京 みかん 2000 2
大阪 りんご 1500 1
大阪 みかん 1800 2

主にROW_NUMBER()、RANK()、DENSE_RANK()などの窓関数と組み合わせて使用される。

UgoUgo

窓関数

ROW_NUMBER() OVER (ORDER BY 売上) -- 1, 2, 3...と連番
RANK() OVER (ORDER BY 売上) -- 1, 1, 3...と同順位があると次の順位をスキップ
DENSE_RANK() OVER (ORDER BY 売上) -- 1, 1, 2...と同順位があっても次の順位をスキップしない