1 2 4 5 6 演 子 3 集 算 230 NULL により、 2 つの SELECT の列数を一致させます。 SELECT a, b ′ NULL FROM f00 UNION SELECT c . d, e FROM bar ・ UNION 演算時のソート ORDER BY によってソートすることも可能ですが、 ORDER BY は必す最後の SELECT に記述します。ソートは UNION でつなげられたすべての SELECT の結 果に対してかかります。しかし、 こで困ったことが起こります。複数の SELECT で同じ列に対して、 ORDER BY による列指定を行うのであれば問題あ りませんが、複数の SELEC T で無関係の選択列リストを持っている場合、 ORDER BY で指定する列名を決定できなくなってしまいます。このような場合、 ORDER BY の列順番指定を行うと問題は解決されます。 ORDER BY に列名では なく、番号 # を指定すると、選択列リストの # 番目でソートさせることが可能で す。 列番号により O 日 DE 日 BY を行います。 SELECT a, b FROM f 〇 0 UN 工 ON SELECT c ′ d FROM bar ORDER BY 1 DB2 、 PostgreSQL 、 MySQL では、サプクエリーを UNION で結合することが できます。各サプクエリーで ORDERBY を使用することが可能です。 BY 付きのサブクエリーを UN ℃ N で結合します。 (SELECT a FROM f00 ORDER BY a) I_JN 工 ON (SELECT a FROM bar ORDER BY a) 参照 EXCEPT 演算子・・・・・・ P231 INTERSECT 演算子・・・・・・ P233 M US 演算子・・ ・・ P232
4 5 6 関 数 分 関 年収 720 520 520 450 DENSE_RANK() OVER(ORDER BY 年収 DESC) 1 2 2 3 ( 以下略 ) 従業員テーブルの年収列の値を性別ことに順位付けを行います。 SELECT 年収 , 性別 , DENSE_RANK ( ) OVER ( PART 工 T 工 ON BY 性別 ORDER BY 年収 DESC) FROM 従業員 ORDER BY 性別 , 年収 DESC 年収 520 450 340 320 720 性別 女 女 女 女 男 ( 以下略 ) DENSE_RANK() OVER(PARTITION BY 性別 .. 1 2 3 4 1 年収列の値を集計した結果で順位付けを行います。 SELECT SUM ( 年収 ) , DENSE-RANK ( ) OVER (ORDER BY SUM ( 年収 ) DESC) FROM 従業員 GROUP BY 性別 SUM( 年収 ) DENSE_RANK() OVER(ORDER BY SIJM( 年収 ) DESC) 1890 1630 1 2 ・ Oracle Oracle では、 NULL 値を最初にするか、最後にするかを指定することができます。 「 ORDER BY a NULLS LAST 」とすることで NULL 値を最後にすることができま す。逆に NULL 値を最初にするには、「 ORDER BYaNULLS F 旧 ST 」とします。 参照 RANK 関数・・ ・・ P431 430 .
1 2 3 5 6 数 分 析 数 4 4.6 438 ・ RANGE によるウインドウ指定 RANGE を指定することで、パーティッション内の任意の範囲をウインドウと して指定することができます。「 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 」とすることで、カレント行 (CURRENT ROW) からパーティッションの最後の行 (UNBOUNDED FOLLOWING) までを集計す ることができます。 BETWEEN CURRENT RO\N AND IJNBOUNDED FOLLOWING を指定します。 SELECT 年齢′ SUM ( 年齢 ) OVER (ORDER BY 年齢 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOW 工 (G) FROM 従業員 ORDER BY 年齢 年齢 20 23 SUM( 年齢 ) OVER(ORDER BY 年齢 RANGE BETWEEN CURRENT … 236 217 197 176 ( 以下略 ) UNBOUNDED の代わりに、どの行を対象とするかを数値で指定することがで きます。「 BETWEEN 10 PRECEDING AND 10 FOLLOWING 」とすれば、カレ ント行の年齢が、 10 歳若いところから、 10 歳上のところまでが対象となります。 BETWEEN 1 0 PRECEDING AND 1 0 FOLLOWING を指 定します。 SELECT 年齢 , SUM ( 年齢 ) OVER (ORDER BY 年齢 RANGE BETWEEN 10 PRECED 工 NG AND IØ FOLLOW 工 (G) FROM 従業員 ORDER BY 年齢 年齢 20 23 32 35 42 44 SUM( 年齢 ) OVER(ORDER BY 年齢 RANGE BETWEEN 1 .. 83 83 83 132 153 153
1 を 0 っ ) 4 5 6 関数 ÄPARTITION BY を使用してみます。 SELECT 性別 , 年齢 , SUM ( 年齢 ) OVER (PARTIT 工 ON BY 性別 ORDER BY 年齢 ) FROM 従業員 ORDER BY 性別 , 年齢 年齢 SUM( 年齢 ) OVER(PARTITION BY 性別 ORDER BY 年齢 ) 性別 女 42 23 女 32 74 女 44 20 男 21 男 35 76 男 42 さて、複雑なようですが、ウインドウという概念を押さえることで、このよう な分析関数を理解することができます。ウインドウは、 PARTITION BY により 区別されるグループ内で、集計を行う対象範囲のことです。ウインドウ内を集計 した結果が返されます。 SUM の場合、合計値を計算します。 AVG ならば、平均 値を計算します。 ウインドウの範囲は、「先頭からカレント行まで」、といった指定や、「カレン ト行に対してどのくらい離れているか」、といった方法で指定することが可能です。 分 析 「 SUM( 年齢 ) OVER(ORDER BY 年齢 ) 」とした場合、カレント行の前だけを対 象に合計が計算されます。つまり、カレント行では、それまでの累計値が計算さ 数 れるのです。これを指定するためのものが、「 ROWS 」または「 RANGE 」です。 省略時には、「 ROWS UNBOUNDED PRECEDING 」であるとみなされます。っ まり、パーティッションの先頭からカレント行までをウインドウとし、合計値が 計算されます。 PRECEDING がカレント行の前という指定になります。 UNBOUNDED P 日 ECED 囚 G を指定します。 SELECT 年齢 . S54 ( 年齢 ) 〇一 R (ORDER BY 年齢 ROWS UNBOUNDED PRECED 工 NG) FROM 従業員 ORDER BY 年齢 年齢 SUM( 年齢 ) OVER(ORDER BY 年齢 ROWS UNBOUNDED PRECEDING) 20 39 60 ( 以下略 ) 437
0B2 DENSE RANK 関数 Å朝も SQL 1 2 ・ 0 「 acl 順位を求める DENSE RANK ( ) OVER ( [ PARTITION BY [ , ・・・Ⅱ ORDER BY 0 い 0 ANSI 標準 4 5 6 関数 … l) →数値 戻り値 順位 「 DENSE_RANK 」関数は、「順位」を計算して返します。 DENSE-RANK のよ うな分析関数は、通常の関数とは記述の方法が異なっています。 DENSE-RANK に 続く括弧の中には引数を記述しません。さらに「 OVER 」と続けて、その括弧の中 に、順位を計算するための、ソートの順番や、パーティッションを指定します。 OVER 中の「 ORDER BY 」以下に指定された式により、順位が決定します。あ るテープルの「成績」列の値が大きい順に、順位を付けたいのなら、次のように 行います。 成績列の値が大きい順にランク付けします。 DENSE_RANK ( ) OVER (ORDER BY 成績 DESC) 「 DESC 」、「 ASC 」の指定が可能であることは通常の ORDER BY と同じです。 DENSE_RANK 関数は、順位を飛ばすことがありません。同列の順位がある場 合でも、飛ばさずに順位付けされます。同列の順位がある場合に、その順位を飛 ばして順位付けするには、「 RANK 」関数を使うことができます。 「 PARTITION BY 」は、順位付けを行うグループの指定です。 PARTITION BY は省略可能です。指定が存在しない場合には、全体に対して順位付けが行わ れます。男性と女性でそれぞれに順位付けを行う、という用途で利用できます。 「 ORDER BY 」には、集計関数を使うことができます。これにより、 SIJM 関 数により合計値を集計した結果によって順位を計算することも可能です。 従業員テープレの年収列の値が大きい順に順位付けを行います。 SELECT 年収 , DENSE-RANK ( ) OVER (ORDER BY 年収 DESC) FROM 従業員 ORDER BY 年収 DESC ・パーティッションを指定する任意の式 ・・順位を計算する上で評価を行う任意の式 分析関数 429.
SQL rve MS ANSI cces 標準 1 2 3 4 5 《 0 コマンド命令 ORDER BY 句 DB2 racl 取得順序の決定 ORDER BY 氾ル ss 加れ [ ASC ー DESC 】 [ , e 工盟 7 ℃ s の ASC ー DESC 】 バラメータ ・任意の式 expresston ・ 「 ORDER BY 句」により、ソートをすることができます。 ORDER BY にはソ ートしたい列または式を記述します。列指定は「 ORDER BY a,b,c 」のように複 数指定することが可能です。こうすると、 a 列でソートし、 a 列の値が同じもので あった場合には b 列でソートされ、さらに c 列でソートされます。 ORDERBY 句 のない SELECT の結果は行の順番は不定です。いつも同じ順番で結果を得たい時 には ORDERBY を指定します。 タ ・ ASC 、 DESC ソートの方法は、デフォルトでは ASC( 小さいものから昇順 ) になっています。 套 大きいものから降順にしたい場合には DESC を指定します。 テーブル foo を列 a の値を昇順にソートします。 SELECT * FROM f00 ORDER BY a ASC テーブル f00 を列 a の値を降順にソートします。 SELECT * FROM f00 ORDER BY a DESC ・ NULL 値のソート順 NULL 値の扱いはデータベースによって異なります。 SQL server 、 MySQL 、 Access では、ソートにおいて NULL 値は一番小さな値として扱われます。 ASC では一番初めに、 DESC では最後にソートされます。 oracle 、 DB2 、 PostgreSQL では逆に一番大きな値としてソートされます。 ASC では最後に、 DESC では最初 にソートされます。 Oracle9i 以降と PostgreSQL 8.3 以降では、 NULLS FIRST 、 NULLS LAST によ り NULL 値の扱いを変更することができます。 stgr SQ 3 ロ卩 68
DB2 SQL 1 2 3 4 5 6 関数 RANK 関数 acl ANSI 標準 A00 s 順位を求める RANK ( ) 0 、 R ( [ PARTITION BYp [ , . ・・Ⅱ ORDER BY ⅵ , 0 … l) →数値 戻り値 順位 ・・パーティッションを指定する任意の式 ・順位を計算する上で評価を行う任意の式 「 RANK 」関数は、「順位」を計算して返します。 DENSE-RANK と同様に分析 関数です。 PARTITION BY と、 ORDER BY の用法については、 DENSE_RANK と同じです。 DENSE_RANK と RANK の違いは、同点となった場合の順位の付け 方だけです。 RANK 関数は、同列の順位が存在する場合、順位を飛ばします。 2 位が 2 つあ るのなら、順位は、 1 位、 2 位、 2 位、 4 位のようになります。 従業員テーブルの年収列の値が大きい順に順位付けをします。 SELECT 年収 , RANK ( ) OVER (ORDER BY 年収 DESC) FROM 従業員 ORDER BY 年収 DESC 年収 RANK() OVER(ORDER BY 年収 DESC) 720 1 520 2 520 2 450 4 分析関数 ( 以下略 ) 参照 DENSE RANK 関数・・ ・・ P429 431
SQL SUM OVER 関数 1 2 3 4 5 6 関数 DB2 racl ANSI 標準 A 3 ウインドウを使った合計 SUM( e ) 0 、田 R い PARTITION BYp い・・・Ⅱ ORDER BY 0 [ , 0 …】 { ROWS 川 ? 偽ー ec ー RANGE 7 、佖れー ec } ) →数値 SUM( e ) 0 、 ( [ PARTITION BYp い… (I) →数値 引第 集計を行う式 ・・パーティッションを指定する任意の式 ・・順位を計算する上で評価を行う任意の式 ・・カレント行から、どこまでを範囲とするかを指定する式 7 ・ 0 ? む S ー ,S 盟 ec ・ ・範囲指定 ? 、 ange—spec ・ 戻ソ」値 合計値 Oracle DB2 SQLServer SUM や AVG といった集計関数に「 OVER 」を加えることで、「ウインドウ」と いった概念を持ち込み、高度な集計を行うことが可能になります。 分 析 「 PARTITION BY 」と「 ORDER BY 」は RANK 関数などの分析関数と同じで 数 す。これらを単純に使用すると、次のようになります。 OVER を使用してみます。 SELECT 年齢 , SUM ( 年齢 ) OVER (ORDER BY 年齢 ) FROM 従業員 ORDER BY 年齢 年齢 SUM( 年齢 ) OVER(ORDER BY 年齢 ) 20 39 60 ( 以下略 ) SUM は集計関数ですが、 OVER を付け、「分析関数」として利用すると、この ようになります。上記の例では、年齢列を小さい順にソートし、それを累計して いく様子がわかります。今現在の位置より上にある行の合計を計算した、と言い 換えることもできます。 PARTITIONBY を併用し、グループごとに累計を計算させることもできます。 4.6 436
行番号を求める ROW_NUMBER ( ) 0 、田 R ( ー PARTITION BYp い盟・・・】】 ORDER BY , 。… l) →数値 ROW NUMBER 関数 SQL DB2 1 2 3 4 5 6 関数 ℃儲 cl ANSI 標準 友り値 行番号 「 ROW_NUMBER 」関数は、「行番号」を計算して返します。 ROW-NUMBER 関数は、 RANK や DENSE_RANK と同様に、分析関数ですので、これらと同じ文 法を持ちます。 OVER 中の ORDER BY 以下に指定された式により、行番号が決 定します。 「成績」列の値が大きい順に、行番号を付けます。 ROW_NUMBER ( ) OVER ( ORDER BY 成績 DESC ) DESC 、 ASC の指定が可能であることは通常の ORDER BY と同じです。 ROW_NUMBER 関数は、同列の値が存在していても、順番に番号を振ります。 RANK や DENSE ー RANK のように同点のものを同じ番号にすることはありません。 「 PARTITION BY 」は、順位付けを行うグループの指定です。 PARTITION BY は省略することができます。指定が存在しない場合には、全体に対して番号 付けが行われます。男性と女性でそれぞれに番号付けを行う、という用途で利用 できます。 「 ORDER BY 」には、集計関数を使うことができます。これにより、 SUM 関 数により合計値を集計した結果によって行番号を計算することも可能です。 実行例については、 RANK 関数、 DENSE ー RANK 関数を参照して下さい。 ・・パーティッションを指定する任意の式 ・順位を計算する上で評価を行う任意の式 4.6 分析関数 RANK 関数・・ ・・ P431 参照 DENSE_RANK 関数・ ・・ P429 432
4 23 の行は、 13 ~ 33 までの合計、つまり、 19 + 20 + 21 + 23 + 32 = 115 になります。 同様に、 32 の行は、 22 ~ 42 までの合計、 23 + 32 + 35 + 42 = 132 になります。 ・集計関数のネスト 分析関数中の ORDERBY には、集計関数による結果を指定することができま す。これによって、 GROUP BY により集計した結果の累計を計算する、といっ たことが可能になります。 SUM をネストすることで、合計の累計を計算します。 SELECT 性別 , SUM ( 年齢 ) , SUM(SUM( 年齢 ) ) OVER(ORDER BY SUM( 年齢 ) ROWS UNBOUNDED PRECED 工 (G) FROM 従業員 GROUP BY 性別 ORDER BY SUM ( 年齢 ) SUM( 年齢 ) SUM(SIJM( 年齢 )) OVER(ORDER BY SUM( 年齢 ) … 性別 女 1 1 8 1 1 8 男 236 1 1 8 5 6 関数 「 SUM(SUM( 年齢 )) 」となっており、集計関数がネストしているようにみえま す。しかし、正確にいうと、外側の SUM は分析関数の SUM であり、内側の SUM が集計関数の SUM です。 SUM により集計された性別ごとの年齢の合計値を、分 分 析 析関数の SUM により、累計を計算しています。少々複雑ですが、それぞれの SUM の役割をしつかり認識すれば理解することができるでしよう。 数 ・ SQL Server SQL server では、 SUM OVER によるウインドウ操作は、 PARTITION BY に 限定されています。 ORDER BY を指定することはできません。 column 移動平均 ウインドウ操作を使うことで、移動平均を簡単に計算することができます。移動平 均は、株価のチャートなどで良くみられるもので、過去 5 日間の平均、のように直 前のいくつかの値を平均したものです。次のようにすれば過去 5 日間の移動平均を 計算できます。 SELECT AVG ( 値 ) OVER ( ORDER BY 日付 ROWS 4 PRECED 工 NG) FROM 株価 4.6 SUM 関数・ ・・ P291 参照 AVG 関数・・ ・・ P283 .439 .