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

APPLY(SQL Server)

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

IMG_1412

自宅から会社に行く途中に道路のそばに咲いているポピーの花です。自転車で通るので、きれいに咲いているときには少し止まって眺めたりするこの頃ですが、若いときは、頭の中は仕事のことか、差し迫ったやるべきことなどでいっぱい(もしくはぼーっとしている?)で、見ていても記憶に残らないという感じだったと思います。妻にあそこにきれいな花が咲いているでしょう?と聞かれても、そうだっけ?という返事をするので、そういう感覚が欠落しているかわいそうな人だとあきれられていたものですが、少しずつ花などの自然も意識するようになってきました。

ちなみに、ポピーは色とりどりの花を咲かせるケシ科の植物の総称で、その実から採れる乳液には、入眠や麻痺の作用があることから、紀元前400年頃のギリシアでは麻酔薬や睡眠導入薬として用いられていたそうです。花言葉も「なぐさめ」「心の平静」「いたわり」「思いやり」などその効能にちなんだものが多くあり、相手をいたわる気持ちや、助けたい気持ちを表現するときによく贈られる花だそうです。

<本日の題材>
APPLY(SQL Server)

今回は、APPLYという演算子について取り上げてみます。
以前、テーブル値関数について記事として載せたことがありますが、あるテーブルのデータの値と、その値によって取得されるテーブル値関数の結果を組み合わせて抽出したいという場合に、APPLY という演算子を使用することができるようです。(SQL Server 2005から)
SQLのFROM句の後に、まずテーブルを記載し、「CROSS APLLY」、もしくは「OUTER APPLY」の後側にテーブル値関数を指定するかたちになり、SELECT のほうではテーブルの列とテーブル値関数の結果の列を含めるかたちになります。
私も今まであまり使ったことがなかったのですが、今回、試してみようと思います。

例)
使用するテーブルは、以前ブログで使ったことのある、日本の都道府県、山、川、湖などの大きさや高さや長さなどをデータにしたランキングのデータを使ってみたいと思います。
今回使用するテーブル(「ランキング」「ランキング区分」)の定義は、下記のような設定。

テーブル定義

データを抽出してみると、
「ランキング区分」テーブルは、

ランキング区分データ

「ランキング」テーブルは、

ランキングデータ

今回使用するテーブル値関数は、「区分NO」の値からランキングテーブルのデータを抽出する簡単なものです。定義は以下:

CREATE FUNCTION [dbo].[ufn_ランク上位取得]
(
         @p区分NO DECIMAL(4,0)
) RETURNS @ランク上位 TABLE (
         区分名 VARCHAR(40),
         ランキング DECIMAL(3,0),
         名称 VARCHAR(40),
         数値 DECIMAL(8,1)
)
AS
BEGIN
         INSERT @ランク上位
         SELECT
                   K.区分名
                  ,RANK() OVER (ORDER BY R.数値 DESC)
                  ,R.名称
                  ,R.数値
         FROM dbo.ランキング R
         JOIN dbo.ランキング区分 K ON R.区分NO = K.区分NO
         WHERE K.区分NO = @p区分NO;
 
        RETURN;
END;

このテーブル値関数にパラメータを直接指定して抽出すると、

SELECT * FROM dbo.ufn_ランク上位取得(1)
ORDER BY ランキング;

テーブル値関数抽出

これを、「ランキング区分」テーブルの「区分NO」をテーブル値関数にセットして、「区分NO」毎にランキング情報を抽出するように、APPLY句を使用すると、以下のように抽出できます。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
  FROM dbo.ランキング区分 K
  CROSS APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

CROSSAPPLY

ここで、「ランキング区分」テーブルにあっても、テーブル値関数で結果のないものは抽出されないのですが、外部結合のように、上記のようなデータも左側のランキング区分の情報は表示し、テーブル値関数側の結果は null で表示したい場合には、「OUTER APPLY」演算子を使用します。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
  FROM dbo.ランキング区分 K
  OUTER APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

OUTERAPPLY

 上記のように、「区分NO」=2, 3 のテーブル値関数では抽出されないデータも表示されることが確認できます。

ちなみに、APPLY演算子の右側は必ずテーブル値関数である必要はなく、上記の内容は、以下のようなSQLでも抽出できます。(CROSS APPLYの例)

SELECT K.区分NO, K.区分名, K.単位, RK.ランキング, RK.名称, RK.数値
  FROM dbo.ランキング区分 K
  CROSS APPLY
   (SELECT
                   K.区分名
                  ,RANK() OVER (ORDER BY R.数値 DESC) AS ランキング
                  ,R.名称
                  ,R.数値
         FROM dbo.ランキング R
         WHERE R.区分NO = K.区分NO) AS RK
