データサイエンス100本ノック（SQL）61~70
新しく出てきた内容
log()：常用対数
ln()：自然対数
round()：四捨五入が出来る
ceil()：引数より大きいか等しく、引数に最も近い整数を返す。
追記
最近になってGCPのBigQueryでも回答を作ってみたので合わせてそのコードも記載する
基本的にPostgreSQLの下にGCPのコードを記していく
S-061:レシート明細テーブル（receipt）の売上金額（amount）を
顧客ID（customer_id）ごとに合計し、合計した売上金額を常用対数化（底=10）して
顧客ID、売上金額合計とともに表示せよ。
ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
結果は10件表示させれば良い。
PostgreSQL
select customer_id,
sum(amount) as amount,
log(sum(amount) + 1) as log_amount
from receipt
where customer_id not like 'Z%'
group by customer_id
limit 10;
BigQuery
select customer_id,
sum(amount) as amount,
log10(sum(amount) + 1) as log_amount
from [データセット名].receipt
where customer_id not like 'Z%'
group by customer_id
limit 10;
--0だと対数変換できないので1を足している。
S-062:レシート明細テーブル（receipt）の売上金額（amount）を
顧客ID（customer_id）ごとに合計し、合計した売上金額を自然対数化(底=e）して
顧客ID、売上金額合計とともに表示せよ。
ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
結果は10件表示させれば良い。
PostgreSQL
select customer_id,
sum(amount) as amount,
ln(sum(amount) + 1) as log_amount
from receipt
where customer_id not like 'Z%'
group by customer_id
limit 10;
BigQuery
select customer_id,
sum(amount) as amount,
ln(sum(amount) + 1) as log_amount
from [データセット名].receipt
where customer_id not like 'Z%'
group by customer_id
limit 10;
S-063:商品テーブル（product）の単価（unit_price）と原価（unit_cost）から、
各商品の利益額を算出せよ。結果は10件表示させれば良い。
PostgreSQL
select product_cd,
unit_price,
unit_cost,
unit_price - unit_cost as unit_profit
from product
limit 10;
BigQuery
select product_cd,
unit_price,
unit_cost,
unit_price - unit_cost as unit_profit
from [データセット名].product
limit 10;
S-064:商品テーブル（product）の単価（unit_price）と原価（unit_cost）から、
各商品の利益率の全体平均を算出せよ。
ただし、単価と原価にはNULLが存在することに注意せよ。
PostgreSQL
select avg(cast((unit_price - unit_cost) as numeric) / cast(unit_price as numeric)) as unit_profit_rate
from product;
BigQuery
select avg(cast((unit_price - unit_cost) as numeric) / cast(unit_price as numeric)) as unit_profit_rate
from [データセット名].product;
S-065:商品テーブル（product）の各商品について、利益率が30%となる新たな単価を求めよ。
ただし、1円未満は切り捨てること。
そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。
ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。
PostgreSQL
select product_cd,
unit_price,
unit_cost,
trunc(unit_cost / 0.7) as new_unit_price,
((trunc(unit_cost / 0.7) - unit_cost) / trunc(unit_cost / 0.7)) as unit_profit_percent
from product
limit 10;
BigQuery
select product_cd,
unit_price,
unit_cost,
trunc(unit_cost / 0.7) as new_unit_price,
((trunc(unit_cost / 0.7) - unit_cost) / trunc(unit_cost / 0.7)) as unit_profit_percent
from [データセット名].product
limit 10;
S-066:商品テーブル（product）の各商品について、利益率が30%となる新たな単価を求めよ。
今回は、1円未満を四捨五入すること。
そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。
ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。
PostgreSQL
select product_cd,
unit_price,
unit_cost,
round(unit_cost / 0.7) as new_price_rounding,
((round(unit_cost / 0.7) - unit_cost) / round(unit_cost / 0.7)) as profit_percent
from product
limit 10;
BigQuery
select product_cd,
unit_price,
unit_cost,
round(unit_cost / 0.7) as new_price_rounding,
((round(unit_cost / 0.7) - unit_cost) / round(unit_cost / 0.7)) as profit_percent
from [データセット名].product
limit 10;
S-067:商品テーブル（product）の各商品について、利益率が30%となる新たな単価を求めよ。
今回は、1円未満を切り上げること。
そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。
ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。
PostgreSQL
select product_cd,
unit_price,
unit_cost,
ceil(unit_cost / 0.7) as new_price_rounding,
((ceil(unit_cost / 0.7) - unit_cost) / ceil(unit_cost / 0.7)) as profit_percent
from product
limit 10;
BigQuery
select product_cd,
unit_price,
unit_cost,
ceil(unit_cost / 0.7) as new_price_rounding,
((ceil(unit_cost / 0.7) - unit_cost) / ceil(unit_cost / 0.7)) as profit_percent
from [データセット名].product
limit 10;
S-068:商品テーブル（product）の各商品について、消費税率10%の税込み金額を求めよ。
1円未満の端数は切り捨てとし、結果は10件表示すれば良い。
ただし、単価（unit_price）にはNULLが存在することに注意せよ。
PostgreSQL
select product_cd,
unit_price,
unit_price * 1.1 as tax_unit_price,
trunc(unit_price * 1.1) as tax_unit_price_trunc
from product
limit 10;
BigQuery
select product_cd,
unit_price,
unit_price * 1.1 as tax_unit_price,
trunc(unit_price * 1.1) as tax_unit_price_trunc
from [データセット名].product
limit 10;
--trunc()：切り捨てが出来る。
S-069:レシート明細テーブル（receipt）と商品テーブル（product）を結合し、顧客毎に
全商品の売上金額合計と、カテゴリ大区分（category_major_cd）が"07"（瓶詰缶詰）
の売上金額合計を計算の上、両者の比率を求めよ。
抽出対象はカテゴリ大区分"07"（瓶詰缶詰）の購入実績がある顧客のみとし、
結果は10件表示させればよい。
PostgreSQL
create table if not exists customer_sum_amount as
select customer_id,
cast(sum(amount) as numeric) as sum_amount
from receipt
group by customer_id;
create table if not exists customer07_sum_amount as
select r.customer_id,
sum(r.amount) as sum_07_amount
from receipt as r
inner join product as pr
on r.product_cd = pr.product_cd and category_major_cd = '07'
group by r.customer_id;
select csa.*,
c07sa.sum_07_amount,
sum_07_amount / sum_amount as sales_rate_07
from customer_sum_amount as csa
inner join customer07_sum_amount as c07sa
on csa.customer_id = c07sa.customer_id;
BigQuery
create table if not exists [データセット名].customer_sum_amount as
select customer_id,
sum(amount) as sum_amount
from [データセット名].receipt
group by customer_id;
create table if not exists [データセット名].customer07_sum_amount as
select r.customer_id,
sum(r.amount) as sum_07_amount
from [データセット名].receipt as r
inner join [データセット名].product as pr
on r.product_cd = pr.product_cd and category_major_cd = '7'
group by r.customer_id;
select csa.*,
c07sa.sum_07_amount,
sum_07_amount / sum_amount as sales_rate_07
from [データセット名].customer_sum_amount as csa
inner join [データセット名].customer07_sum_amount as c07sa
on csa.customer_id = c07sa.customer_id;
S-070:レシート明細テーブル（receipt）の売上日（sales_ymd）に対し、
顧客テーブル(customer)の会員申込日(application_date)からの経過日数を計算し、
顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。
結果は10件表示させれば良い（なお、sales_ymdは数値、
application_dateは文字列でデータを保持している点に注意）。
PostgreSQL
create table if not exists sales as
select distinct customer_id,
sales_ymd
from receipt;
create table if not exists app_date as
select customer_id,
application_date
from customer;
select sa.customer_id,
sa.sales_ymd,
ap.application_date,
to_date(cast(sa.sales_ymd as text), 'YYYYMMDD') - to_date(ap.application_date, 'YYYYMMDD') as elapsed_days
from sales as sa
inner join app_date as ap
on sa.customer_id = ap.customer_id;
BigQuery
create table if not exists [データセット名].sales as
select distinct customer_id,
sales_ymd
from [データセット名].receipt;
create table if not exists [データセット名].app_date as
select customer_id,
application_date
from [データセット名].customer;
select sa.customer_id,
sa.sales_ymd,
ap.application_date,
date_diff(parse_date('%Y%m%d', cast(sa.sales_ymd as string)), parse_date('%Y%m%d', ap.application_date), day) as elapsed_days,
from [データセット名].sales as sa
inner join [データセット名].app_date as ap
on sa.customer_id = ap.customer_id;
Discussion