给定一个订单表
要求
1,单笔订单抽取10%的分成,且不超过10元
2,每月单商户抽取分成最多不超过50元,超过分成即为0,不多收
求日结报表,
需要展现字段维度:日期,商户,当日每个商户的消费笔数,当日金额和,当日的实际分成金额,累计到当日的分成金额
原始数据表是我随便弄的,SQL如下:
-- MySQL dump 10.13 Distrib 8.0.21, for Win64 (x86_64)
--
-- Host: localhost Database: db001
-- ------------------------------------------------------
-- Server version 8.0.21
--
-- Table structure for table `table001`
--
DROP TABLE IF EXISTS `table001`;
CREATE TABLE `table001` (
`time` int NOT NULL COMMENT '时间',
`order_id` int NOT NULL COMMENT '订单id',
`name` varchar(45) NOT NULL COMMENT '商户名称',
`money` int NOT NULL COMMENT '金额',
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
LOCK TABLES `table001` WRITE;
INSERT INTO `table001` VALUES (20201020,2001,'a',10),(20201020,2002,'a',20),(20201020,2003,'a',30),(20201020,2004,'a',40),(20201020,2005,'a',50),(20201020,2006,'a',60),(20201020,2007,'b',70),(20201020,2008,'b',80),(20201020,2009,'c',90),(20201020,2010,'c',100),(20201020,2011,'d',110),(20201020,2012,'d',120),(20201020,2013,'d',130),(20201021,2014,'a',140),(20201021,2015,'a',150),(20201021,2016,'b',160),(20201021,2017,'b',170),(20201021,2018,'c',180),(20201019,2019,'c',190),(20201020,2020,'d',200),(20201021,2021,'d',210),(20201022,2022,'d',220),(20201023,2023,'d',230),(20201021,2024,'d',200),(20201024,2025,'d',210),(20201022,2026,'d',100),(20201023,2027,'d',110),(20201023,2028,'d',120),(20201022,2029,'d',111);
UNLOCK TABLES;
大致的数据如下图:
话不多说,SQL附上
/*
1,单笔订单抽取10%的分成,且不超过10元
2,每月单商户抽取分成最多不超过50元,超过分成即为0,不多收
求日结报表,
需要展现字段维度:日期,商户,当日每个商户的消费笔数,当日金额和,当日的实际分成金额,累计到当日的分成金额
*/
-- 创建临时表,处理分成数据,同时增加top字段 辅助下一步的计算
drop table if exists order_table_mid;
create table if not exists order_table_mid
as
-- 求出累计分成所得,本需求最主要的还是 累加函数 sum( col ) over(partition by col_1 order by col_2,col_3)
select
*,row_number() over(partition by name order by money_sum ) top
from
(
select
time
,name
,order_id
,money
,money_fc
,if( sum(money_fc) over(partition by name order by time,order_id ) >= 50
, 50
, sum(money_fc) over(partition by name order by time,order_id )
) money_sum
from
(
-- 求出每笔订单的实际分成所得
select
time
,name
,order_id
,money
,if(money*0.1 < 10, money*0.1, 10) money_fc
from db001.table001
)a
group by
time
,name
,order_id
,money
,money_fc
)b ;
-- 创建结果表
create table if not exists order_table_result
as
select
time
,name
,count(order_id)
,sum(money) -- 当日金额和
,sum(money_fc) -- 当日实际分成和
,max(money_sum) -- 累计到当日的分成,取最大的分成数据
from
(
select
a.time
,a.name
,a.order_id
,a.money
,a.money_sum - ifnull(b.money_sum, 0.0) money_fc -- 当日实际分成 = 累计到当日的分成- 累计到前一天分成
-- 是不是简单问题复杂化?这里主要处理当天刚好有多个订单,同时累计到中间的时候才满足 累计分成50元的条件
,a.money_sum
,b.top
,b.money_sum money_sum_b
from order_table_mid a
left join order_table_mid b
on a.name=b.name and a.top = b.top+1
)b
group by
time
,name;
select * from order_table_result;
结果图