数値変換[59~62]

61【対数化】

S-061: レシート明細テーブル（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、合計した売上金額を常用対数化（底=10）して顧客ID、売上金額合計とともに表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。結果は10件表示させれば良い。

%%sql
SELECT
    customer_id,
    SUM(amount),
    LOG(SUM(amount) + 1) AS log_amount
FROM
    receipt
WHERE
    customer_id NOT LIKE 'Z%'
GROUP BY
    customer_id
LIMIT 10;

LOG(値)：底を10とした対数を返す

62【対数化】

S-062: レシート明細テーブル（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、合計した売上金額を自然対数化(底=e）して顧客ID、売上金額合計とともに表示せよ（ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること）。結果は10件表示させれば良い。

%%sql
SELECT
    customer_id,
    SUM(amount),
    LN(SUM(amount) + 1) AS log_amount
FROM
    receipt
WHERE
    customer_id NOT LIKE 'Z%'
GROUP BY
    customer_id
LIMIT 10;

LN(値)：底を自然対数に取った対数を返す

四則演算

63【引き算】

S-063: 商品テーブル（product）の単価（unit_price）と原価（unit_cost）から、各商品の利益額を算出せよ。結果は10件表示させれば良い。

%%sql
SELECT
    product_cd, 
    unit_price, 
    unit_cost,
    unit_price - unit_cost AS unit_profit
FROM
    product
LIMIT 10;

64【割り算】

S-064: 商品テーブル（product）の単価（unit_price）と原価（unit_cost）から、各商品の利益率の全体平均を算出せよ。ただし、単価と原価にはNULLが存在することに注意せよ。

%%sql

SELECT AVG((unit_price * 1.0 - unit_cost) / unit_price) AS unit_profit_rate
FROM product
LIMIT 10;

利益率：利益を売上高で割ったもの(%)
$\frac{利益}{売上高}\times100 = (1-\frac{原価}{売上高})\times100$

65【有効桁数】

S-065: 商品テーブル（product）の各商品について、利益率が30%となる新たな単価を求めよ。ただし、1円未満は切り捨てること。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。

%%sql
SELECT
    product_cd, 
    unit_price, 
    unit_cost,
    -- 利益率が30％の単価
    TRUNC(unit_cost / 0.7) as new_price,
    -- 新しい単価での利益率の計算
    ((TRUNC(unit_cost / 0.7) - unit_cost) / TRUNC(unit_cost / 0.7)) AS new_profit
FROM
    product
LIMIT 10;

66【四捨五入】

S-066: 商品テーブル（product）の各商品について、利益率が30%となる新たな単価を求めよ。今回は、1円未満を四捨五入すること。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。

%%sql
SELECT
    product_cd, 
    unit_price, 
    unit_cost,
    ROUND(unit_cost / 0.7) AS new_price,
    ((ROUND(unit_cost / 0.7) - unit_cost) / ROUND(unit_cost / 0.7)) AS new_profit
FROM
    product
LIMIT 10

ROUND(値 [,桁数])：値を四捨五入する。桁数に正の数のnを指定すると、小数点n今で四捨五入する。桁数に負の数を-mを指定すると、整数部分m+1桁目までを四捨五入する。

67【切り上げ】

S-067: 商品テーブル（product）の各商品について、利益率が30%となる新たな単価を求めよ。今回は、1円未満を切り上げること。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。

%%sql
SELECT
    product_cd, 
    unit_price, 
    unit_cost,
    CEIL(unit_cost / 0.7) as new_price,
    ((CEIL(unit_cost / 0.7) - unit_cost) / CEIL(unit_cost / 0.7)) as new_profit
FROM product
LIMIT 10;

CEIL(値)：小数以下を切り上げる

68【切り捨て】

S-068: 商品テーブル（product）の各商品について、消費税率10%の税込み金額を求めよ。 1円未満の端数は切り捨てとし、結果は10件表示すれば良い。ただし、単価（unit_price）にはNULLが存在することに注意せよ。

%%sql
SELECT
    product_cd,
    unit_price,
    TRUNC(unit_price * 1.1) AS tax_price
FROM product
LIMIT 10;

TRUNK(値)：小数以下を切り捨て

69【割合】

S-069: レシート明細テーブル（receipt）と商品テーブル（product）を結合し、(1)顧客毎に全商品の売上金額合計と、(2)カテゴリ大区分（category_major_cd）が"07"（瓶詰缶詰）の売上金額合計を計算の上、両者の比率を求めよ。抽出対象はカテゴリ大区分"07"（瓶詰缶詰）の購入実績がある顧客のみとし、結果は10件表示させればよい。

%%sql
-- (1)：売上金額を顧客ごとに集計
WITH amount_all AS(
    SELECT
        customer_id,
        sum(amount) AS sum_all
    FROM
        receipt
    GROUP BY
        customer_id
),
-- (2)：カテゴリが07の売上金額を計算する
amount_07 AS (
    SELECT
        r.customer_id,
        SUM(r.amount) AS sum_07
    FROM
        receipt r
    JOIN
        product p
    ON
        r.product_cd = p.product_cd 
        AND  p.category_major_cd = '07'
    GROUP BY
        customer_id
)
SELECT
    amount_all.customer_id,
    sum_all,
    sum_07,
　　-- 割合の計算
    sum_07 * 1.0 / sum_all as sales_rate
FROM
    amount_all
JOIN
    amount_07
ON
    amount_all.customer_id = amount_07.customer_id
LIMIT 10;

70【経過日数の計算】

S-070: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、顧客テーブル（customer）の会員申込日（application_date）からの経過日数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）

%%sql
WITH receit_distinct AS (
    SELECT DISTINCT
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
　　-- 売上日から申込日を引いた値から日数の要素を取得する
    EXTRACT(DAY FROM (TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD') 
                      - TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_days
FROM
    receit_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10;

EXTRACT(日付要素 FROM 元の日付式)：日付式から任意の日付要素(YAER, MONTH, DAY, HOUR, MINUTE, SECONDなど)を取得

71【経過月数の計算】

S-071: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、顧客テーブル（customer）の会員申込日（application_date）からの経過月数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。1ヶ月未満は切り捨てること。

%%sql

WITH receit_distinct AS (
    SELECT distinct
        customer_id,
        sales_ymd
    FROM
        receipt
),
time_age_tbl AS(
    SELECT
        c.customer_id,
        r.sales_ymd,
        c.application_date,
        AGE(TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'),
                          TO_TIMESTAMP(c.application_date, 'YYYYMMDD')) AS time_age
    FROM
        receit_distinct r
    JOIN
        customer c
    ON
        r.customer_id = c.customer_id
)
SELECT customer_id, sales_ymd, application_date,
    extract(year from time_age) * 12 + extract(month from time_age) AS elapsed_months
FROM
    time_age_tbl
LIMIT 10

EXTRACT(日付要素 FROM 元の日付式)：日付式から任意の日付要素(YAER, MONTH, DAY, HOUR, MINUTE, SECONDなど)を取得

72【経過年数】

S-072: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、顧客テーブル（customer）の会員申込日（application_date）からの経過年数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。1年未満は切り捨てること。

%%sql
WITH receit_distinct AS (
    SELECT DISTINCT
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(YEAR FROM AGE(TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD'), 
                      TO_TIMESTAMP(c.application_date, 'YYYYMMDD'))) AS elapsed_years
FROM
    receit_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10

EXTRACT(日付要素 FROM 元の日付式)：日付式から任意の日付要素(YAER, MONTH, DAY, HOUR, MINUTE, SECONDなど)を取得

73【経過時間の計算】

S-073: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、顧客テーブル（customer）の会員申込日（application_date）からのエポック秒による経過時間を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。なお、時間情報は保有していないため各日付は0時0分0秒を表すものとする

%%sql
WITH receit_distinct AS (
    SELECT distinct
        customer_id,
        sales_ymd
    FROM
        receipt
)
SELECT
    c.customer_id,
    r.sales_ymd,
    c.application_date,
    EXTRACT(EPOCH FROM TO_TIMESTAMP(CAST(r.sales_ymd AS VARCHAR), 'YYYYMMDD')) - 
                      EXTRACT(EPOCH FROM TO_TIMESTAMP(c.application_date, 'YYYYMMDD')) AS elapsed_epoch
FROM
    receit_distinct r
JOIN
    customer c
ON
    r.customer_id = c.customer_id
LIMIT 10

74【月曜からの経過日数】

S-074: レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、当該週の月曜日からの経過日数を計算し、売上日、当該週の月曜日付とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値でデータを保持している点に注意）

%%sql
SELECT
    customer_id,
    TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD'),
    EXTRACT(DOW FROM (TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') - 1)) AS elapsed_years,
    TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD')
        - CAST(EXTRACT(DOW FROM (TO_DATE(CAST(sales_ymd AS VARCHAR), 'YYYYMMDD') - 1)) AS INTEGER) AS monday
FROM
    receipt
LIMIT 10;

EXTRACTでDOWを指定すると、曜日を取得できる

サンプリング[75~76]

75【ランダムサンプリング】

S-075: 顧客テーブル（customer）からランダムに1%のデータを抽出し、先頭から10件データを抽出せよ。

%%sql
SELECT * FROM customer WHERE RANDOM() <= 0.01
LIMIT 10;

RANDOM()：0~1の間で乱数を作る

76【層化】

S-076: 顧客テーブル（customer）から性別（gender_cd）の割合に基づきランダムに10%のデータを層化抽出データし、性別ごとに件数を集計せよ。

%%sql
-- カテゴリ数が少ない場合はそれぞれサンプリングしUNIONするほうが簡単だが、カテゴリ数が多いケースを考慮して以下のSQLとした
-- RANDOMでORDER BYしているため、大量データを扱う場合は注意が必要
WITH cusotmer_random AS (
    SELECT customer_id, g_cd, cnt
    FROM (
        SELECT
            ARRAY_AGG(customer ORDER BY RANDOM()) AS customer_r, 
            gender_cd AS g_cd, 
            COUNT(1) AS cnt
        FROM
            customer
        GROUP BY
            gender_cd
    )sample, UNNEST(customer_r)
),
cusotmer_rownum AS(
    SELECT * , ROW_NUMBER() OVER(PARTITION BY g_cd) AS rn
    FROM cusotmer_random
)
SELECT
    g_cd, 
    COUNT(1)
FROM
    cusotmer_rownum
WHERE rn <= cnt * 0.1
GROUP BY g_cd;

ARRAY_AGG：指定した行を集約して配列にする
UNNEST(配列)：配列を一列の配列に変える

外れ値・異常値[77~78]

77【外れ値除外】

S-077: レシート明細テーブル（receipt）の売上金額（amount）を顧客単位に合計し、合計した売上金額の外れ値を抽出せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。なお、ここでは外れ値を平均から3σ以上離れたものとする。結果は10件表示させれば良い。

%%sql
-- 売上合計の計算＆’Z'で始まる顧客を除外
WITH sales_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 sales_amount
CROSS JOIN (
    SELECT AVG(sum_amount) AS avg_amount, STDDEV_SAMP(sum_amount) AS std_amount
    FROM sales_amount   
) stats_amount
WHERE ABS(sum_amount - avg_amount) / std_amount > 3
LIMIT 10;

区間内に測定データがある確率
±σ（σ区間）：68.3％
±2σ（2σ区間）：95.4％
±3σ（3σ区間）：99.7％
±6σ（6σ区間）：99.9997％

78【外れ値除外】

S-078: レシート明細テーブル（receipt）の売上金額（amount）を顧客単位に合計し、合計した売上金額の外れ値を抽出せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。なお、ここでは外れ値を第一四分位と第三四分位の差であるIQRを用いて、「第一四分位数-1.5×IQR」よりも下回るもの、または「第三四分位数+1.5×IQR」を超えるものとする。結果は10件表示させれば良い。

%%sql
WITH sales_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 sales_amount
CROSS JOIN (
    SELECT PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY sum_amount) as amount_25per,
           PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY sum_amount) as amount_75per
    FROM sales_amount   
) stats_amount
WHERE sum_amount < amount_25per - (amount_75per - amount_25per) * 1.5 
    OR amount_75per + (amount_75per - amount_25per) * 1.5 < sum_amount
LIMIT 10;

欠損値[79~83]

79【欠損値確認】

S-079: 商品テーブル（product）の各項目に対し、欠損数を確認せよ。

SELECT 
    SUM(CASE WHEN product_cd IS NULL THEN 1 ELSE 0 END) AS product_cd,
    SUM(CASE WHEN category_major_cd IS NULL THEN 1 ELSE 0 END) AS category_major_cd,
    SUM(CASE WHEN category_medium_cd IS NULL THEN 1 ELSE 0 END) AS category_medium_cd,
    SUM(CASE WHEN category_small_cd IS NULL THEN 1 ELSE 0 END) AS category_small_cd,
    SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS unit_price,
    SUM(CASE WHEN unit_cost IS NULL THEN 1 ELSE 0 END) AS unit_cost
FROM product LIMIT 10

各行についてNULLに1のラベルを付けて、NULLの個数をカウントする

80【欠損値レコードの除外】

S-080: 商品テーブル（product）のいずれかの項目に欠損が発生しているレコードを全て削除した新たなproduct_1を作成せよ。なお、削除前後の件数を表示させ、前設問で確認した件数だけ減少していることも確認すること。

%%sql
-- もしproduct_1が既に存在していれば削除しておく
DROP TABLE IF EXISTS product_1;

-- 
CREATE TABLE product_1 AS (
    SELECT * FROM product
    WHERE unit_price IS NOT NULL OR unit_cost IS NOT NULL
);
SELECT COUNT(1) FROM product_1;

NULLであるかどうかはカラム IS NULL　で確認