🐕

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

2021/11/30に公開

PostgreSQL

SQL

初心者

idea

新しく出てきた内容

ウインドウ関数（lag）
数値型⇔文字列型⇔日付型の間の変換方法（to_char, to_date, to_timestamp）
castを使用した型変換 [cast(A as B)]
日付型の値の処理に使用できるextract関数

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

S-041:レシート明細テーブル（receipt）の売上金額（amount）を
　　　　日付（sales_ymd）ごとに集計し、前日からの売上金額増減を計算せよ。
　　　なお、計算結果は10件表示すればよい。

PostgreSQL
 with sales_amount_by_date as (
                               select sales_ymd,
                                      sum(amount) as amount
                               from receipt
                               group by sales_ymd
                               order by sales_ymd
                              )
 select sales_ymd,
        lag(sales_ymd,1) over(order by sales_ymd) as lag_ymd,
        amount,
        lag(amount,1) over(order by sales_ymd) as lag_ymd_amount,
        amount - lag(amount,1) over(order by sales_ymd) as diff_amount
 from sales_amount_by_date
 limit 10;

BigQuery
 with sales_amount_by_date as (
                               select sales_ymd,
                                      sum(amount) as amount
                               from [データセット名].receipt
                               group by sales_ymd order by sales_ymd
                              )
 select sales_ymd,
        lag(sales_ymd,1) over(order by sales_ymd) as lag_ymd,
        amount,
        lag(amount,1) over(order by sales_ymd) as lag_ymd_amount,
        amount - lag(amount,1) over(order by sales_ymd) as diff_amount
 from sales_amount_by_date
 order by sales_ymd
 limit 10;

ウインドウ関数の lag() を使う
lag(value,offset) over(order by )
　：現在行よりoffset行だけ前の行で評価されたvalueを返す。（下にずれる）
　・offset：ずらす行数

lag()の反対の操作をするウインドウ関数としてlead()が存在する。
・lead(value,offset) over(order by ) （上にずれる）

S-042:レシート明細テーブル（receipt）の売上金額（amount）を日付（sales_ymd）ごとに
　　　　集計し、各日付のデータに対し、１日前、２日前、３日前のデータを結合せよ。
　　　結果は10件表示すればよい。

PostgreSQL
 with sales_amount_by_date as (
                               select sales_ymd,
                                      sum(amount) as amount
                               from receipt
                               group by sales_ymd
                               order by sales_ymd
                              ),
      sales_amount_with_lag as (
                                select sales_ymd,
                                       amount,
                                       lag(sales_ymd,1) over(order by sales_ymd) as lag_ymd1,
                                       lag(amount,1) over(order by sales_ymd) as lag_amount1,
                                       lag(sales_ymd,2) over(order by sales_ymd) as lag_ymd2,
                                       lag(amount,2) over(order by sales_ymd) as lag_amount2,
                                       lag(sales_ymd,3) over(order by sales_ymd) as lag_ymd3,
                                       lag(amount,3) over(order by sales_ymd) as lag_amount3
                                from sales_amount_by_date
                               )
 select *
 from sales_amount_with_lag
 where lag_ymd3 is not null
 limit 10;

BigQuery
 with sales_amount_by_date as (
                               select sales_ymd,
                                      sum(amount) as amount
                               from [データセット名].receipt
                               group by sales_ymd
                               order by sales_ymd
                              ),
      sales_amount_with_lag as (
                                select sales_ymd, 
                                       amount, 
                                       lag(sales_ymd,1) over(order by sales_ymd) as lag_ymd1,
                                       lag(amount,1) over(order by sales_ymd) as lag_amount1,
                                       lag(sales_ymd,2) over(order by sales_ymd) as lag_ymd2,
                                       lag(amount,2) over(order by sales_ymd) as lag_amount2,
                                       lag(sales_ymd,3) over(order by sales_ymd) as lag_ymd3,
                                       lag(amount,3) over(order by sales_ymd) as lag_amount3
                                from sales_amount_by_date
                               )
 select *
 from sales_amount_with_lag
 where lag_ymd3 is not null
 order by sales_ymd
 limit 10;

with句とウィンドウ関数を使用

S-043とS-044は割愛（SQL向きではないらしい）

S-043: レシート明細テーブル（receipt）と顧客テーブル（customer）を結合し、性別（gender）と年代（ageから計算）ごとに売上金額（amount）を合計した売上サマリテーブル（sales_summary）を作成せよ。性別は0が男性、1が女性、9が不明を表すものとする。

ただし、項目構成は年代、女性の売上金額、男性の売上金額、性別不明の売上金額の4項目とすること（縦に年代、横に性別のクロス集計）。また、年代は10歳ごとの階級とすること。

S-044: 前設問で作成した売上サマリテーブル（sales_summary）は性別の売上を横持ちさせたものであった。このテーブルから性別を縦持ちさせ、年代、性別コード、売上金額の3項目に変換せよ。ただし、性別コードは男性を'00'、女性を'01'、不明を'99'とする。

