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を使用して、各項目がすべて一致するもの以外を抽出するかたちになります。

今日は以上まで

にほんブログ村