Presto SQL实现每月截止日（每月倒数第5天）自动返回日期区间

核心思路 #

先批量生成符合规则的日期区间（每个区间结束日为当月倒数第5天，下一个区间开始日为上一区间结束日+1，首个区间从指定起始日开始），再按区间关联交易数据完成统计。以下提供两种常用场景的实现方案：

方案一：Python 实现（适合数据分析师/工程师做离线统计） #

用 datetime 处理日期逻辑，结合 pandas 完成数据统计：

from datetime import datetime, timedelta
import pandas as pd

# 计算指定年月的倒数第5天
def get_last_fifth_day(year, month):
    # 先取下月第一天，减1天得当月最后一天，再减4天就是倒数第5天
    last_day = datetime(year, month + 1, 1) - timedelta(days=1)
    return (last_day - timedelta(days=4)).date()

# 生成连续的统计区间列表
def generate_intervals(start_date_str, end_date_str=None):
    start_date = datetime.strptime(start_date_str, "%Y-%m-%d").date()
    intervals = []
    current_year, current_month = start_date.year, start_date.month

    while True:
        current_end = get_last_fifth_day(current_year, current_month)
        # 首个区间从指定起始日开始，后续区间从上一结束日+1开始
        current_start = start_date if not intervals else intervals[-1]["end"] + timedelta(days=1)
        
        # 处理起始日晚于当月倒数第5天的特殊情况（直接跳到下月区间）
        if current_start > current_end:
            current_month += 1
            if current_month > 12:
                current_month = 1
                current_year += 1
            current_end = get_last_fifth_day(current_year, current_month)
        
        intervals.append({"start": current_start, "end": current_end})

        # 达到指定结束日期则停止生成
        if end_date_str:
            end_date = datetime.strptime(end_date_str, "%Y-%m-%d").date()
            if current_end >= end_date:
            break

        # 切换到下一个月
        current_month += 1
        if current_month > 12:
            current_month = 1
            current_year += 1
    return intervals

# 示例：生成2023-01-01开始的前2个区间
intervals = generate_intervals("2023-01-01")[:2]

# 模拟交易数据（实际场景替换为你的数据源）
transactions = pd.DataFrame({
    "transaction_date": pd.date_range("2023-01-01", "2023-02-28", freq="D").repeat(5),
    "user_id": [i for i in range(30)] * 59
})

# 按区间统计用户数和交易数
for interval in intervals:
    mask = (transactions["transaction_date"] >= interval["start"]) & (transactions["transaction_date"] <= interval["end"])
    filtered_data = transactions[mask]
    user_count = filtered_data["user_id"].nunique()
    trans_count = len(filtered_data)
    print(f"区间 {interval['start']} 至 {interval['end']}：{user_count}位用户，{trans_count}笔交易")

方案二：SQL 实现（适合数据库端实时统计） #

以 MySQL 为例，用递归 CTE 生成区间，再关联交易表统计：

-- 生成按每月倒数第5天截止的统计区间
WITH RECURSIVE date_intervals AS (
    -- 初始区间：从指定起始日到第一个月的倒数第5天
    SELECT
        CAST('2023-01-01' AS DATE) AS start_date,
        LAST_DAY('2023-01-01') - INTERVAL 4 DAY AS end_date,
        YEAR('2023-01-01') AS current_year,
        MONTH('2023-01-01') AS current_month
    UNION ALL
    -- 递归生成后续区间
    SELECT
        end_date + INTERVAL 1 DAY AS start_date,
        LAST_DAY(DATE_ADD(CONCAT(current_year, '-', current_month, '-01'), INTERVAL 1 MONTH)) - INTERVAL 4 DAY AS end_date,
        CASE WHEN current_month = 12 THEN current_year + 1 ELSE current_year END AS current_year,
        CASE WHEN current_month = 12 THEN 1 ELSE current_month + 1 END AS current_month
    FROM date_intervals
    -- 可自定义停止条件，比如到2023年底
    WHERE end_date < '2023-12-31'
)
-- 关联交易表统计核心数据
SELECT
    di.start_date,
    di.end_date,
    COUNT(DISTINCT t.user_id) AS user_count,
    COUNT(t.transaction_id) AS transaction_count
FROM date_intervals di
LEFT JOIN transactions t
    ON t.transaction_date BETWEEN di.start_date AND di.end_date
GROUP BY di.start_date, di.end_date
ORDER BY di.start_date;

关键注意事项 #

1. 月份天数适配：两种方案都用了「当月最后一天减4天」的逻辑，自动处理2月（平/闰年）、30天/31天月份的差异，确保倒数第5天计算准确。
2. 边界处理：若指定的起始日晚于当月倒数第5天，代码会自动跳过无效区间，直接生成下一个月的有效统计区间。
3. 统计准确性：用户数统计需用去重逻辑（nunique()/COUNT(DISTINCT)），避免同一用户多次交易被重复计数。

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