コンピュータクワガタ

かっぱのかっぱによるコンピュータ関連のサイトです

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も含む)仕様の中ですぐに使えそうな構文を抽出して紹介しました。仕様自体はとても大きくまだまだありますので興味の有る方は以下の本を見てぜひ紹介してください。

SQL2003ハンドブック―SQL最新標準規格

SQL2003ハンドブック―SQL最新標準規格

広告