Go to Qiita Advent Calendar 2024 Top
LoginSignup
7
5

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本ノック解説[61~80]

Last updated at Posted at 2020-10-02

数値変換[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　で確認
7
5
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
Sign upLogin
7
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?