【達人に学ぶSQL徹底指南書メモ】1. CASE 式のススメ

1.1 複数列の最大値

複数行の中から最大値/最小値を選ぶのは簡単。
適当なキーで GROUP BY して、MAX/MIN を取るだけ。

複数列の中から最大値/最小値を選ぶとき、CASE 式が有効。
※ Oracle, PostgreSQL, MySQL の場合は、GREATEST/LEAST を使えば一撃。

Greatests

key	x	y	z
A	1	2	3
B	5	5	2
C	4	7	1
D	3	3	8

x と y の最大値を取得する

SELECT
  key,
  CASE
    WHEN x < y THEN y
    ELSE x
  END AS greatest
FROM
  Greatests;

結果

key	greatest
A	2
B	5
C	7
D	3

x, y, z の最大値を求める

SELECT
  key,
  CASE
    WHEN 
      CASE WHEN x < y THEN y ELSE x END < z
    THEN 
      z
    ELSE 
      CASE WHEN x < y THEN y ELSE x END
  END AS greatest
FROM
  Greatests;

ネストが深くなるとコードが読みづらいので、行持ちに変換して MAX 関数を使うのもアリ。

SELECT key, MAX(col)
FROM (
  SELECT key, x AS col
  FROM Greatests
  UNION ALL
  SELECT key, y AS col
  FROM Greatests
  UNION ALL
  SELECT key, z AS col
  FROM Greatests
) AS tmp
GROUP BY key;

結果

key	greatest
A	3
B	5
C	7
D	8

churuchuru

 1.2 行列変換行持ちから列持ちへの水平展開する。
PopTbl2


pref_name
sex
population


徳島
1
60

徳島
2
40

香川
1
100

香川
2
100

愛媛
1
100

愛媛
2
50

高知
1
100

高知
2
100

福岡
1
100

...
...
,,,

SELECT
  sex AS '性別',
  SUM(population) AS '全国',
  SUM(
    CASE
      WHEN pref_name = '徳島' THEN population
      ELSE 0
    END
  ) AS '徳島',
  SUM(
    CASE
      WHEN pref_name = '香川' THEN population
      ELSE 0
    END
  ) AS '香川',
  SUM(
    CASE
      WHEN pref_name = '愛媛' THEN population
      ELSE 0
    END
  ) AS '愛媛',
  SUM(
    CASE
      WHEN pref_name = '高知' THEN population
      ELSE 0
    END
  ) AS '高知',
  SUM(
    CASE
      WHEN pref_name IN('徳島', '香川', '愛媛', '高知') THEN population
      ELSE 0
    END
  ) AS '四国'
FROM
  PopTbl2
GROUP BY
  sex;
結果


性別
全国
徳島
香川
愛媛
高知
四国


男
855
60
100
100
100
360

女
845
40
100
50
100
290

pref_name	sex	population
徳島	1	60
徳島	2	40
香川	1	100
香川	2	100
愛媛	1	100
愛媛	2	50
高知	1	100
高知	2	100
福岡	1	100
...	...	,,,

性別	全国	徳島	香川	愛媛	高知	四国
男	855	60	100	100	100	360
女	845	40	100	50	100	290

churuchuru

1.3 ORDER BY でソート列を作る

Greatests

key	x	y	z
A	1	2	3
B	5	5	2
C	4	7	1
D	3	3	8

B-A-D-C の順番に並べる。

SELECT
  key
FROM
  Greatests
ORDER BY
  CASE key
    WHEN 'B' THEN 1
    WHEN 'A' THEN 2
    WHEN 'D' THEN 3
    WHEN 'C' THEN 4
    ELSE NULL
  END;

結果

key
B
A
D
C

SELECT
  key,
  CASE key
    WHEN 'B' THEN 1
    WHEN 'A' THEN 2
    WHEN 'D' THEN 3
    WHEN 'C' THEN 4
    ELSE NULL
  END AS sort_col
FROM
  Greatests
ORDER BY
  sort_col;

結果

key	greatest
B	1
A	2
D	3
C	4