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

筑波山に行ったときに、頂上付近で撮った写真です。私ははじめてだったのですが、何度も行ったことのある知人数人と一緒に、子供たちを連れて行ってきました。
筑波山は関東地方に人が住むようになったころから、信仰の対象として仰がれてきたということで、男体山（標高871m）と女体山（標高877m）という2つの山をいざなぎ、いざなみの神として仰いできたということです。山の中腹に筑波山神社の拝殿があり、頂上付近にも写真のような本殿があります。
頂上からはまわりが一望できて、晴れていれば素晴らしい景色になります。ロープウェイもありましたが、頑張って子供たちと登りました。ただし、結構急な坂もあり、久しぶりの運動で、登るのが大変でした。あらためて年を感じさせられました。

＜本日の題材＞
UNION、UNION ALL演算子

前回、前々回と集合演算の差、積を行う演算子について見ました。順番が逆になってしまった気がしますが、今日は複数の検索結果を統合する和集合を求める演算子について見てみます。

複数のSELECT文での問い合わせの結果に対して、和集合を抽出する演算子として、UNION、UNION ALLがあります。この演算子は、Oracle、SQL Serverともに使用できます。

前回の抽出で使用したtbl_A、tbl_Bを利用して、少なくともどちらかのテーブルに存在するレコードを抽出してみます。このとき、重複するレコードは1行にまとめて表示します。
（SQL Serverで確認）

SELECT * FROM tbl_A
UNION
SELECT * FROM tbl_B;

 前々回、tbl_A、tbl_B のデータを確認していますが、たしかに、tbl_A、tbl_B のどちらかに存在するレコードが全て抽出されました。

ここで、重複する行を1つにまとめずに、そのまま抽出する場合には、「UNION ALL」句を使用します。上記の例を、UNION ALLを使用して実行すると、

SELECT * FROM tbl_A
UNION ALL
SELECT * FROM tbl_B;

 tbl_A、tbl_B のデータが重複データをまとめずに全て抽出されているのが確認できます。

※4パフォーマンスの観点から言うと、重複データを1行にまとめる処理（DISTINCT、UNION、INTERSECT、MINUSなど）は暗黙的にソート処理を行うため、その分処理が重くなり、レスポンスとしては遅くなる傾向があります。重複するデータがないということがあらかじめ分かっている場合には、和集合の場合はUNIONではなくUNION ALLを使用するようにしたほうがよいと言えます。

前々回で、同じ構造の2つのテーブルA、Bがあったとして、その2つのテーブルが完全に一致しているかどうかを確認するために、MINUS演算子を使用する例を上げましたが、一致するかどうかだけを確認したいならば、UNIONを使って確認するという方法もあります。

A, Bどちらも同じ行数のレコードが存在しているという状況がわかっていれば、下記のSQLを実行します。

SELECT COUNT(*)
  FROM
   (
    SELECT * FROM tbl_A
    UNION
    SELECT * FROM tbl_B
    ) AS tbl_C;

この結果が、A, B のテーブルのレコード数と一致していれば、A、Bは一致しているということができることになります。

ちなみに、上のサンプルデータで試した場合、以下のようになり、tbl_A、tbl_Bは一致していないことがわかります。

※Oracleでも同様の処理が可能です。

今日は以上まで

にほんブログ村

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

久しぶりに新宿のほうに仕事で行く用事があり、昼休みに新宿中央公園から都庁を撮った写真です。

話は変わりますが、先日の母の日に、子供たちが一人ひとり、妻にプレゼントをしていました。娘の一人はイヤリングのようなものをプレゼントしていたようです。まだ中学に入ったばかりの長男は、少し立派なチョコレートをあげてました。妻もまんざらではないようです。
私は妻と一緒にちょっとしたお買いものにお付き合い。妻の頑張りは大きいですよね。感謝しないと！
父の日は、何かあるかな～！

＜本日の題材＞
INTERSECT演算子

