Go to Qiita Advent Calendar 2024 Top
LoginSignup
9
4

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.

@Strix9289

【SQL編】データサイエンス100本ノック【21~40】

Last updated at Posted at 2020-10-02

#始めに
[1~20]の解説はこちら(https://qiita.com/Strix9289/items/9dfe4c911f6c206ff215)
[41~60]の解説はこちら(https://qiita.com/Strix9289/items/0d0023de3c28c3244529)

集計[21~33]

21【カウント】

S-021: レシート明細テーブル（receipt）に対し、件数をカウントせよ。

%%sql
SELECT COUNT(1) FROM receipt;
  • COUNT(カラム)：カラムの行数を返す（NULLを含まない）
  • COUNT(1) or COUNT(*)：NULLを含む、テーブルの全用件数を返す

22【ユニークなレコードをカウント】

S-022: レシート明細テーブル（receipt）の顧客ID（customer_id）に対し、ユニーク件数をカウントせよ。

%%sql
SELECT COUNT(DISTINCT customer_id) FROM receipt;
  • (DISTINCT カラム)：値の重複を除外して計算する

23【合計】

S-023: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）と売上数量（quantity）を合計せよ。

%%sql
SELECT store_cd, SUM(amount) AS amount, SUM(quantity) AS quantity
FROM receipt
GROUP BY store_cd;
  • SUM：指定された行の合計を返す
  • GROUP BY：指定された行でグループ化

24【最大値】

S-024: レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上日（sales_ymd）を求め、10件表示せよ。

%%sql
SELECT customer_id, MAX(sales_ymd)
FROM receipt
GROUP BY customer_id;
  • MAX：指定された行の最大値を返す

25【最小値】

S-025: レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに最も古い売上日（sales_ymd）を求め、10件表示せよ。

%%sql
SELECT customer_id, MIN(sales_ymd)
FROM receipt
GROUP BY customer_id;
  • MIN：指定された行の最小値を返す

26【集計結果を条件で絞る】

S-026: レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上日（sales_ymd）と古い売上日を求め、両者が異なるデータを10件表示せよ。

%%sql
SELECT customer_id, MAX(sales_ymd), MIN(sales_ymd)
FROM receipt
GROUP BY customer_id
HAVING MAX(sales_ymd) != MIN(sales_ymd)
LIMIT 10;
  • HAVING：グループ化されたものに対してWHEREのように絞り込みを行う

27【平均】

S-027: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の平均を計算し、降順でTOP5を表示せよ。

%%sql
SELECT store_cd, AVG(amount) AS avr_amount
FROM receipt
GROUP BY store_cd
ORDER BY avr_amount DESC
LIMIT 5;
  • AVG：指定された行の平均値を求める

28【中央値】

S-028: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の中央値を計算し、降順でTOP5を表示せよ。

%%sql
SELECT store_cd, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY amount) AS amount_50per
FROM receipt
GROUP BY store_Cd
ORDER BY amount_50per DESC
LIMIT 5;
  • PERCENTILE_CONT(値)：指定したパーセントタイルの値を引数にとり、該当のデータを返す。
  • OVER句ではなく、WITHIN GROPU(ORDER BY カラム)でソート順を指定する必要がある。

29【最頻値】

S-029: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに商品コードの最頻値を求めよ。

%%sql
SELECT store_cd, MODE() WITHIN GROUP(ORDER BY product_cd)
FROM receipt
GROUP BY store_cd
ORDER BY store_cd;
  • MODE：指定した行の最頻値を返す。
  • PERCENTITlE_CONTと同様にWITHIN GROUPでソートする。

30【分散】

S-030: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の標本分散を計算し、降順にTOP5を表示せよ。

%%sql
SELECT store_cd, VAR_SAMP(amount) AS vars_amount
FROM receipt
GROUP BY store_cd
ORDER BY vars_amount DESC
LIMIT 5;

31【標準偏差】

S-031: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の標本標準偏差を計算し、降順にTOP5を表示せよ。

%%sql
SELECT store_cd, STDDEV_SAMP(amount) AS stds_amount
FROM receipt
GROUP BY store_cd
ORDER BY stds_amount DESC
LIMIT 5;
  • STDDEV, STDDEV_POP：標本標準偏差を返す
  • STDDEV_SMAP：不偏標準偏差を返す

32【パーセンタイル】

S-032: レシート明細テーブル（receipt）に対し、売上金額（amount）について25％刻みでパーセンタイル値を求めよ。

%%sql
SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY amount) AS amount_25per,
    PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY amount) AS amount_50per,
    PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY amount) AS amount_75per,
    PERCENTILE_CONT(1.0) WITHIN GROUP(ORDER BY amount) AS amount_100per
FROM receipt
  • PERCENTILE_CONT(値)：指定したパーセントタイルの値を引数にとり、該当のデータを返す
  • OVER句ではなく、WITHIN GROPU(ORDER BY カラム)でソート順を指定する必要がある。

33【集計結果を条件で絞る】

S-033: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の平均を計算し、330以上のものを抽出せよ。

