👋

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

5 min read

新しく出てきた内容

log():常用対数
ln():自然対数
round():四捨五入が出来る
ceil():引数より大きいか等しく、引数に最も近い整数を返す。


S-061:レシート明細テーブル(receipt)の売上金額(amount)を
     顧客ID(customer_id)ごとに合計し、合計した売上金額を常用対数化(底=10)して
     顧客ID、売上金額合計とともに表示せよ。
    ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
    結果は10件表示させれば良い。

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;

--0だと対数変換できないので1を足している。


S-062:レシート明細テーブル(receipt)の売上金額(amount)を
     顧客ID(customer_id)ごとに合計し、合計した売上金額を自然対数化(底=e)して
     顧客ID、売上金額合計とともに表示せよ。
    ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。
    結果は10件表示させれば良い。

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件表示させれば良い。

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が存在することに注意せよ。

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が存在することに注意せよ。

with new_ as (select product_cd,
                     unit_price,
		     unit_cost,
		     trunc(unit_cost / 0.7) as new_unit_price
               from product)
select product_cd,
       unit_price,
       unit_cost,
       new_unit_price,
       (new_unit_price - unit_cost) / new_unit_price as unit_profit_percent
 from new_ limit 10;

S-066:商品テーブル(product)の各商品について、利益率が30%となる新たな単価を求めよ。
    今回は、1円未満を四捨五入すること。
    そして結果を10件表示させ、利益率がおよそ30%付近であることを確認せよ。
    ただし、単価(unit_price)と原価(unit_cost)にはNULLが存在することに注意せよ。

with product1 as (select product_cd,
                         unit_price,
			 unit_cost,
			 unit_cost / 0.7 as new_price,
			 round(unit_cost / 0.7) as new_price_rounding
                   from product)
select *,
       (new_price_rounding - unit_cost) / new_price_rounding as profit_percent 
 from product1 limit 10;

S-067:商品テーブル(product)の各商品について、利益率が30%となる新たな単価を求めよ。
    今回は、1円未満を切り上げること。
    そして結果を10件表示させ、利益率がおよそ30%付近であることを確認せよ。
    ただし、単価(unit_price)と原価(unit_cost)にはNULLが存在することに注意せよ。

with product1 as (select product_cd,
                         unit_price,
			 unit_cost, unit_cost / 0.7 as new_price,
			 ceil(unit_cost / 0.7) as new_price_rounding from product)
select *,
       (new_price_rounding - unit_cost) / new_price_rounding as profit_percent 
 from product1 limit 10;

S-068:商品テーブル(product)の各商品について、消費税率10%の税込み金額を求めよ。
    1円未満の端数は切り捨てとし、結果は10件表示すれば良い。
    ただし、単価(unit_price)にはNULLが存在することに注意せよ。

select product_cd,
       unit_price,
       unit_price * 1.1,
       trunc(unit_price * 1.1) as tax_unit_price
 from product  limit 10;

--trunc():切り捨てが出来る。


S-069:レシート明細テーブル(receipt)と商品テーブル(product)を結合し、顧客毎に
     全商品の売上金額合計と、カテゴリ大区分(category_major_cd)が"07"(瓶詰缶詰)
     の売上金額合計を計算の上、両者の比率を求めよ。
    抽出対象はカテゴリ大区分"07"(瓶詰缶詰)の購入実績がある顧客のみとし、
     結果は10件表示させればよい。

with r1 as (select customer_id,
                   cast(sum(amount) as numeric) as sum_amount
             from receipt group by customer_id),
     r_p as (select r.customer_id,
                    cast(sum(r.amount) as numeric) 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 r1.*,
       r_p.sum_07_amount,
       sum_07_amount / sum_amount as sales_rate
 from r1 inner join r_p as r_p on r1.customer_id = r_p.customer_id;


修正が必要
S-070:レシート明細テーブル(receipt)の売上日(sales_ymd)に対し、
     顧客テーブル(customer)の会員申込日(application_date)からの経過日数を計算し、
     顧客ID(customer_id)、売上日、会員申込日とともに表示せよ。
    結果は10件表示させれば良い(なお、sales_ymdは数値、
     application_dateは文字列でデータを保持している点に注意)。

select distinct r.customer_id,
                r.sales_ymd,
		cu.application_date,
		to_date(cast(r.sales_ymd as text), 'YYYYMMDD') - to_date(cu.application_date, 'YYYYMMDD') as elapsed_days
 from receipt as r inner join customer as cu on r.customer_id = cu.customer_id limit 10;

Discussion

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