🐕

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

5 min read

新しく出てきた内容

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


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

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;

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

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


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

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;

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件を抽出すれば良い。

select customer_id, birth_day, to_char(birth_day, 'YYYYMMDD') 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件を抽出すれば良い。

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

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


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

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;

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


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

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

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


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

select to_char(extract(year from to_timestamp(sales_epoch)),'FM9999') 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件を抽出すれば良い。

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

Discussion

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