ITコーディネータのシュウです。

この写真も、先回に続き、鳥の写真を撮るのに凝り始めたという知人の方から頂いたものです。たぶん白鷺だと思いますが、電線の上に止まっているところをきれいに撮っています。よく田んぼとかで白鷺を見かけることはありますが、電線に乗ることもあるんですね。

 そういえば、先日これはおもしろいと知人に紹介されたドラマで「夢をかなえるゾウ」のスペシャル男の成功編というのをDVDを借りて見ました。その話では人間の体にゾウの鼻、4本の腕を持ったインドのガネーシャという神様が、主人公の男性（小栗旬）に与える様々な課題を実践していく中で、人生をよい方向へと切り開いていく様子が描かれていますが、笑いあり、感動ありの内容でとてもよかったです。そこで出される課題は、以下のようなものだったと思います。（覚えている範囲内で）
・靴を磨く
・コンビニで（お釣りを）募金する
・食事は腹八分目にする
・人の欲しがる物を先取りしてあげる
・会った人を笑わせる
・トイレ掃除をする
・まっすぐ帰宅する
・その日がんばった自分を褒める
・一日何かをやめてみる
・毎朝、全身鏡を見て身なりを整える
・夢を楽しく想像する
・運が良いと口に出して言う
・明日の準備をする
・身近にいる大切な人を喜ばせる
・人のいい所を見つけ褒める
・人の長所を盗む
・サプライズをして喜ばせる

全ての課題に取り組んで、最後には、神様がそばにいなくてもやって行けるまで、どんどん主人公が成長していくという内容でしたが、それぞれの課題に対して、主人公が素直に取り組むのがとてもえらいと思いながら見ていました。
そのドラマの中で、仕事の会議中、最悪のように思える状況で、「運がいい」と口に出して言い、実際にそう思うことで発想を転換できて、厳しいと思っていた状況がよい方向に変わっていくシーンがありました。フィクションだからな！という思いが湧きつつも、確かにやってみる価値はあるなと考えさせられるところもいろいろとありました。

家族で一緒にこのビデオを見たので、妻や子供も、この中で出された課題を紙に書きとめて、自分も実践しようと意気込んでいましたが、さて、やっているのやら。

本題に移りましょうか。今回は、FIRST/LAST関数について取り上げてみたいと思います。

＜本日の題材＞
FIRST/LAST関数 (ORACLE)

以前、順位付の関数として、RANK関数やDENSE_RANK関数を取り上げたことがありましたが、本日は、DENSE_RANK関数と一緒に使うかたちで使用するFIRST関数、LAST関数を取り上げてみたいと思います。（ORACLEの環境）

構文は、以下のようになります。

グループ関数 KEEP
　( DENSE_RANK FIRST/LAST ORDER BY ソート列１,[ソート列２,・・・] )
      OVER( [ PARTITION BY 項目１,[項目２,・・・]] )

以前にDENSE_RANK関数を取り上げたときに使ったのが商品マスタでしたので、今回もそれを使ってみます。今回はORACLEで試します。

CREATE TABLE syomst(
  syo_cd   VARCHAR2(10)
, syo_name VARCHAR2(20)
, bnrui    VARCHAR2(20)
, price    NUMBER(10)
, CONSTRAINT PK_syomst PRIMARY KEY (syo_cd));

データを登録します。

INSERT INTO syomst VALUES('A0001', 'チョコレート', 'お菓子', 120);
INSERT INTO syomst VALUES('B0001', 'りんご', '果物', 100);
INSERT INTO syomst VALUES('C0001', 'キャベツ', '野菜', 160);
INSERT INTO syomst VALUES('A0002', 'ビスケット', 'お菓子', 200);
INSERT INTO syomst VALUES('B0002', '桃', '果物', 160);
INSERT INTO syomst VALUES('C0002', 'にんじん', '野菜', 150);
INSERT INTO syomst VALUES('A0003', 'ガム', 'お菓子', 100);
INSERT INTO syomst VALUES('B0003', 'みかん', '果物', 80);
INSERT INTO syomst VALUES('C0003', 'じゃがいも', '野菜', 100);
INSERT INTO syomst VALUES('A0004', 'スナック', 'お菓子', 140);
INSERT INTO syomst VALUES('B0004', '梨', '果物', 120);
INSERT INTO syomst VALUES('C0004', '玉ねぎ', '野菜', 150);
COMMIT;

