データサイエンス100本ノック（構造化データ加工編）解説_S006-S010

はじめに

第二回です。前回はこちら。

凡例

• [XXX]：　XXXは省略可能
• (XXX)：　XXXの論理的な名前
• 本来は、%%sqlを答えの1行目に記載する必要があります。

問題

6問目：「または」の条件

S-006: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上数量（quantity）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

• 顧客ID（customer_id）が"CS018205000001"
• 売上金額（amount）が1,000以上または売上数量（quantity）が5以上

S-006答え

SELECT
  r.receipt
  ,r.sales_ymd
  ,r.customer_id
  ,r.product_cd
  ,r.amount
FROM
  receipt r
WHERE
  customer_id = "CS018205000001"
  AND (
    amount >= 1000
    OR quantity >= 5
  )
  

複雑な条件指定
この問題では、3つの条件があります。

条件	内容
A	顧客ID（customer_id）が"CS018205000001"
B	売上金額（amount）が1,000以上
C	売上数量（quantity）が5以上

この問題では、
「A　を満たす」と 「B または C　を満たす」の2つの条件を満たす必要がありますから、
A　かつ　（B または C）という式を書けばよいです。

7問目 範囲

S-007: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

• 顧客ID（customer_id）が"CS018205000001"
• 売上金額（amount）が1,000以上2,000以下

S-007答え

SELECT
  r.receipt
  ,r.sales_ymd
  ,r.customer_id
  ,r.product_cd
  ,r.amount
FROM
  receipt r
WHERE 
  customer_id = "CS018205000001"
  AND amount BETWEEN 1000 AND 2000
;

範囲指定
A<=X<=Bの式はBETWEENを使って表すことができます。
構文

X BETWEEN A AND B

これ次と同じ意味です。

X >= A
AND X <= B

8問目：否定

S-008: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

• 顧客ID（customer_id）が"CS018205000001"
• 商品コード（product_cd）が"P071401019"以外

S-008答え

SELECT
  r.receipt
  ,r.sales_ymd
  ,r.customer_id
  ,r.product_cd
  ,r.amount
FROM
  receipt r
WHERE 
  customer_id = "CS018205000001"
  AND product_cd <> "P071401019"
;

否定
XがAでない　という条件は <>　を使って表します
構文

X <> A

9問目：ド・モルガン

S-009: 以下の処理において、出力結果を変えずにORをANDに書き換えよ。

SELECT * FROM store WHERE NOT (prefecture_cd = '13' OR floor_area > 900)`

S-009答え

SELECT
  *
FROM
  store s
WHERE 
   prefecture_cd <> '13'
   AND floor_area <= 900
;

ド・モルガンの法則
「かつ」、「または」、と否定を組み合わせた条件に関して次の2つが成り立つというものです。

1. 『「AかつB」　の否定』 と　『「'A'の否定」または「'B'の否定」』
   例 $ 「犬派　かつ　猫派」　の否定 =「犬派でない　もしくは　猫派でない」$
2. 『「AまたはB」　の否定』 と　『「'A'の否定」かつ　「'B'の否定」』
   例：$「犬派　もしくは　猫派」　の否定 = 「犬派でない　かつ　猫派でもない」$

真理値表

パターン	1	2	3	4
A	T	T	F	F
B	T	F	T	F
1.の真理値表
「A かつ B」	T	F	F	F
「A かつ B」の否定	F	T	T	T
「Aの否定」	F	F	T	T
「Bの否定」	F	T	F	T
「Aの否定」 または 「Bの否定」	F	T	T	T
2.の真理値表
:-:	:-:	:-:	:-:	:-:
「A または B」	T	T	T	F
「A かつ B」の否定	F	F	F	T
「Aの否定」 かつ 「Bの否定」	F	F	F	T

この問題では、　A OR B の否定 の書き換えなので、
'A'の否定 AND 'B'の否定　とすればよい　ということがわかります。

10問目：曖昧検索

S-010: 店舗データ（store）から、店舗コード（store_cd）が"S14"で始まるものだけ全項目抽出し、10件表示せよ。

S-010答え

SELECT
  *
FROM
  store s
WHERE 
   store_cd LIKE 'S14%'
;

LIKE
部分一致のデータを抽出したいときには、LIKE　を用います。
構文

SELECT * FROM WHERE 検索列 LIKE (検索文字列)

また、検索文字列にはワイルドカードを利用できます。

ワイルドカード	概要	検索文字列例	ヒットする値の例	備考
%	0文字以上の任意の文字列	S14%	「S141000」、「S14」
_	任意の1文字	S14_	「S141」、「S142」	「_」は2文字ではヒットしない
[]	括弧内の任意の1文字	S[123]	「S1」、「S2」	[a-c]とすると、a,b,cがヒットする

※否定のワイルドカードはDBMSによって仕様が異なります。[!a-z]でa〜z以外の文字列を指すものと、[^a-z]を使うものをがあるようです。

参考文献・クレジット

本記事では、以下のライセンスのもと提供されている資料を参考にしています。

MIT License

© 2020 The Japan DataScientist Society
MIT Licenseの詳細

Creative Commons Attribution-NoDerivatives 4.0 International（CC BY-ND 4.0）

ライセンスの詳細

※ 本記事では、元の内容を改変せずに引用・解説を行っています。

その他参考文献