動的SQL(2)

ITコーディネータのシュウです。
IMG_0294
きれいな白い渡り鳥?が川辺に止まっているのを偶然撮った写真です。シラサギでしょうか?
昔から、シラサギは縁起がいい鳥と言われているようで、ときに、田んぼにシラサギがいっぱいいるのを見たことがあって、ものすごく縁起がいい!と感動したことを覚えています。
シラサギは、汚い田んぼや河川には住まないとされていて、シラサギのいるところはきれいなところだといえるようですね。

<本日の題材>
動的SQL(Oracle)

今回は、前回に引き続いて動的SQLについての内容です。前回は動的なDDL文をEXECUTE IMMEDIATE文を使用して実行する例を取り上げましたが、今回は、動的なDML文を実行する例です。

例)対象レコードの件数を確認した後に、レコードを削除するSQLで、対象のテーブル名とWhereの条件文をパラメータとして渡すプロシージャ(動的なDML文)

CREATE OR REPLACE PROCEDURE del_table(tab_name VARCHAR2, clause VARCHAR2)
IS
Sql_stmt1 VARCHAR2(100);
Sql_stmt2 VARCHAR2(100);
V_COUNT NUMBER(4);

BEGIN
Sql_stmt1 := 'SELECT COUNT(*) FROM '||tab_name||' WHERE ' || clause;
DBMS_OUTPUT.PUT_LINE(Sql_stmt1);
EXECUTE IMMEDIATE Sql_stmt1 INTO V_COUNT;
DBMS_OUTPUT.PUT_LINE('件数は '||V_COUNT||' 件です');

Sql_stmt2 := 'DELETE FROM '||tab_name||' WHERE ' || clause;
DBMS_OUTPUT.PUT_LINE(Sql_stmt2);
EXECUTE IMMEDIATE Sql_stmt2;
COMMIT;
END;
/

※DBMS_OUTPUT パッケージは標準出力にメッセージを表示することができ、デバッグ目的などでよく使用されます。

受注テーブルのデータを指定した条件で削除します。
その前に、データを確認

SELECT * FROM 受注テーブル
ORDER BY 受注NO;
受注テーブル結果1

受注テーブルの定義は以下:
受注テーブル定義

プロシージャの実行
パラメータとして、'受注テーブル'、'受注日 = ''20141024''' を渡します。
('で囲む文字列の中に'(シングルクォーテーション)を含む場合には、'を2つ連続して記述する必要があります)

EXECUTE del_table('受注テーブル','受注日 = ''20141024''');
execute_del

処理の結果、受注テーブルを再度確認します。
SELECT * FROM 受注テーブル
ORDER BY 受注NO;
受注テーブル結果2

受注日が「20141024」のデータが削除され、delete文が実行されたことが確認できました。

パラメータのテーブル名、条件を別の値にすれば、異なるテーブルから異なる条件のデータを削除することができます。

今日は以上まで

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

動的SQL(1)

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

IMG_0895

娘がこの夏にボランティアの関係でカンボジアに行ってきたときに撮った写真です。

写真はメコン川で、中国チベット高原を源流としてミャンマー・ラオス国境、タイ・ラオス国境、カンボジア・ベトナムを通り南シナ海に抜ける、世界でも有数の国際河川の一つです。
カンボジアではこの夏も、豪雨のためメコン川流域で洪水が起きて、大変だったようです。
娘が訪問した村では、下の写真のように、水害で家の下のほうは水につかってしまい、船にたまった水をかきだしている様子を撮っています。(どの家も交通手段として船を持っているとのこと)
日本も台風や地震、火山噴火など様々な災害が起きており、犠牲になった方のご冥福を祈るばかりですが、世界でもいろいろと災害があって、その中で暮らして行かなければならないので、苦労が多いですね。

IMG_1042

<本日の題材>
動的SQL(Oracle)

通常のSQLでは、UPDATEやINSERTなどの処理をどの表のどの項目に対して行うのか、というのがあらかじめ明確になっているのですが、一部のケースでは、様々なSQL文を実行時に作成、またはパラメータとして受けて処理を行うという場合があります。
この場合は、SQL文の構成は実行時まで明確ではなく、実行のたびに変わる可能性があり、これを動的SQLといいます。この動的SQLは実行時にプログラムに入力、または作成される文字列に格納されるかたちになります。

動的SQLを実行する方法としては、EXECUTE IMMEDIATE文を使用する方法、カーソル変数を使用する方法などがありますが、今回は、EXECUTE IMMEDIATE文を使用する方法を取り上げます。

例)パラメータとして、削除するテーブル名を渡すプロシージャ(動的なDDL文)

CREATE OR REPLACE PROCEDURE truncate_table(tab_name VARCHAR2)
IS
Sql_stmt VARCHAR2(100) := 'TRUNCATE TABLE '|| tab_name;
BEGIN
EXECUTE IMMEDIATE Sql_stmt;
END;
/

テーブル:商品マスタのデータを削除します。
その前に、データを確認

SELECT * FROM 商品マスタ;
商品マスタ

プロシージャの実行
パラメータとして、「商品マスタ」を渡します。

EXECUTE truncate_table(‘商品マスタ’);
execute1

SELECT COUNT(*) FROM 商品マスタ;
execute_結果1

データが1件もなくなり、truncate文が実行されたことが確認できました。
今日は以上まで

にほんブログ村 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技術ブログへ
にほんブログ村

MERGE文(2)

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

1395797133260_2

前回の題材に上げたMERGE文について、SQL Serverではできないと思っている方もいるのではないかという話を聞き、今回はSQL Serverでもできることを報告します。

<本日の題材>
MERGE文(SQL Server編)

MERGE文の説明は前回既に行なったので、今回は省きます。
前回と同じ例をSQL Server2008で試してみました。

元データを商品テーブルA、登録・更新したいテーブルを商品テーブルBとします。

CREATE TABLE dbo.商品テーブルA(
商品CD VARCHAR(10)
, 商品名 VARCHAR(20)
, CONSTRAINT PK_商品テーブルA PRIMARY KEY (商品CD));

CREATE TABLE dbo.商品テーブルB(
商品CD VARCHAR(10)
, 商品名 VARCHAR(20)
, CONSTRAINT PK_商品テーブルB PRIMARY KEY (商品CD));

データがそれぞれ以下のような場合:
SELECT * FROM dbo.商品テーブルA;
sqls_merge_a

SELECT * FROM dbo.商品テーブルB;
sqls_merge_b

商品テーブルAのデータを元に、商品テーブルBに既に商品CDが存在するデータは、商品名を変更し、商品CDが存在しないものは商品テーブルBにデータを登録する場合のMERGE文を実行します。

MERGE INTO dbo.商品テーブルB
USING dbo.商品テーブルA
ON (dbo.商品テーブルA.商品CD = dbo.商品テーブルB.商品CD)
WHEN MATCHED THEN
UPDATE SET
商品名 = dbo.商品テーブルA.商品名
WHEN NOT MATCHED THEN
INSERT (商品CD,商品名)
VALUES (商品テーブルA.商品CD,商品テーブルA.商品名);
-- (2 行処理されました)

上記を実行した後に、商品テーブルBを確認すると、
SELECT * FROM 商品テーブルB;
sqls_merge_b_2

商品CD:00001 については、商品名のみ更新し、はじめは存在していなかった商品CD:00002 のデータが追加されているのが、SQL Server2008でも確認できました。

※SQL Server2008からMERGE文は対応するようになっていますので、それ以降のバージョンでは問題なく動作します。

別の例)
上記のような同じ構造のテーブルからの更新・登録ではなく、ユーザが何かのアプリで入力した場合のような定数値の情報からデータを更新・登録するケースもあるので、その際のMERGE文の例を以下に追記します。

例えば、変数@商品CD、@商品名を使用し、商品CD「00001」、商品名「商品1(甘さ控えめ)」という値を代入して使用する場合:

DECLARE @商品CD VARCHAR(10);
DECLARE @商品名 VARCHAR(20);

SET @商品CD = '00001';
SET @商品名 = '商品1(甘さ控えめ)';

MERGE INTO dbo.商品テーブルB
USING (SELECT @商品CD AS 商品CD, @商品名 AS 商品名) AS 商品情報
ON (dbo.商品テーブルB.商品CD = 商品情報.商品CD)
WHEN MATCHED THEN
UPDATE SET
商品名 =商品情報.商品名
WHEN NOT MATCHED THEN
INSERT (商品CD,商品名)
VALUES (商品情報.商品CD,商品情報.商品名);
--(1 行処理されました)

上記を実行した後に、商品テーブルBを確認すると、
SELECT * FROM 商品テーブルB;
sqls_merge_b_3

商品CD:00001 については、商品名が更新されているのが確認できます。

今日は以上まで

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

MERGE文

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

IMG_0409

本当に久しぶりのアップになります。
システムの納品の対応で、ここ1ヶ月、精神的にも肉体的にもかなり忙しくて大変な状態でした。受託開発は、途中で変更や内容が膨らんで、予定通りに進めるのが難しく、本当に納期に間に合うのか?と冷や汗をいつもかくことが多いと思うのは私だけでしょうか?
そこのスコープの管理を如何にお客様との間できちんと行うのかがとても重要であることはいつも思っているのですが。。。
必死になってときに残業も惜しまず、頑張るのですが、なぜそうなっているのか事情がわかりにくい分野でもあり、意外に周りの目は冷たかったりして...トホホ。
でも、問題を一つ一つ解決していき、納期が見えてきたときの達成感も、我々しか味わえないものかも知れませんね。といっても、最後の検収が上がるまでは何があるかわからないので、まだ緊張が解けないこの頃です。

心の余裕が出てきたところで、またブログ再開します。

<本日の題材>
MERGE文

ある抽出結果の情報を指定のテーブルに登録したいけれど、既にそのデータ(キー情報が同じデータ)が登録したいテーブルに存在していたら、値の違う部分を更新し、まだ存在していなければ登録する、というような要件に出くわすときが時折あります。

このとき、データが既に存在しているかどうかを確認して、IF文でUPDATE、INSERTを切り分けるということは、結構面倒ですが、これを一度のSQLで実行することができるのが、MERGE文です。

簡単な例として、同一構造のテーブルを元データとして、データを更新、なければ登録する場合は、下記のようになります。

元データを商品テーブルA、登録・更新したいテーブルを商品テーブルBとします。

CREATE TABLE 商品テーブルA(
商品CD VARCHAR2(10)
, 商品名 VARCHAR2(20)
, CONSTRAINT PK_商品テーブルA PRIMARY KEY (商品CD));

CREATE TABLE 商品テーブルB(
商品CD VARCHAR2(10)
, 商品名 VARCHAR2(20)
, CONSTRAINT PK_商品テーブルB PRIMARY KEY (商品CD));

データがそれぞれ以下のような場合:

SELECT * FROM 商品テーブルA;
merge_a

SELECT * FROM 商品テーブルB;
merge_b

商品テーブルAのデータを元に、商品テーブルBに既に商品CDが存在するデータは、商品名を変更し、商品CDが存在しないものは商品テーブルBにデータを登録する場合に以下のMERGE文を実行します。

MERGE INTO 商品テーブルB
USING 商品テーブルA
ON (商品テーブルA.商品CD = 商品テーブルB.商品CD)
WHEN MATCHED THEN
UPDATE SET
商品名 = 商品テーブルA.商品名
WHEN NOT MATCHED THEN
INSERT (商品CD, 商品名)
VALUES (商品テーブルA.商品CD, 商品テーブルA.商品名)
;

上記を実行した後に、商品テーブルBを確認すると、

SELECT * FROM 商品テーブルB;
merge_b_2

商品CD:00001 については、商品名のみ更新し、はじめは存在していなかった商品CD:00002 のデータが追加されているのが確認できます。(Oracleで検証)

今日は以上まで

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

縦に並んだ複数行のデータをカンマ区切りで横並びに表示

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

IMG_0402

写真は、前回と同じく「古代蓮の里」で撮った古代蓮の花です。
今年も暑い夏が続くと思えば、大型台風の影響で大雨などもあり、とても変わりやすく難しい天候ですね。台風などでこれ以上被害が出ないことを祈りつつ、天気予報をいつもチェックして注意しないといけないですね。

<本日の題材>
縦に並んだ複数行のデータをカンマ区切りで横並びに表示する方法(SQL Server)

縦に並んだ複数行のデータを、カンマ区切りで横並びに表示する方法について、最近SQL Serverで確認したので、忘れないようにアップしておきます。
SQL Serverでは、SQLクエリにFOR XML句というものを使うことで、SQLクエリの結果をXML形式で取得することができるのですが、今回はそれを使ってみる方法です。

例えば、地方とその地方に属する都道府県を、それぞれ、地域マスタ、府県マスタということで、下記のように持たせてみます。

SELECT * FROM dbo.地域マスタ
ORDER BY 地域CD;
地域マスタ

SELECT * FROM dbo.府県マスタ
ORDER BY 府県CD;
府県マスタ
… 続きは省略

ここで、上記の地域ごとの都道府県の情報を、カンマ区切りで横並びに表示させたいときに、以下のようにすると可能です。

SELECT
A.地域CD
, A.地域名
,(SELECT都道府県名 + ','
FROM dbo.府県マスタ
WHERE 地域CD = A.地域CD
ORDER BY 府県CD
FOR XML PATH('')) AS 都道府県名
FROM dbo.地域マスタ A
ORDER BY A.地域CD
;
府県結果

FOR XML句をPATHモードで指定し、スペース区切りの文字列を生成しながら、カンマでつなげるというかたちにすると、上記の結果のようになります。
今日は以上まで

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

外部結合について(2)

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

前回紹介したさきたま古墳のそばに、古代蓮の里という公園(行田市の天然記念物であり市の花である「古代蓮(行田蓮)」をシンボルとする公園)があり、そこに行った時の写真です。6月下旬から8月上旬にかけて42種類12万株の花蓮を見ることができるということですが、花弁の数が少ない原始的な形態を持つ行田蓮は、約1400~3000年前の蓮であると言われています。 午前中、正午くらいまでが見ごろのようですよ! 何か神秘的な雰囲気もあり、結構きれいですね。

<本日の題材> 外部結合について(SQL Server 編) 前回実施した外部結合の内容を、SQL Server2012でも試してみましたので、報告します。

EMP表(従業員表)とDEPT表(部署表)をDEPTNOを条件として外部結合する例です。

1.左外部結合: LEFT [OUTER] JOIN の例 ([OUTER]は省略可能)

SELECT E.empno, E.ename, E.job, D.dname, D.deptno FROM dbo.EMP E LEFT OUTER JOIN dbo.DEPT D ON E.deptno = D.deptno ORDER BY E.empno;

外部結合1_sqlserver

2.右外部結合: RIGHT [OUTER] JOIN の例

SELECT E.empno, E.ename, E.job, D.dname, D.deptno FROM dbo.DEPT D RIGHT OUTER JOIN dbo.EMP E ON D.deptno = E.deptno ORDER BY E.empno;

外部結合1_sqlserver

3.完全外部結合: FULL [OUTER] JOIN

SELECT E.empno, E.ename, E.job, D.dname, D.deptno FROM dbo.EMP E FULL OUTER JOIN dbo.DEPT D ON E.deptno = D.deptno ORDER BY E.empno;

完全外部結合_sqlserver

SQL Server2012でも同様な内容を確認しました。ちなみに、SQL Server2000の頃は、外部結合は *= という演算子を使っていましたが、データベースの互換性レベル(モード)が90以上ではこの演算子はサポートされなくなっていますね。 Oracleも昔は (+) というのを使用していましたが。。。

今日は以上まで

 

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

外部結合について

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

埼玉古墳__

映画「のぼうの城」で有名な忍城のある埼玉県行田市にあります、さきたま古墳に行ってきました。前方後円墳8基と円墳1基の5~7世紀ごろの大型古墳が残る、全国有数の大型古墳群です。写真は、直径105m、高さ約19mの日本最大の円墳である丸墓山古墳の頂上から見た稲荷山古墳です。

ところで、ここのところ、大手通信教育の会社の個人情報漏えい事件、マレーシア航空機の撃墜事件、中国の食品会社の使用期限切れ食肉使用問題、イスラエルとパレスチナの紛争など、様々な事件や問題が世界中で起きていることを感じます。

個人情報漏洩の件では、私も以前、協力会社のSEとして、ある大手会社の個人情報を扱う立場にいたこともありましたが、そこでは定期的に協力会社の社員にも個人情報に関する教育やWEBでのテストなどを実施して、危機意識を高める努力をしていたこともありますし、何か起きたときに真っ先に疑われるのが協力会社のSEということも感じていたので、とても注意して作業をしていました。

2003年に成立し、2005年から施工された個人情報保護法が、ビッグデータの利活用に関連して来年には改正される方向ですが、今回の件も考慮したかたちでの対応が必要と思われますね。

うちの子供もまさに今回問題となった通信教育を受けていて、名簿を買ったといわれる企業から急にDMが届いたので、何で急に?と不思議に思ったのを覚えています。
しかし、成人式が近い子供の振袖の宣伝やら、高校卒業が近い子供への各種専門学校からのDM、その他塾や通信教育の宣伝、本当にたくさんのDMが日々送られてきますね。どこの会社も頑張っているのはわかるのですが。
ときに、DMを包んでいるプラスチックゴミの分別など、手間がかかるなと感じてしまう私でした。

<本日の題材>
外部結合について

SELECT文で、複数のテーブルを結合して結果を出す場合に、ある条件で、両方のテーブルに一致する値を持つレコードだけでなく、一方のテーブルにしかないレコードも一緒に表示させたいケースも結構あります。今回は、外部結合についてちょこっと再確認してみます。

外部結合の種類と構文:
1.左外部結合: LEFT [OUTER] JOIN
SELECT .., .., ..
FROM テーブルA LEFT [OUTER] JOIN テーブルB
ON テーブルA.列名 = テーブルB.列名

左側の表(テーブルA)の内容を全て抽出し、右側の表(テーブルB)は  条件で一致するレコードのみを表示します。

2.右外部結合: RIGHT [OUTER] JOIN
SELECT .., .., ..
FROM テーブルA RIGHT [OUTER] JOIN テーブルB
ON テーブルA.列名 = テーブルB.列名

右側の表(テーブルB)の内容を全て抽出し、左側の表(テーブルA)は  条件で一致するレコードのみを表示します。

3.完全外部結合: FULL [OUTER] JOIN
SELECT .., .., ..
FROM テーブルA FULL [OUTER] JOIN テーブルB
ON テーブルA.列名 = テーブルB.列名

左側の表(テーブルA)、右側の表(テーブルB)とも条件に一致しない  レコードも含めてすべて表示します。

例)よくある例としては、EMP表(従業員表)とDEPT表(部署表)があって、DEPTNOという部署コードが2つのテーブルをジョインするキーとなっている場合で、従業員と所属部署の一覧を見たいというときに外部結合を使用する例です。このとき、新しい従業員を雇ったのでEMP表にデータを登録するのですが、所属部署は設定せず、後から決めるというときに、その時点での一覧を見ると通常の結合(内部結合)では部署コードのない新入社員が抽出されないため、以下のように外部結合で抽出します。

新入社員の登録:
INSERT INTO EMP(empno,ename,hiredate) VALUES(8000,'SATOU','2014-07-20');

従業員と所属部署の一覧:
SELECT E.empno, E.ename, E.job, D.dname, D.deptno
FROM emp E
LEFT OUTER JOIN dept D ON E.deptno = D.deptno
ORDER BY E.empno;

外部結合1

上記は左外部結合ですが、これを右外部結合で表すと、
SELECT E.empno, E.ename, E.job, D.dname, D.deptno
FROM dept D
RIGHT OUTER JOIN emp E ON D.deptno = E.deptno
ORDER BY E.empno;

外部結合2

さらに、完全外部結合で表すと、DEPT表には存在するが、EMP表ではその部署コードを持つ従業員が存在しない部署も表示されます。
SELECT E.empno, E.ename, E.job, D.dname, D.deptno
FROM emp E
FULL OUTER JOIN dept D ON E.deptno = D.deptno
ORDER BY E.empno;

完全外部結合

今回は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技術ブログへ
にほんブログ村

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