ここで、商品の分類毎の値段が最も高いものと低いものを出す場合、金額だけ抽出すればよいのであれば、普通は以下のようにします。

SELECT bnrui, MIN(price), MAX(price)
  FROM syomst
 GROUP BY bnrui
 ORDER BY bnrui;

ここで、商品の分類毎の値段が最も高いものと低いものの金額とともに商品も抽出したいという場合には、例えば以下のようにすることができます。

SELECT
  bnrui AS 分類
, MIN(syo_name) KEEP (DENSE_RANK FIRST ORDER BY price) AS 商品名_最安
, MIN(price) KEEP (DENSE_RANK FIRST ORDER BY price) AS 最安価格
, MIN(syo_name) KEEP (DENSE_RANK LAST ORDER BY price) AS 商品名_最高
, MIN(price) KEEP (DENSE_RANK LAST ORDER BY price) AS 最高価格
  FROM syomst
 GROUP BY bnrui
 ORDER BY bnrui;

実際のデータを確認してみると、

SELECT
  bnrui AS 分類
, syo_name AS 商品名
, price AS 価格
  FROM syomst
 ORDER BY bnrui, price;

確かに、各分類の最も安い価格のものと高い価格のものが抽出されていたことが確認できます。

次に、各商品の金額を表示するとともに、各分類の最も安い金額と高い金額を同じ行で表示するということを行ってみます。先ほどの、FIRST/LAST関数に、OVER(PARTITION BY )句を使用することで可能になります。

SELECT
  bnrui AS 分類
, syo_cd AS 商品CD
, syo_name AS 商品名
, price AS 価格
, MIN(price) KEEP (DENSE_RANK FIRST ORDER BY price)
   OVER(PARTITION BY bnrui) AS 分類の最安価格
, MIN(price) KEEP (DENSE_RANK LAST ORDER BY price)
   OVER(PARTITION BY bnrui) AS 分類の最高価格
  FROM syomst
 ORDER BY bnrui, syo_cd;

それぞれの商品の価格を、同じ分類の最も安い金額と高い金額と比較して見ることができるようになりました。

今日は以上まで

ITコーディネータのシュウです。

ショッピングセンターの外の広場が飾りつけられているのを、娘が以前夕方に撮った写真です。こういう飾りがあるだけで雰囲気は変わりますよね。これから、クリスマスに向けてもっといろいろな場所できれいな飾りが見れるようになるのではないかと楽しみです。

＜本日の題材＞
集計関数（小計、合計）

システムの帳票を出力する場合に、よく小計や合計を出力するケースがありますよね。これをSQL側で行いたいということも多々あるかと思います。今回は、GROUP BY句で集計を行う際に、小計や合計を出す方法について、取り上げてみたいと思います。

商品マスタのデータと、売上テーブルのデータが以下のような場合の、受注日ごと、及び商品の分類ごとの集計を出す場合を考えてみます。

SELECT * FROM 商品マスタ
ORDER BY 商品CD;
 

SELECT * FROM 売上テーブル
ORDER BY 受注番号;

受注日ごと、及び商品の分類ごとの集計を抽出します。（Oracleの場合）

SELECT A.受注日, B.分類
, SUM(A.商品数量) AS 数量, SUM(A.売上金額) 売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY A.受注日, B.分類
 ORDER BY A.受注日, B.分類;

次に、受注日ごと、及び商品の分類ごとの集計に、小計、合計を出すように、ROLLUP句を使用してみます。

SELECT A.受注日, B.分類
, SUM(A.商品数量) AS 数量, SUM(A.売上金額) 売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY ROLLUP(A.受注日, B.分類)
 ORDER BY A.受注日, B.分類;


ここで、小計、合計のときに NULL の表示になるのが嫌な場合には、以下のようなかたちで表現を変更することもできます。

SELECT
  DECODE(GROUPING(A.受注日), 1, '総合計', A.受注日) AS 受注日
, DECODE(GROUPING(A.受注日), 1, ' ', DECODE(GROUPING(B.分類), 1, '1日分小計', B.分類)) AS 分類
, SUM(A.商品数量) AS 数量
, SUM(A.売上金額) AS 売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY ROLLUP(A.受注日, B.分類)
 ORDER BY A.受注日, B.分類;

