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
;