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

PIVOT、UNPIVOT（SQL Server）

2017年2月7日ＩＴＣシュウちゃんコメントをどうぞ

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

知人の方から、家に夏みかんの木があって、たくさん実がなるということで頂きました。その写真です。

さて、米国では、トランプ大統領が就任しましたが、就任直後から大統領令を連発し、大きな話題になるとともに、中にはきわめて大きな反発も起こっていますね。オバマケア見直し、TPPの離脱をはじめ、選挙のときから掲げてきた内容を具体的に実施しているわけですが、大きな波紋を呼んだものとしては、メキシコ国境の壁の建設、そして、中東・アフリカ７カ国からの渡航者の入国禁止（シリアからの難民受け入れの拒否も含む）です。
特に入国禁止については、米国政府内でも批判的な声が上がり、反対するデモも各地で起きていて、かなり混乱した状況ですね。この大統領令は憲法に違反しているということで、差し止めを命じるワシントン州の連邦地裁も現れました。強力な力を持つ大統領令ですが、司法のほうで歯止めをすることが可能だということが今回の内容で確認できました。さすが、民主主義の国！

しかし、おっかない世の中になってきました。これからどうなるのやら？　できれば平和裏に進んでほしい。僕もやっぱり平和が好きですから。でもよく考えてみると、うちのかみさんもおっかなかったっけなあ？

＜本日の題材＞
PIVOT、UNPIVOT （SQL Server）

以前、複数行のデータを集計して横展開という記事をアップしたことがありましたが、今回はこれと同様のことを、SQL ServerのPIVOTという関係演算子を使って試してみたいと思います。

前回の記事では、部品別の日別の仕入数量のデータが、部品コード、年月日、数量というようなレイアウトでDBに登録されている場合に、部品別に、月を横に並べて仕入数量の月別合計値を表示させるようなケースについて、case文を使いながら各月を横に並べて表示させるようにしていました。
このテーブルのデータを単純に抽出すると以下のようになります。

SELECT * FROM dbo.部品発注表;

部品別に、月を横に並べて仕入数量の月別合計値を表示させることを、pivot演算子を使って試してみます。

SELECT *
FROM (select 部品コード, substring(年月日,5,2)+'月' as '月', 数量
from [dbo].[部品発注表]) as B_tab
PIVOT (
SUM(数量) FOR 月 IN
([01月], [02月], [03月], [04月], [05月], [06月], [07月], [08月], [09月], [10月], [11月], [12月])
) as PVTab
ORDER BY PVTab.部品コード;

上記のSQLでは、FROM の後の集計対象テーブルの後に、PIVOT句を設定し、FOR .. IN で設定した値を列として出力して、それ毎にSUM関数で設定した項目[数量]を集計しています。なお、SUM関数とFOR .. IN で指定されていない「部品コード」でGROUP BYがされていて、それぞれの行が出力されています。最後のORDER BY句はオプションですが、並び順を設定できます。
Excelのピボットと同様のことが、SQLでできるのが確認できました。

また、以前別の記事「横に並んだ項目を縦の行データに変換」で、最初から列として登録されているデータを、複数行の縦のデータに変換して抽出するという件を取り上げましたが、UNPIVOT という関数がこれに該当します。

前回のデータをそのまま利用する場合に、まず、「商品売上」テーブルのデータをそのまま抽出すると、

SELECT * FROM dbo. 商品売上;

上記データについて、各月ごとのデータを行として表示する場合に、UNPIVOT演算子を使ったSQLは以下のようになります。

SELECT 商品CD, 月, 月売上
FROM
(SELECT * FROM dbo.商品売上) p
UNPIVOT
(月売上 FOR 月 IN
(売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月, 売上10月, 売上11月, 売上12月, 売上1月, 売上2月, 売上3月)
)AS unpvt
ORDER BY unpvt.商品CD desc;

上記のSQLでは、FROM の後の対象テーブル（最初から横に並んだ項目を持つ）の後に、UNPIVOT句を設定し、FOR .. IN で設定した値を行として出力して、それ毎に月の売上を抽出しています。
また、FOR .. IN で指定されていない「商品CD」でGROUP BYがされていて、商品CD毎の、各月毎の売上が出力されています。

今日は以上まで

にほんブログ村

関数

FIRST / LAST 関数

2016年2月15日ＩＴＣシュウちゃんコメントをどうぞ

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;

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

今日は以上まで

にほんブログ村

SELECT, 関数

EXISTS句

2015年1月20日ＩＴＣシュウちゃんコメントをどうぞ

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

じゃーん。また、ブルートレイン「北斗星」の写真を載せてみました。さぞかし、北斗星のファンか、鉄道マニアと思われるかもしれませんが、実はそういう訳ではありません。
帰宅途中の駅で、たくさんのマニアの方が写真を撮っていたので、ついつられて一緒になって写真を撮ってしまいました。私って、優柔不断というか、つられ易いタイプですよね。
ただ、そんなにマニアでもない私ですが、今年中にブルートレインがなくなってしまうという噂が真実だとすると、本当に残念に思ってしまうのでした。

