MySQL：条件分岐 #16

#(19)条件分岐 case式
-- (1)ランク分け
select
  u.id as user_id,
  count(*) as num, #count(user_id)でも同じ
    case
      when count(*) >= 5 then 'A'
      when count(*) >= 2 then 'B'
      else 'C'
    end as user_rank
from
  users as u
inner join
  orders as o
on u.id=o.user_id
group by user_id
order by user_rank;

-- (2)個数がnullのときゼロに置き換える
select
  p.id,
  p.name,
  -- sum(od.product_qty) as num
  case
    when sum(od.product_qty) is null then 0
    else sum(od.product_qty)
  end as num
from
  products as p
left outer join
  order_details as od
on p.id=od.product_id
group by p.id;

-- (3)ランク分け
select
  p.id as product_id,
  p.name as name,
  sum(od.product_qty) as num,
  case
    when sum(od.product_qty) >= 20 then 'A'
    when sum(od.product_qty) >= 10 then 'B'
    else 'C'
  end as rank
from
  products as p
left outer join
  order_details as od
  on  p.id=od.product_id
group by product_id
order by rank;

#(20)実践問題
-- (1)平均客単価
select
  -- round(sum(amount)/count(id),0) as average
  round(avg(amount),0) as average
from
  orders;
  
-- (2)月別の平均客単価 date_format関数を使用
select
  date_format(order_time,'%Y%m') as order_year_month,
  round(avg(amount),0) as average_costomer_spend
from
  orders
group by date_format(order_time,'%Y%m')
order by order_year_month;

-- (3) 都道府県別の平均客単価
select
  pf.id as prefecture_id,
  pf.name as prefecture_name,
  round(avg(o.amount),0) as average_coutomer_spend
from
  orders as o
left outer join
  users as u
  on o.user_id=u.id
left outer join
  prefectures as pf
  on u.prefecture_id=pf.id
group by u.prefecture_id
order by prefecture_id;

-- (4) 都道府県別および月別の平均客単価
select
  pf.id,
  pf.name,
  date_format(o.order_time,'%Y%m') as order_year_month,
  round(avg(o.amount),0) as average_costomer_spend
from
  orders as o
left outer join
  users as u
  on o.user_id=u.id
left outer join
  prefectures as pf
  on u.prefecture_id=pf.id
group by u.prefecture_id , order_year_month
order by u.prefecture_id , order_year_month ;

100

101

#(19)条件分岐 case式

-- (1)ランク分け

select

u.id as user_id,

count(*) as num, #count(user_id)でも同じ

case

when count(*) >= 5 then 'A'

when count(*) >= 2 then 'B'

else 'C'

end as user_rank

from

users as u

inner join

orders as o

on u.id=o.user_id

group by user_id

order by user_rank;

-- (2)個数がnullのときゼロに置き換える

select

p.id,

p.name,

-- sum(od.product_qty) as num

case

when sum(od.product_qty) is null then 0

else sum(od.product_qty)

end as num

from

products as p

left outer join

order_details as od

on p.id=od.product_id

group by p.id;

-- (3)ランク分け

select

p.id as product_id,

p.name as name,

sum(od.product_qty) as num,

case

when sum(od.product_qty) >= 20 then 'A'

when sum(od.product_qty) >= 10 then 'B'

else 'C'

end as rank

from

products as p

left outer join

order_details as od

on p.id=od.product_id

group by product_id

order by rank;

#(20)実践問題

-- (1)平均客単価

select

-- round(sum(amount)/count(id),0) as average

round(avg(amount),0) as average

from

orders;

-- (2)月別の平均客単価 date_format関数を使用

select

date_format(order_time,'%Y%m') as order_year_month,

round(avg(amount),0) as average_costomer_spend

from

orders

group by date_format(order_time,'%Y%m')

order by order_year_month;

-- (3) 都道府県別の平均客単価

select

pf.id as prefecture_id,

pf.name as prefecture_name,

round(avg(o.amount),0) as average_coutomer_spend

from

orders as o

left outer join

users as u

on o.user_id=u.id

left outer join

prefectures as pf

on u.prefecture_id=pf.id

group by u.prefecture_id

order by prefecture_id;

-- (4) 都道府県別および月別の平均客単価

select

pf.id,

pf.name,

date_format(o.order_time,'%Y%m') as order_year_month,

round(avg(o.amount),0) as average_costomer_spend

from

orders as o

left outer join

users as u

on o.user_id=u.id

left outer join

prefectures as pf

on u.prefecture_id=pf.id

group by u.prefecture_id , order_year_month

order by u.prefecture_id , order_year_month ;

この記事を書いた人
最新の記事

月	火	水	木	金	土	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

Keita N

最新記事 by Keita N (全て見る)