ORDER BY K.区分NO, RK.ランキング;

CROSSAPPLY2

今まではあまり使ったことはなかったのですが、今後は、使えるときもあるのではないかと思います。

今日は以上まで

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

OVER句(Oracle)

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

IMG_1325

4月も終盤を迎え、もうすぐゴールデンウイークですね。
先日、埼玉県の埼玉県比企郡滑川町にある、国営武蔵丘陵森林公園に行ってきました。長年埼玉にいながら、実は行ったことがなかったのですが、東京ドーム65個分の広さがあり、雑木林にはたくさんの木があって、ストレス解消、森林浴にはもってこいの場所だと思いました。上の写真はハーブガーデンというハーブがいろいろと植えられているところの写真です。
下の写真は、ネモフィラという花が咲いている花畑です。

IMG_1352

日本には17個くらいの国営の公園があるのですが、ここが一番最初に整備されたところだそうです。
季節によっていろいろと見れる花もあり、サイクリングコースや子供が遊べるキッズコーナー、日本一大きなエアートランポリンなど、いろいろと楽しめそうです。ゆっくり歩いたので、一部しか回れませんでしたが、今度また家族で行こうかと思いました。
埼玉にも、探せばいろんないいところがありますね。今まで忙しさにかまけて行かなかったのが本当に残念!
子供が小さいときに、もっと自然と触れ合う機会を持つようにすればよかったね、と妻と話をすることが増えた今日この頃です。

