离线数仓：理论篇
数仓基本介绍
数仓四大特征：
1> 面向主题：要分析的需求是什么，对应的主题是什么；
2> 集成性；
3> 非易失性：存储的都是过去既定发生的数据，不会再进行更改；
4> 时变性：随时间推移，数据需要增加最新的相关的数据，同时分析手段也会发生变化；
数据仓库与数据集市区别：
数据仓库：将公司中所有的数据全部都聚合在一起，形成的数据集合，称为数据仓库；
数据集市：对数据仓库中数据，根据要分析的主题，抽取出相关性的数据，得到这个数据集合，称为数据集市；因此可以说，数据集市就是一个小型数据仓库；
维度分析：
1> 什么是维度：指看待问题的角度；
2> 维度分类：
定性维度：字符类型的特征，比如区域维度包括全国各省份，需要计算的各种情况，没有具体范围；（这种维度在SQL上最多的表示通过group by处理）
定量维度：数值类型的特征，如价格区间、销量区间等，需要计算某一个固定范围情况；（这种维度在SQL上最多的表示通过where来处理）
3> 维度的分层和分级：细化维度
例如：
时间维度：一个层次四个级别：年、月、天、小时；
课程维度：（4层）
课程名称：只有一个级别，每门课程的名称；
课程分类：有两个级别，大类和小类；
课程难度：有三个级别，简单、一般、困难；
课程等级：有三个级别，初、中、高；
地区维度：一个层次三个级别，省、市、县
4> 维度的上卷和下钻操作
上卷维度：年、月、天
下钻维度：小时、分
5> 什么是指标：衡量事务的标准或者度量
（常见指标关键词：sum max min avg count topN 比率）
6> 指标的分类：
绝对数值：指的是需要计算出具体结果的指标，具体的大小和多少，如价格、销量、分数等；
相对数值：指的需要计算比率范围的情况，相对数值反映一定的程度，如及格率、购买率、涨幅等。；
数仓建模
数仓建模主要适用于**指导**怎么在数据仓库中进行构建表，以及如何进行数据仓库的分层构建，以及包括表中应该有哪些字段，这都是由一套理论来指导的。
常见的数仓规范主要由两种：三范式建模规范、维度建模规范。
对于三范式建模，要求在构建的时候，尽可能避免数据冗余出现。同时每个表需要有一个主键，三范式建模方案一般应用RDBMS中，而对于维度建模理论更多侧重于数据分析，所以在建模过程中，只要是利于分析的建模都是合格的，在此基础上允许数据可以出现一定的冗余情况。
在维度建模的思想中，主要定义两种类型的表：事实表和维度表
维度建模的中心思想：宽表，空间换时间，减少join场景；
1.维度建模--事实表
1> 事实表：一般指定的分析主题，这个主题所对应的表就是事实表，后者可以说，包含指标字段的表就是事实表；
2> 事实表的分类：
+ 1.事务事实表：表示最开始的事实表，其实就是事务事实表，一条数据就是一个事实；
+ 2.周期快照事实表：以具有规律性的、可预见性的时间间隔来记录事实，时间间隔如每天、每月、每年。
+ 3.累计快照事实表：累计快照事实表代表的是完全覆盖一个事务或产品的生命周期的时间跨度，它通常具有多个时期字段，用来记录整个生命周期的关键时间点。
2.维度建模--维度表
1> 维度表：略
2> 维度表分类：
+ 高基数维度：数量大；
+ 低基数维度：数据量小，变动性小；
建模模型
- 建模模型主要是描述整个数仓在不断发展过程中，产生模型架构
- 建模流程：确认业务流程 -> 确认模型粒度 -> 确认维度 -> 确认事实
1> 星型模型：
+ 特点：只有一个事实表，意味着只有一个分析的主题，在事实表的周围围绕了多张维度表 且维度表和维度表没有任何的关联；
+ 说明：数仓发展初期易产生的模式；
2> 雪花模型：
+ 特点：只有一个事实表，意味着只有一个分析的主题，在事实表的周围围绕了多张维度表，维度表可以接着关联其它的维度表；
+ 说明：数仓发展进入畸形的时候产生的模型，此模型不利于后期的维护，关联的越多，对分析起来的难度越大，在生产中尽量不要出现这种模型架构，即使出现，不要出现维度层次过多的问题；
3> 星座模型：
+ 特点：有多个事实表，意味着有多个分析的主题，在事实表周围围绕了多个维度表，在条件符合情况下，多个事实表可以共享维度表；
+ 说明：数仓发展的中后期易产生的模型；
缓慢渐变维
- 概念：维度中字段值可能会随时间发生变化；
- 注意：如果不维护一个数据的历史变化信息，那么在进行数仓分析的时候，是有可能对未来分析结果产生影响；
如何实现缓慢渐变维呢？
1> SCD1: 对于历史变化的数据直接进行覆盖，此种操纵仅适合于对于错误的数据处理；
2> SCD2(拉链表)： 对原有表增加两个新的字段，一个是起始时间字段start_time, 一个是截止时间字段end_time，当有数据发生变更后，只需要对上一次数据进行标记起止范围，新增一个变更后的数据即可，由此产生一个拉链数据状态；
+ 好处：不会修改原有记录数据，利于维护操作，而且可以对多个历史版本进行数据存储操作；
+ 弊端：造成数据冗余，占用更大的磁盘空间；
3> SCD3: 直接对原有表进行新增列的方案，一旦有数据发生变更，新增一列字段，标记当前最新数据即可，以此来维护历史变化的数据，适合于磁盘空间不足，而且只需要维护少量历史变化的情况；
+ 优点： 尽可能减少了数据冗余的情况；
+ 弊端：不利于维护，仅能维护少量的历史变化版本；
数仓分层架构
-
数仓分层目的：
- 让业务逻辑化繁为简，数据结构清晰；
- 层次分明，便于维护，统一数据口径
-
原有数仓分层：宽泛hive分层架构 三层
- ODS层：源数据层 Operation Data Store
- 作用：对接数据源，保持相同粒度（直接拷贝源数据，加个分区字段记录同步时间）
- DW层： 数据仓库层 Data Warehouse （主要是用于进行数据分析的工作，数据都是来源于ODS层；）
- 明细层DWD（Data Warehouse Detail）
- 表分类（维度、事实）
- 维度退化
- 少量清洗、转换（json拉平、脱敏、字段名修改、单位统一）
- 中间层DWM (Data Warehouse Middle)
- 提前聚合
- 业务层DWS (Data Warehouse Service)
- 形成宽表 (减少join操作，存储空间换取时间效率)
- 明细层DWD（Data Warehouse Detail）
- APP层：数据应用层
- 前端应用直接读取的数据源，或者根据报表、专题分析的需求而计算生成的数据。
- DIM层：维度层 (dimension)
- 高基数维度数据：用户资料表、商品资料表等类似资料表。数据可能达千万级或着上亿级别；
- 低基数维度数据：一般是配置表、字典表，如日期维度表。数据量可能是个位数或者几千、几万；
- ODS层：源数据层 Operation Data Store
-
每层表变化
-
ODS层：加分区字段 pt_d 表示增量同步时间
-
DWD层：(拉链表)加分区字段start_date，和普通字段end_date；
- 拉链表操作：
1.首次导入：INSERT OVERWRITE TABLE dwd.table1 PARTITION(start_date) SELECT id ,is_valid ,'9999-99-99' as end_date ,pt_d as start_date FROM ods.table;-- .1.重建临时表 DROP TABLE IF EXISTS dwd.table_tmp; CREATE TABLE dwd.table_tmp ( id STRING COMMENT '唯一标识' ,is_valid TINYINT COMMENT '数据是否失效，0:失效； 1：开启' ,end_date STRING COMMENT '拉链结束日期' ) COMMENT '表描述' PARTITIONED BY (start_date STRING) ROW FORMAT DELIMITIED FIELDS TERMINATED BY '\001' STORED AS ORC tblproperties( 'orc.compress'='SNAPPY' ); -- 2.合并新旧数据to临时表 INSERT OVERWRITE TABLE dwd.table_tmp PARTITION(start_date) SELECT * FROM ( -- 1.新数据 SELECT id ,is_valid ,'9999-99-99' AS end_date ,pt_d AS start_date FROM ods.table WHERE pt_d='$date' UNION ALL -- 2.更新后的旧数据 SELECT o.id ,o.is_valid -- 注意旧数据有可能有多条，只改end_date为9999-99-99的旧数据 ,if(n.id is not null and o.end_date='9999-99-99', date_add(n.pt_d-1), o.end_date) end_date ,o.start_date FROM dwd.table1 o LEFT JOIN ( SELECT * FROM ods.table WHERE pt_d='$date' ) as n ON o.id=n.id -- 只维护更新最近30天的数据，超过不管 WHERE o.start_date >= date_add(n.pt_d, -30) ) dd ORDER BY start_date, id; -- 3.临时表覆盖拉链表 INSERT OVERWRITE TABLE dwd.table1 PARTITION(start_date) SELECT * FROM dwd.table_tmp;
- 拉链表操作：
-
DWB层：维度退化，生成明细宽表
用分区字段start_date作为dwb层分区字段pt_d,没有end_date- 细节:
1.每个表通过end_date='9999-99-99'和is_valid=1选取有效数据;
2.店铺地址通过地址表(包含各种地址:店铺,商圈,用户)的type字段确定为店铺地址;
3.通过区域表的自关联得到code对应的省市区信息;
- 细节:
-
DWS层：主题统计宽表（日期维度上卷 、维度和指标明确）
- 细节点:
1.在做有层层递进关系的单维度时,如果有父级维度,要带上它进行分组,这样从粗到细上观看,更有利于了解该维度;
- 细节点:
-
样例
- DWS层建表（销售主题日统计宽表）
-- 销售主题日统计宽表
CREATE TABLE yp_dws.dws_sale_daycout
(
-- ======== 维度 (本层不做日期维度) ========
-- ------- 日期维度 (有层层递进关系) -------
-- 1.日期维度 (作为分区字段)
-- dt
-- ------- 地理维度 (有层层递进关系) -------
-- 2.城市维度
city_id STRING COMMENT '城市id'
,city_name STRING COMMENT '城市name'
-- 3.商圈维度
,trade_area_id STRING COMMENT '商圈id'
,trade_area_name STRING COMMENT '商圈名称'
-- 4.店铺维度
,store_id STRING COMMENT '店铺的id'
,store_name STRING COMMENT '店铺名称'
-- ------- 品牌维度 -------
-- 5.品牌维度
,brand_id STRING COMMENT '品牌id'
,brand_name STRING COMMENT '品牌名称'
-- ------- 商品分类维度 (有层层递进关系) -------
-- 6.大类
,max_class_id STRING COMMENT '商品大类id'
,max_class_name STRING COMMENT '大类名称'
-- 7.中类
,mid_class_id STRING COMMENT '中类id'
,mid_class_name STRING COMMENT '中类名称'
-- 8.小类
,min_class_id STRING COMMENT '小类id'
,min_class_name STRING COMMENT '小类名称'
-- 维度分析类型(都是单维度分析)
,group_type STRING
COMMENT '分组类型：store，trade_area，city，brand，min_class，mid_class，max_class，all'
-- ========= 指标 (日统计) ==========
-- ------------ 金额指标 ------------
-- 1.销售收入
,sale_amt STRING COMMENT '销售收入'
-- 2.平台收入
,plat_amt STRING COMMENT '平台收入'
-- 3.配送成交额
,deliver_sale_amt STRING COMMENT '配送成交额'
-- 4.小程序成交额
,miniapp_sale_amt STRING COMMENT '小程序成交额'
-- 5.安卓成交额
,android_sale_amt STRING COMMENT '安卓APP成交额'
-- 6.苹果APP成交额
,ios_sale_amt STRING COMMENT '苹果APP成交额'
-- 7.PC商城成交额
,pcweb_sale_amt STRING COMMENT 'PC商城成交额'
-- ------------ 订单量指标 ------------
-- 8.成交单量
,order_cnt STRING COMMENT '成交单量'
-- 9.参评单量
,eva_order_cnt STRING COMMENT '参评单量comment=>cmt'
-- 10.差评单量
,bad_eva_order_cnt STRING COMMENT '差评单量negtive-comment=>ncmt'
-- 11.配送成交单量
,deliver_order_cnt STRING COMMENT '配送单量'
-- 12.退款单量
,refund_order_cnt STRING COMMENT '退款单量'
-- 13.小程序成交单量
,miniapp_order_cnt STRING COMMENT '小程序成交单量'
-- 14.安卓APP订单量
,android_order_cnt STRING COMMENT '安卓APP订单量'
-- 15.苹果APP订单量
,ios_order_cnt STRING COMMENT '苹果APP订单量'
-- 16.PC商城成交单量
,pcweb_order_cnt STRING COMMENT 'PC商城成交单量'
) COMMENT '销售主题日统计宽表'
partitioned by (dt STRING)
row format delimited fields terminated by '\t'
stored AS orc
tblproperties (
'orc.compress'='SNAPPY'
);
- 1.按pt_d统计指标
-- 1.按dt统计指标
-- INSERT INTO yp_dws.dws_sale_daycout
WITH order_base AS
(
select
-- ========= 维度字段 =========
-- 1.日期 作为分区字段 放最后面
od.dt
-- 2.城市维度
,sd.city_code as city_id
,sd.city_name
-- 3.商圈维度
,sd.trade_area_id
,sd.trade_area_name
-- 4.店铺维度
,sd.store_id
,sd.store_name
-- 5.品牌维度
,gd.brand_id
,gd.brand_name
-- 6.商品分类维度
-- 大类
,gd.max_class_id
,gd.max_class_name
-- 中类
,gd.mid_class_id
,gd.mid_class_name
-- 小类
,gd.min_class_id
,gd.min_class_name
-- ========= 指标字段 =========
-- 订单量指标
,od.order_id -- 需要去重 (因为一种商品对应一条订单数据
-- 金额指标
-- 销售收入
,od.order_amount -- order_amount(订单总金额:购买总金额-优惠金额)
-- 与goods_amount值重复 (用户购买的商品的总金额+运费
-- 平台收入
,od.plat_fee
-- 配送成交额
,od.delivery_fee
-- ========== 判断条件 (即:用于分组)==========
-- 订单来源 (以此的 小程序,安卓,苹果,pc商城的成交额和订单量)
,od.order_from
--
-- 订单是否参评 (多次参评,会产生多次评论id吗?
,od.evaluation_id
-- 差评单量 (参评订单中是否差评)
,od.geval_scores
-- 配送成交量 (是否有配送id)
,od.delievery_id
-- 退款成交量 (是否有退款id)
,od.refund_id
-- 每一种商品购买都会在订单表中形成一条记录
,row_number() over(partition by od.order_id) rn
-- 订单明细宽表 goods_id 商品id store_id 店铺id
FROM yp_dwb.dwd_order_detail od
-- 店铺明细宽表 store_id 店铺id
LEFT JOIN yp_dwb.dwb_shop_detail sd
ON od.store_id=sd.store_id
-- 商品明细宽表 id 商品id
LEFT JOIN yp_dwb.dwb_goods_detail gd
ON od.goods_id=gd.id
)
SELECT
-- ============ 维度 ============
dt
-- ============ 金额 ============
-- 1.销售收入 (coalesce 如果值为null,指定值)
,sum(coalesce(order_amount,0)) AS sale_amt
-- 2.平台收入
,sum(coalesce(plat_fee,0)) AS plat_amt
-- 3.配送成交额
,sum(coalesce(delivery_fee,0)) AS deliver_sale_amt
-- 4.小程序成交额(销售额)
,sum(if(order_from='miniapp',coalesce(order_amount,0),0)) AS miniapp_sale_amt
-- 5.安卓成交额
,sum(if(order_from='android',coalesce(order_amount,0),0)) AS android_sale_amt
-- 6.苹果APP成交额
,sum(if(order_from='ios',coalesce(order_amount,0),0)) AS ios_sale_amt
-- 7.PC商城成交额
,sum(if(order_from='pcweb',coalesce(order_amount,0),0)) AS ios_sale_amt
-- ============ 订单量 ==============
-- 8.成交单量
,count(order_id) AS order_cnt
-- 9.参评单量
,count(if(evaluation_id is not null, order_id, null)) AS eva_order_cnt
-- 10.差评单量
,count(if(evaluation_id is not null AND cast(geval_scores as int)<=6, order_id, null)) AS bad_eva_order_cnt
-- 11.配送成交单量
,count(if(delievery_id is not null, order_id, null)) AS deliver_order_cnt
-- 12.退款单量
,count(if(refund_id is not null, order_id, null)) AS refund_order_cnt
-- 13.小程序成交单量
,count(if(order_from='miniapp', order_id, NULL)) AS miniapp_order_cnt
-- 14.安卓APP订单量
,count(if(order_from='android', order_id, NULL)) AS android_order_cnt
-- 15.苹果APP订单量
,count(if(order_from='ios', order_id, NULL)) AS ios_order_cnt
-- 16.PC商城成交单量
,count(if(order_from='pcweb', order_id, NULL)) AS pcweb_order_cnt
FROM order_base ob
WHERE ob.rn=1 -- 剔除重复订单id(一个订单可能有多个商品,每个商品一条订单数据)
GROUP BY dt
;
- 2.按 城市,商圈,店铺 统计指标
-- 2.按 城市,商圈,店铺 统计指标
-- INSERT INTO yp_dws.dws_sale_daycout
WITH order_base AS
(
select
-- ========= 维度字段 =========
-- 1.日期 作为分区字段 放最后面
od.dt
-- 2.城市维度
,sd.city_code as city_id
,sd.city_name
-- 3.商圈维度
,sd.trade_area_id
,sd.trade_area_name
-- 4.店铺维度
,sd.store_id
,sd.store_name
-- 5.品牌维度
,gd.brand_id
,gd.brand_name
-- 6.商品分类维度
-- 大类
,gd.max_class_id
,gd.max_class_name
-- 中类
,gd.mid_class_id
,gd.mid_class_name
-- 小类
,gd.min_class_id
,gd.min_class_name
-- ========= 指标字段 =========
-- 订单量指标
,od.order_id -- 需要去重 (因为一种商品对应一条订单数据
-- 金额指标
-- 销售收入
,od.order_amount -- order_amount(订单总金额:购买总金额-优惠金额)
-- 与goods_amount值重复 (用户购买的商品的总金额+运费
-- 平台收入
,od.plat_fee
-- 配送成交额
,od.delivery_fee
-- ========== 判断条件 (即:用于分组)==========
-- 订单来源 (以此的 小程序,安卓,苹果,pc商城的成交额和订单量)
,od.order_from
--
-- 订单是否参评 (多次参评,会产生多次评论id吗?
,od.evaluation_id
-- 差评单量 (参评订单中是否差评)
,od.geval_scores
-- 配送成交量 (是否有配送id)
,od.delievery_id
-- 退款成交量 (是否有退款id)
,od.refund_id
-- 每一种商品购买都会在订单表中形成一条记录
,row_number() over(partition by od.order_id) rn
-- 订单明细宽表 goods_id 商品id store_id 店铺id
FROM yp_dwb.dwd_order_detail od
-- 店铺明细宽表 store_id 店铺id
LEFT JOIN yp_dwb.dwb_shop_detail sd
ON od.store_id=sd.store_id
-- 商品明细宽表 id 商品id
LEFT JOIN yp_dwb.dwb_goods_detail gd
ON od.goods_id=gd.id
)
SELECT
-- ============ 维度 ============
-- 1.固有维度: 日期
dt
-- 2.城市维度
,city_id
,city_name
-- 3.商圈维度
,trade_area_id
,trade_area_name
-- 4.店铺维度
,store_id
,store_name
,case
-- 先遇到符合条件,就匹配,由前往后执行.
when grouping(store_id)=0
then 'store'
when grouping(trade_area_id)=0
then 'trade_area'
when grouping(city_id)=0
then 'city_id'
when grouping(dt)=0
then 'all'
end AS group_type
-- ============ 金额 ============
-- 1.销售收入 (coalesce 如果值为null,指定值)
,sum(coalesce(order_amount,0)) AS sale_amt
-- 2.平台收入
,sum(coalesce(plat_fee,0)) AS plat_amt
-- 3.配送成交额
,sum(coalesce(delivery_fee,0)) AS deliver_sale_amt
-- 4.小程序成交额(销售额)
,sum(if(order_from='miniapp',coalesce(order_amount,0),0)) AS miniapp_sale_amt
-- 5.安卓成交额
,sum(if(order_from='android',coalesce(order_amount,0),0)) AS android_sale_amt
-- 6.苹果APP成交额
,sum(if(order_from='ios',coalesce(order_amount,0),0)) AS ios_sale_amt
-- 7.PC商城成交额
,sum(if(order_from='pcweb',coalesce(order_amount,0),0)) AS ios_sale_amt
-- ============ 订单量 ==============
-- 8.成交单量
,count(order_id) AS order_cnt
-- 9.参评单量
,count(if(evaluation_id is not null, order_id, null)) AS eva_order_cnt
-- 10.差评单量
,count(if(evaluation_id is not null AND cast(geval_scores as int)<=6, order_id, null)) AS bad_eva_order_cnt
-- 11.配送成交单量
,count(if(delievery_id is not null, order_id, null)) AS deliver_order_cnt
-- 12.退款单量
,count(if(refund_id is not null, order_id, null)) AS refund_order_cnt
-- 13.小程序成交单量
,count(if(order_from='miniapp', order_id, NULL)) AS miniapp_order_cnt
-- 14.安卓APP订单量
,count(if(order_from='android', order_id, NULL)) AS android_order_cnt
-- 15.苹果APP订单量
,count(if(order_from='ios', order_id, NULL)) AS ios_order_cnt
-- 16.PC商城成交单量
,count(if(order_from='pcweb', order_id, NULL)) AS pcweb_order_cnt
FROM order_base ob
WHERE ob.rn=1 -- 剔除重复订单id(一个订单可能有多个商品,每个商品一条订单数据)
GROUP BY
grouping sets(
dt
,(dt, city_id, city_name)
,(dt, trade_area_id, trade_area_name, city_id, city_name)
,(dt, store_id, store_name, trade_area_id, trade_area_name, city_id, city_name)
);
-
- 按照 ALL,品牌, 城市, 商圈, 店铺 ---统计销售额和订单量(单维度)
-- 按照品牌-统计-销售额
--insert into yp_dws.dws_sale_daycount
with order_base as
(
select
-- 维度
SUBSTRING(o.create_date,1,10) as dt,
s.city_id,
s.city_name,
s.trade_area_id,
s.trade_area_name,
s.id as store_id,
s.store_name,
g.brand_id,
g.brand_name,
-- 订单量指标
o.order_id,
-- 金额指标
o.order_amount,
o.total_price as goods_price,
o.plat_fee,
o.delivery_fee,
-- 判断条件
o.order_from,
o.evaluation_id,
o.geval_scores,
o.delievery_id,
o.refund_id,
row_number() over(partition by o.order_id) rn, -- 城市 商圈 店铺 订单id 品牌id 商品id (多个同种商品在同一订单下是一条数据)
row_number() over(partition by s.city_id, o.order_id) city_rn, -- 没必要 使用rn=1去重就行
row_number() over(partition by s.trade_area_id, o.order_id) trade_area_rn, -- 没必要 使用rn=1去重就行
row_number() over(partition by o.store_id, o.order_id) store_rn, -- 没必要 使用rn=1去重就行,一个订单只属于一家店, 不同店铺,订单不可能相同
row_number() over(partition by g.brand_id, o.order_id) brand_rn, -- 有必要 使用brand_rn去重 计算品牌订单量使用,一个品牌多种商品
row_number() over(partition by g.brand_id, o.order_id, o.goods_id) brand_goods_rn -- 没必要,多个相同商品在一起 不用去重
from yp_dwb.dwb_order_detail o
left join yp_dwb.dwb_shop_detail s on o.store_id=s.id
left join yp_dwb.dwb_goods_detail g on o.goods_id=g.id
where o.is_valid=1
and o.order_id='dd190404391438bed2'
)
select
-- =============维度=============
dt
,city_id
,city_name
,trade_area_id
,trade_area_name
,store_id
,store_name
,brand_id
,brand_name
,case
when grouping(brand_id)=0
then 'brand'
when grouping(store_id)=0
then 'store'
when grouping(trade_area_id)=0
then 'trade_area'
when grouping(city_id)=0
then 'city'
when grouping(dt)=0
then 'all'
end
as group_type
-- =============金额=============
-- 销售收入
,case
when grouping(brand_id)=0
then sum(if(brand_goods_rn=1, coalesce(goods_price, 0), 0)) -- 没有重复值,不需要去重
when grouping(store_id)=0
then sum(if(store_rn=1, coalesce(order_amount, 0), 0)) -- 因为订单id有重复,使用rn=1去重就行
when grouping(trade_area_id)=0
then sum(if(trade_area_rn=1, coalesce(order_amount, 0), 0)) -- 因为订单id有重复,使用rn=1去重就行
when grouping(city_id)=0
then sum(if(city_rn=1, coalesce(order_amount, 0), 0)) -- 因为订单id有重复,使用rn=1去重就行
when grouping(dt)=0
then sum(if(rn=1, coalesce(order_amount, 0), 0)) -- 因为订单id有重复,使用rn=1去重就行
end
as sale_amt
-- 平台收入
,case
when grouping(brand_id)=0
then null -- 因为只有每个订单的平台收入,没有细化到每种商品的平台收入,所以以品牌为维度时没法求, 为null
when grouping(store_id)=0
then sum(if(rn=1, coalesce(plat_fee,0), 0))
when grouping(trade_area_id)=0
then sum(if(rn=1, coalesce(plat_fee,0), 0))
when grouping(city_id)=0
then sum(if(rn=1, coalesce(plat_fee,0), 0))
when grouping(dt)=0
then sum(if(rn=1, coalesce(plat_fee,0), 0))
end
as plat_amt
-- 配送成交额 -- 配送费是根据订单算的,没有细化到每种商品的配送费收入,所以以品牌为维度时没法求, 为null
,case
when grouping(brand_id)=0
then null
when grouping(store_id)=0
then sum(if(rn=1, coalesce(delivery_fee,0), 0))
when grouping(trade_area_id)=0
then sum(if(rn=1, coalesce(delivery_fee,0), 0))
when grouping(city_id)=0
then sum(if(rn=1, coalesce(delivery_fee,0), 0))
when grouping(dt)=0
then sum(if(rn=1, coalesce(delivery_fee,0), 0))
end
as deliver_sale_amt
-- 小程序成交额 -- 各种数据来源的销售额
,case
when grouping(brand_id)=0
then sum(if(brand_goods_rn=1 and order_from='miniapp', coalesce(goods_price, 0), 0)) -- 没有重复值,不需要使用brand_goods_rn=1去重
when grouping(store_id)=0
then sum(if(rn=1 and order_from='miniapp', coalesce(order_amount, 0), 0))
when grouping(trade_area_id)=0
then sum(if(rn=1 and order_from='miniapp', coalesce(order_amount, 0), 0))
when grouping(city_id)=0
then sum(if(rn=1 and order_from='miniapp', coalesce(order_amount, 0), 0))
when grouping(dt)=0
then sum(if(rn=1 and order_from='miniapp', coalesce(order_amount, 0), 0))
end
as mini_app_sale_amt
-- 安卓APP成交额
,case
when grouping(brand_id)=0
then sum(if(brand_goods_rn=1 and order_from='android', coalesce(goods_price, 0), 0)) -- 没有重复值,不需要使用brand_goods_rn=1去重
when grouping(store_id)=0
then sum(if(rn=1 and order_from='android', coalesce(order_amount, 0), 0))
when grouping(trade_area_id)=0
then sum(if(rn=1 and order_from='android', coalesce(order_amount, 0), 0))
when grouping(city_id)=0
then sum(if(rn=1 and order_from='android', coalesce(order_amount, 0), 0))
when grouping(dt)=0
then sum(if(rn=1 and order_from='android', coalesce(order_amount, 0), 0))
end
as android_sale_amt
-- 苹果APP成交额
,case
when grouping(brand_id)=0
then sum(if(brand_goods_rn=1 and order_from='ios', coalesce(goods_price, 0), 0)) -- 没有重复值,不需要使用brand_goods_rn=1去重
when grouping(store_id)=0
then sum(if(rn=1 and order_from='ios', coalesce(order_amount, 0), 0))
when grouping(trade_area_id)=0
then sum(if(rn=1 and order_from='ios', coalesce(order_amount, 0), 0))
when grouping(city_id)=0
then sum(if(rn=1 and order_from='ios', coalesce(order_amount, 0), 0))
when grouping(dt)=0
then sum(if(rn=1 and order_from='ios', coalesce(order_amount, 0), 0))
end
as ios_sale_amt
-- PC商城成交额
,case
when grouping(brand_id)=0
then sum(if(brand_goods_rn=1 and order_from='pcweb', coalesce(goods_price, 0), 0)) -- 没有重复值,不需要使用brand_goods_rn=1去重
when grouping(store_id)=0
then sum(if(rn=1 and order_from='pcweb', coalesce(order_amount, 0), 0))
when grouping(trade_area_id)=0
then sum(if(rn=1 and order_from='pcweb', coalesce(order_amount, 0), 0))
when grouping(city_id)=0
then sum(if(rn=1 and order_from='pcweb', coalesce(order_amount, 0), 0))
when grouping(dt)=0
then sum(if(rn=1 and order_from='pcweb', coalesce(order_amount, 0), 0))
end
as pcweb_sale_amt
-- =============订单量=============
-- 单量
,case
when grouping(brand_id)=0 -- grouping 是针对某个组进行判断的,不是对组内的每条数据进行判断
then count(if(brand_rn=1, order_id, null)) -- 一个订单中,可能有同一品牌的不同商品 ,所以需要标号去重
when grouping(store_id)=0
then count(if(store_rn=1, order_id, null)) -- 使用rn=1标号去重就行
when grouping(trade_area_id)=0
then count(if(trade_area_rn=1, order_id, null)) -- 使用rn=1标号去重就行
when grouping(city_id)=0
then count(if(city_rn=1, order_id, null)) -- 使用rn=1标号去重就行
when grouping(dt)=0
then count(if(rn=1, order_id, null)) -- 使用rn=1标号去重就行
end
as order_cnt
-- 参评单量 -- 各种评价的单量
,case
when grouping(brand_id)=0
then count(if(brand_rn=1 and evaluation_id is not null, order_id, null)) -- 需要去重
when grouping(store_id)=0
then count(if(store_rn=1 and evaluation_id is not null, order_id, null))
when grouping(trade_area_id)=0
then count(if(trade_area_rn=1 and evaluation_id is not null, order_id, null))
when grouping(city_id)=0
then count(if(city_rn=1 and evaluation_id is not null, order_id, null))
when grouping(dt)=0
then count(if(rn=1 and evaluation_id is not null, order_id, null))
end
as eva_order_cnt
-- 差评单量 -- 各种评价的单量
,case
when grouping(brand_id)=0
then count(if(brand_rn=1 and evaluation_id is not null and geval_scores<=6, order_id, null)) -- 需要去重
when grouping(store_id)=0
then count(if(rn=1 and evaluation_id is not null and geval_scores<=6, order_id, null))
when grouping(trade_area_id)=0
then count(if(rn=1 and evaluation_id is not null and geval_scores<=6, order_id, null))
when grouping(city_id)=0
then count(if(rn=1 and evaluation_id is not null and geval_scores<=6, order_id, null))
when grouping(dt)=0
then count(if(rn=1 and evaluation_id is not null and geval_scores<=6, order_id, null))
end
as bad_eva_order_cnt
-- 配送单量
,case
when grouping(brand_id)=0
then count(if(brand_rn=1 and delievery_id is not null, order_id, null)) -- 需要去重
when grouping(store_id)=0
then count(if(rn=1 and delievery_id is not null, order_id, null))
when grouping(trade_area_id)=0
then count(if(rn=1 and delievery_id is not null, order_id, null))
when grouping(city_id)=0
then count(if(rn=1 and delievery_id is not null, order_id, null))
when grouping(dt)=0
then count(if(rn=1 and delievery_id is not null, order_id, null))
end
as deliver_order_cnt
-- 退款单量
,case
when grouping(brand_id)=0
then count(if(brand_rn=1 and refund_id is not null, order_id, null)) -- 需要去重
when grouping(store_id)=0
then count(if(rn=1 and refund_id is not null, order_id, null))
when grouping(trade_area_id)=0
then count(if(rn=1 and refund_id is not null, order_id, null))
when grouping(city_id)=0
then count(if(rn=1 and refund_id is not null, order_id, null))
when grouping(dt)=0
then count(if(rn=1 and refund_id is not null, order_id, null))
end
as refund_order_cnt
-- 小程序单量 -- 各种来源的单量
,case
when grouping(brand_id)=0
then count(if(brand_rn=1 and order_from='miniapp', order_id, null)) -- 需要去重
when grouping(store_id)=0
then count(if(rn=1 and order_from='miniapp', order_id, null))
when grouping(trade_area_id)=0
then count(if(rn=1 and order_from='miniapp', order_id, null))
when grouping(city_id)=0
then count(if(rn=1 and order_from='miniapp', order_id, null))
when grouping(dt)=0
then count(if(rn=1 and order_from='miniapp', order_id, null))
end
as miniapp_order_cnt
-- 安卓APP订单量 -- 各种来源的单量
,case
when grouping(brand_id)=0
then count(if(brand_rn=1 and order_from='android', order_id, null))
when grouping(store_id)=0
then count(if(rn=1 and order_from='android', order_id, null))
when grouping(trade_area_id)=0
then count(if(rn=1 and order_from='android', order_id, null))
when grouping(city_id)=0
then count(if(rn=1 and order_from='android', order_id, null))
when grouping(dt)=0
then count(if(rn=1 and order_from='android', order_id, null))
end
as android_order_cnt
-- 苹果APP订单量 -- 各种来源的单量
,case
when grouping(brand_id)=0
then count(if(brand_rn=1 and order_from='ios', order_id, null))
when grouping(store_id)=0
then count(if(rn=1 and order_from='ios', order_id, null))
when grouping(trade_area_id)=0
then count(if(rn=1 and order_from='ios', order_id, null))
when grouping(city_id)=0
then count(if(rn=1 and order_from='ios', order_id, null))
when grouping(dt)=0
then count(if(rn=1 and order_from='ios', order_id, null))
end
as ios_order_cnt
-- PC商城单量 -- 各种来源的单量
,case
when grouping(brand_id)=0
then count(if(brand_rn=1 and order_from='pcweb', order_id, null))
when grouping(store_id)=0
then count(if(rn=1 and order_from='pcweb', order_id, null))
when grouping(trade_area_id)=0
then count(if(rn=1 and order_from='pcweb', order_id, null))
when grouping(city_id)=0
then count(if(rn=1 and order_from='pcweb', order_id, null))
when grouping(dt)=0
then count(if(rn=1 and order_from='pcweb', order_id, null))
end
as pcweb_order_cnt
from order_base b
#where b.rn=1 -- 因为有细化到商品的,不能订单id去重
group by
grouping sets(
dt,
(dt, city_id, city_name),
(dt, trade_area_id, trade_area_name, city_id, city_name),
(dt, store_id, store_name, trade_area_id, trade_area_name, city_id, city_name),
(dt, brand_id, brand_name)
)
--order by dt desc, sum(coalesce(order_amount, 0)) desc
;
- 综合
with order_base as
(
select
-- 维度
SUBSTRING(o.create_date,1,10) as dt,
s.city_id,
s.city_name,
s.trade_area_id,
s.trade_area_name,
s.id as store_id,
s.store_name,
g.brand_id,
g.brand_name,
g.max_class_id,
g.max_class_name,
g.mid_class_id,
g.mid_class_name,
g.min_class_id,
g.min_class_name,
-- 订单量指标
o.order_id,
-- 金额指标
o.order_amount,
o.total_price as goods_price,
o.plat_fee,
o.delivery_fee,
-- 判断条件
o.order_from,
o.evaluation_id,
o.geval_scores,
o.delievery_id,
o.refund_id,
row_number() over(partition by o.order_id) rn,
row_number() over(partition by s.city_id, o.order_id) city_rn,
row_number() over(partition by s.trade_area_id, o.order_id) trade_area_rn,
row_number() over(partition by o.store_id, o.order_id) store_rn,
row_number() over(partition by g.brand_id, o.order_id) brand_rn,
row_number() over(partition by g.max_class_id, o.order_id) max_class_rn,
row_number() over(partition by g.max_class_id, g.mid_class_id, o.order_id) mid_class_rn,
row_number() over(partition by g.max_class_id, g.mid_class_id, g.min_class_id, o.order_id) min_class_rn,
row_number() over(partition by g.brand_id, o.order_id, o.goods_id) brand_goods_rn,
row_number() over(partition by g.max_class_id, o.order_id, o.goods_id) max_class_goods_rn,
row_number() over(partition by g.max_class_id, g.mid_class_id, o.order_id, o.goods_id) mid_class_goods_rn,
row_number() over(partition by g.max_class_id, g.mid_class_id, g.min_class_id, o.order_id, o.goods_id) min_class_goods_rn
from yp_dwb.dwb_order_detail o
left join yp_dwb.dwb_shop_detail s on o.store_id=s.id
left join yp_dwb.dwb_goods_detail g on o.goods_id=g.id
where o.is_valid=1
and o.order_id='dd190404391438bed2'
)
select
-- =============维度=============
dt
,city_id
,city_name
,trade_area_id
,trade_area_name
,store_id
,store_name
,brand_id
,brand_name,
max_class_id,
max_class_name,
mid_class_id,
mid_class_name,
min_class_id,
min_class_name
,case
when grouping(brand_id)=0
then 'brand'
when grouping(min_class_id)=0
then 'min_class'
when grouping(mid_class_id)=0
then 'mid_class'
when grouping(max_class_id)=0
then 'max_class'
when grouping(store_id)=0
then 'store'
when grouping(trade_area_id)=0
then 'trade_area'
when grouping(city_id)=0
then 'city'
when grouping(dt)=0
then 'all'
end
as group_type
-- =============金额=============
-- 销售收入
,case
when grouping(brand_id)=0
then sum(if(brand_goods_rn=1, coalesce(goods_price, 0), 0))
when grouping(min_class_id)=0
then sum(if(min_class_goods_rn=1, coalesce(goods_price, 0), 0))
when grouping(mid_class_id)=0
then sum(if(mid_class_goods_rn=1, coalesce(goods_price, 0), 0))
when grouping(max_class_id)=0
then sum(if(max_class_goods_rn=1, coalesce(goods_price, 0), 0))
when grouping(store_id)=0
then sum(if(store_rn=1, coalesce(order_amount, 0), 0))
when grouping(trade_area_id)=0
then sum(if(trade_area_rn=1, coalesce(order_amount, 0), 0))
when grouping(city_id)=0
then sum(if(city_rn=1, coalesce(order_amount, 0), 0))
when grouping(dt)=0
then sum(if(rn=1, coalesce(order_amount, 0), 0))
end
as sale_amt,
-- 平台分润金额
case
-- 店铺
when grouping(store_id)=0
then sum(case when rn=1 and store_id is not null then plat_fee else 0 end)
-- 商圈
when grouping(trade_area_id)=0
then sum(case when rn=1 and trade_area_id is not null then plat_fee else 0 end)
-- 城市
when grouping(city_id)=0
then sum(case when rn=1 and city_id is not null then plat_fee else 0 end)
-- 品牌
when grouping(brand_id)=0
then null
-- 小类
when grouping(min_class_id)=0
then null
-- 中类
when grouping(mid_class_id)=0
then null
-- 大类
when grouping(max_class_id)=0
then null
-- 全部
else sum(case when rn=1 then plat_fee else 0 end)
end
as plat_amt,
-- 配送额
case
-- 店铺
when grouping(store_id)=0
then sum(case when rn=1 and store_id is not null then delivery_fee else 0 end)
-- 商圈
when grouping(trade_area_id)=0
then sum(case when rn=1 and trade_area_id is not null then delivery_fee else 0 end)
-- 城市
when grouping(city_id)=0
then sum(case when rn=1 and city_id is not null then delivery_fee else 0 end)
-- 品牌
when grouping(brand_id)=0
then null
-- 小类
when grouping(min_class_id)=0
then null
-- 中类
when grouping(mid_class_id)=0
then null
-- 大类
when grouping(max_class_id)=0
then null
-- 全部
else sum(case when rn=1 then delivery_fee else 0 end)
end
as deliver_sale_amt,
-- 小程序销售额
case
-- 店铺
when grouping(store_id)=0
then sum(case when o.order_from='miniapp' and rn=1 and store_id is not null then order_amount else 0 end)
-- 商圈
when grouping(trade_area_id)=0
then sum(case when o.order_from='miniapp' and rn=1 and trade_area_id is not null then order_amount else 0 end)
-- 城市
when grouping(city_id)=0
then sum(case when o.order_from='miniapp' and rn=1 and city_id is not null then order_amount else 0 end)
-- 品牌
when grouping(brand_id)=0
then sum(case when o.order_from='miniapp' and brand_goods_rn=1 and brand_id is not null then goods_price else 0 end)
-- 小类
when grouping(min_class_id)=0
then sum(case when o.order_from='miniapp' and min_class_goods_rn=1 and min_class_id is not null then goods_price else 0 end)
-- 中类
when grouping(mid_class_id)=0
then sum(case when o.order_from='miniapp' and mid_class_goods_rn=1 and mid_class_id is not null then goods_price else 0 end)
-- 大类
when grouping(max_class_id)=0
then sum(case when o.order_from='miniapp' and max_class_goods_rn=1 and max_class_id is not null then goods_price else 0 end)
-- 全部
else sum(case when o.order_from='miniapp' and rn=1 then order_amount else 0 end)
end
as mini_app_sale_amt,
-- 安卓销售额
case
-- 店铺
when grouping(store_id)=0
then sum(case when o.order_from='android' and rn=1 and store_id is not null then order_amount else 0 end)
-- 商圈
when grouping(trade_area_id)=0
then sum(case when o.order_from='android' and rn=1 and trade_area_id is not null then order_amount else 0 end)
-- 城市
when grouping(city_id)=0
then sum(case when o.order_from='android' and rn=1 and city_id is not null then order_amount else 0 end)
-- 品牌
when grouping(brand_id)=0
then sum(case when o.order_from='android' and brand_goods_rn=1 and brand_id is not null then goods_price else 0 end)
-- 小类
when grouping(min_class_id)=0
then sum(case when o.order_from='android' and min_class_goods_rn=1 and min_class_id is not null then goods_price else 0 end)
-- 中类
when grouping(mid_class_id)=0
then sum(case when o.order_from='android' and mid_class_goods_rn=1 and mid_class_id is not null then goods_price else 0 end)
-- 大类
when grouping(max_class_id)=0
then sum(case when o.order_from='android' and max_class_goods_rn=1 and max_class_id is not null then goods_price else 0 end)
-- 全部
else sum(case when o.order_from='android' and rn=1 then order_amount else 0 end)
end
as android_sale_amt,
-- ios销售额
case
-- 店铺
when grouping(store_id)=0
then sum(case when o.order_from='ios' and rn=1 and store_id is not null then order_amount else 0 end)
-- 商圈
when grouping(trade_area_id)=0
then sum(case when o.order_from='ios' and rn=1 and trade_area_id is not null then order_amount else 0 end)
-- 城市
when grouping(city_id)=0
then sum(case when o.order_from='ios' and rn=1 and city_id is not null then order_amount else 0 end)
-- 品牌
when grouping(brand_id)=0
then sum(case when o.order_from='ios' and brand_goods_rn=1 and brand_id is not null then goods_price else 0 end)
-- 小类
when grouping(min_class_id)=0
then sum(case when o.order_from='ios' and min_class_goods_rn=1 and min_class_id is not null then goods_price else 0 end)
-- 中类
when grouping(mid_class_id)=0
then sum(case when o.order_from='ios' and mid_class_goods_rn=1 and mid_class_id is not null then goods_price else 0 end)
-- 大类
when grouping(max_class_id)=0
then sum(case when o.order_from='ios' and max_class_goods_rn=1 and max_class_id is not null then goods_price else 0 end)
-- 全部
else sum(case when o.order_from='ios' and rn=1 then order_amount else 0 end)
end
as ios_sale_amt,
-- pc销售额
case
-- 店铺
when grouping(store_id)=0
then sum(case when o.order_from='pcweb' and rn=1 and store_id is not null then order_amount else 0 end)
-- 商圈
when grouping(trade_area_id)=0
then sum(case when o.order_from='pcweb' and rn=1 and trade_area_id is not null then order_amount else 0 end)
-- 城市
when grouping(city_id)=0
then sum(case when o.order_from='pcweb' and rn=1 and city_id is not null then order_amount else 0 end)
-- 品牌
when grouping(brand_id)=0
then sum(case when o.order_from='pcweb' and brand_goods_rn=1 and brand_id is not null then goods_price else 0 end)
-- 小类
when grouping(min_class_id)=0
then sum(case when o.order_from='pcweb' and min_class_goods_rn=1 and min_class_id is not null then goods_price else 0 end)
-- 中类
when grouping(mid_class_id)=0
then sum(case when o.order_from='pcweb' and mid_class_goods_rn=1 and mid_class_id is not null then goods_price else 0 end)
-- 大类
when grouping(max_class_id)=0
then sum(case when o.order_from='pcweb' and max_class_goods_rn=1 and max_class_id is not null then goods_price else 0 end)
-- 全部
else sum(case when o.order_from='pcweb' and rn=1 then order_amount else 0 end)
end
as pcweb_sale_amt
-- =============订单量=============
-- 单量
,case
when grouping(brand_id)=0
then count(if(brand_rn=1, order_id, null))
when grouping(min_class_id)=0
then count(if(min_class_rn=1, order_id, null))
when grouping(mid_class_id)=0
then count(if(mid_class_rn=1, order_id, null))
when grouping(max_class_id)=0
then count(if(max_class_rn=1, order_id, null))
when grouping(store_id)=0
then count(if(store_rn=1, order_id, null))
when grouping(trade_area_id)=0
then count(if(trade_area_rn=1, order_id, null))
when grouping(city_id)=0
then count(if(city_rn=1, order_id, null))
when grouping(dt)=0
then count(if(rn=1, order_id, null))
end
as order_cnt
-- 参评单量
,case
when grouping(brand_id)=0
then count(if(brand_rn=1 and evaluation_id is not null, order_id, null))
when grouping(min_class_id)=0
then count(if(min_class_rn=1 and evaluation_id is not null, order_id, null))
when grouping(mid_class_id)=0
then count(if(mid_class_rn=1 and evaluation_id is not null, order_id, null))
when grouping(max_class_id)=0
then count(if(max_class_rn=1 and evaluation_id is not null, order_id, null))
when grouping(store_id)=0
then count(if(store_rn=1 and evaluation_id is not null, order_id, null))
when grouping(trade_area_id)=0
then count(if(trade_area_rn=1 and evaluation_id is not null, order_id, null))
when grouping(city_id)=0
then count(if(city_rn=1 and evaluation_id is not null, order_id, null))
when grouping(dt)=0
then count(if(rn=1 and evaluation_id is not null, order_id, null))
end
as eva_order_cnt,
-- 差评单量
case
-- 店铺
when grouping(store_id)=0
then count(case when evaluation_id is not null and coalesce(geval_scores, 0)<=6 and rn=1 then o.order_id else null end)
-- 商圈
when grouping(trade_area_id)=0
then count(case when evaluation_id is not null and coalesce(geval_scores, 0)<=6 and rn=1 then o.order_id else null end)
-- 城市
when grouping(city_id)=0
then count(case when evaluation_id is not null and coalesce(geval_scores, 0)<=6 and rn=1 then o.order_id else null end)
-- 品牌
when grouping(brand_id)=0
then count(case when evaluation_id is not null and coalesce(geval_scores, 0)<=6 and brand_rn=1 then o.order_id else null end)
-- 小类
when grouping(min_class_id)=0
then count(case when evaluation_id is not null and coalesce(geval_scores, 0)<=6 and min_class_rn=1 then o.order_id else null end)
-- 中类
when grouping(mid_class_id)=0
then count(case when evaluation_id is not null and coalesce(geval_scores, 0)<=6 and mid_class_rn=1 then o.order_id else null end)
-- 大类
when grouping(max_class_id)=0
then count(case when evaluation_id is not null and coalesce(geval_scores, 0)<=6 and max_class_rn=1 then o.order_id else null end)
-- 全部
else count(case when evaluation_id is not null and coalesce(geval_scores, 0)<=6 and rn=1 then o.order_id else null end)
end
as bad_eva_order_cnt,
-- 配送单量
case
-- 店铺
when grouping(store_id)=0
then count(case when delievery_id is not null and rn=1 then o.order_id else null end)
-- 商圈
when grouping(trade_area_id)=0
then count(case when delievery_id is not null and rn=1 then o.order_id else null end)
-- 城市
when grouping(city_id)=0
then count(case when delievery_id is not null and rn=1 then o.order_id else null end)
-- 品牌
when grouping(brand_id)=0
then count(case when delievery_id is not null and brand_rn=1 then o.order_id else null end)
-- 小类
when grouping(min_class_id)=0
then count(case when delievery_id is not null and min_class_rn=1 then o.order_id else null end)
-- 中类
when grouping(mid_class_id)=0
then count(case when delievery_id is not null and mid_class_rn=1 then o.order_id else null end)
-- 大类
when grouping(max_class_id)=0
then count(case when delievery_id is not null and max_class_rn=1 then o.order_id else null end)
-- 全部
else count(case when delievery_id is not null and rn=1 then o.order_id else null end)
end
as deliver_order_cnt,
--退款单量
case
-- 店铺
when grouping(store_id)=0
then count(case when refund_id is not null and rn=1 then o.order_id else null end)
-- 商圈
when grouping(trade_area_id)=0
then count(case when refund_id is not null and rn=1 then o.order_id else null end)
-- 城市
when grouping(city_id)=0
then count(case when refund_id is not null and rn=1 then o.order_id else null end)
-- 品牌
when grouping(brand_id)=0
then count(case when refund_id is not null and brand_rn=1 then o.order_id else null end)
-- 小类
when grouping(min_class_id)=0
then count(case when refund_id is not null and min_class_rn=1 then o.order_id else null end)
-- 中类
when grouping(mid_class_id)=0
then count(case when refund_id is not null and mid_class_rn=1 then o.order_id else null end)
-- 大类
when grouping(max_class_id)=0
then count(case when refund_id is not null and max_class_rn=1 then o.order_id else null end)
-- 全部
else count(case when refund_id is not null and rn=1 then o.order_id else null end)
end
as refund_order_cnt,
--小程序订单量
case
-- 店铺
when grouping(store_id)=0
then count(case when o.order_from='miniapp' and rn=1 then o.order_id else null end)
-- 商圈
when grouping(trade_area_id)=0
then count(case when o.order_from='miniapp' and rn=1 then o.order_id else null end)
-- 城市
when grouping(city_id)=0
then count(case when o.order_from='miniapp' and rn=1 then o.order_id else null end)
-- 品牌
when grouping(brand_id)=0
then count(case when o.order_from='miniapp' and brand_rn=1 then o.order_id else null end)
-- 小类
when grouping(min_class_id)=0
then count(case when o.order_from='miniapp' and min_class_rn=1 then o.order_id else null end)
-- 中类
when grouping(mid_class_id)=0
then count(case when o.order_from='miniapp' and mid_class_rn=1 then o.order_id else null end)
-- 大类
when grouping(max_class_id)=0
then count(case when o.order_from='miniapp' and max_class_rn=1 then o.order_id else null end)
-- 全部
else count(case when o.order_from='miniapp' and rn=1 then o.order_id else null end)
end
as miniapp_order_cnt,
--android订单量
case
-- 店铺
when grouping(store_id)=0
then count(case when o.order_from='android' and rn=1 then o.order_id else null end)
-- 商圈
when grouping(trade_area_id)=0
then count(case when o.order_from='android' and rn=1 then o.order_id else null end)
-- 城市
when grouping(city_id)=0
then count(case when o.order_from='android' and rn=1 then o.order_id else null end)
-- 品牌
when grouping(brand_id)=0
then count(case when o.order_from='android' and brand_rn=1 then o.order_id else null end)
-- 小类
when grouping(min_class_id)=0
then count(case when o.order_from='android' and min_class_rn=1 then o.order_id else null end)
-- 中类
when grouping(mid_class_id)=0
then count(case when o.order_from='android' and mid_class_rn=1 then o.order_id else null end)
-- 大类
when grouping(max_class_id)=0
then count(case when o.order_from='android' and max_class_rn=1 then o.order_id else null end)
-- 全部
else count(case when o.order_from='android' and rn=1 then o.order_id else null end)
end
as android_order_cnt,
--ios订单量
case
-- 店铺
when grouping(store_id)=0
then count(case when o.order_from='ios' and rn=1 then o.order_id else null end)
-- 商圈
when grouping(trade_area_id)=0
then count(case when o.order_from='ios' and rn=1 then o.order_id else null end)
-- 城市
when grouping(city_id)=0
then count(case when o.order_from='ios' and rn=1 then o.order_id else null end)
-- 品牌
when grouping(brand_id)=0
then count(case when o.order_from='ios' and brand_rn=1 then o.order_id else null end)
-- 小类
when grouping(min_class_id)=0
then count(case when o.order_from='ios' and min_class_rn=1 then o.order_id else null end)
-- 中类
when grouping(mid_class_id)=0
then count(case when o.order_from='ios' and mid_class_rn=1 then o.order_id else null end)
-- 大类
when grouping(max_class_id)=0
then count(case when o.order_from='ios' and max_class_rn=1 then o.order_id else null end)
-- 全部
else count(case when o.order_from='ios' and rn=1 then o.order_id else null end)
end
as ios_order_cnt,
--pc订单量
case
-- 店铺
when grouping(store_id)=0
then count(case when o.order_from='pcweb' and rn=1 then o.order_id else null end)
-- 商圈
when grouping(trade_area_id)=0
then count(case when o.order_from='pcweb' and rn=1 then o.order_id else null end)
-- 城市
when grouping(city_id)=0
then count(case when o.order_from='pcweb' and rn=1 then o.order_id else null end)
-- 品牌
when grouping(brand_id)=0
then count(case when o.order_from='pcweb' and brand_rn=1 then o.order_id else null end)
-- 小类
when grouping(min_class_id)=0
then count(case when o.order_from='pcweb' and min_class_rn=1 then o.order_id else null end)
-- 中类
when grouping(mid_class_id)=0
then count(case when o.order_from='pcweb' and mid_class_rn=1 then o.order_id else null end)
-- 大类
when grouping(max_class_id)=0
then count(case when o.order_from='pcweb' and max_class_rn=1 then o.order_id else null end)
-- 全部
else count(case when o.order_from='pcweb' and rn=1 then o.order_id else null end)
end
as pcweb_order_cnt
from order_base o
--where b.rn=1
group by
grouping sets(
dt,
(dt, city_id, city_name),
(dt, trade_area_id, trade_area_name, city_id, city_name),
(dt, store_id, store_name, trade_area_id, trade_area_name, city_id, city_name),
(dt, brand_id, brand_name),
(dt, max_class_id, max_class_name),
(dt, mid_class_id, mid_class_name, max_class_id, max_class_name),
(dt, min_class_id, min_class_name, mid_class_id, mid_class_name, max_class_id, max_class_name)
)
--order by dt desc, sum(coalesce(order_amount, 0)) desc
;
- DM层
DROP TABLE IF EXISTS yp_dm.dm_sale;
CREATE TABLE yp_dm.dm_sale(
date_time string COMMENT '统计日期,不能用来分组统计',
time_type string COMMENT '统计时间维度：year、month、week、date',
year_code string COMMENT '年code',
year_month string COMMENT '年月',
month_code string COMMENT '月份编码',
day_month_num string COMMENT '一月第几天',
dim_date_id string COMMENT '日期',
year_week_name_cn string COMMENT '年中第几周',
group_type string COMMENT '分组类型：store，trade_area，city，brand，min_class，mid_class，max_class，all',
city_id string COMMENT '城市id',
city_name string COMMENT '城市name',
trade_area_id string COMMENT '商圈id',
trade_area_name string COMMENT '商圈名称',
store_id string COMMENT '店铺的id',
store_name string COMMENT '店铺名称',
brand_id string COMMENT '品牌id',
brand_name string COMMENT '品牌名称',
max_class_id string COMMENT '商品大类id',
max_class_name string COMMENT '大类名称',
mid_class_id string COMMENT '中类id',
mid_class_name string COMMENT '中类名称',
min_class_id string COMMENT '小类id',
min_class_name string COMMENT '小类名称',
-- =======统计=======
-- 销售收入
sale_amt DECIMAL(38,2) COMMENT '销售收入',
-- 平台收入
plat_amt DECIMAL(38,2) COMMENT '平台收入',
-- 配送成交额
deliver_sale_amt DECIMAL(38,2) COMMENT '配送成交额',
-- 小程序成交额
mini_app_sale_amt DECIMAL(38,2) COMMENT '小程序成交额',
-- 安卓APP成交额
android_sale_amt DECIMAL(38,2) COMMENT '安卓APP成交额',
-- 苹果APP成交额
ios_sale_amt DECIMAL(38,2) COMMENT '苹果APP成交额',
-- PC商城成交额
pcweb_sale_amt DECIMAL(38,2) COMMENT 'PC商城成交额',
-- 成交单量
order_cnt BIGINT COMMENT '成交单量',
-- 参评单量
eva_order_cnt BIGINT COMMENT '参评单量comment=>cmt',
-- 差评单量
bad_eva_order_cnt BIGINT COMMENT '差评单量negtive-comment=>ncmt',
-- 配送成交单量
deliver_order_cnt BIGINT COMMENT '配送单量',
-- 退款单量
refund_order_cnt BIGINT COMMENT '退款单量',
-- 小程序成交单量
miniapp_order_cnt BIGINT COMMENT '小程序成交单量',
-- 安卓APP订单量
android_order_cnt BIGINT COMMENT '安卓APP订单量',
-- 苹果APP订单量
ios_order_cnt BIGINT COMMENT '苹果APP订单量',
-- PC商城成交单量
pcweb_order_cnt BIGINT COMMENT 'PC商城成交单量'
)
COMMENT '销售主题宽表'
ROW format delimited fields terminated BY '\t'
stored AS orc tblproperties ('orc.compress' = 'SNAPPY');
insert into yp_dm.dm_sale
-- 获取日期数据（周、月的环比/同比日期）
with dt1 as (
select
dim_date_id, date_code
,date_id_mom -- 与本月环比的上月日期
,date_id_mym -- 与本月同比的上年日期
,year_code
,month_code
,year_month --年月
,day_month_num --几号
,week_day_code --周几
,year_week_name_cn --年周
from yp_dwd.dim_date
),
groupby as (
select
-- 统计日期
'2021-03-17' as date_time,
-- 时间维度 year、month、date
case when grouping(dt1.year_code, dt1.month_code, dt1.day_month_num, dt1.dim_date_id) = 0
then 'date'
when grouping(dt1.year_code, dt1.year_week_name_cn) = 0
then 'week'
when grouping(dt1.year_code, dt1.month_code, dt1.year_month) = 0
then 'month'
when grouping(dt1.year_code) = 0
then 'year'
end
as time_type,
dt1.year_code,
dt1.year_month,
dt1.month_code,
dt1.day_month_num, --几号
dt1.dim_date_id,
dt1.year_week_name_cn, --第几周
-- 产品维度类型：store，trade_area，city，brand，min_class，mid_class，max_class，all
CASE WHEN grouping(dc.store_id)=0
THEN 'store'
WHEN grouping(dc.trade_area_id)=0
THEN 'trade_area'
WHEN grouping(dc.city_id)=0
THEN 'city'
WHEN grouping(dc.brand_id)=0
THEN 'brand'
WHEN grouping(dc.min_class_id)=0
THEN 'min_class'
WHEN grouping(dc.mid_class_id)=0
THEN 'mid_class'
WHEN grouping(dc.max_class_id)=0
THEN 'max_class'
ELSE 'all'
END
as group_type_new,
grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id) grouping_id,
group_type group_type_old,
dc.city_id,
dc.city_name,
dc.trade_area_id,
dc.trade_area_name,
dc.store_id,
dc.store_name,
dc.brand_id,
dc.brand_name,
dc.max_class_id,
dc.max_class_name,
dc.mid_class_id,
dc.mid_class_name,
dc.min_class_id,
dc.min_class_name,
-- 统计值
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.sale_amt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.sale_amt)
ELSE null
end
as sale_amt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.plat_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.plat_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.plat_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.plat_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.plat_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.plat_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.plat_amt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.plat_amt)
ELSE null
end
as plat_amt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.deliver_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.deliver_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.deliver_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.deliver_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.deliver_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.deliver_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.deliver_sale_amt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.deliver_sale_amt)
ELSE null
end
as deliver_sale_amt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.mini_app_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.mini_app_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.mini_app_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.mini_app_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.mini_app_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.mini_app_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.mini_app_sale_amt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.mini_app_sale_amt)
ELSE null
end
as mini_app_sale_amt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.android_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id,
dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.android_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.android_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.android_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.android_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.android_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.android_sale_amt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.android_sale_amt)
ELSE null
end
as android_sale_amt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.ios_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.ios_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.ios_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.ios_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.ios_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.ios_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.ios_sale_amt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.ios_sale_amt)
ELSE null
end
as ios_sale_amt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.pcweb_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.pcweb_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.pcweb_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.pcweb_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.pcweb_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.pcweb_sale_amt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.pcweb_sale_amt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.pcweb_sale_amt)
ELSE null
end
as pcweb_sale_amt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.order_cnt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.order_cnt)
ELSE null
end
as order_cnt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.eva_order_cnt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.eva_order_cnt)
ELSE null
end
as eva_order_cnt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.bad_eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.bad_eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.bad_eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.bad_eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.bad_eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.bad_eva_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.bad_eva_order_cnt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.bad_eva_order_cnt)
ELSE null
end
as bad_eva_order_cnt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.deliver_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.deliver_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.deliver_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.deliver_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.deliver_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.deliver_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.deliver_order_cnt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.deliver_order_cnt)
ELSE null
end
as deliver_order_cnt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.refund_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.refund_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.refund_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.refund_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.refund_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.refund_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.refund_order_cnt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.refund_order_cnt)
ELSE null
end
as refund_order_cnt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.miniapp_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.miniapp_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.miniapp_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.miniapp_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.miniapp_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.miniapp_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.miniapp_order_cnt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.miniapp_order_cnt)
ELSE null
end
as miniapp_order_cnt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.android_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.android_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.android_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.android_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.android_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.android_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.android_order_cnt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.android_order_cnt)
ELSE null
end
as android_order_cnt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.ios_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.ios_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.ios_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.ios_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.ios_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.ios_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.ios_order_cnt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.ios_order_cnt)
ELSE null
end
as ios_order_cnt,
CASE WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=15 and group_type='store'
THEN sum(dc.pcweb_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=79 and group_type='trade_area'
THEN sum(dc.pcweb_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=111 and group_type='city'
THEN sum(dc.pcweb_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=119 and group_type='brand'
THEN sum(dc.pcweb_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=120 and group_type='min_class'
THEN sum(dc.pcweb_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=124 and group_type='mid_class'
THEN sum(dc.pcweb_order_cnt)
WHEN grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=126 and group_type='max_class'
THEN sum(dc.pcweb_order_cnt)
when grouping(dc.store_id, dc.trade_area_id, dc.city_id, dc.brand_id, dc.min_class_id, dc.mid_class_id, dc.max_class_id)=127 and group_type='all'
then sum(dc.pcweb_order_cnt)
ELSE null
end
as pcweb_order_cnt
from yp_dws.dws_sale_daycount dc
left join dt1 on dc.dt = dt1.date_code
--WHERE dc.dt >= '2019-01-01'
group by
grouping sets (
-- 年
(dt1.year_code, group_type),
(dt1.year_code, city_id, city_name, group_type),
(dt1.year_code, city_id, city_name, trade_area_id, trade_area_name, group_type),
(dt1.year_code, city_id, city_name, trade_area_id, trade_area_name, store_id, store_name, group_type),
(dt1.year_code, brand_id, brand_name, group_type),
(dt1.year_code, max_class_id, max_class_name, group_type),
(dt1.year_code, max_class_id, max_class_name,mid_class_id, mid_class_name, group_type),
(dt1.year_code, max_class_id, max_class_name,mid_class_id, mid_class_name,min_class_id, min_class_name, group_type),
-- 月
(dt1.year_code, dt1.month_code, dt1.year_month, group_type),
(dt1.year_code, dt1.month_code, dt1.year_month, city_id, city_name, group_type),
(dt1.year_code, dt1.month_code, dt1.year_month, city_id, city_name, trade_area_id, trade_area_name, group_type),
(dt1.year_code, dt1.month_code, dt1.year_month, city_id, city_name, trade_area_id, trade_area_name, store_id, store_name, group_type),
(dt1.year_code, dt1.month_code, dt1.year_month, brand_id, brand_name, group_type),
(dt1.year_code, dt1.month_code, dt1.year_month, max_class_id, max_class_name, group_type),
(dt1.year_code, dt1.month_code, dt1.year_month, max_class_id, max_class_name,mid_class_id, mid_class_name, group_type),
(dt1.year_code, dt1.month_code, dt1.year_month, max_class_id, max_class_name,mid_class_id, mid_class_name,min_class_id, min_class_name, group_type),
-- 日
(dt1.year_code, dt1.month_code, dt1.day_month_num, dt1.dim_date_id, group_type),
(dt1.year_code, dt1.month_code, dt1.day_month_num, dt1.dim_date_id, city_id, city_name, group_type),
(dt1.year_code, dt1.month_code, dt1.day_month_num, dt1.dim_date_id, city_id, city_name, trade_area_id, trade_area_name, group_type),
(dt1.year_code, dt1.month_code, dt1.day_month_num, dt1.dim_date_id, city_id, city_name, trade_area_id, trade_area_name, store_id, store_name, group_type),
(dt1.year_code, dt1.month_code, dt1.day_month_num, dt1.dim_date_id, brand_id, brand_name, group_type),
(dt1.year_code, dt1.month_code, dt1.day_month_num, dt1.dim_date_id, max_class_id, max_class_name, group_type),
(dt1.year_code, dt1.month_code, dt1.day_month_num, dt1.dim_date_id, max_class_id, max_class_name,mid_class_id, mid_class_name, group_type),
(dt1.year_code, dt1.month_code, dt1.day_month_num, dt1.dim_date_id, max_class_id, max_class_name,mid_class_id, mid_class_name,min_class_id, min_class_name, group_type),
-- 周
(dt1.year_code, dt1.year_week_name_cn, group_type),
(dt1.year_code, dt1.year_week_name_cn, city_id, city_name, group_type),
(dt1.year_code, dt1.year_week_name_cn, city_id, city_name, trade_area_id, trade_area_name, group_type),
(dt1.year_code, dt1.year_week_name_cn, city_id, city_name, trade_area_id, trade_area_name, store_id, store_name, group_type),
(dt1.year_code, dt1.year_week_name_cn, brand_id, brand_name, group_type),
(dt1.year_code, dt1.year_week_name_cn, max_class_id, max_class_name, group_type),
(dt1.year_code, dt1.year_week_name_cn, max_class_id, max_class_name,mid_class_id, mid_class_name, group_type),
(dt1.year_code, dt1.year_week_name_cn, max_class_id, max_class_name,mid_class_id, mid_class_name,min_class_id, min_class_name, group_type)
)
-- order by time_type desc
)
select
-- 统计日期
date_time,
-- 时间维度 year、month、date
time_type,
year_code,
year_month,
month_code,
day_month_num, --几号
dim_date_id,
year_week_name_cn, --第几周
-- 产品维度类型：store，trade_area，city，brand，min_class，mid_class，max_class，all
group_type_new as group_type,
-- 业务属性维度
city_id,
city_name,
trade_area_id,
trade_area_name,
store_id,
store_name,
brand_id,
brand_name,
max_class_id,
max_class_name,
mid_class_id,
mid_class_name,
min_class_id,
min_class_name,
-- 订单金额
sale_amt, plat_amt, deliver_sale_amt, mini_app_sale_amt, android_sale_amt, ios_sale_amt, pcweb_sale_amt,
-- 订单量
order_cnt, eva_order_cnt, bad_eva_order_cnt, deliver_order_cnt, refund_order_cnt, miniapp_order_cnt, android_order_cnt, ios_order_cnt, pcweb_order_cnt
from groupby
where sale_amt is not null
;
常用
日期与时间值
DATE '2021-08-22'
TIMESTAMP '2021-08-22 03:04:05.321'
时间与字符串之间转换
-- 时间转字符
-- 将timestamp转换为指定format格式的string
date_format(timestamp,format) -> varchar
例:
date_format(date_add('day',-30,date '2019-05-17'), '%Y')
-- 字符转时间
-- 将format格式的String转换为时间类型
date_parse(string, format) -> timestamp
-- 字符转日期
-- 等同于CAST(x AS date)
date(x) -> date
-- format格式
年: %Y
月：%m
日：%d
时：%H
分：%i
秒：%s
周几：%w(0..6)
注意: mysql中: yyyy-MM-dd HH:mm:ss
-- 时间加减
-- 进行unit单元的时间运算,减法可以用负数来执行.
date_add(unit,value,timestamp) -> [same as input]
例:
date_add('day', -30, date '2021-7-21')
date_add('day', -30, from_unixtime(unix_timestamp(),'%Y%m%d'))
-- 时间timestamp2-timestamp1后,以unit单位进行展示差值
date_diff(unit,timestamp,timestamp2) -> bigint
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· winform 绘制太阳，地球，月球 运作规律
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· 上周热点回顾（3.3-3.9）