フィルタリングと式

目標

BETWEEN、IN、LIKE、COALESCE、CASE、IS DISTINCT FROM、LIMIT/OFFSETを実際のデータに適用して、欲しい集合を正確に記述できるようになります。

なぜ重要なのか

SQLで条件を書くということは、集合を定義することです。そして、この集合演算には、他の言語にない3つ目の真理値が割り込みます。NULLが関与した比較は、真でも偽でもない不明になり、WHEREは真の行だけを通過させるので、不明は静かに除外されます。

この性質のために、city = '서울'とcity <> '서울'の行数を足しても、全体になりません(韓国語で「ソウル」を意味する語です)。どちらにも属さないNULLの行があるからです。この種のバグは、エラーを出さずに数字だけが静かに狂うため、発見が遅れます。今回のラボで自分で確認しておけば、後でレポートの数字が合わないときに、最初に疑う場所ができます。

ステップ

1. priceが50000以上150000以下の商品を含むビューv_mid_priceを作ります。
2. statusがpaidまたはshippedの注文を含むビューv_multi_statusを作ります。
3. nameに프로という文字が含まれる商品を含むビューv_pro_productsを作ります(韓国語で「プロ」を意味する語です)。
4. 顧客のidとcityの2つのカラムだけを含み、cityが空なら미상に置き換えたビューv_city_filledを作ります。カラム名はid、cityです(韓国語で「不明」を意味する語です)。
5. 注文のidと規模の分類を含むビューv_order_sizeを作ります。カラムはid、sizeで、total_amountが1000000以上なら대형、300000以上なら중형、残りは소형です(韓国語の3つの語は、それぞれ「大型」「中型」「小型」を意味します)。
6. cityが서울でない顧客を含むビューv_not_seoulを作ります。cityが空の顧客も含まれる必要があります(韓国語で「ソウル」を意味する語です)。
7. 商品をpriceの降順、同じならidの昇順で並べたときの3ページ目(1ページ20件)を含むビューv_page3を作ります。
8. countryがKR、is_activeが真、signup_dateが2024-07-01以降(同じ日を含む)、tierがgoldまたはvipの顧客のid、name、tier、signup_dateを含むビューv_targetを作ります。並べ替えはsignup_dateの降順、同じならidの昇順です。

参考

• 接続: PGPASSWORD=lab psql -h 127.0.0.1 -U lab -d labdb
• ビューのカラム名と順序まで採点するので、エイリアスを正確に合わせてください。
• よくある間違い1: BETWEENは両端の値を含みます。未満と勘違いすると、境界の行がずれます。
• よくある間違い2: ステップ6をcity <> '서울'と書くと、NULLの顧客が抜けます。行数を数えて確認してみてください(韓国語で「ソウル」を意味する語です)。

価格の区間で絞り込む

priceが50000以上150000以下の商品を含むビューv_mid_priceを作ります。

区間条件を一度に書くキーワードがあります。両端の値が含まれるか確認してください。

複数の値のうち1つを選ぶ

statusがpaidまたはshippedの注文を含むビューv_multi_statusを作ります。

ORを何度も書く代わりに、リストで表現するキーワードがあります。

名前に特定の文字列を含む商品を探す

nameに프로という文字が含まれる商品を含むビューv_pro_productsを作ります(韓国語で「プロ」を意味する語です)。

部分一致を探すには、ワイルドカードを前後の両方に付ける必要があります。

空の値をデフォルト値で埋める

顧客のidとcityの2つのカラムだけを含み、cityが空なら미상に置き換えたビューv_city_filledを作ります。カラム名はid、cityです(韓国語で「不明」を意味する語です)。

NULLのときだけ代替値を返す関数があります。元の値がある行は、そのままにする必要があります。

条件に応じて分類する

注文のidと規模の分類を含むビューv_order_sizeを作ります。カラムはid、sizeで、total_amountが1000000以上なら대형、300000以上なら중형、残りは소형です(韓国語の3つの語は、それぞれ「大型」「中型」「小型」を意味します)。

CASE WHENは、上から最初に真になる枝を選びます。大きな区間から書けば、重なりを避けられます。

NULLまで含めて反転する

cityが서울でない顧客を含むビューv_not_seoulを作ります。cityが空の顧客も含まれる必要があります(韓国語で「ソウル」を意味する語です)。

通常の不等号では、NULLの行が引っかかりません。NULLを1つの値のように比較してくれる演算子があります。

3ページ目を取得する

商品をpriceの降順、同じならidの昇順で並べたときの3ページ目(1ページ20件)を含むビューv_page3を作ります。

スキップする行数と取得する行数を別々に指定します。1ページが20件なら、3ページ目は何件スキップする必要があるでしょうか。

4つの条件を組み合わせたターゲットリスト

countryがKR、is_activeが真、signup_dateが2024-07-01以降(同じ日を含む)、tierがgoldまたはvipの顧客のid、name、tier、signup_dateを含むビューv_targetを作ります。並べ替えはsignup_dateの降順、同じならidの昇順です。

前に使った条件をANDで結び、必要なカラムだけを順番に選び、並べ替えの基準を2つ指定します。