カテゴリー別アーカイブ: 関数

集計関数(小計、合計)

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

1389614692322

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

<本日の題材>
集計関数(小計、合計)

システムの帳票を出力する場合に、よく小計や合計を出力するケースがありますよね。これを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.分類;

ora_groupby

次に、受注日ごと、及び商品の分類ごとの集計に、小計、合計を出すように、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.分類;
ora_groupby_rollup

ここで、小計、合計のときに 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.分類;
ora_groupby_rollup_grouping
※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.分類;
ora_groupby_cube
最後の総合計のところで、商品の分類ごとのデータが抽出されているのが 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.分類;
sqlsv_groupby

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.分類;
sqlsv_groupby_rollup2
※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.分類;
sqlsv_groupby_cube2
※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技術ブログへ
にほんブログ村

RANK, DENSE_RANK

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

IMG_1063

関東最古の神社の一つと言われる鷲宮神社の『土師祭』(はじさい)が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.商品マスタ;
rank順位

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

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

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.商品マスタ;
parttion_by順位付

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SELECTのCASE句について

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

1402143552101

たまにはITコーディネータについての情報もあったほうがいいかと思い、ITコーディネータ協会のHPから気づいた点を報告します。結構HPもリニューアルしていますね。

それで、1年以上前からイノベーションが創出できる次世代高度IT人材の育成について、独立行政法人情報処理推進機構(IPA)と共同で呼びかけ「IT融合人材育成連絡会」を立ち上げていました。そして「IT融合人材」の具体的な育成と組織のあり方についての検討成果についての最終報告書がホームページで公開されているようです。
http://www.itc.or.jp/news/inv20140325.html

イノベーションを創出できる人材を、あるプロセスに従うことで効果的に育成できれば?日本の将来にとっても本当に大きいことですよね。頑張れニッポン!

<本日の題材>
SELECTのCASE句について

今回は、最近システムを作り込むときに確認した内容で、SELECT文で複数の条件でのそれぞれの件数の結果を1つのSQLで実施する際に、CASE句を利用したケースについて記載します。(ORACLEです)

例)あるテーブル(受注実績テーブルとします)のデータで、あるフラグ(判定フラグとします)に「1」が立っているものと「0」のもの、及び全体の合計件数を抽出する。

SELECT
受注年月
, COUNT(CASE 判定フラグ WHEN '1' THEN 1 ELSE NULL END) AS 判定有
, COUNT(CASE 判定フラグ WHEN '0' THEN 1 ELSE NULL END) AS 判定無
, COUNT(*) 合計件数
FROM 受注実績
GROUP BY 受注年月
ORDER BY 受注年月;

oracle_case_1

他の方法としては、インラインビューを使用して下記のように行うこともできます。

SELECT
受注実績1.年月
, 受注実績1.件数 判定有
, 受注実績2.件数 判定無
, 受注実績3.件数 合計件数
FROM
(SELECT 受注年月, COUNT(*) 件数 FROM 受注実績
WHERE 判定フラグ = '1'
GROUP BY 受注年月) 受注実績1
, (SELECT 受注年月, COUNT(*) 件数 FROM 受注実績
WHERE 判定フラグ = '0'
GROUP BY 受注年月) 受注実績2
, (SELECT 受注年月, COUNT(*) 件数 FROM 受注実績
GROUP BY 受注年月) 受注実績3
WHERE 受注実績1.受注年月 = 受注実績2.受注年月
AND 受注実績1.受注年月 = 受注実績3.受注年月
ORDER BY 受注実績1.受注年月;

oracle_case_2

 

でも、CASE句を使って抽出したほうが簡単ですね。
CASE文はいろいろなところで使用できますが、1つの例としてあげました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SELECTのTOP N分析(Oracle)

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

公園_4

サッカーワールドカップは、日本は十分力を出し切れず、一次予選敗退。本当に残念でした。最後のコロンビア戦は結構頑張っていたし、前半最後に追いついて、これはやってくれるんじゃないかと思ったのですが、カウンターを見事に決められ、終わってみれば完敗でした。アジアのチームはどこも1勝もできず、世界の壁はやはり厚いです。

また、忙しかったお客様の開発案件も、いよいよ運用開始が近くなってきました。まだ気が抜けません。

<本日の題材>
SELECTのTOP N分析について

前回、Microsoft SQL ServerのSELECTでのTOP句について見ましたが、今回はこれと同様のことをOracleで行う場合について考えてみます。Oracleの場合には、ROW_NUMBER関数を使うと同様なことが可能です。

例)今日の受注テーブルから受注番号順に先頭5件を抽出する。 SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY 受注NO) JNO, 受注NO, 受注日, 顧客番号, 決済金額 FROM 受注テーブル)
WHERE JNO <=5 ORDER BY JNO;

Oracle_row_number1

また、ROW_NUMBER関数を使用せずに、インラインビューを使用した問い合わせでも可能です。

SELECT * FROM (
SELECT * FROM 受注テーブル
ORDER BY 受注NO
)
WHERE ROWNUM <= 5
ORDER BY 受注NO;

Oracle_インラインビュー1

顧客番号順に並べた最初の5件ということにしたい場合には、以下のようになります。

SELECT * FROM (
SELECT * FROM 受注テーブル
ORDER BY 顧客番号
)
WHERE ROWNUM <= 5
ORDER BY 顧客番号;

Oracle_インラインビュー2

OracleにはTOP句はないですが、上記のような方法で同様のことができますね。
※ちなみに、SQL ServerでもROW_NUMBER関数は使えます。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SELECT TOP句について

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

1402143571315

いよいよサッカーワールドカップが始まりました。
今回は、逆転勝ちの試合も結構多いようですね。スペインがオランダに5対1で敗れた衝撃的な試合もありました。日本も初戦のコートジボアール戦は逆転負けしてしまいましたが、2戦目のギリシャ戦は、是非勝利を飾ってほしいものです。


<本日の題材>
SELECTのTOP句について

Microsoft SQL Serverの場合には、SELECTでのクエリの結果の行数を指定した行数、または割合(何パーセントか)に制限するTOP句という便利なものがあります。

今回はこれについて見てみたいと思います。

例)今日の受注テーブルから先頭5件を抽出する。
SELECT TOP 5 * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112);

sqlserver_9回目_1

例)先頭の5%を抽出するという場合は以下:
SELECT TOP 5 percent * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112);

これを、さらに、顧客番号順に並べた最初の5件を表示したいという場合には、以下のようになります。

SELECT TOP 5 * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112)
ORDER BY 顧客番号;

sqlserver_9回目_2

結構便利に使えます。
次回は、これをOracleで表現する場合を題材にする予定です。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SQL 比較関数(Greatest、Least)について

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

お久しぶりです。

<本日の題材>
比較関数について

データベースの違いによって、ある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 テーブル名;

greatest_ora

これは、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;

sqlserver_greatest

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村