＜本日の題材＞
EXISTS句

今日は、EXISTS句について取り上げてみます。
EXISTS句は、通常は相関副問合せ（親の問い合わせで処理された各レコードを副問い合わせにおいて評価する）のかたちで使用することが多いと思います。副問合せにおいて、結果行が1行以上存在すればTRUE、存在しなければFALSEを返し、TRUEの場合の行が結果として抽出されることになります。

記述の方法は以下のようになります。

SELECT 列名1, 列名2, … , 列名n
FROM 表名1
WHERE EXISTS
(SELECT ‘x’ FROM 表名2 -- ※
WHERE 表名1.列名x = 表名2.列名x
AND 他抽出条件)

※副問合せの中で SELECT ‘x’ のように固定値を使用しているのは、行がもどされるかどうかのみが判定されればよく、戻る列の値が何であっても問題はないこと、また定数を指定することで「存在有無の確認」であることをより明確に表現するという意味で、ORACLEなどでは慣習的に’x’などを指定することが多いようです。（SELECT * などでも問題はありません）

例として、以前使用したことのある、商品マスタと、売上テーブルを使用します。
まず、商品マスタのデータは以下：

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

売上テーブルの「2014/11」のレコードが以下の場合：

SELECT *
FROM 売上テーブル
WHERE TO_CHAR(受注日,'YYYY-MM') = '2014-11'
ORDER BY 受注番号;

商品マスタのデータで、「2014/11」に受注のあった商品の一覧を抽出する場合、

SELECT 商品CD, 商品名
FROM 商品マスタ
WHERE EXISTS (SELECT 'x'
FROM 売上テーブル
WHERE 商品CD = 商品マスタ.商品CD
AND TO_CHAR(受注日,'YYYY-MM') = '2014-11')
ORDER BY 商品CD;

2014/11」に1回でも受注のあった商品が抽出されました。

逆に、「商品マスタのデータで、「2014/11」に一度も受注のなかった商品の一覧を抽出する場合、NOT EXISTS を使用します。

SELECT 商品CD, 商品名
FROM 商品マスタ
WHERE NOT EXISTS
(SELECT 'x'
FROM 売上テーブル
WHERE 商品CD = 商品マスタ.商品CD
AND TO_CHAR(受注日,'YYYY-MM') = '2014-11')
ORDER BY 商品CD;

SQL Serverでも同様です。
「2014/11」に1回でも受注のあった商品を抽出する場合：

SELECT商品CD,商品名
FROM dbo.商品マスタ
WHERE EXISTS
(SELECT 'x'
FROM dbo.売上テーブル
WHERE 商品CD = 商品マスタ.商品CD
AND SUBSTRING(CONVERT(VARCHAR, 受注日,112),1,6) = '201411') 　　　　　　　　　　　　　　　　　　--※2
ORDER BY 商品CD;

※2：SQL Server 2012からは、FORMAT関数を使っても可能

「2014/11」に1度も受注のなかった商品を抽出する場合（FORMAT関数を使う）：

SELECT商品CD,商品名
FROM dbo.商品マスタ
WHERE NOT EXISTS
(SELECT 'x'
FROM dbo.売上テーブル
WHERE 商品CD = 商品マスタ.商品CD
AND FORMAT(受注日,'yyyyMM') = '201411')
ORDER BY 商品CD;

結果はORACLEと同様になりました。

今日は以上まで

にほんブログ村

関数

集計関数（小計、合計）

2014年12月3日ＩＴＣシュウちゃんコメントをどうぞ

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が独自のようですね。

今日は以上まで

にほんブログ村

関数

RANK, DENSE_RANK

2014年9月27日ＩＴＣシュウちゃんコメントをどうぞ

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でも同様です。

今日は以上まで

にほんブログ村

SELECT, 関数

SELECTのCASE句について

2014年7月11日ＩＴＣシュウちゃんコメントをどうぞ

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

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

それで、1年以上前からイノベーションが創出できる次世代高度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 受注年月;

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

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.受注年月;

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

今日は以上まで

にほんブログ村

SELECT, 関数

SELECTのTOP N分析（Oracle）

2014年6月27日ＩＴＣシュウちゃんコメントをどうぞ

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

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

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

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

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

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

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

今日は以上まで

にほんブログ村

SELECT, 関数

SELECT TOP句について

2014年6月18日ＩＴＣシュウちゃんコメントをどうぞ

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

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

＜本日の題材＞
SELECTのTOP句について

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

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

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

例）先頭の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 顧客番号;

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

今日は以上まで

にほんブログ村

関数

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

2014年6月2日ＩＴＣシュウちゃんコメントをどうぞ

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;

今日は以上まで

にほんブログ村

DB & SQL 技術ブログ

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

PIVOT、UNPIVOT（SQL Server）

FIRST / LAST 関数

EXISTS句

集計関数（小計、合計）

RANK, DENSE_RANK

SELECTのCASE句について

SELECTのTOP N分析（Oracle）

SELECT TOP句について

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

主にSQLについて書いていきたいと思います。