前回、集合演算の差を行う関数としての、MINUS、EXCEPT関数について見ましたが、今日はINTERSECTという、積集合（ただし、重複するデータについては1行にまとめる）を取得する演算子について見てみます。
積集合とは、与えられた集合の集まり全てに共通に含まれている要素を集めることにより得られる集合のこと。論理演算子ANDに相当し、共通集合もしくは「交わり」とも呼ばれます。

複数のSELECT文での問い合わせの結果に対して、共通の集合を重複しないかたちで抽出します。
なお、この演算子は、Oracle、SQL Serverともで使用できます。

例１）
前回の抽出で使用したtbl_A、tbl_Bを利用して、どちらのテーブルにも存在する全く同じ値のレコードを抽出してみます。
（SQL Serverで確認）

SELECT * FROM tbl_A
INTERSECT
SELECT * FROM tbl_B;

  

前回、tbl_A、tbl_B のデータを確認していますが、たしかに、tbl_A、tbl_B のどちらにも存在するレコードが抽出されました。

例２）
あるメーカーの3月に行ったキャンペーンに応募した顧客のデータを、「CP_応募」テーブルに登録したとします。
データは以下：
①
SELECT * FROM dbo.CP_応募
ORDER BY 顧客ID;

 次に、上記のキャンペーンに応募した顧客も含めて、4月の売上金額の多い顧客について、売上金額が1万円以上の顧客を金額の高い順に抽出した結果が以下とします。
②
SELECT
  ROW_NUMBER() OVER (ORDER BY SUM(売上金額) DESC) AS 売上順位
, 顧客ID
, SUM(売上金額) AS 売上合計
  FROM dbo.売上
 WHERE FORMAT(出荷日, 'yyyyMM') = '201504'
 GROUP BY 顧客ID
 HAVING SUM(売上金額) >= 10000
 ORDER BY SUM(売上金額) DESC;

 上記の①と②の結果で、どちらにも存在する顧客IDを抽出したいというときに、INTERSECT関数を使用してみます。

SELECT 顧客ID
   FROM dbo.CP_応募
 INTERSECT
 SELECT 顧客ID
   FROM dbo.売上
  WHERE FORMAT(出荷日, 'yyyyMM') = '201504'
  GROUP BY 顧客ID
  HAVING SUM(売上金額) >= 10000
  ORDER BY 顧客ID;

 3月のCPに応募した顧客が、4月の売上の上位（1万円以上）に5人含まれていることが確認できました。

ただ、これは「CP_応募」テーブルと「売上」テーブルを、下記SQLのように「顧客ID」でジョインして抽出するということでも同様の結果が出ます。というか、どちらかというと、ジョインを使うのが一般的なやり方かとは思われますが。

SELECT A.顧客ID
  FROM dbo.売上 A
  JOIN dbo.CP_応募 B ON A.顧客ID = B.顧客ID
 WHERE FORMAT(A.出荷日, 'yyyyMM') = '201504'
 GROUP BY A.顧客ID
 HAVING SUM(A.売上金額) >= 10000
 ORDER BY A.顧客ID;

 このINTERSECT演算子は、Oracleでも同様に使用することができます。
例１）について、Oracle環境で実施した結果は、以下になります。

ちなみに、MySQLではやはりINTERSECT演算子は対応していないようなので、EXISTS句を利用するなどして、全ての項目が一致するレコードを抽出することになります。

 今日は以上まで

にほんブログ村

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

ゴールデンウィーク(GW)も過ぎちゃいましたね。今年は、カレンダー通り休みがとれたのですが、今の時期、どことなく憂鬱に感じるのは私だけでしょうか？

今年は、GWの期間に、埼玉県の加須市騎西(きさい)にある、玉敷(たましき)神社の藤の花を家族で見に行って来ました。この藤の木は、県指定天然記念物であり、なんと樹齢400年、幹回り4.8メートル、枝張り約700平方メートルにも及ぶそうです。すごいですね。4月下旬くらいから5月上旬くらいまでが見ごろだとか。一応、一番見ごろな時に、見れたわけです。

