SQL 100本ノック 知識の整理(34-43)

前回に引き続きSQL100本ノックを解いていく。この辺りはWITHの使い方がテーマになっている気がする。

Q34:

Q34 レシート明細データ（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
;

まず、customer_idごとの売り上げの合計が含まれるテーブルを作成する。次にそのテーブルから平均を求める。

Q35:

レシート明細データ（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
;

途中までQ34と同じ。平均を求めるときにもう一度WITHで一時テーブルを作る方法もあるにはあるが読みにくいので、サブクエリを用いている。

Q38:

顧客データ（customer）とレシート明細データ（receipt）から、顧客ごとの売上金額合計を求め、10件表示せよ。ただし、売上実績がない顧客については売上金額を0として表示させること。また、顧客は性別コード（gender_cd）が女性（1）であるものを対象とし、非会員（顧客IDが"Z"から始まるもの）は除外すること。

WITH customer_amount AS(
    SELECT
        customer_id,
        SUM(amount) AS sum_amount
    FROM
        receipt
    GROUP BY
        customer_id
),
customer_data AS(
    SELECT
        customer_id
    FROM
        customer
    WHERE
        gender_cd = '1'
        AND customer_id NOT LIKE 'Z%'
)
SELECT
    c.customer_id,
    COALESCE(a.sum_amount, 0)
FROM
    customer_data c
LEFT OUTER JOIN
    customer_amount a
ON
    c.customer_id = a.customer_id
LIMIT 10
;

まず、顧客IDと顧客ごとの売上金額の合計がわかるテーブルを作成する。次に顧客IDに女性かつ非会員の排除をいう条件が付いているので、その条件で絞った顧客IDのテーブルを作成する。最後に二つのテーブルを結合して求めていたテーブルを出力。ただし、売上実績のない顧客については売上金額を0として表示する必要がある。customer_amountがNULLとなっている所に0を代入するためにCOALESCE句を用いる。結合の際はIDを全て表示させる必要があるため、左外部結合を用いる。完成系のテーブルを想像して、そこから逆算すると一時テーブルとして作成するべき内容が見えてくる。

Q39:

レシート明細データ（receipt）から、売上日数の多い顧客の上位20件を抽出したデータと、売上金額合計の多い顧客の上位20件を抽出したデータをそれぞれ作成し、さらにその2つを完全外部結合せよ。ただし、非会員（顧客IDが"Z"から始まるもの）は除外すること。

WITH customer_data AS(
    SELECT
        customer_id,
        sales_ymd,
        amount
    FROM
        receipt
    WHERE
        customer_id NOT LIKE 'Z%'
),
customer_amount AS(
    SELECT
        customer_id,
        SUM(amount) sum_amount
    FROM
        customer_data
    GROUP BY
        customer_id
    ORDER BY
        sum_amount DESC
    LIMIT 20
),
customer_days AS(
    SELECT
        customer_id,
        COUNT(DISTINCT sales_ymd) come_days
    FROM
        customer_data
    GROUP BY
        customer_id
    ORDER BY
        come_days DESC
    LIMIT 20
)
SELECT
    COALESCE(d.customer_id, a.customer_id) customer_id,
    d.come_days,
    a.sum_amount
FROM
    customer_days d
FULL OUTER JOIN
    customer_amount a
ON
    d.customer_id = a.customer_id
;

完成形は、ID、トータルの売上日数、売上金額の合計が出力されるイメージ。最後はIDで完全外部結合かな。最初の2つのテーブルで必要なカラムの抽出とこれまでやったような簡単な処理を行う。3つ目のテーブルでIDごとの売上日数の集約を行う。COUNT(DISTINCT sales_ymd)は1日に複数回の買い物をしたというケースを排除している。２回買い物をしても売上日数は1日分としてカウント。
最後に完全外部結合。COALESCE()について、come_daysにIDのデータが入っていない場合は、customer_amountのIDの値が選択される。

Q41:

レシート明細データ（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、前回売上があった日からの売上金額増減を計算せよ。そして結果を10件表示せよ。

WITH sales_amount_date AS(
    SELECT
        sales_ymd,
        SUM(amount) AS amount
    FROM
        receipt
    GROUP BY
        sales_ymd
),
sales_amount_lag AS(
    SELECT
        sales_ymd,
        LAG(sales_ymd, 1) OVER(ORDER BY sales_ymd) lag_ymd,
        amount,
        LAG(amount, 1) OVER(ORDER BY sales_ymd) lag_amount
    FROM
        sales_amount_date
)
SELECT
    sales_ymd,
    amount,
    lag_ymd,
    lag_amount,
    amount - lag_amount AS diff_amount
FROM
    sales_amount_lag
ORDER BY
    sales_ymd
LIMIT 10
;

完成形は、日付と売上、前日との差がわかるようなテーブルをイメージ。まず日付ごとに売上の合計を集計。次に日々の売り上げの増減を考える。LAG関数は現在の行の前の行のデータを取得するために使用される関数である。この関数はORDER BY句で指定された列の順序に従って、前の行を決定する。今回の場合、ORDER BYでsales_ymdを指定することで日付と売上合計の前日のデータが手に入る。最後にsales_amount_lagテーブルを整理して求めたいテーブルを作成する。

Q43:

レシート明細データ（receipt）と顧客データ（customer）を結合し、性別コード（gender_cd）と年代（ageから計算）ごとに売上金額（amount）を合計した売上サマリデータを作成せよ。性別コードは0が男性、1が女性、9が不明を表すものとする。
ただし、項目構成は年代、女性の売上金額、男性の売上金額、性別不明の売上金額の4項目とすること（縦に年代、横に性別のクロス集計）。また、年代は10歳ごとの階級とすること。

CREATE TABLE sales_summary AS
    WITH gender_era_amount AS(
        SELECT
            TRUNC(age / 10) * 10 AS era,
            c.gender_cd,
            SUM(r.amount) AS amount
        FROM
            customer c
        JOIN
            receipt r
        ON
            c.customer_id = r.customer_id
        GROUP BY
            era,
            c.gender_cd
    )
    SELECT
        era,
        SUM(CASE WHEN gender_cd = '0' THEN amount END) AS male,
        SUM(CASE WHEN gender_cd = '1' THEN amount END) AS female,
        SUM(CASE WHEN gender_cd = '9' THEN amount END) AS unknown
    FROM
        gender_era_amount
    GROUP BY 
        era
    ORDER BY
        era
;
SELECT
    *
FROM
    sales_summary
;

完成形は年代が順番に並び、年代/性別ごとの売上がわかるイメージ。年代の導出がカギとなるので、TRUNC関数について整理してみる。

TRUNC関数は数値または日付を切り捨てるために使用される関数。数値の場合、第二引数に切り捨てる小数点以下を指定できる。指定がない場合は小数点以下を切り捨てた整数部分が返される。

日付の場合、例えばYEARを指定したらMONTH以下が切り捨てられる。ただしTRUNC関数は日付型の値を返すため、必要に応じて日付フォーマットを変換する必要がある。

最初の一時テーブルでは年代/性別ごとの売上を表示するが、クロス集計にはなっていない。次のSELECT句で、0を集計して男性、1を集計して女性、9を集約して不明のカラムを作成する。