DB2 GROUP BY 拡張

DB2 GROUP BY 拡張

DB2テンプレート:Category SQL

ROLLUP

• グループごとのサマリーを作成

SELECT t.name AS tour_name,
       c.name AS county_name,
       COUNT(*) AS falls_count
FROM upfall u INNER JOIN trip t
      ON u.id = t.stop
   INNER JOIN county c
      ON u.county_id = c.id
GROUP BY ROLLUP(t.name, c.name);

TOUR_NAME  COUNTY_NAME FALLS_COUNT
---------- ----------- ----------------------
M-28       Alger       3
M-28       Baraga      1
M-28       Ontonogan   2
M-28                   6
US-2       Delta       1
US-2       Gogebic     1
US-2       Dickenson   1
US-2       Ontonogan   1
US-2                   4
Munising   Alger       6
Munising               6

CUBE

• 指定列のすべての可能な組み合わせサマリ

SELECT t.name AS tour_name,
       c.name county_name,
       COUNT(*) AS falls_count
FROM upfall u INNER JOIN trip t
      ON u.id = t.stop
   INNER JOIN county c
      ON u.county_id = c.id
WHERE t.name = 'Munising'
GROUP BY CUBE(t.name, c.name);

TOUR_NAME  COUNTY_NAME FALLS_COUNT
---------- ----------- ----------------------
                       6
           Alger       6
Munising               6
Munising   Alger       6

GROUP BY で有用な関数

GROUPING( column )

• CUBE, ROLLUPの結果が NULL の場合、1を返す、そうでない場合、0を返す