S-045:顧客テーブル（customer）の生年月日（birth_day）は
　　　　日付型（Date）でデータを保有している。
　　　これをYYYYMMDD形式の文字列に変換し、顧客ID（customer_id）とともに抽出せよ。
　　　データは10件を抽出すれば良い。

PostgreSQL
 select customer_id,
        to_char(birth_day, 'YYYYMMDD')
 from customer
 limit 10;

BigQuery
 select customer_id,
        replace(cast(birth_day AS string), '-', '') as birth_day
 from [データセット名].customer
 limit 10;

to_char (日付型(or数値), 文字列（書式設定）) → 日付型(or数値)を文字列に変換する
日付/時刻型の書式テンプレートパターン
・YYYY 年（4桁以上）
・MM 月番号(01–12)
・DD 月内の日にち番号 (01–31)

S-046:顧客テーブル（customer）の申し込み日（application_date）は
　　　　 YYYYMMDD形式の文字列型でデータを保有している。
　　　これを日付型（dateやdatetime）に変換し、顧客ID（customer_id）とともに抽出せよ。
　　　データは10件を抽出すれば良い。

PostgreSQL
 select customer_id,
        to_date(application_date, 'YYYYMMDD') as application_date
 from customer
 limit 10;

BigQuery
 select customer_id,
        parse_date('%Y%m%d', application_date) as application_date
 from [データセット名].customer
 limit 10;

to_date (文字列型, 文字列（書式設定）) → 文字列を日付型に変換する

S-047:レシート明細テーブル（receipt）の売上日（sales_ymd）は
　　　　 YYYYMMDD形式の数値型でデータを保有している。
　　　これを日付型（dateやdatetime）に変換し、
　　　　レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。
　　　データは10件を抽出すれば良い。

PostgreSQL
 with text_receipt as (
                       select cast(sales_ymd as text) as text_sales_ymd, 
                              receipt_no, 
                              receipt_sub_no
                       from receipt
                      )
 select to_date(text_sales_ymd, 'YYYYMMDD') as date_sales_ymd,
        receipt_no,
        receipt_sub_no
 from text_receipt
 limit 10;

BigQuery
 with text_receipt as (
                       select cast(sales_ymd as string) as text_sales_ymd, 
                              receipt_no, 
                              receipt_sub_no
                       from [データセット名].receipt
                      )
 select parse_date('%Y%m%d', text_sales_ymd) as date_sales_ymd,
        receipt_no,
        receipt_sub_no
 from text_receipt
 limit 10;

数値から直接日付に変換できないので、数値→文字列→日付で変換する。
数値から文字列に変換するときにはcastを使用した。
（to_charでやろうとしたが上手くいかなかった。）

S-048:レシート明細テーブル（receipt）の売上エポック秒（sales_epoch）は
　　　　数値型のUNIX秒でデータを保有している。
　　　これを日付型（timestamp型）に変換し、
　　　　レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。
　　　データは10件を抽出すれば良い。

PostgreSQL
 select to_timestamp(sales_epoch),
        receipt_no,
        receipt_sub_no
 from receipt
 limit 10;

BigQuery
 select cast(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(cast(sales_epoch as int64)), 'Asia/Tokyo') as datetime),
        receipt_no,
        receipt_sub_no
 from [データセット名].receipt
 limit 10;

to_timestamp (文字列, 文字列) → 文字列をタイムスタンプに変換する

gcp
1.unixtimeを文字列から数値にする
2.unixtime → TIMESTAMP 変換
3.TIMESTAMP → 日付文字列変換
4.日付文字列 → DATETIME 変換

S-049:レシート明細テーブル（receipt）の販売エポック秒（sales_epoch）を
　　　　日付型（timestamp型）に変換し、 "年"だけ取り出して
　　　　レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。
　　　データは10件を抽出すれば良い。

PostgreSQL
 select to_char(extract(year from to_timestamp(sales_epoch)),'FM9999') as sales_year,
        receipt_no,
        receipt_sub_no
 from receipt
 limit 10;

BigQuery
 select format_date('%Y', cast(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(cast(sales_epoch as int64)), 'Asia/Tokyo') as date)) as sales_year,
        receipt_no,
        receipt_sub_no
 from [データセット名].receipt
 limit 10;

extract(field from source)
extract関数を使って、日付/時刻の値から年や時などの部分フィールドを抽出できる。extract関数はdouble precision型(浮動小数点型)の値を返す。

S-050:レシート明細テーブル（receipt）の売上エポック秒（sales_epoch）を
　　　　日付型（timestamp型）に変換し、"月"だけ取り出して
　　　　レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。
　　　なお、"月"は0埋め2桁で取り出すこと。データは10件を抽出すれば良い。

PostgreSQL
 select to_char(extract(month from to_timestamp(sales_epoch)),'FM00') as sales_month,
        receipt_no,
        receipt_sub_no
 from receipt
 limit 10;

BigQuery
 select format_date('%m', cast(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(cast(sales_epoch as int64)), 'Asia/Tokyo') as date)) as sales_month,
        receipt_no,
        receipt_sub_no
 from [データセット名].receipt
 limit 10;

新しく出てきた内容

Discussion