如何在MySQL中基于首次记录日期获取周/月度统计并重置成本

嘿Anthony，我来帮你搞定这两个需求！咱们把问题拆成周期统计和cost自动重置累计两部分来处理，应该就能解决你之前用DATE_ADD/DATE_SUB没达到预期的问题了。

一、基于数据库首条记录的周期统计 #

核心思路是先拿到数据库里第一条记录的日期，以此为起点划分7天周期和月度周期，再分组统计总和。

1. 统计每7天的记录总和 #

首先获取首条记录的日期，然后通过计算每条记录与首条日期的天数差，划分到对应的7天周期里：

-- 先定义首条记录日期变量
SET @first_record_date = (SELECT MIN(create_date) FROM your_table);

SELECT
  -- 计算当前记录所属的7天周期起始日
  DATE_ADD(@first_record_date, INTERVAL FLOOR(DATEDIFF(create_date, @first_record_date)/7) * 7 DAY) AS week_cycle_start,
  COUNT(*) AS total_records, -- 记录总数
  SUM(your_field) AS total_sum -- 你需要统计的字段总和（比如cost）
FROM your_table
GROUP BY week_cycle_start
ORDER BY week_cycle_start;

这里的FLOOR(DATEDIFF(...) /7)会计算出当前记录属于第几个7天周期，再乘以7天加到首条日期上，就能得到该周期的起始日，分组后就能拿到每个周期的统计数据。

2. 月度统计 #

月度统计分两种情况，你可以根据需求选择：

• 自然月度统计：按常规的年月分组，不需要依赖首条记录日期：

SELECT
  DATE_FORMAT(create_date, '%Y-%m-01') AS month_start,
  COUNT(*) AS total_records,
  SUM(your_field) AS total_sum
FROM your_table
GROUP BY month_start
ORDER BY month_start;

• 基于首条记录的“30天周期”统计：和7天周期逻辑类似，把7换成30即可：

SET @first_record_date = (SELECT MIN(create_date) FROM your_table);

SELECT
  DATE_ADD(@first_record_date, INTERVAL FLOOR(DATEDIFF(create_date, @first_record_date)/30) *30 DAY) AS 30day_cycle_start,
  COUNT(*) AS total_records,
  SUM(your_field) AS total_sum
FROM your_table
GROUP BY 30day_cycle_start
ORDER BY 30day_cycle_start;

二、每7天重置cost并每日累计 #

这个需求可以在应用层或者数据库层实现，我给你两种方案：

1. 应用层处理（更灵活） #

在插入每日记录时，先计算当前日期距离首条记录的天数，判断是否处于新周期的第一天：

# 伪代码示例（Python）
import mysql.connector
from datetime import date, timedelta

db = mysql.connector.connect(host="your_host", user="your_user", password="your_pwd", database="your_db")
cursor = db.cursor()

# 获取首条记录日期
cursor.execute("SELECT MIN(create_date) FROM your_table")
first_date = cursor.fetchone()[0]

# 计算当前处于周期内的第几天
current_day_diff = (date.today() - first_date).days
cycle_day = current_day_diff % 7  # 0=第7天，1-6=第1-6天

daily_cost = 100  # 当日产生的cost值
if cycle_day == 0:
    # 新周期第一天，累计cost重置为当日值
    accumulated_cost = daily_cost
else:
    # 非新周期第一天，累加之前的累计值
    cycle_start = first_date + timedelta(days=(current_day_diff //7)*7)
    cursor.execute("SELECT SUM(daily_cost) FROM your_table WHERE create_date BETWEEN %s AND %s", 
                   (cycle_start, date.today()-timedelta(days=1)))
    prev_sum = cursor.fetchone()[0] or 0
    accumulated_cost = prev_sum + daily_cost

# 插入记录
cursor.execute("INSERT INTO your_table(create_date, daily_cost, accumulated_cost) VALUES(%s, %s, %s)", 
               (date.today(), daily_cost, accumulated_cost))
db.commit()
cursor.close()
db.close()

2. 数据库层自动处理（用事件调度器） #

如果想让数据库自动重置累计值，可以用MySQL的事件调度器：
首先开启事件调度器：

SET GLOBAL event_scheduler = ON;

然后创建一个每7天执行一次的事件，重置累计cost字段：

CREATE EVENT reset_cost_cycle
ON SCHEDULE
  -- 从首条记录的第7天开始，每7天执行一次
  EVERY 7 DAY
  STARTS DATE_ADD((SELECT MIN(create_date) FROM your_table), INTERVAL 7 DAY)
DO
  -- 假设你有一个存储累计cost的表cost_summary，重置total_cost为0
  UPDATE cost_summary SET total_cost = 0;

同时，在插入每日记录时，直接累加当前的total_cost：

INSERT INTO your_table(create_date, daily_cost)
VALUES(CURDATE(), 100);

-- 同步更新累计cost
UPDATE cost_summary SET total_cost = total_cost + 100;

为什么之前用DATE_ADD/DATE_SUB没成功？ #

大概率是你没有以首条记录日期作为周期起点，而是用了当前日期或者固定日期，导致周期划分和你预期的不一致。上面的方案都是基于首条记录日期来计算周期，应该能匹配你的需求。

内容的提问来源于stack exchange，提问作者Anthony Mends