5.1.1 业务需求:计算7月份某周每个会员订单数,以及每个会员订单占整体比
思路拆解:…… 待补充
思路复盘:…… 待补充
5.1.1 业务需求:计算7月份某周每个会员订单数,以及每个会员订单占整体比
-- 子查询-select
-- 我写的代码
SELECT
dimMemberID,
order_num,
(SELECT sum(order_num) from
(
SELECT
dimMemberID,
COUNT(salesNo) AS order_num
from fct_sales
WHERE dimDateID between '20170703' and '20170709'
and dimMemberID<>0
GROUP BY dimMemberID
) a ) AS order_total,
CONCAT( ROUND( order_num /
(SELECT sum(order_num) from (
SELECT
dimMemberID,
COUNT(salesNo) AS order_num
from fct_sales
WHERE dimDateID between '20170703' and '20170709'
and dimMemberID<>0
GROUP BY dimMemberID
) a ) ,4 )*100 , '%') AS ratio
from
(
SELECT
dimMemberID,
COUNT(salesNo) AS order_num
from fct_sales
where dimDateID between '20170703' and '20170709' -- 7月份某周
and dimMemberID<>0
GROUP BY dimMemberID
) a
-- 人家写的代码
# 计算7月份某周每个会员订单数,以及每个会员订单占整体比
select dimMemberID -- 每个会员
,count(distinct salesNo) as order_number -- 每个会员对应订单数( 要去重吗?)
,(select count(salesNo) from dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID<>0) AS order_total
,count(distinct salesNo)/(select count(salesNo) from dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID<>0) as order_rate -- 常数项/订单占比
from dw.fct_sales -- 订单表
where dimDateID between '20170703' and '20170709' -- 7月份某周
and dimMemberID<>0
group by dimMemberID;
部分输出结果:
– 学习记录,作为复盘使用