18
15

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

【SQL編】データサイエンス100本ノック解説【1~20】

Last updated at Posted at 2020-10-02

始めに

こちらは筆者がデータサイエンス100ノックをSQLをやるにあたって、必用になった知識をまとめながら解説したものになります。一部、実際の解答と表記が異なる場合があります。(ほとんどは、SQL構文を大文字にしたもの)

データサイエンス100ノックの始め方を解説したものではありません。

筆者はSQLの学習をしたばかりなので、間違いなどがあるかもしれません。その場合はコメントにて教えていただけると幸いです。

行・列の操作[1~9]

1【全項⽬指定】

S-001: レシート明細テーブル(receipt)から全項目を10件抽出し、どのようなデータを保有しているか目視で確認せよ。

%%sql
SELECT * FROM receipt LIMIT 10;
  • SELECTでカラムを選択する。"*"をつけるとテーブルの全カラムを選択する。
  • FROMで選択したカラムがあるテーブルを指定する。
  • LIMITで表示するデータの行数を指定。

2【列指定】

S-002: レシート明細のテーブル(receipt)から売上日(sales_ymd)、顧客ID(customer_id)、商品コード(product_cd)、売上金額(amount)の順に列を指定し、10件表示させよ。

%%sql
SELECT sales_ymd, customer_id, product_cd, amount FROM receipt LIMIT 10
  • 列は指定した順番で表示される。

3【列名変更】

S-003: レシート明細のテーブル(receipt)から売上日(sales_ymd)、顧客ID(customer_id)、商品コード(product_cd)、売上金額(amount)の順に列を指定し、10件表示させよ。ただし、sales_ymdはsales_dateに項目名を変更しながら抽出すること。

%%sql
SELECT sales_ymd as sales_date, customer_id, product_cd, amount FROM receipt LIMIT 10
  • SELECTでカラムを選択するとき、カラム名の後に"AS"を加えることでカラム名を変更できる。

4【単一条件】

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

顧客ID(customer_id)が"CS018205000001"

%%sql
SELECT sales_ymd as sales_date, customer_id, product_cd, amount
FROM receipt
WHERE customer_id = 'CS018205000001'
  • WHERE句でカラムの条件を指定することができる。

5【複数条件】

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

顧客ID(customer_id)が"CS018205000001"
売上金額(amount)が1,000以上

%%sql

SELECT sales_ymd as sales_date, customer_id, product_cd, amount
FROM receipt
WHERE customer_id = 'CS018205000001' AND amount >= 1000
  • WHERE句で複数の条件を指定するときは条件同士を"AND"でつなげる

6【複数条件】

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

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

%%sql
SELECT sales_ymd as sales_date, customer_id, product_cd, quantity, amount
FROM receipt
WHERE customer_id = 'CS018205000001' AND (amount >= 1000 OR quantity >= 5)
  • WHERE句ではANDのほかに、ORや()が使用できる。

7【範囲指定】

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

顧客ID(customer_id)が"CS018205000001"
売上金額(amount)が1,000以上2,000以下

%%sql
SELECT sales_ymd as sales_date, customer_id, product_cd, amount
FROM receipt
WHERE customer_id = 'CS018205000001' AND amount BETWEEN 1000 AND 2000
  • WHERE句でA≦x≦Bを表現するとき、BTWEENで区間を指定できる。

8【不一致】

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

顧客ID(customer_id)が"CS018205000001"
商品コード(product_cd)が"P071401019"以外

%%sql
SELECT sales_ymd as sales_date, customer_id, product_cd, amount
FROM receipt
WHERE customer_id = 'CS018205000001' AND product_cd != 'P07140101
  • 否定は"!="で表現できる。

9【補集合】

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

select * from store where not (prefecture_cd = '13' or floor_area > 900)

%%sql
SELECT * FROM store WHERE prefecture_cd != '13' AND floor_area <= 900

あいまい検索[10~16]

10【前方一致】

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

%%sql
SELECT * FROM store WHERE store_cd LIKE 'S14%' LIMIT 10;
  • 部分一致する文字列を取得する場合は、WHERE カラム LIKE '単語'
  • 任意の0文字以上を許す場合は**%**
  • 任意の一文字を許す場合は**'_'**を追加

11【後方一致】

S-011: 顧客テーブル(customer)から顧客ID(customer_id)の末尾が1のものだけ全項目抽出し、10件だけ表示せよ。

%%sql
SELECT * FROM customer WHERE customer_id LIKE '%1' LIMIT 10;

12【部分一致】

S-012: 店舗テーブル(store)から横浜市の店舗だけ全項目表示せよ

%%sql
SELECT * FROM store WHERE address LIKE '%横浜市%';
  • 住所に'横浜市'を含むすべてのレコードを取得する。

13【部分一致】

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

%%sql
SELECT * FROM customer WHERE status_cd ~ '^[A:F]' LIMIT 10;

14【部分一致】

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

%%sql
SELECT * FROM customer WHERE status_cd ~ '[1-9]$' LIMIT 10;

15【部分一致】

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

%%sql
SELECT * FROM customer WHERE status_cd ~ '^[A:F].*[1-9]$';
  • '.':任意の一文字
  • '*':0回以上の繰り返し

16【フォーマット一致】

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

%%sql
SELECT * FROM store WHERE tel_no ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';
  • {数字}:指定された数字の回数の繰り返し

ソート

17【並び替え(昇順)】

S-017: 顧客テーブル(customer)を生年月日(birth_day)で高齢順にソートし、先頭10件を全項目表示せよ。

%%sql
SELECT * FROM customer ORDER BY birth_day ASC LIMIT 10;
  • ORDER BY カラム でカラムを並び替え
  • ASC:昇順

18【並び替え(降順)】

S-018: 顧客テーブル(customer)を生年月日(birth_day)で若い順にソートし、先頭10件を全項目表示せよ。

%%sql
SELECT * FROM customer ORDER BY birth_day DESC LIMIT 10;
  • DESC:降順

19【順位付け(同一順位あり)】

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

%%sql
SELECT customer_id, amount, RANK() OVER(ORDER BY amount DESC) AS ranking
FROM receipt
LIMIT 10;
  • まず、ORDER BY amount DESCで売上金額を降順に並べる
  • 次に、売上金額を基準にした場合の順位をRANK()で取得する
  • RANK:同率があった場合順位は同じになり、その次の順位を飛ばす
  • DENS_RANK : 同率があった場合順位は同じになり、その次の順位は飛ばさない

20【順位付け(同一順位なし)】

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

%%sql
SELECT customer_id, amount, ROW_NUMBER() OVER(ORDER BY amount DESC) AS ranking
FROM receipt LIMIT 10;
  • ROW_NUMBER : 同率があっても同じ順位にはならず、順位をその場合のみランダムでカウント

21~

(https://qiita.com/Strix9289/items/a7b813ade174ebb27409)

18
15
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
18
15

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?