🐕

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

2021/11/30に公開

## 新しく出てきた内容

ウインドウ関数（lag）

castを使用した型変換 [cast(A as B)]

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：ずらす行数

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;
``````

（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;
``````