如何根据表中给定起止日期范围统计字段总和并按单日拆分输出

实现方案 #

核心思路是先生成查询参数ra_frdt到ra_todt范围内的所有连续自然日序列，再和你原有聚合得到的task时间区间做关联，匹配每个自然日所属的task区间即可完成拆分。
以下是不同数据库的实现代码：

Oracle 版本（你当前用的to_date语法符合Oracle规范，优先参考这个）

WITH date_range AS (
    -- 生成参数范围内的所有连续自然日
    SELECT to_date(:ra_frdt,'dd/mm/yyyy') + LEVEL - 1 AS dt
    FROM dual
    CONNECT BY LEVEL <= to_date(:ra_todt,'dd/mm/yyyy') - to_date(:ra_frdt,'dd/mm/yyyy') + 1
),
task_agg AS (
    -- 保留你原有的聚合逻辑
    SELECT sum(task_changepoint) task_changepoint, Task_id, task_dt_from, task_dt_to
    FROM fb_task 
    WHERE (task_dt_from BETWEEN to_date(:ra_frdt,'dd/mm/yyyy') AND to_date(:ra_todt,'dd/mm/yyyy')) 
        OR (task_dt_to BETWEEN to_date(:ra_frdt,'dd/mm/yyyy') AND to_date(:ra_todt,'dd/mm/yyyy'))
    GROUP BY Task_id, task_dt_from, task_dt_to
)
-- 关联得到单日维度的结果
SELECT 
    ta.task_changepoint,
    ta.Task_id,
    to_char(dr.dt, 'dd/mm/yyyy') AS date
FROM task_agg ta
JOIN date_range dr 
    ON dr.dt BETWEEN ta.task_dt_from AND ta.task_dt_to
ORDER BY ta.Task_id, dr.dt;

MySQL 8.0+ 版本

WITH RECURSIVE date_range AS (
    SELECT STR_TO_DATE(:ra_frdt,'%d/%m/%Y') AS dt
    UNION ALL
    SELECT dt + INTERVAL 1 DAY 
    FROM date_range
    WHERE dt < STR_TO_DATE(:ra_todt,'%d/%m/%Y')
),
task_agg AS (
    SELECT sum(task_changepoint) task_changepoint, Task_id, task_dt_from, task_dt_to
    FROM fb_task 
    WHERE (task_dt_from BETWEEN STR_TO_DATE(:ra_frdt,'%d/%m/%Y') AND STR_TO_DATE(:ra_todt,'%d/%m/%Y')) 
        OR (task_dt_to BETWEEN STR_TO_DATE(:ra_frdt,'%d/%m/%Y') AND STR_TO_DATE(:ra_todt,'%d/%m/%Y'))
    GROUP BY Task_id, task_dt_from, task_dt_to
)
SELECT 
    ta.task_changepoint,
    ta.Task_id,
    DATE_FORMAT(dr.dt, '%d/%m/%Y') AS date
FROM task_agg ta
JOIN date_range dr 
    ON dr.dt BETWEEN ta.task_dt_from AND ta.task_dt_to
ORDER BY ta.Task_id, dr.dt;

PostgreSQL 版本

WITH date_range AS (
    SELECT generate_series(
        to_date(:ra_frdt,'dd/mm/yyyy'),
        to_date(:ra_todt,'dd/mm/yyyy'),
        '1 day'::interval
    ) AS dt
),
task_agg AS (
    SELECT sum(task_changepoint) task_changepoint, Task_id, task_dt_from, task_dt_to
    FROM fb_task 
    WHERE (task_dt_from BETWEEN to_date(:ra_frdt,'dd/mm/yyyy') AND to_date(:ra_todt,'dd/mm/yyyy')) 
        OR (task_dt_to BETWEEN to_date(:ra_frdt,'dd/mm/yyyy') AND to_date(:ra_todt,'dd/mm/yyyy'))
    GROUP BY Task_id, task_dt_from, task_dt_to
)
SELECT 
    ta.task_changepoint,
    ta.Task_id,
    to_char(dr.dt, 'dd/mm/yyyy') AS date
FROM task_agg ta
JOIN date_range dr 
    ON dr.dt BETWEEN ta.task_dt_from AND ta.task_dt_to
ORDER BY ta.Task_id, dr.dt;

注意事项 #

如果你的数据中存在同一个Task_id的多个时间区间重叠的情况，同一个日期会匹配到多条记录，需要合并的话可以在外层再增加一层按Task_id、dt分组的聚合逻辑即可。

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