用SQL提取经营分析周报指标

业务背景：

以周为粒度总结日常的工作成果，是企业中比较普遍的，一般以周会的形式进行。一般周会都会回顾上周的数据，以及同历史进行对比。根据数据表现进行讨论。

行业分析知识：
① 关注的KPI指标：销售金额、会员数、订单数
② 一般的分析维度：区域、品类、门店、日期

dw

# 3、各品类7月10号到16号本周的经营状况以及环比上周数据
-- 指标：销售金额、订单数、会员数
-- 维度：品类、本周
-- 相关表：fct_sales、fct_sales_item、dim_goods

SELECT T4.categoryID4
            ,T4.categoryName4
            ,T4.amt as amt_week
            ,T5.amt as amt_lastweek
            ,T4.order_num as order_num_week
            ,T5.order_num as order_num_lastweek
            ,T4.member_num as member_num_week
            ,T5.member_num as member_num_lastweek
            ,T4.order_num/ T5.order_num - 1 as order_rate -- 订单环比
FROM
(SELECT categoryID4
            ,categoryName4
            ,sum(t1.AMT) as amt
            ,count(DISTINCT t1.salesNo) as order_num
            ,count(DISTINCT dimMemberID) as member_num
FROM fct_sales t1
INNER JOIN fct_sales_item t2 ON t1.salesID = t2.salesID
INNER JOIN dim_goods t3 ON t2.GoodsID = t3.dimGoodsID
WHERE t1.dimDateID BETWEEN 20170710 AND 20170716
GROUP BY categoryID4
        ,categoryName4) T4
INNER JOIN                         
(SELECT categoryID4
            ,categoryName4
            ,sum(t1.AMT) as amt
            ,count(DISTINCT t1.salesNo) as order_num
            ,count(DISTINCT dimMemberID) as member_num
FROM fct_sales t1
INNER JOIN fct_sales_item t2 ON t1.salesID = t2.salesID
INNER JOIN dim_goods t3 ON t2.GoodsID = t3.dimGoodsID
WHERE t1.dimDateID BETWEEN 20170703 AND 20170709
GROUP BY categoryID4
            ,categoryName4) T5 ON T4.categoryID4 = T5.categoryID4;

输出结果：