SQL 每日未结销售额，滚动汇总? [英] SQL Daily Outstanding Sales, Rolling Aggregate?

问题描述

通过下面的测试数据，我试图显示有多少销售没有被同事采取行动，未完成的销售是任何仍显示为新"的记录，我一直在使用以下内容来识别这些记录.

With the below test data I am trying to show how many sales have not been actioned by a colleague, an outstanding sale is any record that is still showing as "New", I have been using the below to identify those.

select saleID, count(*) group by saleID having count(*)=1

我遇到的问题是我发现很容易显示总体有多少迄今为止仍然未完成，但我不知道如何显示有多少是优秀说 3 天前.这个想法是，当每天绘制图表时，这将显示一个趋势，该趋势将突出显示在每天结束时是否存在未完成销售额的增加/减少.理想情况下，输出应符合以下内容

The trouble I have is that I find it easy to show overall how many are still outstanding to date but I can't figure out how to show how many were outstanding say 3 days ago. The idea is that this will show a trend when charted daily which will highlight if there has been an increase/decrease of outstanding sales by the end of each day. Ideally the output would be along the lines of the below

   Date      VolumeOutstanding 
2020-01-01    0
2020-01-02    1
2020-01-03    3
2020-01-04    2    

数据集

SaleID   Date         Outcome
1        2020-01-01   New
1        2020-01-01   Complete
2        2020-01-01   New
2        2020-01-02   Complete
3        2020-01-03   New
4        2020-01-03   New
5        2020-01-03   New
5        2020-01-04   Complete

推荐答案

您可以使用条件聚合和累积总和:

You can use conditional aggregation and a cumulative sum:

select date,
       sum(sum(case when outcome = 'New' then 1
                    when outcome  'Complete' then -1
                    else 0
               end)
           ) over (order by date) as VolumeOutstanding
from t
group by date
order by date;

这假设每个 saleId 最多有一个新"记录和一个完整"记录——这在您的示例数据中是有意义的并且是正确的.

This assumes that each saleId has at most one "new" and one "complete" record -- which makes sense and is true in your sample data.

这篇关于SQL 每日未结销售额，滚动汇总?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！