🕌

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

2021/11/30に公開

PostgreSQL

SQL

初心者

idea

はじめに

前回の記事でローカル環境のPostgreSQLにデータを入れるところまで行ったので、今回からは実際に問題とその回答を書いていくことにする。

100問全部取り組んだわけでは無く、解答を見ても良く分からなかった問題やSQLで処理することが大変な問題についてはコードを割愛している。（念のため、問題文だけは記載する）

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

学習する内容

select句、from句、as、where句（行の絞り込み）、between演算子、文字列のあいまい検索

S-001:レシート明細テーブル（receipt）から全項目を10件抽出し、
　　　　どのようなデータを保有しているか目視で確認せよ。

PostgreSQL
 select * from receipt limit 10;

BigQuery
 select * from `[プロジェクト名].[データセット名].receipt` limit 10;

・「*」で全項目選択、「limit 10」で10件抽出。
・gcpの場合はテーブル名だけだと出来ないことがあるので注意

S-002:レシート明細のテーブル(receipt)から
　　　　売上日(sales_ymd)、顧客ID(customer_id)、
　　　　商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示せよ。

PostgreSQL
 select sales_ymd,
        customer_id,
        product_cd,
        amount
 from receipt
 limit 10;

BigQuery
 select sales_ymd,
        customer_id,
        product_cd,
        amount
 from [プロジェクト名].[データセット名].receipt
 limit 10;

S-003:レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID(customer_id)、
　　　　商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示せよ。
　　　ただし、sales_ymdはsales_dateに項目名を変更しながら抽出すること。

PostgreSQL
 select sales_ymd as sales_date,
        customer_id,
        product_cd,
        amount
 from receipt
 limit 10;

BigQuery
 select sales_ymd as sales_date,
        customer_id,
        product_cd,
        amount
 from [プロジェクト名].[データセット名].receipt
 limit 10;

・「as」の後に名前を書くことで別名を使うことが出来る。

S-004:レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID(customer_id)、
　　　　商品コード（product_cd）、売上金額（amount）の順に列を指定し、
　　　　以下の条件を満たすデータを抽出せよ。
・顧客ID（customer_id）が"CS018205000001"

PostgreSQL
 select sales_ymd,
        customer_id,
        product_cd,
        amount
 from receipt
 where customer_id = 'CS018205000001';

BigQuery
 select sales_ymd,
        customer_id,
        product_cd,
        amount
 from [データセット名].receipt
 where customer_id = 'CS018205000001';

・where句を使うことで抽出する行を具体的に指定することが出来る。

S-005:レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID(customer_id)、
　　　　商品コード（product_cd）、売上金額（amount）の順に列を指定し、
　　　　以下の条件を満たすデータを抽出せよ。
・顧客ID（customer_id）が"CS018205000001"
・売上金額（amount）が1,000以上

PostgreSQL
 select sales_ymd,
       customer_id,
       product_cd,
       amount
 from receipt
 where customer_id = 'CS018205000001' and amount >= 1000;

BigQuery
 select sales_ymd,
        customer_id,
        product_cd,
        amount
 from [データセット名].receipt
 where customer_id = 'CS018205000001' and amount >= 1000;

・where句では条件を複数指定することが出来る。（andなどで一つ一つ書かないといけない）

S-006:レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID(customer_id)、
　　　　商品コード（product_cd）、売上数量（quantity）、売上金額（amount）の順に
　　　　列を指定し、以下の条件を満たすデータを抽出せよ。
・顧客ID（customer_id）が"CS018205000001"
・売上金額（amount）が1,000以上または売上数量（quantity）が5以上

PostgreSQL
 select sales_ymd,
        customer_id,
        product_cd,
        quantity,
        amount
  from receipt
  where customer_id = 'CS018205000001' and (amount >= 1000 or quantity >= 5);

BigQuery
 select sales_ymd,
        customer_id,
        product_cd,
        quantity,
        amount
 from [データセット名].receipt
 where customer_id = 'CS018205000001' and (amount >= 1000 or quantity >= 5);

S-007:レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID(customer_id)、
　　　　商品コード（product_cd）、売上金額（amount）の順に列を指定し、
　　　　以下の条件を満たすデータを抽出せよ。
・顧客ID（customer_id）が"CS018205000001"
・売上金額（amount）が1,000以上2,000以下

PostgreSQL
 select sales_ymd,
        customer_id,
        product_cd,
        quantity,
        amount 
 from receipt
 where customer_id = 'CS018205000001' and amount between 1000 and 2000;

BigQuery
 select sales_ymd,
        customer_id,
        product_cd,
        quantity,
        amount
 from [データセット名].receipt
 where customer_id = 'CS018205000001' and amount between 1000 and 2000;

・between演算子を使って表記することが出来る。
（a between x and y) --> (x <= a and a <= y)

S-008:レシート明細テーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、
　　　　商品コード（product_cd）、売上金額（amount）の順に列を指定し、
　　　　以下の条件を満たすデータを抽出せよ。
・顧客ID（customer_id）が"CS018205000001"
・商品コード（product_cd）が"P071401019"以外

PostgreSQL
 select sales_ymd,
        customer_id,
        product_cd,
        quantity,
        amount
 from receipt
 where customer_id = 'CS018205000001' and product_cd != 'P071401019';

BigQuery
 select sales_ymd,
        customer_id,
        product_cd,
        quantity, 
        amount
 from [データセット名].receipt
 where customer_id = 'CS018205000001' and product_cd != 'P071401019';

S-009:以下の処理において、出力結果を変えずにORをANDに書き換えよ。
select * from store where not (prefecture_cd = '13' or floor_area > 900)

PostgreSQL
 select *
 from store
 where prefecture_cd != '13' and floor_area <= 900;

BigQuery
 select *
 from [データセット名].store
 where not (prefecture_cd = '13' or floor_area > 900)

・「コードが13である行、またはエリアが900より上である行」を否定している。
つまり、「コードが13以外の行、かつエリアが900以下である行」を取り出したいということ。

S-010:店舗テーブル（store）から、店舗コード（store_cd）が"S14"で始まるものだけ全項目抽出し、10件だけ表示せよ。

PostgreSQL
 select *
 from store
 where store_cd like 'S14%' limit 10;

BigQuery
 select *
 from [データセット名].store
 where store_cd like 'S14%' limit 10;

・where句の中でlike演算子を使うことで文字列のあいまい検索をする事ができる。

% ：任意の⻑さの任意の文字から構成される文字列を表す（０文字でも可）。
_ ：１文字の任意文字を表す。
　例）人の名前で検索をしたい場合
　～like '%中%' ：　名前のどこか（最初と最後以外）に「中」が含まれる人を検索する。
　～like '佐---'　：　名前が佐から始まる４文字の人を検索する。

はじめに

学習する内容

Discussion