👌

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

3 min read

新しく出てきた内容

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

正規表現の参考

https://qiita.com/luohao0404/items/7135b2b96f9b0b196bf3

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

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

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

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

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

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

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

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

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


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

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

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


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

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

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


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

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

・{m}:m回の繰り返し(n{4}:nnnn)


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

select * from customer order by birth_day asc limit 10;

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


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

select * from customer order by birth_day desc limit 10;

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

select customer_id, amount, rank() over(order by amount desc) as rank_result from receipt 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)が等しい場合でも別順位を付与すること。

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

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

Discussion

ログインするとコメントできます