GROUP BY 句の拡張機能
GROUP BY 句の CUBE 拡張機能を使用すると、調査データを 1 回参照するだ けで、調査データ全体における州別、性別、教育別の平均収入を計算し、state、 gender、education カラムの考えられるすべての組み合わせにおける平均収入 を計算することができます。CUBE 演算子を使用すると、たとえば、すべての 州における全女性の平均収入を計算したり、調査対象者全員の平均収入を、各 自の教育別および州別に計算したりすることができます。
CUBE でグループを計算するときには、計算されたグループのカラムに NULL 値が挿入されます。最初からデータベース内に格納されていた NULL なのか、 CUBE の結果として生成された NULL なのかを区別するためには、GROUPING 関数を使用する必要があります。GROUPING 関数は、指定されたカラムが上位 レベルのグループにマージされている場合は 1 を返します。
CUBE 例 2 次のクエリは、GROUP BY CUBE と GROUPING 関数を併用する 方法を示しています。
SELECT case grouping(state) WHEN 1 THEN 'ALL' ELSE state
END AS c_state, case grouping(sex) WHEN 1 THEN 'ALL'
ELSE sex end AS c_gender, case grouping(dept_id)
WHEN 1 THEN 'ALL' ELSE cast(dept_id as char(4)) end
AS c_dept, COUNT(*), CAST(ROUND(AVG(salary),2) AS
NUMERIC(18,2))AS AVERAGE
FROM employee WHERE state IN ('MA' , 'CA')
GROUP BY CUBE(state, sex, dept_id)
ORDER BY 1,2,3;
このクエリの結果は次のとおりです。クエリで指定されているとおり、小計 ローを示すために CUBE によって生成された NULL は、小計ロー内で ALL に 置き換えられています。
state | sex | dept_id | count | avg salary |
ALL | ALL | 100 | 19 | 58462.48 |
ALL | ALL | 200 | 14 | 50888.43 |
ALL | ALL | 300 | 9 | 59500.00 |
ALL | ALL | 400 | 16 | 43640.67 |
ALL | ALL | 500 | 9 | 33752.20 |
ALL | ALL | ALL | 67 | 50160.38 |
ALL | F | 100 | 6 | 58243.42 |
ALL | F | 200 | 5 | 59730.60 |
ALL | F | 300 | 7 | 59685.71 |
ALL | F | 400 | 8 | 41959.88 |
ALL | F | 500 | 4 | 29950.00 |
ALL | F | ALL | 30 | 50713.08 |
ALL | M | 100 | 13 | 58563.59 |
ALL | M | 200 | 9 | 45976.11 |
ALL | M | 300 | 2 | 58850.00 |
ALL | M | 400 | 8 | 45321.47 |
ALL | M | 500 | 5 | 36793.96 |
ALL | M | ALL | 37 | 49712.25 |
CA | ALL | 200 | 3 | 52200.00 |
58 | Sybase IQ |
