「大人の教養・知識・気付き」を伸ばすブログ

一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。目下、データ分析・語学に力点を置いています。

MENU

データベースとSQL(19/20)

はじめに

 データ分析を行う場合、データの管理運用、蓄積も重要な論点となる。そのためにはSQLやデータベースの知識が求められる。そこでまずは

を基にSQLの勉強をしていく。

16. GROUPING演算子

  • 小計・合計を同時に求める機能に\mathrm{GROUPING}演算子がある。
  • \mathrm{GROUPING}演算子は標準\mathrm{SQL}の機能であるものの、一部の\mathrm{DBMS}では利用できない。

16.1 ROLLUP

 グループごとの小計に加え総計を求めたい場合、\mathrm{SELECT}文を2回用いることになるため、冗長かつ計算コストが高くなる。このために代わりに用いるのが\mathrm{GROUPING}演算子である。\mathrm{GROUPING}演算子には

  • \mathrm{ROLLUP}
  • \mathrm{CUBE}
  • \mathrm{GROUPING\ SETS}

がある。

16.2 ROLLUPの使い方

-- ROLLUP
SELECT A.a, A.b, sum(A.c) AS SUM
FROM ABS AS A
GROUP BY ROLLUP(A.b) -- MySQLではGROUP BY A.b WITH ROLLUP

 これは①\mathrm{GROUP\ BY}()および②\mathrm{GROUP\ BY(A.b)}を一度に行っているのに等しい。①を超集合行という。

16.3 GROUPING関数

 \mathrm{ROLLUP}では集約行が\mathrm{NULL}であることに起因する\mathrm{NULL}と超集合行に起因する\mathrm{NULL}とが混同される。これを類別するのに\mathrm{GROUPING}関数を用いる。この関数は引数に取った列の値が超集合列のために生じた\mathrm{NULL}の場合には1を、それ以外の値には0を返す。そこに更に\mathrm{CASE}文を使えば任意の文字列を充てることができる。

SELECT CASE WHEN GROUPING(A.a) = 1
             THEN 'a 集計値'
             ELSE A.a END AS Species
     CASE WHEN GROUPING(A.b) = 1
             THEN '集計値'
             ELSE CAST(A.b AS VARCHAR(16)) END AS b -- CASE文の出力データ型を一致させるためにCASTする
     SUM(A.c) AS SUM
FROM ABC as A
GROUP BY ROLLUP(A.a, A.b)

16.4 CUBE

 \mathrm{GROUP\ BY}句に与えられた集約キーのすべての可能な組み合わせを1つにまとめるのに\mathrm{CUBE}を用いる。

SELECT CASE WHEN GROUPING(A.a) = 1
             THEN 'a 集計値'
             ELSE A.a END AS Species
     CASE WHEN GROUPING(A.b) = 1
             THEN '集計値'
             ELSE CAST(A.b AS VARCHAR(16)) END AS b -- CASE文の出力データ型を一致させるためにCASTする
     SUM(A.c) AS SUM
FROM ABC as A
GROUP BY CUBE(A.a, A.b)

16.5 GROUPING SETS

 \mathrm{ROLLUP}および\mathrm{CUBE}で得られた結果の一部のレコードのみが欲しい場合には \mathrm{GROUPING\ SETS}を用いる。

SELECT CASE WHEN GROUPING(A.a) = 1
             THEN 'a 集計値'
             ELSE A.a END AS Species
     CASE WHEN GROUPING(A.b) = 1
             THEN '集計値'
             ELSE CAST(A.b AS VARCHAR(16)) END AS b -- CASE文の出力データ型を一致させるためにCASTする
     SUM(A.c) AS SUM
FROM ABC as A
GROUP BY GROUPING SETS (A.a, A.b)

これにより総計の行は出力されない。

プライバシーポリシー お問い合わせ