<本日の題材>
OVER句(Oracle

前回、SQL Serverに関して、OVER句のROWSというものを題材に取り上げてみましたが、Oracleでも同様のことはできるのか?ということを調べてみたところ、以前からできるようなので、今回はOracleで確認してみたいと思います。

OracleでもOVER句と一緒に以下のような句が使用できます。

前後の行の値を取得する
LAG
--- 前の行
LEAD
--- 後の行

前回の例でも見た移動平均や移動累計の取得
ROWS n PRECEDING
--- 現在の行からn行前を含めて対象とする

最初の行、最後の行、n番目の行
FIRST_VALUE
--- パーティションの最初の行
LAST_VALUE
--- パーティションの最後の行
NTH_VALUE
--- パーティションのN番目の行

※上記で、IGNORE NULLS という句を付けると、NULL以外で最初、最後などの指定ができる

OVER句やPARTITION BY句を絡めた書き方としては、

LAG(項目名)
OVER (PARTITION BY 項目名1
     ORDER BY 項目名2
     )

項目名1ごとに、項目名2の並び順で、項目名の前の行の値(LAG)、項目名の後の行の値(LEAD)を抽出します。

LAG(項目名)のところは、以下のような設定が可能
LEAD(項目名)
FIRST_VALUE(項目名)
LAST_VALUE(項目名)

※LAG、LEADの代わりにSUM(項目名)、MAX(項目名)なども使えます。

例として、前回SQL Serverで行ったオリンピックのメダル数の抽出の例をOracleでも試してみます。

テーブルは以下の定義:
CREATE TABLE olympic_medal(
 season VARCHAR2(4)
,year   DECIMAL(4)
,color  VARCHAR(2)
,counts     INT
,CONSTRAINT pk_olympic_medal PRIMARY KEY (season, year, count));

このテーブルに、夏季、冬季の日本が取ったメダル数を登録してみます。(前回と同様にデータをInsertします。途中省略)

INSERT INTO olympic_medal VALUES('夏季',1912,'金',0);
INSERT INTO olympic_medal VALUES('夏季',1912,'銀',0);
INSERT INTO olympic_medal VALUES('夏季',1912,'銅',0);

INSERT INTO olympic_medal VALUES('冬季',2018,'金',4);
INSERT INTO olympic_medal VALUES('冬季',2018,'銀',5);
INSERT INTO olympic_medal VALUES('冬季',2018,'銅',4);

夏季、冬季それぞれの、各年毎、メダル色毎のメダル数(前回、次回も含めて)と、直近3回のメダル色毎の合計数、平均数を抽出し、年の新しい順に抽出してみます。

--まず出力結果のフォーマットを設定します。
col 季節 format a4
col 年 format 9999
col 色 format a4
col 今回 format 9999
col 前回 format 9999
col 次回 format 9999
col 直近3回合計 format 9999
col 直近3回平均 format 9999

--以下がSQL
SELECT
 season 季節
,year 年
,color 色
,counts 今回
,LAG(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ) AS 前回
,LEAD(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ) AS 次回
,SUM(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
     -- 現在の行から2行前から現在行までの3行分
     ) AS 直近3回合計
,AVG(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
   -- 現在の行から2行前から現在行までの3行分
     ) AS 直近3回平均
FROM olympic_medal
ORDER BY season, year DESC, color;

86_1

次に、金、銀、銅で色を分けずに、季節、年毎のメダル数の合計について、最初からの累計の合計個数と平均個数を抽出し、冬季、夏季の年の新しい順に表示してみます。

col 季節 format a4
col 年 format 9999
col 色 format a4
col 今回 format 9999
col 累計の合計 format 9999
col 累計の平均 format 9999

WITH C_YEAR_MEDAL(season, year, counts) AS
(SELECT season, year, SUM(counts)
   FROM olympic_medal
GROUP BY season, year
)
SELECT
season
,year
,counts
,SUM(counts)
   OVER (PARTITION BY season
     ORDER BY year
     ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW     -- 最初の行から現在行まで
     ) AS 累計の合計counts
,AVG(counts)
   OVER (PARTITION BY season
     ORDER BY year
     ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW     -- 最初の行から現在行まで
     ) AS 累計の平均counts
FROM C_YEAR_MEDAL
ORDER BY season DESC, year DESC;

86_2

上記の結果から、SQL Serverと同様に、OVER句を使用することで、各行に対しての直近の数回や累計の合計などを抽出することができることが確認できました。

今日は以上まで

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

OVER句のROWS(SQL Server)

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

IMG_1284

2018年も、あっという間に3月の最後の週を迎えました。
ここしばらく、開発の案件が忙しく、ブログの原稿を作る時間が取れませんでした。
多少落ち着いてきたので、久しぶりに書いています。上の写真は、休みの日に息子と神奈川のほうに行く機会があり、桜が満開になっていたので写真を撮ってきました。やはり埼玉に比べると、少し神奈川のほうが暖かい気がしますし、桜も開花が早いですね!
久しぶりに長男と二人きりで電車に乗ったり、歩いたりして過ごしました。今度高校に入学しますが、いつの間にか成長している息子を見て、無事に育ってくれてとても感謝の気持ちが湧いてきました。これからいろいろとぶつかる壁や課題も出てくると思うけれど、頑張って乗り越えてほしいです。

それから、dbSheetClientの呉信用金庫様の事例がアップされています。
Excel I/Fの各種業務をdbSheetClientを使って、
圧倒的な処理スピードの向上を実現!(10分以上が瞬時に)
トップマネジメントを含めて全社員(約650名)が利用!
興味のある方は以下をご参照ください。
https://www.newcom07.jp/dbsheetclient/usrvoice/kure_shinkin.html

 <本日の題材>
OVER句のROWS(SQL Server)

以前、このブログでも「RANK, DENSE_RANK」という題目で、ランキング関数の中でOVER句を使った内容を題材として取り上げましたが、OVER句と一緒に使用する引数に、ROWS句というものがあり、行の計算対象を指定することができるという機能がありましたので、今回、それを取り上げてみます。これは、SQL Server 2012からの機能で、以下のような記述で範囲を指定します。

OVER (PARTITION BY 項目名1
     ORDER BY 項目名2
     ROWS BETWEEN A AND B
     )

上記の書き方で、項目名1毎にグループ化しながら、項目名2の並び順で、AからBの範囲内での抽出結果を表示することができます。(PARTITION BY句、ORDER BY句、ROWS句は必要なときのみ指定します)

このときの、A、Bの記述の仕方には、以下のようなものがあります。

CURRENT ROW
  --- 現在の行
n PRECEDING
  --- 現在の行からn行前
n FOLLOWING
  --- 現在の行から n 行後
UNBOUNDED PRECEDING
  --- パーティションの最初の行
UNBOUNDED FOLLOWING
  --- パーティションの最後の行

例)今回は、平昌オリンピックで日本中が盛り上がったことから、オリンピックで日本が取ったメダル数をデータにしたものを使用します。

テーブルは以下の定義:
CREATE TABLE DBO.メダル数(
季節  VARCHAR(4)
,年    DECIMAL(4)
,メダル色 VARCHAR(2)
,個数     INT
,CONSTRAINT pk_メダル数 PRIMARY KEY (季節,年,メダル色));

このテーブルに、夏季、冬季の日本が取ったメダル数を登録してみます。