ところで、出店の大判焼きもうまかったですよ。家族みんなで食べるからうまいのかな？
さて、頭を切り替え、仕事、仕事。頑張るぞ~！トホホホ・・・

＜本日の題材＞
MINUS、EXCEPT演算子

今回は、集合演算の差を行う演算子としての、MINUS、EXCEPTについて見てみます。同じ構造の2つのテーブルA,Bがあったとして、2つのテーブルのデータが完全に一致しているのかを確認したくなるときがありますね。そのときに、一致していないデータを抽出したいときに使用できます。元々Oracleを使用していた方はMINUS 演算子がすぐに連想されるかと思いますが、標準SQLではEXCEPT（SQL-92から）が採用されているんですね。ですので、SQLServerではEXCEPT 演算子を使用します（SQL Server2005からサポート）。

例）データベースがOracleの場合：

項目数、及びデータ型が一致する2つのテーブルtbl_A、tbl_Bの定義がともに以下のようであるとします。

テーブル定義：tbl_A
 　CD_A         INT
     Name_A   VARCHAR2(20)

 テーブル定義：tbl_B
　CD_B          INT
    Name_B   VARCHAR2(20)

データは、
SELECT * FROM tbl_A;

SELECT * FROM tbl_B;

テーブルtbl_A, tbl_B ともに7件のレコードがありますが、tbl_Aに存在して、tbl_Bに存在しないレコードを抽出したいときには、以下のようにします。

SELECT * FROM tbl_A
MINUS
SELECT * FROM tbl_B;

（どちらのテーブルにも存在し、全ての項目の値が一致しているレコードは、抽出結果に表示されません。１つの項目でも値が違っていれば、抽出されてきます。）

反対に、tbl_Bに存在して、tbl_Aに存在しないレコードを抽出したいときには、以下のようになります。

SELECT * FROM tbl_B
MINUS
SELECT * FROM tbl_A;

なお、どうしてもソートを行いたい場合には、最初のSELECTの項目に別名を設定して、それをORDER BY で使用することでできます。

SELECT CD_A AS CD, Name_A AS NAME
FROM tbl_A
MINUS
SELECT CD_B, Name_B
FROM tbl_B
ORDER BY CD;

※MINUS演算子の後のほうのSELECTの項目に別名をつけて ORDER BY で指定した場合はエラーになります。

 なお、MINUS 演算子前後のSELECTの抽出項目の数が違う場合や、項目の定義が異なる状態でも、抽出はエラーになります。

また、今回は tbl_A と tbl_B が全く同じ構造のテーブルでテストしましたが、そうではない場合でも、データを比較したい項目を MINUS演算子の前後のSELECT 文で指定し、その項目数と型が一致していれば、同様に抽出を行うことは可能です。

例２）SQL Serverの場合：

上記の例と同様のテーブルを作成しておきます。
・tbl_Aに存在して、tbl_Bに存在しないレコードを抽出したいとき

SELECT * FROM tbl_A
EXCEPT
SELECT * FROM tbl_B;

・反対にtbl_Bに存在して、tbl_Aに存在しないレコードを抽出したいとき

SELECT * FROM tbl_B
EXCEPT
SELECT * FROM tbl_A;

 ・tbl_Aに存在して、tbl_Bに存在しないレコードを抽出し、CDの内容でソートしたいとき

SELECT CD_A AS CD, Name_A AS NAME
FROM tbl_A
EXCEPT
SELECT CD_B, Name_B
FROM tbl_B
ORDER BY CD;

 ※EXCEPT演算子の後のほうのSELECT 項目に別名をつけてORDER BYで指定した場合はエラーになります。

 ちなみに、MySQLではEXCEPT関数はサポートしていないようです。
この場合、NOT EXISTSを使用して、各項目がすべて一致するもの以外を抽出するかたちになります。

今日は以上まで

にほんブログ村