17.05 - ROLLUPオプションの例 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ操作言語

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
2021年1月
Content Type
プログラミング リファレンス
Publication ID
B035-1146-175K-JPN
Language
日本語 (日本)

例: 基本のROLLUP

次の例は、郡(county)、都市(city)、州(state)のそれぞれの合計利益幅、および全体としての利益幅を返します。この例では、ROLLUPが地理上の単一の次元の3つの詳細レベルでデータをグループ分けする方法を示します。

次のようなsales_viewテーブル データがあると仮定します。疑問符(?)はnullを示します。

sales_view
PID cost sale margin state county city
1 38350 50150 11800 CA Los Angeles Long Beach
1 63375 82875 19500 CA San Diego San Diego
1 46800 61200 14400 CA Los Angeles Avalon
1 40625 53125 12500 CA Los Angeles Long Beach
1 30000 33000 3000 CA San Diego ?

このテーブルに対する次のSELECTリクエストは、GROUP BY句でROLLUPを使用して、州(state)、郡(county)、および都市(city)をロールアップします。

     SELECT state, county, city, SUM(margin)
     FROM sales_view
     GROUP BY ROLLUP (state, county, city);

問合わせにより次のデータがレポートされます。

state
county
city
SUM(margin)
-----
------
----
-----------
CA
San Diego
San Diego
19500
CA
San Diego
?
3000
CA
Los Angeles
Avalon
14400
CA
Los Angeles
Long Beach
24300
CA
Los Angeles
?
38700
CA
San Diego
?
22500
CA
?
?
61200
?
?
?
61200
このクエリーは、次の4つの異なるレベルでグループ分けまたは制御の切れ目を計算します。
  1. statecountycity
  2. statecounty
  3. state
  4. テーブルの中のすべてのデータ

ロールアップされる列のそれぞれの制御の切れ目はnullで表わされます。これらのnullは、欠落した情報ではなく空のセットを表わします。

次のテーブルは現在の例についてこのことを要約したものです。

制御の切れ目の数 切れ目を指定する列セット 列セットをグループ分けする順序 ロールアップされた列としてnullで表わされる列セット
1 City State

County

City

なし。

2番目の行にあるcity列は、テーブルの該当行にあるその列の欠落情報を示しており、レポート内の制御の切れ目を表わす空のセットを示しているわけではありません。CASE式を使ってこのあいまいさを解消する方法については、例: GROUPING関数を使用するROLLUPを参照してください。

2 CityおよびCounty State

County

City
3 City、County、およびState State CityおよびCounty
4 All All City、County、およびState

例: GROUPING関数を使用するROLLUP

NULLは例: 基本のROLLUPで生成されたレポートにある欠落情報と空集合の両方を表わします。例えば、sales_viewの5番目の行にはSan Diego郡(County)の都市がnullであり、これはレポートの2番目の行に反映されています。この場合nullは行の5番目の行の都市(City)を表わすnullと1:1で対応しています。

レポートの5~8行目にある都市(city)のnullは、欠落情報ではなく空のセットを示します。nullは、示されているレベルで情報がレポートされないことを意味しており、sales_view基本テーブルから情報が欠落していることを意味するわけではありません。レポートの6行目には混乱が見られるようです。なぜならSan Diego郡(County)でレポートされている都市(City)の1つについては情報が欠落してですからです。しかし、San Diego郡のすべての都市の情報はこの行で集計されているため、問題はありません。その郡の都市がnullであることが分かっているからです。

  • (state, county)で記号的に表わされている合計の最初のレベルは複数の郡(county)を分割しています。そのため、レポートの5行目ではLos Angeles郡のすべての都市(city)が縮小され、6行目ではSan Diego郡のすべての都市が縮小されています。
  • (state)で記号的に表わされている合計の2番目のレベルは複数の州(state)を分割しています。そのため、7行目ではCalifornia州のすべての都市(city)と郡(county)が縮小されています。
  • 合計の最初のレベルはすべての州(state)の合計をレポートし、8行目はこの合計レベルを反映しています。唯一の州であるCaliforniaのデータがsales_viewテーブルに記録されているため、7行目と8行目の合計は同じです。sales_viewテーブルにその他の州のデータが記録されていれば、このようにはなりません。

CASE式をGROUPING関数(<Teradata Vantage™ - SQL関数、式、および述部、B035-1145>を参照)と共に使用すれば、疑問符(?)ではなく文字列を使って空集合のインスタンスをレポートするので、欠落情報を表わすNULLと空集合を表わすNULLを的確に区別することができます。この例では、空のセットを表わす文字列は(-all-)という節です。

     SELECT CASE GROUPING(state)
             WHEN 1
             THEN '(-all-)'
             ELSE state
            END AS state,
            CASE GROUPING(county)
             WHEN 1
             THEN '(-all-)'
             ELSE county
            END AS county,
            CASE GROUPING(city)
             WHEN 1
             THEN '(-all-)'
             ELSE city
            END AS city,
     SUM(margin)
     FROM sales_view
     GROUP BY ROLLUP (state, county, city);

この問合わせは前の問合わせと同じ情報をレポートしますが、表現はもっとわかりやすくなっています。

state
county
city
SUM(margin)
-----
------
----
-----------
CA
San Diego
San Diego
19500
CA
San Diego
?
3000
CA
Los Angeles
Avalon
14400
CA
Los Angeles
Long Beach
24300
CA
Los Angeles
(-all-)
38700
CA
San Diego
(-all-)
22500
CA
(-all-)
(-all-)
61200
(-all-)
(-all-)
(-all-)
61200