目录
题目
求2024-01-01 每个门店 的 销售单量, 销售数量, 销售金额, 线上单量, 线下单量
准备数据
-- 创建库
create database db_2;
use db_2;
-- 创建商品销售明细(核销)天表
CREATE TABLE dwm_sold_goods_sold_dtl_i (
trade_date_time VARCHAR(19) COMMENT '核销时间',
trade_date VARCHAR(10) COMMENT '交易日期',
store_no VARCHAR(50) COMMENT '店铺编码',
goods_no VARCHAR(50) COMMENT '商品编码',
trade_type BIGINT COMMENT '结算类型(0.正常交易,1.赠品发放,2.退货,4.培训,5.取消交易)',
is_online_order BIGINT COMMENT '是否为线上单:0否,1是',
parent_order_no VARCHAR(50) COMMENT '订单编号',
sale_qty DECIMAL(27, 3) COMMENT '商品销售数量',
sale_amount DECIMAL(27, 2) COMMENT '商品销售金额'
);
-- 插入数据到 商品销售明细(核销)天表
INSERT INTO dwm_sold_goods_sold_dtl_i
(trade_date_time, trade_date, store_no, goods_no, trade_type, is_online_order, parent_order_no, sale_qty, sale_amount)
VALUES
('2024-01-01 10:00:00', '2024-01-01', 'S001', 'G001', 0, 0, 'ORD001', 2, 200.00),
('2024-01-01 11:00:00', '2024-01-01', 'S001', 'G001', 5, 0, 'ORD001', 2, 200.00),
('2024-01-01 18:00:00', '2024-01-01', 'S001', 'G001', 0, 1, 'ORD012', 3, 100.00),
('2024-01-01 18:00:00', '2024-01-01', 'S001', 'G002', 0, 1, 'ORD012', 4, 200.00),
('2024-01-01 18:00:00', '2024-01-01', 'S001', 'G002', 0, 0, 'ORD013', 5, 300.00),
('2024-01-01 12:00:00', '2024-01-01', 'S002', 'G003', 0, 1, 'ORD003', 3, 300.00),
('2024-01-01 13:00:00', '2024-01-01', 'S002', 'G004', 0, 1, 'ORD003', 4, 400.00),
('2024-01-01 14:00:00', '2024-01-01', 'S002', 'G004', 0, 0, 'ORD005', 5, 500.00);
分析数据
还是要排除取消订单的那个值
select
trade_date,
store_no,
count(distinct if(trade_type=0, parent_order_no, null)) - count(distinct if(trade_type=5, parent_order_no, null)) as 销售的订单量,
sum(if(trade_type=0, sale_qty, 0)) - sum(if(trade_type=5, sale_qty, 0)) as 销售数量,
sum(if(trade_type=0, sale_amount, 0)) - sum(if(trade_type=5, sale_amount, 0)) as 销售金额,
count(distinct if(trade_type=0 and is_online_order=1, parent_order_no, null))
- count(distinct if(trade_type=5 and is_online_order=1, parent_order_no, null)) as 线上的销售单量,
count(distinct if(trade_type=0 and is_online_order=0, parent_order_no, null))
- count(distinct if(trade_type=5 and is_online_order=0, parent_order_no, null)) as 线下的销售单量
from dwm_sold_goods_sold_dtl_i
where trade_date='2024-01-01'
group by trade_date, store_no
;
总结
-- count(distinct if(trade_type=0, parent_order_no, null)) as 正常交易的订单量,
-- count(distinct if(trade_type=5, parent_order_no, null)) as 取消交易的订单量,