sql - 具有空值的外部连接表的累积和

Question

我有一个包含 2 个日期列的表，分别代表 JIRA 项目的创建和关闭。对于报告，我需要获取每个日期创建和关闭的项目数以及累积数。

但是，在某些日期，没有创建或关闭项目，因此完整外连接返回空值。这会导致累积和列中出现空值，我无法更正。

我尝试了很多不同的合并方法:

Select
    coalesce(A.createdate, B.closedate) datum
    ,COALESCE(A.created, 0) created
    ,COALESCE(B.closed,0) closed
    ,sum(created) OVER(ORDER BY A.createdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumSum
    ,sum(coalesce(created,0)) OVER(ORDER BY A.createdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumSum2
    ,COALESCE(sum(created) OVER(ORDER BY A.createdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0) cumSum3
    ,sum(coalesce(created,0)) OVER(ORDER BY A.createdate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 preceding) + coalesce(created,0) cumSum4
from
(SELECT convert(date,[Created]) createdate, COUNT(*) created
  FROM [jira].[dbo].[report_clean_full]
  group by convert(date,[Created])) A
full outer join (SELECT convert(date,Resolved) closedate, COUNT(*) closed
  FROM [jira].[dbo].[report_clean_full]
  where status = 'Closed'
  group by convert(date,Resolved)) B
on A.createdate = B.closedate
order by 1

结果

2012-08-17  1   0   1   1   1   1
2012-08-23  1   0   2   2   2   2
2012-08-30  4   0   6   6   6   6
2012-09-03  1   0   7   7   7   7
2012-09-06  25  0   32  32  32  32
2012-09-07  30  3   62  62  62  62
2012-09-10  11  6   73  73  73  73
2012-09-11  6   1   79  79  79  79
2012-09-13  1   0   80  80  80  80
2012-09-14  10  1   90  90  90  90
2012-09-17  7   1   97  97  97  97
2012-09-18  4   2   101 101 101 101
2012-09-19  4   2   105 105 105 105
2012-09-20  19  5   124 124 124 124
2012-09-21  12  0   136 136 136 136
2012-09-24  7   1   143 143 143 143
2012-09-26  0   7   NULL    0   0   0
2012-09-27  18  2   161 161 161 161
2012-09-28  7   0   168 168 168 168
2012-10-01  4   1   172 172 172 172

在 2012 年 9 月 26 日，只有已关闭的项目，但我希望 cumSum 显示 143 而不是 0(最终重复已关闭的项目并减去以获取未清项目的数量)

谁能解释一下我做错了什么？

Answer 1

类似于我对 gpn 评论的回复:

解决方案是合并 OVER() 中 ORDER BY 的日期。据我了解，问题不在于值本身，而在于 NULL 范围。

sum(coalesce(created,0)) OVER(ORDER BY coalesce(A.createdate,b.closedate) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumSumCreated