如何在SQL中基于年龄组列生成连续日期范围组合？

首先明确你的原始数据（假设表名为rate_table）：

RateDate  AgeFrom  AgeTo  NoOfChild
8/1/2018  0       10     3
8/1/2018  11      17     3
8/2/2018  0       10     3
8/2/2018  11      17     3
8/3/2018  0       10     3
8/3/2018  11      17     3
8/4/2018  0       10     3
8/4/2018  11      17     3
8/5/2018  0       9      3
8/5/2018  10      17     3
8/6/2018  0       10     3
8/6/2018  11      17     3
8/7/2018  0       10     3
8/7/2018  11      17     3
8/8/2018  0       10     3
8/8/2018  11      17     3
8/9/2018  0       10     3
8/9/2018  11      17     3
8/10/2018 0       10     3
8/10/2018 11      17     3

你的需求是：把连续日期中年龄组规则完全一致的记录合并，输出合并后的日期范围和对应的年龄组，最终得到这样的结果：

FromDate   ToDate     Age From  Age To  Age From  Age To
8/1/2018   8/4/2018   0         10      11        17
8/5/2018   8/5/2018   0         9       10        17
8/6/2018   8/10/2018  0         10      11        17

核心思路 #

要实现这个需求，关键是两步：

1. 给每一天的年龄组规则生成一个唯一标识，用来判断不同日期的规则是否完全一致；
2. 识别出连续拥有相同标识的日期区间，然后聚合输出。

SQL实现（兼容主流数据库） #

下面的代码适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库：

WITH daily_age_signature AS (
    -- 第一步：按日期聚合，生成当天年龄组的唯一签名
    SELECT
        RateDate,
        -- 把当天所有年龄组按AgeFrom排序后拼接成字符串，确保规则一致则签名相同
        GROUP_CONCAT(CONCAT(AgeFrom, '-', AgeTo) ORDER BY AgeFrom) AS age_rule_signature,
        -- 用JSON数组存储当天的所有年龄组信息，方便后续提取
        JSON_ARRAYAGG(JSON_OBJECT('from', AgeFrom, 'to', AgeTo)) AS age_groups
    FROM rate_table -- 替换成你的实际表名
    GROUP BY RateDate
),
date_groups AS (
    -- 第二步：生成连续相同规则的分组ID
    SELECT
        RateDate,
        age_rule_signature,
        age_groups,
        -- 当当天签名和前一天不同时，分组ID加1，以此区分不同的规则区间
        SUM(CASE WHEN prev_signature = age_rule_signature THEN 0 ELSE 1 END) OVER (ORDER BY RateDate) AS group_id
    FROM (
        SELECT
            RateDate,
            age_rule_signature,
            age_groups,
            -- 获取前一天的规则签名
            LAG(age_rule_signature) OVER (ORDER BY RateDate) AS prev_signature
        FROM daily_age_signature
    ) t
)
-- 第三步：按分组聚合，输出日期范围和对应的年龄组
SELECT
    MIN(RateDate) AS FromDate,
    MAX(RateDate) AS ToDate,
    -- 提取第一个年龄组的起止
    JSON_UNQUOTE(JSON_EXTRACT(age_groups, '$[0].from')) AS `Age From 1`,
    JSON_UNQUOTE(JSON_EXTRACT(age_groups, '$[0].to')) AS `Age To 1`,
    -- 提取第二个年龄组的起止
    JSON_UNQUOTE(JSON_EXTRACT(age_groups, '$[1].from')) AS `Age From 2`,
    JSON_UNQUOTE(JSON_EXTRACT(age_groups, '$[1].to')) AS `Age To 2`
FROM date_groups
GROUP BY group_id, age_rule_signature, age_groups
ORDER BY FromDate;

针对旧版MySQL（不支持JSON）的替代方案 #

如果你的MySQL版本低于8.0，或者不支持JSON函数，可以用字符串拼接来存储年龄组：

