👌

データサイエンス100本ノック（SQL）11~20

2021/11/30に公開

PostgreSQL

SQL

初心者

idea

新しく出てきた内容

正規表現、order by句（結果の並び替えに使用できる）、ウインドウ関数(rank,row number)

正規表現の参考

Pythonの正規表現の例とあるがSQLでも使用することが出来た。

https://kino-code.com/sql19/

追記
最近になってGCPのBigQueryでも回答を作ってみたので合わせてそのコードも記載する
基本的にPostgreSQLの下にGCPのコードを記していく

S-011:顧客テーブル（customer）から顧客ID（customer_id）の末尾が1のものだけ
　　　　全項目抽出し、10件だけ表示せよ。

PostgreSQL
 select *
 from customer
 where customer_id like '%1'
 limit 10;

BigQuery
 select *
 from [データセット名].customer
 where customer_id like '%1'
 limit 10;

S-012:店舗テーブル（store）から横浜市の店舗だけ全項目表示せよ。

PostgreSQL
 select *
 from store
 where address like '%横浜市%';

BigQuery
 select *
 from [データセット名].store
 where address like '%横浜市%';

S-013:顧客テーブル（customer）から、ステータスコード（status_cd）の先頭が
　　　　アルファベットのA〜Fで始まるデータを全項目抽出し、10件だけ表示せよ。
　　　　--正規表現を使用する

PostgreSQL
 select *
 from customer
 where status_cd ~ '^[A-F]'
 limit 10;

BigQuery
 select *
 from [データセット名].customer
 where regexp_contains(status_cd, '^[A-F]')
 limit 10;

・^：文字列の先頭を表す正規表現。
・[]：集合を表す正規表現（[A-F]:A,B,C,D,E,F）

S-014:顧客テーブル（customer）から、ステータスコード（status_cd）の末尾が
　　　　数字の1〜9で終わるデータを全項目抽出し、10件だけ表示せよ。

PostgreSQL
 select *
 from customer
 where status_cd ~ '[1-9]$'
 limit 10;

BigQuery
 select *
 from [データセット名].customer
 where regexp_contains(status_cd, '[1-9]$')
 limit 10;

・$：文字列の末尾を表す正規表現。

S-015:顧客テーブル（customer）から、ステータスコード（status_cd）の先頭が
　　　　アルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、
　　　　 10件だけ表示せよ。

PostgreSQL
 select *
 from customer
 where status_cd ~ '^[A-F].*[1-9]$'
 limit 10;

BigQuery
 select *
 from [データセット名].customer
 where regexp_contains(status_cd, '^[A-F].*[1-9]$')
 limit 10;

・「.」：任意の１文字を表す正規表現
・「*」：直前の項目の0回以上複数の繰り返しを表す正規表現

S-016:店舗テーブル（store）から、
　　　　電話番号（tel_no）が3桁-3桁-4桁のデータを全項目表示せよ。

PostgreSQL
 select *
 from store
 where tel_no ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

BigQuery
 select *
 from [データセット名].store
 where regexp_contains(tel_no, '^[0-9]{3}-[0-9]{3}-[0-9]{4}$');

・{m}：m回の繰り返し（n{4}：nnnn）

S-017:顧客テーブル（customer）を生年月日（birth_day）で高齢順にソートし、
　　　　先頭10件を全項目表示せよ。

PostgreSQL
 select *
 from customer
 order by birth_day asc
 limit 10;

BigQuery
 select *
 from [データセット名].customer
 order by birth_day asc
 limit 10;

・order by 句で結果を並べ替えることが出来る
　　昇順に並べ替える：asc　or　省略
　　降順に並べ替える：desc

S-018:顧客テーブル（customer）を生年月日（birth_day）で若い順にソートし、
　　　　先頭10件を全項目表示せよ。

PostgreSQL
 select *
 from customer
 order by birth_day desc
 limit 10;

BigQuery
 select *
 from [データセット名].customer
 order by birth_day desc
 limit 10;

S-019:レシート明細テーブル（receipt）に対し、
　　　　 1件あたりの売上金額（amount）が高い順にランクを付与し、先頭10件を抽出せよ。
　　　項目は顧客ID(customer_id)、売上金額(amount)、付与したランクを表示させること。
　　　なお、売上金額（amount）が等しい場合は同一順位を付与するものとする。

PostgreSQL
 select customer_id,
        amount,
        rank() over(order by amount desc) as rank_result
 from receipt
 limit 10;

BigQuery
 select customer_id,
        amount,
        rank() over(order by amount desc) as rank_result
 from [データセット名].receipt
 order by amount desc
 limit 10;

rank()というウインドウ関数を使うことでランクを付与できる。
rank()は同率の場合同率順位を付与し、その次の順位を飛ばす。（1,2,3,3,5...）
ほとんど同じ動きをする関数としてdense rank()があるが、こちらは順位を飛ばさない。
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（1,2,3,3,4,...）

ウインドウ関数は常にover句を含む（over句の中で細かい処理順を決める）

S-020:レシート明細テーブル（receipt）に対し、
　　　　 1件あたりの売上金額（amount）が高い順にランクを付与し、先頭10件を抽出せよ。
　　　項目は顧客ID(customer_id)、売上金額(amount)、付与したランクを表示させること。
　　　なお、売上金額（amount）が等しい場合でも別順位を付与すること。

PostgreSQL
 select customer_id,
        amount,
        row_number() over(order by amount desc) as rank_result
 from receipt
 limit 10;

BigQuery
 select customer_id,
        amount,
        row_number() over(order by amount desc) as rank_result
 from [データセット名].receipt
 order by amount desc
 limit 10;

row number()というウインドウ関数でもランクを付与できる。
ただし、row number()は同率の場合でも同じ順位にはならない。
row number()は順位をつけるための関数ではなく、連番を振るための関数らしい。

新しく出てきた内容

Discussion