SELECTでデータを取り出してみる

目標

PostgreSQLに直接接続して、SELECT、WHERE、DISTINCT、ORDER BYで必要な行だけを選び出し、その結果をビューとして保存できるようになります。

なぜ重要なのか

SQLは命令的ではなく宣言的です。「このファイルを開いて1行ずつ読んで比較せよ」ではなく、「こういう条件を満たす行をください」と書けば、どう探すかは、データベースがそのときの統計を見て決めます。そのため、SQLを上手に使うということは、欲しい集合を正確に記述する能力であり、速いアルゴリズムを知る能力ではありません。

このラボで特に注目してほしいのは、NULLです。NULLは「値がない」ではなく「わからない」に近く、どんな値と比較しても真になりません。この性質1つが、照会、集計、結合の全般に波及するので、今のうちに手で確認しておくのがよいです。

ビューとして保存する理由もあります。ビューは、結果をコピーしておくものではなく、クエリそのものに名前を付けるものなので、元のデータが変わればビューの結果も一緒に変わります。採点スクリプトが、みなさんの作ったビューを元のデータと突き合わせられるのも、この性質のおかげです。

ステップ

1. customersテーブルの全行数を数え、その数字だけを/root/rowcount.txtに保存します。
2. is_activeが真の顧客だけを含むビューv_active_customersを作ります。カラムは元のままにします。
3. countryがKRで、かつtierがgoldの顧客だけを含むビューv_kr_goldを作ります。
4. emailが空の顧客だけを含むビューv_missing_emailを作ります。
5. productsのcategoryを重複なしで含むビューv_categoriesを作ります。カラムはcategory1つだけである必要があります。
6. is_activeが真で、かつpriceが200000以上の商品を含むビューv_expensiveを作ります。
7. ordered_atがTIMESTAMPTZ '2025-07-01 00:00:00+09'以降(同じ値を含む)の注文を含むビューv_recent_ordersを作ります。
8. countryがKRで、かつis_activeが真の顧客のid、name、tierをこの順序で含むビューv_kr_reportを作ります。並べ替えはtierの昇順で、同じならidの昇順です。

参考

• 接続: psql -h 127.0.0.1 -U lab -d labdb(パスワードはlabで、環境変数PGPASSWORDを使うと便利です)
• テーブルの一覧は\dt、カラム構造は\d customersで見られます。
• ファイルに値だけを取り出すときは、psql -tAc "select ..." > 파일の形が便利です(プレースホルダーはファイル名です)。
• よくある間違い1: WHERE email = NULLは、エラーも出ずに、ただ0行を返します。
• よくある間違い2: ビューを作り直すときはCREATE OR REPLACE VIEWを使いますが、カラムの数や名前が変わる場合は、先にDROP VIEWする必要があります。

顧客数を数えてみる

customersテーブルの全行数を数え、その数字だけを/root/rowcount.txtに保存します。

行数を数える集計関数と、psqlの結果だけを取り出すオプション(-t、-A)を組み合わせると、ファイルに数字だけを残せます。

アクティブな顧客のビューを作る

is_activeが真の顧客だけを含むビューv_active_customersを作ります。カラムは元のままにします。

boolean型のカラムは、= trueを付けずに、条件にそのまま使えます。ビューはCREATE VIEW view_name AS SELECT ...の形です。

2つの条件を同時にかける

countryがKRで、かつtierがgoldの顧客だけを含むビューv_kr_goldを作ります。

2つの条件をどちらも満たす必要があるので、ANDで結びます。文字列の比較は、大文字と小文字を区別します。

メールアドレスがない顧客を探す

emailが空の顧客だけを含むビューv_missing_emailを作ります。

NULLはどんな値と比較しても真になりません。等号の代わりに、専用の演算子が必要です。

カテゴリの一覧を取り出す

productsのcategoryを重複なしで含むビューv_categoriesを作ります。カラムはcategory1つだけである必要があります。

重複をなくすキーワードをSELECTの直後に付けます。結果のカラムは1つである必要があるので、他のカラムを入れないでください。

販売中の高額商品を探す

is_activeが真で、かつpriceが200000以上の商品を含むビューv_expensiveを作ります。

価格の条件と販売中かどうかの条件を、両方かける必要があります。20万ウォン以上なので、境界値を含みます。

特定の時点以降の注文を選ぶ

ordered_atがTIMESTAMPTZ '2025-07-01 00:00:00+09'以降(同じ値を含む)の注文を含むビューv_recent_ordersを作ります。

ordered_atはタイムゾーンを含む型です。比較する値にもタイムゾーンを明示すると、サーバー設定に関係なく同じ結果になります。

レポート用のビューで仕上げる

countryがKRで、かつis_activeが真の顧客のid、name、tierをこの順序で含むビューv_kr_reportを作ります。並べ替えはtierの昇順で、同じならidの昇順です。

必要なカラムだけを選んで、エイリアスを合わせてください。並べ替えの基準が2つのときは、ORDER BYにカンマで並べます。