Mysql种如何求和连续11天的流水并取最大值对应的时间范围

背景

老板: 春节过完了, 开工了. 我看开工到今天刚好11天(2023/1/28-2023/2/7), 我们公司流水破千万了, 我想看看这是不是历史首次, 要是真是, 那我们得做个海报好好吹一下了
我: 现在拉数据给你

Mysql实现

Mysql实现比Python实现复杂很多, 但是也是可以实现的

1. 建表

create table tmp_max_flow(
	start_date varchar(33) default null comment '开始日期',
	end_date varchar(33) default null comment '结束日期',
	flow double(12,2) not null default '0' comment '区间流水'
) comment '区间流水'

2. 建存储过程

create procedure tmp_max_flow()
begin 
	declare base_date date;
	set date = '2021-09-01'; # 我们数据库中有流水的最早日期
	
	truncate tmp_max_flow;
	while base_date <= curdate() do
		set @start_date = base_date;
		set @end_date   = date_add(base_date,interval 10 day);
		insert into tmp_max_flow 
			select 
				@start_date,
				@end_date,
				sum(amount) as flow
				from stage_customer
				where bizTime is not null 
					and date(bizTime) between @start_date and @end_date
				group by 1,2
			;	
	end while;
end 

保存, 运行. 可以在UI界面点击运行, 也可以写SQLcall tmp_max_flow()运行

3. 取数

跑完存储过程后, tmp_max_flow表中就会有数据了, 一个降序即可取流水最大值及对应的区间了

select 
	start_date,
	end_date,
	flow
	from tmp_max_flow
	order by flow desc limit 1

Python实现

Python实现其实是最简单的, 通过pymysql模块连接到数据库, 循环日期套进Sql取数即可

Mysql类, 个人比较喜欢将简单打包成更简单

# coding:utf-8
from Udfs.pyconfig import Database
import pymysql 
import logging

class Mysql:
    def __init__(self,config:dict = Database().chami):
        host = config['host']
        user = config['user']
        self.db = pymysql.connect(
                host   = config['host'],
                port   = config['port'],
                user   = config['user'],
                passwd = config['password'],
                db     = config['database'],
        )
        self.cs = self.db.cursor()
        logging.info(f"Connect {host} with {user}")
    
    def execute(self,sql):
        '''执行SQL'''
        logging.info(sql) if len(sql) <= 600 else logging.info(sql[:599] + '...')
        capword = sql.split()[0]
        self.cs.execute(sql)
        if capword.lower() in ['select','show']:
            result = self.cs.fetchall()
            _dlen  = len(result)
            logging.info(f'Data Length : {_dlen}')
        else:
            self.db.commit()
            logging.info('COMMITED')
            result = None
        self.close()
        return result 

循环日期取数

from Udfs.Mysql import Mysql
import datetime

def run():
	sql_order = '''	
		select 
			sum(a.amount) as flow # 流水
			from stage_customer a 
			where a.bizTime is not null # 有成单的订单
				and a.bizTime between '{}' and '{}' # 成单日期
	'''
	
	base_date = datetime.datetime(2021,5,23)
	
	count = 0
	step  = 10 # 步长, 计算11天即为10, 间隔10天
	while True:
	    start_dt       = (base_date + datetime.timedelta(days=count))
	    start_datetime = start_dt.strftime('%Y-%m-%d 00:00:00')
	    end_dt         = (start_dt + datetime.timedelta(days=step))
	    end_datetime   = end_dt.strftime('%Y-%m-%d 23:59:59')
	    flow           = Mysql().execute(sql_order.format(start_datetime,end_datetime))[0][0]
	    count += 1
	    print(f'{start_datetime}|{end_datetime}|{flow}') # 输出到屏幕
	    if end_dt >= datetime.datetime.now():
	        break

因为偷懒, 所以输出到了屏幕, 复制到Excel里面分列加排序处理一下就能拿到最大值了

---

结论

个人比较懒, 觉得Python逻辑简单易行, 所以直接选用了Python方法, 写代码加运行全程下来30分钟左右

最后发现历史上连续11天达成1000万流水的居然有30次之多, 本次排名都到了32名了!

老板: 好吧[捂脸]

---

- END -