SQL2003や99の新しい文法を追ってなかったので確認してみた。
WEB+DB PRESS Vol.66に連載されているSQL緊急救命室を見て、あまりにも最近（でもないけど）のSQLを知らないことに愕然とし改めてSQLの構文で知らない部分を確認してみました。
前提
環境はOracle 10g R2です。わりと古い環境ですので、最近のシステムをお使いであれば大抵の場合には以下で紹介する構文は使えるのではないかと思います。
テーブル定義は以下をを前提としています。
CREATE TABLE uriage_table ( seihin_id VARCHAR2(3 BYTE), year NUMBER(4,0), month NUMBER(2,0), uriage NUMBER(10,0) NOT NULL, CONSTRAINT uriage_table_pk PRIMARY KEY(seihin_id,year,month) );
データはINSERT文で挿入してください。
INSERT INTO URIAGE TABLE(seihin_id,year,month,uriage) VALUES('001',2012,1,1); INSERT INTO URIAGE TABLE(seihin_id,year,month,uriage) VALUES('001',2012,2,9); INSERT INTO URIAGE TABLE(seihin_id,year,month,uriage) VALUES('001',2012,3,3); INSERT INTO URIAGE TABLE(seihin_id,year,month,uriage) VALUES'(002',2012,1,10); INSERT INTO URIAGE TABLE(seihin_id,year,month,uriage) VALUES('002',2011,12,20); INSERT INTO URIAGE TABLE(seihin_id,year,month,uriage) VALUES('003',2012,1,300); INSERT INTO URIAGE TABLE(seihin_id,year,month,uriage) VALUES('004',2012,1,4000); INSERT INTO URIAGE TABLE(seihin_id,year,month,uriage) VALUES('004',2012,3,3000); INSERT INTO URIAGE TABLE(seihin_id,year,month,uriage) VALUES('004',2011,11,4000);
挿入後のテーブルのイメージは以下のようになります。
SEIHIN_ID YEAR MONTH URIAGE --------- ---- ----- ---------- 001 2012 1 1 001 2012 2 9 001 2012 3 3 002 2011 12 20 002 2012 1 10 003 2012 1 300 004 2011 11 4000 004 2012 1 4000 004 2012 3 3000
WITH句
最初にWITH句を紹介します。WITH句を使うことでFROM句に書いていた一時表を事前に定義しすっきりとした形で書くことができます。
構文的にはhttp://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_10.html#5133を参照してください。
例えば、製品ごとの売上累計に対する月毎の売上の割合を求める場合を考えます。製品ごとの売上の合計はseihin_idでGROUP BYしてuriageをSUMすることでできます。これで月毎の売上を割ることで割合が計算できます。WITHを使うことで集計をWITH句に書き、以下のようにすっきりとした形で問い合わせをすることができます。
WITH sum_uriage AS ( SELECT seihin_id,SUM(uriage) sum_uriage FROM uriage_table GROUP BY seihin_id ) SELECT uriage_table.seihin_id,year,month,uriage,((uriage / sum_uriage) * 100) wariai FROM uriage_table, sum_uriage WHERE sum_uriage.seihin_id=uriage_table.seihin_id ORDER BY seihin_id,year,month
SEIHIN_ID YEAR MONTH URIAGE WARIAI --------- ---- ----- ---------- ------ 001 2012 1 1 7.69230769230769230769230769230769230769 001 2012 2 9 69.23076923076923076923076923076923076923 001 2012 3 3 23.07692307692307692307692307692307692308 002 2011 12 20 66.66666666666666666666666666666666666667 002 2012 1 10 33.33333333333333333333333333333333333333 003 2012 1 300 100 004 2011 11 4000 36.36363636363636363636363636363636363636 004 2012 1 4000 36.36363636363636363636363636363636363636 004 2012 3 3000 27.27272727272727272727272727272727272727
クロス集計
CUBE
MS Accessなどではクロス集計が昔からできました。SQL標準でもCUBE関数を使用することでクロス集計ができるようになりました。CUBEをGROUP BYで用いることで、引数で指定した項目の組み合わせの集計を行えます。
Oracleのドキュメントでクロス集計はhttp://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_10.html#10538に書いてあります。
SELECT seihin_id,year,SUM(uriage) FROM uriage_table GROUP BY CUBE(seihin_id, year) ORDER BY seihin_id,year
SEIHIN_ID YEAR SUM(URIAGE)
--------- ------ ------------
001 2012 13
001 13
002 2011 20
002 2012 10
002 30
003 2012 300
003 300
004 2011 4000
004 2012 7000
004 11000
2011 4020
2012 7323
11343
ROLLUP
CUBEは引数で指定した項目の組み合わせでしたが、ROLLUPでは小計を集計します。
Oracleのドキュメントはhttp://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_10.html#10414です。
SELECT seihin_id,year,SUM(uriage) FROM uriage_table GROUP BY ROLLUP(seihin_id, year) ORDER BY seihin_id,year
SEIHIN_ID YEAR SUM(URIAGE)
--------- ---- -----------
001 2012 13
001 13
002 2011 20
002 2012 10
002 30
003 2012 300
003 300
004 2011 4000
004 2012 7000
004 11000
11343
GROUPING SETS
CUBEやROLLUPでも必要な集計が行えることがほとんどだと思います。ですが、場合によっては（特にCUBE）過剰な集計パターンになることがあります。実行効率を考えた場合に必要なパターンだけをグループ化して集計するために、GROUPING SETSが使用できます。
Oracleのドキュメントはhttp://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/statements_10.html#10639
seihin_idとyearの組み合わせの集計をする場合にはGROUPING SETSに(seishin_id,year)と指定します。
SELECT seihin_id,year,SUM(uriage) FROM uriage_table GROUP BY GROUPING SETS((seihin_id,year)) ORDER BY seihin_id,year
SEIHIN_ID YEAR SUM(URIAGE) --------- ---- ----------- 001 2012 13 002 2011 20 002 2012 10 003 2012 300 004 2011 4000 004 2012 7000
総合計も集計する場合には、()という空の組み合わせを追加します。
SELECT seihin_id,year,SUM(uriage) FROM uriage_table GROUP BY GROUPING SETS((seihin_id,year),()) ORDER BY seihin_id,year
SEIHIN_ID YEAR SUM(URIAGE)
--------- ---- -----------
001 2012 13
002 2011 20
002 2012 10
003 2012 300
004 2011 4000
004 2012 7000
11343
分析ファンクション
RANK
分析ファンクションは、テーブルを特定のPARTITIONで区切りそれを対象に集計をするものです。
Oracleのドキュメントとしてはhttp://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/functions.html#25267で解説されています。
まずPARTITIONを使わない例を紹介します。売上の逆順、つまり売上の多い順にRANK関数を使用して順位を付けます。ここで注目する点は、同値の場合にはRANKは同じ値を返し、順位をその分飛ばします。以下の例だと1位が2つあるため、その次の順位が3になっています。プログラムでも書けないことは無いですがやや面倒な処理をSQLで処理できるため、場合によっては有効に使うことができます。
SELECT seihin_id,year,month, RANK() OVER (ORDER BY uriage DESC) AS seihin_rank FROM uriage_table ORDER BY seihin_rank,seihin_id,year,month
SEIHIN_ID YEAR MONTH SEIHIN_RANK --------- ----- ------ ------------ 004 2011 11 1 004 2012 1 1 004 2012 3 3 003 2012 1 4 002 2011 12 5 002 2012 1 6 001 2012 2 7 001 2012 3 8 001 2012 1 9
次は、同じRANK関数を使用して、seihin_idごとの順位を求める例です。PARTITION BYでどの単位で集計するのかを決めます。
SELECT seihin_id,year,month, RANK() OVER (PARTITION BY seihin_id ORDER BY uriage DESC) AS seihin_rank FROM uriage_table ORDER BY seihin_id,seihin_rank,year,month
SEIHIN_ID YEAR MONTH SEIHIN_RANK --------- ----- ------ ------------ 001 2012 2 1 001 2012 3 2 001 2012 1 3 002 2011 12 1 002 2012 1 2 003 2012 1 1 004 2011 11 1 004 2012 1 1 004 2012 3 3
ROW_NUMBER
ROW_NUMBER関数を用いて擬似的な通し番号を振りそこから値を抽出する例を消化します。ROW_NUMBER関数では、指定のPARTITIONの中をORDER順に並べて1〜の通し番号をふることができます。そこで、それを一時表として製品ごとに売り上げトップの年月を抽出します。同じ売上の場合にはより新しいものを優先しています。
SELECT seihin_id,year,month,uriage FROM (SELECT seihin_id,year,month,uriage, ROW_NUMBER() OVER(PARTITION BY seihin_id ORDER BY uriage DESC,year DESC,month DESC) row_num FROM uriage_table) uriage_work WHERE row_num=1
SEIHIN_ID YEAR MONTH URIAGE --------- ----- ------ ------- 001 2012 2 9 002 2011 12 20 003 2012 1 300 004 2012 1 4000
売上累計を求める
製品ごとにそれぞれの製品の年月までの売上の累計を求めるということもできます。ここではじめてROWS BETWEENという構文が出てきます。BETWEENはWHERE句で使用するBETWEENと変わらずA AND BでA〜Bの範囲となります。ここでAに当たる部分で使用されているUNBOUNDED PRECEDINGはPARTITIONで区切った中の最初の行という意味になります。BのCURRENT ROWは現在行を表すので、製品の最初の行から現在行までの集計をすることになります。
SELECT seihin_id,year,month, SUM(uriage) OVER(PARTITION BY seihin_id ORDER BY year,month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ruikei FROM uriage_table ORDER BY seihin_id,year,month
SEIHIN_ID YEAR MONTH RUIKEI --------- ----- ------ ------- 001 2012 1 1 001 2012 2 10 001 2012 3 13 002 2011 12 20 002 2012 1 30 003 2012 1 300 004 2011 11 4000 004 2012 1 8000 004 2012 3 11000
RANGE
ROWS BETWEENに対して、RANGE BETWEENという構文もあります。RANGEはORDERに指定した項目の値の範囲を指定します。以下の例だとuriageがカレント行-2〜カレント行+6の範囲を集計します。
SELECT seihin_id,year,month,uriage,row_num FROM (SELECT seihin_id,year,month,uriage, COUNT(*) OVER(PARTITION BY seihin_id ORDER BY uriage RANGE BETWEEN 2 PRECEDING AND 6 FOLLOWING) row_num FROM uriage_table) uriage_work
SEIHIN_ID YEAR MONTH URIAGE ROW_NUM --------- ---- ----- ---------- ------- 001 2012 1 1 2 001 2012 3 3 3 001 2012 2 9 1 002 2012 1 10 1 002 2011 12 20 1 003 2012 1 300 1 004 2012 3 3000 1 004 2012 1 4000 2 004 2011 11 4000 2
WITHIN
最後にWITHINを紹介します。WITHINを用いると指定のグループの中で引数の中の値がどうなるかを判定できます。
例えば、以下の例だとuriage 10は小さい方から何番目に位置するのかを求めることができます。
SELECT RANK(10) WITHIN GROUP(ORDER BY uriage) rank10 FROM uriage_table
RANK10
------
4
まとめ
SQL2003（99も含む）仕様の中ですぐに使えそうな構文を抽出して紹介しました。仕様自体はとても大きくまだまだありますので興味の有る方は以下の本を見てぜひ紹介してください。
- 作者: 土田正士,小寺孝
- 出版社/メーカー: ソフトリサーチセンター
- 発売日: 2004/12
- メディア: 単行本
- クリック: 1回
- この商品を含むブログ (4件) を見る