WITH daily_age_signature AS (
    SELECT
        RateDate,
        GROUP_CONCAT(CONCAT(AgeFrom, '-', AgeTo) ORDER BY AgeFrom) AS age_rule_signature,
        -- 用|分隔不同年龄组，每个年龄组用,分隔起止
        GROUP_CONCAT(CONCAT(AgeFrom, ',', AgeTo) ORDER BY AgeFrom SEPARATOR '|') AS age_groups_str
    FROM rate_table
    GROUP BY RateDate
),
date_groups AS (
    SELECT
        RateDate,
        age_rule_signature,
        age_groups_str,
        SUM(CASE WHEN prev_signature = age_rule_signature THEN 0 ELSE 1 END) OVER (ORDER BY RateDate) AS group_id
    FROM (
        SELECT
            RateDate,
            age_rule_signature,
            age_groups_str,
            LAG(age_rule_signature) OVER (ORDER BY RateDate) AS prev_signature
        FROM daily_age_signature
    ) t
)
SELECT
    MIN(RateDate) AS FromDate,
    MAX(RateDate) AS ToDate,
    -- 提取第一个年龄组的From
    SUBSTRING_INDEX(SUBSTRING_INDEX(age_groups_str, '|', 1), ',', 1) AS `Age From 1`,
    -- 提取第一个年龄组的To
    SUBSTRING_INDEX(SUBSTRING_INDEX(age_groups_str, '|', 1), ',', -1) AS `Age To 1`,
    -- 提取第二个年龄组的From
    SUBSTRING_INDEX(SUBSTRING_INDEX(age_groups_str, '|', 2), ',', 1) AS `Age From 2`,
    -- 提取第二个年龄组的To
    SUBSTRING_INDEX(SUBSTRING_INDEX(age_groups_str, '|', 2), ',', -1) AS `Age To 2`
FROM date_groups
GROUP BY group_id, age_rule_signature, age_groups_str
ORDER BY FromDate;

支持任意数量自定义年龄组的扩展方案 #

如果你的年龄组数量不固定（可能多于2个），可以改成每行输出一个年龄组的形式，这样更灵活：

WITH daily_age_signature AS (
    SELECT
        RateDate,
        GROUP_CONCAT(CONCAT(AgeFrom, '-', AgeTo) ORDER BY AgeFrom) AS age_rule_signature,
        AgeFrom,
        AgeTo
    FROM rate_table
    GROUP BY RateDate, AgeFrom, AgeTo
),
date_groups AS (
    SELECT
        RateDate,
        age_rule_signature,
        AgeFrom,
        AgeTo,
        SUM(CASE WHEN prev_signature = age_rule_signature THEN 0 ELSE 1 END) OVER (ORDER BY RateDate) AS group_id
    FROM (
        SELECT
            RateDate,
            age_rule_signature,
            AgeFrom,
            AgeTo,
            LAG(age_rule_signature) OVER (ORDER BY RateDate) AS prev_signature
        FROM daily_age_signature
    ) t
)
SELECT
    MIN(RateDate) AS FromDate,
    MAX(RateDate) AS ToDate,
    AgeFrom AS `Age From`,
    AgeTo AS `Age To`
FROM date_groups
GROUP BY group_id, age_rule_signature, AgeFrom, AgeTo
ORDER BY FromDate, AgeFrom;

这个版本的输出会是这样的：

FromDate   ToDate     Age From  Age To
8/1/2018   8/4/2018   0         10
8/1/2018   8/4/2018   11        17
8/5/2018   8/5/2018   0         9
8/5/2018   8/5/2018   10        17
8/6/2018   8/10/2018  0         10
8/6/2018   8/10/2018  11        17

这样不管你有多少个自定义年龄组，都能正确输出对应的日期范围。

内容的提问来源于stack exchange，提问作者Deepak Singh Rautela