※GROUPING関数は、ROLLUPやCUBEの操作の場合に、小計や合計を表すNULLの場合に「1」を戻し、その他は「0」を戻すので、それを使って表示を変更しています。受注日のところの合計のところは、NULLではなく「総合計」、分類のところは、NULLではなく「1日分小計」としました。（総合計のところの分類は今回は空白にしました）

また、CUBE句を使用すると、指定された列のすべての組合せについての小計を作成します。

小計や合計のときの表現を考慮して
SELECT
  DECODE(GROUPING(A.受注日), 1, '総合計', A.受注日) AS 受注日
, CASE WHEN GROUPING(B.分類) = 0 THEN B.分類
       WHEN GROUPING(A.受注日) = 1 THEN ' '
       WHEN GROUPING(B.分類) = 1 THEN '1日分小計'
       END AS 分類
, SUM(A.商品数量) AS 数量
, SUM(A.売上金額) AS 売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY CUBE(A.受注日, B.分類)
 ORDER BY A.受注日, B.分類;

最後の総合計のところで、商品の分類ごとのデータが抽出されているのが ROLLUP句の場合と異なります。

●SQL Serverの場合
通常の集計について：

SELECT A.受注日, B.分類, SUM(A.商品数量)数量, SUM(A.売上金額)売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY A.受注日, B.分類
 ORDER BY A.受注日, B.分類;

ROLLUPを使った例は以下（こちらも総合計、小計の表現を設定したもの）：
SELECT
  CASE WHEN GROUPING(A.受注日) = 1 THEN '総合計'
       ELSE CAST(A.受注日 AS VARCHAR)
       END AS 受注日
, CASE WHEN GROUPING(B.分類) = 0 THEN B.分類
       WHEN GROUPING(A.受注日) = 1 THEN ' '
       WHEN GROUPING(B.分類) = 1 THEN '1日分小計'
       END AS 分類
, SUM(A.商品数量)数量
, SUM(A.売上金額)売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY A.受注日, B.分類 WITH ROLLUP
 ORDER BY 受注日, GROUPING(B.分類), B.分類;

※ROLLUP句は、GROP BY Col1, Col2,.. WITH ROLLUP となります。

CUBEを使った例は以下（こちらも総合計、小計の表現を設定したもの）：
SELECT
  CASE WHEN GROUPING(A.受注日) = 1 THEN '総合計'
       ELSE CAST(A.受注日 AS VARCHAR)
       END AS 受注日
, CASE WHEN GROUPING(B.分類) = 0 THEN B.分類
      WHEN GROUPING(A.受注日) = 1 THEN ' '
       WHEN GROUPING(B.分類) = 1 THEN '1日分小計'
       END AS 分類
, SUM(A.商品数量)数量
, SUM(A.売上金額)売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY A.受注日, B.分類 WITH CUBE
 ORDER BY 受注日, GROUPING(B.分類), B.分類;

※CUBE句は、GROP BY Col1, Col2,.. WITH CUBE となります。
Oracleのときと同様な結果が出ました。

ちなみに、MySQLの場合も ROLLUP・CUBE句は、SQL Serverと同じく
  GROP BY Col1, Col2,.. WITH ROLLUP
　GROP BY Col1, Col2,.. WITH CUBE
となります。Oracleが独自のようですね。

今日は以上まで

ITコーディネータのシュウです。

関東最古の神社の一つと言われる鷲宮神社の『土師祭』(はじさい)が9月最初の日曜日(9/7)に行われたときの写真です。千貫神輿という大きな神輿を担いで通りを練り歩く姿は結構迫力があります。
また、鷲宮神社は人気まんが作品『らき☆すた』の舞台となった神社であり、聖地と呼ばれるだけあって、お祭りには地元の方だけでなく、『らき☆すた』ファンの皆さんや、アニメの登場人物やキャラクターに扮するコスプレ姿の若者などが大勢訪れ、賑わっていましたね。

　　
普段の鷲宮神社の鳥居と本殿です。

＜本日の題材＞
RANK、DENSE_RANK

