はじめに

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

凡例

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

問題

11問目：後方一致

S-011: 顧客データ（customer）から顧客ID（customer_id）の末尾が1のものだけ全項目抽出し、10件表示せよ。

S-011答え

SELECT
  *
FROM
  store s
WHERE 
   customer_id LIKE '%1'
LIMIT 10
;

後方一致ですから、「%+ヒットさせたい文字列」とすればよいです。
2文字以上の文字列としたい場合は、%_1 の形で_と組み合わせます。

12問目部分一致

S-012: 店舗データ（store）から、住所 (address) に"横浜市"が含まれるものだけ全項目表示せよ。

S-012答え

SELECT
  *
FROM
  store s
WHERE 
   address LIKE '%横浜市%'
;

部分一致なので、% + 検索文字列 + % とすればよいでしょう。

13問目正規表現を用いた検索

S-013: 顧客データ（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まるデータを全項目抽出し、10件表示せよ。

S-013答え

SELECT
  *
FROM
  customer c
WHERE 
   status_cd REGEXP '^[A-F].*'
LIMIT 10;
;

構文

 WHERE '文字列' REGEXP '正規表現パターン'

正規表現を用いて、複数の特定の文字列と一致させたい場合は、[一致対象文字たち]　とするのでした。
また、正規表現で開始を表すのは、^です。
加えて、 REGEXP は一部でも一致すれば1を返すため、下記でもOKです。

開始部分だけの正規表現

status_cd REGEXP '^[A-F]'

今回の問題では、[A-F] を [ABCDEF]と記載しても正解できます。
また、↓のように、[]を使わずとも表現することはできます。

[]を使わない

SELECT
  *
FROM
  customer c
WHERE 
   status_cd LIKE '%A%'
   OR status_cd LIKE 'B%'
   OR status_cd LIKE 'C%'
   OR status_cd LIKE 'D%'
   OR status_cd LIKE 'E%'
   OR status_cd LIKE 'F%'
LIMIT 10;
;

14問目：特定の複数文字の検索

S-014: 顧客データ（customer）から、ステータスコード（status_cd）の末尾が数字の1〜9で終わるデータを全項目抽出し、10件表示せよ。

S-014答え

SELECT
  *
FROM
  customer c
WHERE 
   status_cd REGEXP '.*[1-9]$'
LIMIT 10;
;

13問目の末尾版です。
末尾の正規表現は $ です。

15問目：複雑な検索①

S-015: 顧客データ（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、10件表示せよ。

S-015答え

SELECT
  *
FROM
  customer s
WHERE 
   status_cd REGEXP '^[A-F].*[1-9]$'
LIMIT 10;
;

13,14問目の複合です！

16問目：複雑な検索

S-016: 店舗データ（store）から、電話番号（tel_no）が3桁-3桁-4桁のデータを全項目表示せよ。

任意の1文字は_ ですから、3文字は___とすればよいですね。

S-016答え(数値であるとは限らない)

SELECT
  *
FROM
  store s
WHERE 
   tel_no LIKE '___-___-____'
;

正規表現

SELECT
  *
FROM
  store s
WHERE 
   tel_no REGEXP '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
;

[一致させたい文字たち] でその文字1字にマッチするのでした。3桁数値-3桁数値-4桁数値のパターン文字列はサクッと作れるといいですね。
DBMSによっては [0-9]{3}のように{回数}で前のパターンが何回続くかを指定できるものもあるようです。（正規表現のように使えます。）

17問目：ソート

S-017: 顧客データ（customer）を生年月日（birth_day）で高齢順にソートし、先頭から全項目を10件表示せよ。

S-017答え

SELECT
  *
FROM
  customer s
ORDER BY birth_day ASC
LIMIT 10
;

ORDER BY
出力結果の順番を指定したい場合は ORDER BY を使います。
構文

 SELECT (カラム名) FROM (テーブル名) ORDER BY (ソートする基準のカラム) [ASC/DESC]

ASC：昇順（小⇨大) 、DESC：降順(大⇨小)　です。
また、ソートするカラム名をSELECT句のカラムの位置で指定できます。
例：

SELECT
  name
  , birth_day
  , gender
FROM
  customer s
ORDER BY 1 ASC
LIMIT 10
;

は、1番目のカラム(name)順にソートされます。
加えて、ソートするカラムは複数指定可能です。

SELECT
  name
  , birth_day
  , id
  , gender
FROM
  customer s
ORDER BY 1 ASC, 2 DESC
LIMIT 10
;

とすると、名前の昇順、誕生日の降順にソートされます。

ちなみに、高齢順というのは、誕生日の小さい順なので注意しましょう。

18問目：降順

S-018: 顧客データ（customer）を生年月日（birth_day）で若い順にソートし、先頭から全項目を10件表示せよ。

S-018答え

SELECT
  *
FROM
  customer s
ORDER BY
  birth_day DESC
LIMIT 10
;

S-017問目の逆順にソートします。
ORDER BY は SELECT句の後に実行されます。
SELECT句の結果が抽出されてから、ソートを実行するため、実行時間を要するケースが多いです。
また、ASC/DESC は省略すると、ASCとして実行されます。

19問目：ランク

S-019: レシート明細データ（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合は同一順位を付与するものとする。

S-019答え

SELECT
  customer_id
  , amount
  ,rank() OVER (ORDER BY amount DESC) AS rank
FROM
  receipt r
LIMIT 10
;

RANK
ランクを付与する際には、RANK関数を使います。

構文

SELECT RANK() OVER (ORDER BY (ランク付与の基準となる列) [ASC/DESC])

RANK()は同じ順位には同じ番号を振ります。
似たような関数にDENSE_RANK() がありますが、こちらは欠番がないように降ります。
例

amount	10	20	20	40	50
関数名	数値	数値	数値	数値	数値
RANK	1	2	2	4	5
DENSE_RANK	1	2	2	3	4

20問目：ランク②

S-020: レシート明細データ（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合でも別順位を付与すること。

S-019答え

SELECT
  customer_id
  , amount
  ,ROW_NUMBER() OVER (ORDER BY amount DESC) AS rank
FROM
  receipt s
LIMIT 10
;

ROW_NUMBER
ランクを付与し、同一順位にも別の値を採番したい場合には、ROW_NUMBER関数を使います。
構文

SELECT ROW_NUMBER() OVER (ORDER BY (ランク付与の基準となる列) [ASC/DESC])

ROW_NUMBERは同一の順位の場合には、ランダムに別の番号を振ります。
例

amount	10	20	20	40	50
関数名	数値	数値	数値	数値	数値
ROW_NUMBER	1	2	3	4	5

参考文献・クレジット

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

MIT License

© 2020 The Japan DataScientist Society
MIT Licenseの詳細

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

ライセンスの詳細

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

その他参考文献