SQL需求
筛选同一部门,同一供应商,30天内采购累计金额达到5万元(含)的记录;
建表语句
CREATE TABLE `same_dept` (
`id` int(10) NOT NULL COMMENT '报账单id',
`dept_name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '部门名称',
`account` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '报账金额',
`create_time` date DEFAULT NULL COMMENT '创建时间',
`end_time` date DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `same_dept` VALUES (1,'部门1','20000','2022-06-25','2022-06-30');
INSERT INTO `same_dept` VALUES (2,'部门1','30000','2022-07-24','2022-06-29');
INSERT INTO `same_dept` VALUES (3,'部门1','10000','2022-07-30','2022-07-31');
INSERT INTO `same_dept` VALUES (4,'部门2','20000','2022-06-25','2022-06-30');
INSERT INTO `same_dept` VALUES (5,'部门2','20000','2022-07-24','2022-06-29');
INSERT INTO `same_dept` VALUES (6,'部门2','10000','2022-07-30','2022-07-31');
INSERT INTO `same_dept` VALUES (7,'部门3','1000','2022-06-25','2022-06-30');
INSERT INTO `same_dept` VALUES (8,'部门3','900','2022-07-24','2022-06-29');
INSERT INTO `same_dept` VALUES (9,'部门3','49000','2022-07-30','2022-07-31');
INSERT INTO `same_dept` VALUES (10,'部门3','1000','2022-08-01','2022-08-01');
INSERT INTO `same_dept` VALUES (11,'部门3','40000','2022-09-01','2022-09-03');
INSERT INTO `same_dept` VALUES (12,'部门3','10000','2022-09-15','2022-09-17');
脚本SQL–方法一(选择最小时间)
最主要的逻辑是要选好参照时间,没有参照时间变数太多了。
# 获取最小时间
select dept_name,min(create_time) from same_dept group by dept_name;
# 根据最小时间 除以 30 进行 分组
select
a.id,
a.dept_name,
a.account,
a.create_time,
b.min_create_time,
round(TIMESTAMPDIFF(DAY,b.min_create_time,a.create_time)/30) as group_id
from same_dept a
inner join
(select dept_name,min(create_time) as min_create_time from same_dept group by dept_name) b on a.dept_name = b.dept_name
# 针对分组 进行统计总数
select
t.dept_name,
t.group_id,
sum(account)
from
(select
a.id,
a.dept_name,
a.account,
a.create_time,
b.min_create_time,
round(TIMESTAMPDIFF(DAY,b.min_create_time,a.create_time)/30) as group_id
from same_dept a
inner join
(select dept_name,min(create_time) as min_create_time from same_dept group by dept_name) b on a.dept_name = b.dept_name) t
group by dept_name,group_id
# 让部门 、分组 作为条件去匹配所有字段
select
z.id,
z.dept_name,
z.account,
z.create_time,
z.group_id,
z1.sum_accout
from
(select
a.id,
a.dept_name,
a.account,
a.create_time,
b.min_create_time,
round(TIMESTAMPDIFF(DAY,b.min_create_time,a.create_time)/30) as group_id
from same_dept a
inner join
(select dept_name,min(create_time) as min_create_time from same_dept group by dept_name) b on a.dept_name = b.dept_name) z
inner join
(select
t.dept_name,
t.group_id,
sum(account) as sum_accout
from
(select
a.id,
a.dept_name,
a.account,
a.create_time,
b.min_create_time,
round(TIMESTAMPDIFF(DAY,b.min_create_time,a.create_time)/30) as group_id
from same_dept a
inner join
(select dept_name,min(create_time) as min_create_time from same_dept group by dept_name) b on a.dept_name = b.dept_name) t
group by dept_name,group_id) z1 on z.dept_name = z1.dept_name and z.group_id = z1.group_id
------------------------------------------------
------------------------------------------------
# 下面是创建建立临时表
# 获取最小时间
drop table if exists min_time_temp;
create table min_time_temp as
select dept_name,min(create_time) as min_create_time from same_dept group by dept_name;
# 根据最小时间 除以 30 进行 分组
drop table if exists group_temp;
create table group_temp as
select
a.id,
a.dept_name,
a.account,
a.create_time,
b.min_create_time,
round(TIMESTAMPDIFF(DAY,b.min_create_time,a.create_time)/30) as group_id
from same_dept a
inner join
min_time_temp b on a.dept_name = b.dept_name;
# 针对分组 进行统计总数
drop table if exists group_sum_temp;
create table group_sum_temp as
select
t.dept_name,
t.group_id,
sum(account)
from
group_temp t
group by dept_name,group_id;
# 让部门 、分组 作为条件去匹配所有字段
select
z.id,
z.dept_name,
z.account,
z.create_time,
z.group_id,
z1.sum_accout
from
group_temp z
inner join
group_sum_temp z1 on z.dept_name = z1.dept_name and z.group_id = z1.group_id
开始查询
最终查询 – 最后再判断是否大于5万,即可
那么问题来了,如果你以3月10号为最小时间,那么4月10和4月11号,这两个就会在不同分组里面,这个是不符合逻辑的。
脚本SQL–方法二(合理利用笛卡尔积和去重)
注意数据展示方式是以可供理解的方式展示,但是实际数据排序不一样,但是最终的值是一样的。
第一部分 样例取:15天内的数据
表结构为:编号,部门,时间,数量
# 假装是排序好的 数据,进行笛卡尔积关联, 左边是根据部门进行时间的降序排序 右边也是根据部门进行降序排序
数据如下
a编号,a部门,a时间,b编号,b部门,b时间 相差天数
1 0001 2023-03-30 0001 2023-03-30 0天
1 0001 2023-03-30 0001 2023-03-20 10天
1 0001 2023-03-30 0001 2023-03-15 15天
1 0001 2023-03-30 0001 2023-03-10 20天
2 0001 2023-03-20 0001 2023-03-30 -10天
2 0001 2023-03-20 0001 2023-03-20 0天
2 0001 2023-03-20 0001 2023-03-15 5天
2 0001 2023-03-20 0001 2023-03-10 10天
3 0001 2023-03-15 0001 2023-03-30 -15天
3 0001 2023-03-15 0001 2023-03-20 -10天
3 0001 2023-03-15 0001 2023-03-15 0天
3 0001 2023-03-15 0001 2023-03-10 5天
这里我们对相差天数进行0<=X<=15天的判断
我们取后半部分数据 ,那么将会有以下三种数据成组出现 (为何取右半部分,因为根据前面的的数据,我们可以判断出后半部分是不通的值)
一组: 2023-03-30
2023-03-20
2023-03-15
二组: 2023-03-20
2023-03-15
2023-03-10
二组: 2023-03-15
2023-03-10
第二部分--存疑
# 根据上面的,就存在 2023-03-30、2023-03-20、2023-03-15为一组 ,2023-03-20、2023-03-15、2023-03-10为一组 这是必须筛选出来的,这样的数据是我们想要的
# 但是如果出现 2023-03-20、2023-03-15、2023-03-10为一组 ,2023-03-15、2023-03-10又为一组,那么2023-03-15、2023-03-10就会出现一组重复数据,我们必须要筛选掉它
二组: 2023-03-20
2023-03-15
2023-03-10
二组: 2023-03-15
2023-03-10
第三部分--解决
# 怎么筛选掉?我的方法 是给上面的数据设置最小时间列(为何要设置最小时间列?因为是倒叙往下排的。如果是升序就最大时间列)设置完如下
最小时间列
二组: 2023-03-20 2023-03-10
2023-03-15 2023-03-10
2023-03-10 2023-03-10
二组: 2023-03-15 2023-03-10
2023-03-10 2023-03-10
# 然后根据最小的时间列进行group by 或者ditsinct 进行去重
#根据 最小时间去重 去重结果为
数据如下
一组: 2023-03-30
2023-03-20
2023-03-15
二组: 2023-03-20
2023-03-15
2023-03-10
# 然后再根据的出来的数据 去关联相关信息 获取统计值 或其他方式