順位付（ランキング）関数に、以前題材にあげたことのあるROW_NUMBER関数、それにRANK関数、DENSE_RANK関数などがあります。
ROW_NUMBER関数が単純な連番であるのに対し、同じ値があったときに、同じ順位を付けることができるのが、RANK、DENSE_RANK関数です。両者の違いは、同じ値があったときの次の値の順位を飛ばした値にするのがRANK、連続した値にするのがDENSE_RANKです。

例として下記のような商品マスタを作成 (SQL Server2008の環境)します。

CREATE TABLE dbo.商品マスタ(
商品CD VARCHAR(10)
, 商品名 VARCHAR(20)
, 分類 VARCHAR(20)
, 値段 DECIMAL(10)
, CONSTRAINT PK_商品マスタ PRIMARY KEY (商品CD));

データが以下のような場合：
SELECT * FROM dbo.商品マスタ
ORDER BY 商品CD;

商品マスタの商品を値段の高い順に表示したいときに、同じ値があったときの次の値の順位を飛ばした値にするRANK関数を使用した場合：

SELECT
RANK() OVER (ORDER BY 値段 DESC) RANK順位
, 商品CD, 商品名, 分類, 値段
FROM dbo.商品マスタ;


同じ値があったときの次の値の順位を連続した値にするDENSE_RANK関数を使用したとき、

SELECT
DENSE_RANK() OVER (ORDER BY 値段 DESC) DENSE_RANK順位
, 商品CD, 商品名, 分類, 値段
FROM dbo.商品マスタ;

ROW_NUMBER、RANK、DENSE_RANKを一緒に並べて使用した場合、
SELECT
ROW_NUMBER() OVER (ORDER BY 値段 DESC) ROW_NUMBER順位
, RANK() OVER (ORDER BY 値段 DESC) RANK順位
, DENSE_RANK() OVER (ORDER BY 値段 DESC) DENSE_RANK順位
, 商品CD, 商品名, 分類, 値段
FROM dbo.商品マスタ;

また、これらの関数は、PARTITION BY句と一緒に使用すると、グループ化してランキングを抽出することができます。

SELECT
ROW_NUMBER() OVER (PARTITION BY 分類 ORDER BY 値段 DESC) ROW_NUMBER順位
, RANK() OVER (PARTITION BY 分類 ORDER BY 値段 DESC) RANK順位
, DENSE_RANK() OVER (PARTITION BY 分類 ORDER BY 値段 DESC) DENSE_RANK順位
,商品CD,商品名,分類,値段
FROM dbo.商品マスタ;

上記結果のように、分類ごとにグループ化して、それぞれの関数のランキングを取得することができます。今回はSQL Serverで確認しましたが、基本的にOracleでも同様です。

今日は以上まで

ITコーディネータのシュウです。

お久しぶりです。

＜本日の題材＞
比較関数について

データベースの違いによって、あるDBでは使用できる関数が、別のDBでは使用できないケースがあります。その一例として、複数項目の最大値・最小値を取得するSQLを、今回は見てみたいと思います。

データベースがOracleの場合には、GREATEST関数・LEAST関数というのがあります。

例）金額に関する3つの項目の中から最大値を求める。具体的には

item_no | priceA | priceB | priceC |
-----------------------------------------------------
item01  |  6000 |  2000 |  3000
item02  |  1000 |  7500 |  4000
item03  |  1500 |  3000 |  8000

というテーブルがあった場合に、以下のような結果を出したいとします。

item_no | 最大値 | 最小値
------------------------------------------
item01  |  6000 |  2000
item02  |  7500 |  1000
item03  |  8000 |  1500

SQL> SELECT item_no
, GREATEST( priceA, priceB, priceC ) AS 最大値
, LEAST( priceA, priceB, priceC ) AS 最小値
FROM テーブル名;

これは、MySQLでも同様の関数があります。
　GREATEST()、LEAST()

上記をSQL Serverで行おうとすると、同様の関数がないため、下記のようなSQLを実行するかたちになります。

SELECT  item_no, MAX(price1) AS 最大値, MIN(price2) AS 最小値 FROM
(           SELECT item_no, priceA AS price1, priceA AS price2 FROM テーブル名
UNION ALL SELECT item_no, priceB AS price1, priceB AS price2 FROM テーブル名
UNION ALL SELECT item_no, priceC AS price1, priceC AS price2 FROM テーブル名
) AS TBL
GROUP BY item_no
ORDER BY item_no;

今日は以上まで