【SQL】データサイエンス100本ノック（構造化データ加工編）に挑戦！！31〜35

こんにちは、ドイです！前回に引き続き、データサイエンス100本ノックに挑戦していきます。

前回の記事

tech.arms-soft.co.jp

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

SELECT store_cd, STDDEV_SAMP(amount) AS amount FROM receipt GROUP BY store_cd ORDER BY amount DESC LIMIT 5;

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

SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY amount) AS amount_25,
    PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY amount) AS amount_50,
    PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY amount) AS amount_75,
    PERCENTILE_CONT(1.0) WITHIN GROUP(ORDER BY amount) AS amount_100
FROM receipt;

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

SELECT store_cd, AVG(amount) AS avg_amount FROM receipt GROUP BY store_cd HAVING AVG(amount)  >= 330;

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

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 AVG(sum_amount)
FROM customer_amount;

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

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;

WITH句（副問い合わせ）

S-034、S-035で使用しているWITH句について解説していきます。
WITH句の基本的な使い方です。

WITH 名前A AS (
    取得のSQL
)
SELECT *
FROM 名前A;

これを踏まえて、S-034のSQLを見てみます。

-- AS以降で取得するサブクエリに「customer_amount」という名前をつけます。
WITH customer_amount AS (

-- 取得のSQL
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM receipt
    WHERE customer_id NOT LIKE 'Z%'
    GROUP BY customer_id
)

-- サブクエリ「customer_amount」からsum_amountの平均値を取得します。
SELECT AVG(sum_amount)
FROM customer_amount;

S-034の問題は「レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに売上金額（amount）を合計して全顧客の平均を求めよ。ただし、顧客IDが\"Z\"から始まるものは非会員を表すため、除外して計算すること」ですが、これらを一度に取得するのではなく

①レシート明細データ（receipt）から、顧客IDが\"Z\"から始まる非会員以外の売上金額（amount）を取得する
② '①'で取得した売上金額（amount）の平均を取得する。

複雑に見える問題でも、サブクエリを使うことで簡単に取得することができます。

まとめ

S-031、S-032の問題は普段使わない関数なので、取得というより関数に慣れない部分がありました。
S-034、S-035の問題は、一見難しそうですがWITH句を使用することでシンプルにデータを取得できました。
引き続き勉強を続けていきます。