0

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

Last updated at 2025-03-26Posted at 2025-03-11

はじめに

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

凡例

[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）

ライセンスの詳細

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

その他参考文献

0

Register as a new user and use Qiita more conveniently

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

0