SQL100本ノックの前半(WITHが出てくるまで)の基礎的な問題に対する個人的な備忘録です。
Q9 13ではない場合(not equal to)
prefecture_cd != '13'
Q10 S14で始まるもの
WHERE store_cd LIKE 'S14%'
Q11 末尾が1のもの
WHERE customer_id LIKE '%1'
Q12 '横浜市'が含まれるもの
WHERE address LIKE '%横浜市%'
Q13 先頭がアルファベットのA-Fで始まるもの
WHERE status_cd ~ '^[A-F]'
Q14 末尾が数字の1-9で終わるもの
WHERE status_cd ~ '[1-9]$'
Q15 先頭A-F、末尾1-9で終わるもの
WHERE status_cd ~ '^[A-F].*[1-9]$'
Q16 電話番号が3桁-3桁-4桁のデータを参照
WHERE tel_no ~'[0-9]{3}-[0-9]{3}-[0-9]{4}$'
-ランクの付け方-
Q19 amountのランクを表示、等しい場合は同一順位を付与
SELECT RANK() OVER(ORDER BY amount DESC) AS ranking
Q20 amountのランクを表示、等しい場合でも別順位を付与
SELECT ROW_NUMBER() OVER(ORDER BY amount DESC) AS ranking
-count-
Q21 receiptデータの件数をカウント
SELECT COUNT(*)
Q22 重複を排除したcustomer_idをカウント
SELECT COUNT(DISTINCT customer_id)
Q23 店舗ごとの売上金額の合計
SELECT
store_id,
SUM(amount) AS amount
Q24 最も新しい or 古い売上年月日
SELECT
MAX(sales_ymd),
MIN(sales_ymd)
-WHERE/HAVING-
Q26 HAVING句
HAVING
GROUP BY句と一緒に使われる条件式で、グループ化された結果に対して条件を適用する。グループ化された結果を制限する。集約関数(SUM,COUNT,AVG...)を使用する場合に用いる。
SELECT
customer_id,
MAX(sales_ymd),
MIN(sales_ymd)
FROM
receipt
GROUP BY
customer_id
HAVING
MAX(sales_ymd) != MIN(sales_ymd)
LIMIT 10
;
WHERE
FROM句で指定されたテーブルからデータを取得する際に、条件を適用するために使用される。データの集合を制限する。
SELECT
customer_id
FROM
receipt
WHERE
customer_id = 'CS018205000001'
;
Q28 amountの中央値の計算
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY amount) AS med_amount
WITHIN GROUP は定型的な記述。ORDER BYでは値を取る対象列の指定とデータの並び順を指定する。
Q29 商品コード(product_cd)ごとの最頻値
SELECT MODE() WITHIN GROUP(ORDER BY product_cd)
Q30 amountの分散
SELECT VAR_POP(amount) AS var_amount
Q31 amountの標準偏差
SELECT STDDEV_POP(amount) AS std_amount
Q33 店舗ごとに売上金額の平均を計算し、330以上のものを抽出
SELECT
store_id,
AVG(amount) AS avg_amount
FROM
receipt
GROUP BY
store_id
HAVING
AVG(amount) >= 330
;