SQL查询指定日期范围内各客户对应频率的计费周期区间

背景 #

现有数据库表billing_cycle，按客户维度存储计费付款周期规则，支持的计费频率包括：

• monthly：月付
• weekly：周付
• bi-weekly：双周付
• Quarterly：季付
• Yearly：年付

表结构 #

表共包含3个字段：

• Customer：客户编号
• Frequency：计费频率
• billing_start_date：计费起始日期

示例数据 #

Customer , Frequency, billing_start_date
001 , Monthly , 04-Feb-2021
002 , Weekly ,  01-Mar-2021
003 , Bi-Weekly , 01-Mar-2021

需求 #

编写查询语句，在给定的起止日期范围（From、To）内，按客户配置的计费频率，返回该客户所有落在范围内的计费周期区间。

输出示例 #

给定查询日期范围为01-Feb-2021至30-Oct-2021时，预期输出如下：

1. 客户001（Monthly月付）：

Pay_period_start ,  Pay_period_end
01-Feb-2021 , 28-Feb-2021
01-Mar-2021 , 31-Mar-2021
01-Apr-2021 , 30-Apr-2021
...
01-Oct-2021 , 31-Oct-2021

2. 客户002（Weekly周付，间隔7天）：

Pay_period_start ,  Pay_period_end
01-Feb-2021 , 07-Feb-2021
08-Feb-2021 , 14-Feb-2021
15-Feb-2021 , 21-Feb-2021
22-Feb-2021 , 28-Feb-2021
01-Mar-2021 , 07-Mar-2021
...
最后一个周期截止到31-Oct-2021

3. 客户003（Bi-Weekly双周付）按相同规则输出对应周期区间即可。

参考实现（兼容MySQL 8.0+/PostgreSQL/SQL Server） #

基于递归CTE实现，无需依赖额外日期维度表，可直接修改参数运行：

注：题目描述Bi-Weekly间隔15天，常规双周计费间隔为14天，如果业务确实要求15天间隔，只需将代码中Bi-Weekly分支的14/13天偏移改为15/14天即可。

-- 定义查询起止日期参数，可按需修改
WITH RECURSIVE params AS (
    SELECT 
        DATE '2021-02-01' AS range_start,
        DATE '2021-10-30' AS range_end
),
-- 递归生成每个客户的所有计费周期起始日
cycle_generator AS (
    -- 锚点：计算每个客户第一个和查询范围重叠的周期起始日
    SELECT 
        bc.Customer,
        bc.Frequency,
        CASE
            WHEN bc.Frequency = 'Monthly' THEN DATE_TRUNC('month', p.range_start)
            WHEN bc.Frequency = 'Weekly' THEN p.range_start - MOD((p.range_start - bc.billing_start_date), 7)
            WHEN bc.Frequency = 'Bi-Weekly' THEN p.range_start - MOD((p.range_start - bc.billing_start_date), 14)
            WHEN bc.Frequency = 'Quarterly' THEN DATE_TRUNC('quarter', p.range_start)
            WHEN bc.Frequency = 'Yearly' THEN DATE_TRUNC('year', p.range_start)
        END AS pay_period_start,
        p.range_end
    FROM billing_cycle bc
    CROSS JOIN params p
    UNION ALL
    -- 递归：按频率偏移生成下一个周期起始日，直到超出查询结束日期
    SELECT 
        Customer,
        Frequency,
        CASE
            WHEN Frequency = 'Monthly' THEN pay_period_start + INTERVAL '1 month'
            WHEN Frequency = 'Weekly' THEN pay_period_start + INTERVAL '7 day'
            WHEN Frequency = 'Bi-Weekly' THEN pay_period_start + INTERVAL '14 day'
            WHEN Frequency = 'Quarterly' THEN pay_period_start + INTERVAL '3 month'
            WHEN Frequency = 'Yearly' THEN pay_period_start + INTERVAL '1 year'
        END AS pay_period_start,
        range_end
    FROM cycle_generator
    WHERE pay_period_start < range_end
)
-- 计算周期截止日，裁剪超出查询范围的部分
SELECT
    Customer,
    pay_period_start,
    LEAST(
        CASE
            WHEN Frequency = 'Monthly' THEN CAST(pay_period_start + INTERVAL '1 month' - INTERVAL '1 day' AS DATE)
            WHEN Frequency = 'Weekly' THEN CAST(pay_period_start + INTERVAL '6 day' AS DATE)
            WHEN Frequency = 'Bi-Weekly' THEN CAST(pay_period_start + INTERVAL '13 day' AS DATE)
            WHEN Frequency = 'Quarterly' THEN CAST(pay_period_start + INTERVAL '3 month' - INTERVAL '1 day' AS DATE)
            WHEN Frequency = 'Yearly' THEN CAST(pay_period_start + INTERVAL '1 year' - INTERVAL '1 day' AS DATE)
        END,
        range_end
    ) AS pay_period_end
FROM cycle_generator, params
WHERE pay_period_start <= range_end
ORDER BY Customer, pay_period_start;

逻辑说明 #

• 月付、季付、年付周期按自然月/季度/年切分，和示例中客户001的输出规则完全对齐
• 周付、双周付周期严格对齐客户配置的billing_start_date做偏移，不会出现周期错位
• 末尾未覆盖完整时长的周期会自动截断到查询范围的截止日期，符合输出要求

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