%%sql
SELECT store_cd, AVG(amount) AS avr_mount
FROM receipt
GROUP BY store_cd
HAVING AVG(amount) >= 300;
  • HAVING：グループ化されたものに対してWHEREのように絞り込みを行う

34【検索結果からのサブクエリ】

S-034: レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに売上金額（amount）を合計して全顧客の平均を求めよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。

%%sql
WITH customer_amount AS(
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM receipt
    WHERE customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
)

SELECT customer_id, sum_amount
FROM customer_amount
WHERE sum_amount >= (SELECT AVG(sum_amount) FROM customer_amount)
LIMIT 5;
  • WITH サブクエリ名 AS(SELECTなど)で新たにサブクエリをまとめられる
  • SUMやそのほかの関数を使用して作られた列を操作したい時などに有効

35【条件指定でのサブクエリ】

S-035: レシート明細テーブル（receipt）に対し、顧客ID（customer_id）ごとに販売金額（amount）を合計して全顧客の平均を求め、平均以上に買い物をしている顧客を抽出せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。なお、データは10件だけ表示させれば良い。

%%sql
WITH customer_amount AS(
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM receipt
    WHERE customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
)

SELECT customer_id, sum_amount
FROM customer_amount
WHERE sum_amount >= (SELECT AVG(sum_amount) FROM customer_amount)
LIMIT 10;
  • 顧客IDごとに集計＆合計→平均＆条件分岐がやりたいため、前半をWITH句を使ってまとめる。

#結合[36~42]

36【内部結合】

S-036: レシート明細テーブル（receipt）と店舗テーブル（store）を内部結合し、レシート明細テーブルの全項目と店舗テーブルの店舗名（store_name）を10件表示させよ。

%%sql
SELECT r.*, s.store_name
FROM receipt r
JOIN store s
ON r.store_cd = s.store_cd
LIMIT 10;
  • 複数のテーブルからカラムを参照する場合は、テーブル名の後ろに別名をつけ、カラムを利用する際は、[テーブル名].[カラム名]とする。
  • (INNER) JOIN：FROMで指定されたテーブルと、JOINの後ろのテーブルをONで指定された結合条件をキーとして結合させる。この時、それぞれが持つキーが一致しているカラム同士のみで結合が行われる。

37【内部結合】

S-037: 商品テーブル（product）とカテゴリテーブル（category）を内部結合し、商品テーブルの全項目とカテゴリテーブルの小区分名（category_small_name）を10件表示させよ。

%%sql
SELECT p.*, c.category_small_name
FROM product p
JOIN category c
ON p.category_small_cd = c.category_small_cd
LIMIT 10;

38【左外部結合】

S-038: 顧客テーブル（customer）とレシート明細テーブル（receipt）から、各顧客ごとの売上金額合計を求めよ。ただし、買い物の実績がない顧客については売上金額を0として表示させること。また、顧客は性別コード（gender_cd）が女性（1）であるものを対象とし、非会員（顧客IDが'Z'から始まるもの）は除外すること。なお、結果は10件だけ表示させれば良い。

%%sql
WITH customer_amount AS (
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM receipt
    GROUP BY customer_id
)
SELECT c.customer_id, COALESCE(a.sum_amount,0)
FROM customer c
LEFT JOIN customer_amount a
ON c.customer_id = a.customer_id
WHERE c.gender_cd = '1'
      and c.customer_id not like 'Z%'
LIMIT 10;
  • LEFT JOINでは、**先に指定したテーブルだけがもつキーの値についても結合を行う。**この時、右側に指定されたテーブル側の情報はすべてNULLとなる。

39【外部結合】

S-039: レシート明細テーブル（receipt）から売上日数の多い顧客の上位20件と、売上金額合計の多い顧客の上位20件を抽出し、完全外部結合せよ。ただし、非会員（顧客IDが'Z'から始まるもの）は除外すること。

%%sql
WITH customer_days AS (
    select customer_id, COUNT(DISTINCT sales_ymd) come_days
    FROM receipt
    WHERE customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
    ORDER BY come_days DESC LIMIT 20
),
customer_amount AS (
    SELECT customer_id, SUM(amount) buy_amount
    FROM receipt 
    WHERE customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
    ORDER BY buy_amount DESC LIMIT 20
)
SELECT COALESCE(d.customer_id, a.customer_id), d.come_days, a.buy_amount
FROM customer_days d
FULL JOIN customer_amount a
ON d.customer_id = a.customer_id;
  1. 売上日数の多い顧客上位20件と売上金額合計の多い顧客上位20件をそれぞれWITH句を使って新たなテーブルにする。
  2. FULL JOINを使って外部結合させる。

40【クロス結合】

S-040: 全ての店舗と全ての商品を組み合わせると何件のデータとなるか調査したい。店舗（store）と商品（product）を直積した件数を計算せよ。

%%sql
SELECT COUNT(1) FROM store CROSS JOIN product;
  • CROSS JOIN：テーブル同士の交差結合を行う
  • 全てのテーブルの列と全てのテーブルの列を持ち、全ての組み合わせを持つテーブルが生成される

[41~60]の解説(https://qiita.com/Strix9289/items/0d0023de3c28c3244529)

9
4
1

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
Sign upLogin
9
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?