INSERT INTO dbo.メダル数 VALUES('夏季',1912,'金',0);
INSERT INTO dbo.メダル数 VALUES('夏季',1912,'銀',0);
INSERT INTO dbo.メダル数 VALUES('夏季',1912,'銅',0);
 
INSERT INTO dbo.メダル数 VALUES('夏季',1920,'金',0);
INSERT INTO dbo.メダル数 VALUES('夏季',1920,'銀',2);
INSERT INTO dbo.メダル数 VALUES('夏季',1920,'銅',0);

--途中省略
INSERT INTO dbo.メダル数 VALUES('冬季',2014,'金',1);
INSERT INTO dbo.メダル数 VALUES('冬季',2014,'銀',4);
INSERT INTO dbo.メダル数 VALUES('冬季',2014,'銅',3);

INSERT INTO dbo.メダル数 VALUES('冬季',2018,'金',4);
INSERT INTO dbo.メダル数 VALUES('冬季',2018,'銀',5);
INSERT INTO dbo.メダル数 VALUES('冬季',2018,'銅',4);

夏季、冬季それぞれの、各年毎、メダル色毎のメダル数と、直近3回のメダル色毎の合計数、平均数を抽出し、年の新しい順に抽出してみます。

SELECT
  季節
,年
,メダル色
,個数
,SUM(個数)
    OVER (PARTITION BY 季節,メダル色
      ORDER BY 年
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  
   -- 現在の行から2行前から現在行までの3行分
      ) AS 直近3回の合計個数
,AVG(個数)
    OVER (PARTITION BY 季節,メダル色
      ORDER BY 年
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    -- 現在の行から2行前から現在行までの3行分
      ) AS 直近3回の平均個数
FROM dbo.メダル数
ORDER BY 季節,年 DESC,メダル色;

sql1

次に、金、銀、銅で色を分けずに、季節、年毎のメダル数の合計について、最初からの累計の合計個数と平均個数を抽出し、冬季、夏季の年の新しい順に表示してみます。

WITH C_YEAR_MEDAL(季節, 年, 個数) AS
(SELECT 季節, 年, SUM(個数)
    FROM dbo.メダル数
  GROUP BY 季節, 年
)
SELECT
  季節
 ,年
 ,個数
,SUM(個数)
      OVER (PARTITION BY 季節
           ORDER BY 年
           ROWS BETWEEN UNBOUNDED PRECEDING 
                           AND  CURRENT ROW
                           -- 最初の行から現在行まで
           ) AS 累計の合計個数
,AVG(個数)
       OVER (PARTITION BY 季節
           ORDER BY 年
           ROWS BETWEEN UNBOUNDED PRECEDING
                            AND CURRENT ROW
                           -- 最初の行から現在行まで
           ) AS 累計の平均個数
FROM C_YEAR_MEDAL
ORDER BY 季節 DESC, 年 DESC;

sql2_1
sql2_2

上記の結果から、OVER句 + ROWS句を使用することで、各行に対しての直近の数回や累計の合計などを抽出することができることが確認できました。

今日は以上まで

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

PIVOT、UNPIVOT(SQL Server)

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

夏みかん

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

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

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

 <本日の題材>
PIVOT、UNPIVOT (SQL Server)

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

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

SELECT * FROM dbo.部品発注表;

blog75_部品発注表

部品別に、月を横に並べて仕入数量の月別合計値を表示させることを、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.部品コード;

 blog75_pivot

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

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

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

SELECT * FROM dbo. 商品売上;

blog75_商品売上

 上記データについて、各月ごとのデータを行として表示する場合に、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;

blog75_unpivot

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

今日は以上まで

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

FIRST / LAST 関数

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

SONY DSC

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

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

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

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

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

<本日の題材>
FIRST/LAST関数 (ORACLE)

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

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

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

以前に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;

syomst_min_max

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

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;

syomst_first_last

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

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

syomst_order

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

次に、各商品の金額を表示するとともに、各分類の最も安い金額と高い金額を同じ行で表示するということを行ってみます。先ほどの、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;

syomst_price_first_last

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

今日は以上まで

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

EXISTS句

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

IMG_0574_3

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

<本日の題材>
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;

exists_商品マスタ表示

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

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

exists_150119受注

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

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

exists_1411受注のあった商品
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;

 not_exists_1411受注のなかった商品

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;

exists_1411受注のあった商品_sqlsv

※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;

not_exists_1411受注のなかった商品sqlsv

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

今日は以上まで

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

集計関数(小計、合計)

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