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.

SQL 100本ノック 知識の整理(-33)

Posted at

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
;
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
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?