Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

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 1 year has passed since last update.

@ukir-A

SQLデータサイエンス100本ノック(記録)61〜80

Posted at

問６１　

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

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;

問６２　

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

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;

問６３　

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

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

問６４　

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

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

問６５　

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

SELECT
    product_cd, 
    unit_price, 
    unit_cost,
    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;

問６６　

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

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

問６７

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

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;

問６８

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

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

問６９　

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

WITH amount_all AS(
    SELECT
        customer_id,
        sum(amount) AS sum_all
    FROM
        receipt
    GROUP BY
        customer_id
),
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;

問７０　

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

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;

問７１　

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

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

問７２　

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

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

問７３　

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

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

問７４　

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

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;

問７５　

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

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

問７６　

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

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;

問７７　

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

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;

問７８　

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

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;

問７９　

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

問８０　

S-080: 商品テーブル（product）のいずれかの項目に欠損が発生しているレコードを全て削除した新たな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;
0
0
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
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?