时间戳

首页 > 解决方案 > 使用无界前面计算运行总计

sql - 使用无界前面计算运行总计

问题描述

我正在编写一个查询，它将给出开始日剩余的小时数，它应该逐渐减少到结束日并达到零。

我尝试使用无界的前置功能。它有所帮助，但并不完全。

SELECT project_id,
       sprint_id,
       est_task_per_sprint,
       start_date,end_date,
         nvl(sum(est_task_per_sprint) over (
           order by end_date rows between unbounded preceding and 1 preceding

       ),0) tot
FROM   project_sprint where project_id=1;

sum(est_task_per_sprint)=1134是总小时数。

我得到的输出：

Proj  Sprint  Hours  startdate  enddate    hoursremaining
   1       1    262  01-JAN-19  31-JAN-19               0
   1      11    263  01-FEB-19  28-FEB-19             262
   1      21    266  01-MAR-19  31-MAR-19             525
   1      31    262  01-APR-19  30-APR-19             791
   1      41    261  01-MAY-19  31-MAY-19            1053

预期的：

Proj  Sprint  Hours  startdate  enddate    hoursremaining
   1       1    262  01-JAN-19  31-JAN-19            1053
   1      11    263  01-FEB-19  28-FEB-19             791
   1      21    266  01-MAR-19  31-MAR-19             525
   1      31    262  01-APR-19  30-APR-19             262
   1      41    261  01-MAY-19  31-MAY-19               0

标签： sqloracle

解决方案

您可以通过从项目的所有小时总数中减去当前行（之前不是1 个）的小时数总和来计算剩余小时数。

我已经添加了按项目 ID 进行的分区，因此这也可以在没有 ID 过滤器的情况下工作，并且由于窗口现在匹配默认值（无界的前一行和当前行之间的行），我已经将该子句排除在外。此示例包括总小时数和已完成小时数，以使其更清晰 - 在您的实际查询中不需要它们。

SELECT project_id,
       sprint_id,
       est_task_per_sprint as hours,
       start_date,
       end_date,
       sum(est_task_per_sprint) over (partition by project_id) as total,
       coalesce(sum(est_task_per_sprint)
                  over (partition by project_id order by end_date), 0) as completed,
       sum(est_task_per_sprint) over (partition by project_id)
         - coalesce(sum(est_task_per_sprint)
                      over (partition by project_id order by end_date), 0) as remaining
FROM   project_sprint
--WHERE  project_id=1
ORDER BY project_id, start_date;

PROJECT_ID  SPRINT_ID      HOURS START_DATE END_DATE        TOTAL  COMPLETED  REMAINING
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1        262 2019-01-01 2019-01-31       1314        262       1052
         1         11        263 2019-02-01 2019-02-28       1314        525        789
         1         21        266 2019-03-01 2019-03-31       1314        791        523
         1         31        262 2019-04-01 2019-04-30       1314       1053        261
         1         41        261 2019-05-01 2019-05-31       1314       1314          0

这与对后面的行求和相同，这次使用 1 个以下和无界以下之间的行的窗口子句：

SELECT project_id,
       sprint_id,
       est_task_per_sprint as hours,
       start_date,
       end_date,
       coalesce(sum(est_task_per_sprint)
                over (partition by project_id order by end_date
                      rows between 1 following and unbounded following), 0) as remaining
FROM   project_sprint
--WHERE  project_id=1
ORDER BY project_id, start_date;

PROJECT_ID  SPRINT_ID      HOURS START_DATE END_DATE    REMAINING
---------- ---------- ---------- ---------- ---------- ----------
         1          1        262 2019-01-01 2019-01-31       1052
         1         11        263 2019-02-01 2019-02-28        789
         1         21        266 2019-03-01 2019-03-31        523
         1         31        262 2019-04-01 2019-04-30        261
         1         41        261 2019-05-01 2019-05-31          0

db<>小提琴

请注意，这些数字与您的预期结果不完全匹配，但那是因为我认为您的预期并不完全正确，并且是基于您的查询产生的数字 - 只是颠倒这些数字是没有意义的。

推荐阅读