こんにちは、ドイです! 前回に引き続き、データサイエンス100本ノックに挑戦していきます。
前回の記事
S-021: レシート明細データ(receipt)に対し、件数をカウントせよ。
SELECT COUNT(*) FROM receipt;
S-022: レシート明細データ(receipt)の顧客ID(customer_id)に対し、ユニーク件数をカウントせよ
SELECT COUNT(DISTINCT customer_id) FROM receipt;
S-023: レシート明細データ(receipt)に対し、店舗コード(store_cd)ごとに売上金額(amount)と売上数量(quantity)を合計せよ。
SELECT store_cd, SUM(amount) AS amount, SUM(quantity) AS quantity FROM receipt GROUP BY store_cd;
S-024: レシート明細データ(receipt)に対し、顧客ID(customer_id)ごとに最も新しい売上年月日(sales_ymd)を求め、10件表示せよ。
SELECT customer_id, MAX(sales_ymd) FROM receipt GROUP BY customer_id LIMIT 10;
S-025: レシート明細データ(receipt)に対し、顧客ID(customer_id)ごとに最も古い売上年月日(sales_ymd)を求め、10件表示せよ。
SELECT customer_id, MIN(sales_ymd) FROM receipt GROUP BY customer_id LIMIT 10;
S-026: レシート明細データ(receipt)に対し、顧客ID(customer_id)ごとに最も新しい売上年月日(sales_ymd)と古い売上年月日を求め、両者が異なるデータを10件表示せよ。
SELECT customer_id, MAX(sales_ymd) ,MIN(sales_ymd) FROM receipt GROUP BY customer_id HAVING MAX(sales_ymd) != MIN(sales_ymd) LIMIT 10;
S-027: レシート明細データ(receipt)に対し、店舗コード(store_cd)ごとに売上金額(amount)の平均を計算し、降順でTOP5を表示せよ。
SELECT AVG(amount) AS amount FROM receipt GROUP BY store_cd ORDER BY amount DESC LIMIT 5;
S-028: レシート明細データ(receipt)に対し、店舗コード(store_cd)ごとに売上金額(amount)の中央値を計算し、降順でTOP5を表示せよ。
SELECT store_cd, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY amount) AS median_amount FROM receipt GROUP BY store_cd ORDER BY median_amount DESC LIMIT 5;
S-029: レシート明細データ(receipt)に対し、店舗コード(store_cd)ごとに商品コード(product_cd)の最頻値を求め、10件表示させよ。
SELECT store_cd, MODE() WITHIN GROUP(ORDER BY product_cd) FROM receipt GROUP BY store_cd LIMIT 10;
S-030: レシート明細データ(receipt)に対し、店舗コード(store_cd)ごとに売上金額(amount)の分散を計算し、降順で5件表示せよ。
SELECT store_cd, VAR_SAMP(amount) AS variance_amount FROM receipt GROUP BY store_cd ORDER BY variance_amount DESC LIMIT 5;
まとめ
Postgresの関数に慣れないので、中央値や最頻値の出し方に苦戦しました。
今回、初めて触れた集約関数です。
中央値:PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY 'カラム名')
分散値:MODE() WITHIN GROUP(ORDER BY 'カラム名')
分散値:VAR_SAMP('カラム名')
なかなか使うことがないので、頭の片隅に入れておきたいと思います。