3.9 ロングとワイド

データベースの管理や集計加工に適したテーブルのレイアウトと, 閲覧に適したレイアウトは必ずしも一致しません. 例えば, 部署別の平均年齢のテーブルはタテに伸びています. もしも部署別平均年齢を集計していた場合, 各部署を列に並べることも考えられます. このように, 1つの列に格納されている数値を複数の列に広げた状態をワイドあるいは横持ち, 逆に複数列を1列に集約した状態をロングまたは縦持ちといいます. ロングからワイドにすることをピボット (pivot) といい, ワイドからロングにすることをアンピボット (unpivot) といいます(どちらもピボットと呼ぶこともあります). 以下は presto の場合の書き方ですが, ピボット/アンピボット処理はSQLの実装によってかなり異なります. presto の場合は, あらかじめピボット後の列名を指定する必要があります. このテーブルだけではこの構文の恩恵が分かりづらいですが, 例えば年度と部署ごとに集計し, 部署についてピボット集計する場合は便利です. 以下のクエリでは, 代用として, 擬似的に年度を表す列を追加しています.

WITH tab1 as (
SELECT 2018 AS year,  t2.id,  COALESCE(t2.name,  '不明') AS name,  t1.num
FROM (
  SELECT department_id,  count(1) as num
  FROM employees
  GROUP BY department_id
) t1
LEFT JOIN
  departments t2
ON t1.department_id = t2.id
ORDER BY t2.id
)
SELECT
  year, 
  kv['総務部'] AS affairs, 
  kv['人事部'] AS hr, 
  kv['開発部'] AS rd, 
  kv['営業部'] AS sales, 
  kv['不明'] AS unknown
FROM (
  SELECT year,  MAP_AGG(name,  num) kv
  FROM tab1
  GROUP BY year
) t