sql - 相对于固定手动列值条件的列的总和

Question

这是我的源数据，其中 TIMESTAMP_WID 指的是 YYYYMMDD

我试图实现的输出结果如下，

逻辑如下，

CUR_TIMESTAMP指，

TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'MON'),-2),'YYYYMMDD'))

到目前为止我尝试过的代码如下，

(SELECT 
OUTLET, 
SUM(SALES_VALUE), 
TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'MON'),-2),'YYYYMMDD')) AS CUR_TIMESTAMP
FROM SOME_TABLE)
UNION
(SELECT 
OUTLET, 
SUM(SALES_VALUE), 
TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'MON'),-3),'YYYYMMDD')) AS CUR_TIMESTAMP
FROM SOME_TABLE)
(SELECT 
OUTLET, 
SUM(SALES_VALUE), 
TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'MON'),-4),'YYYYMMDD')) AS CUR_TIMESTAMP
FROM SOME_TABLE)

在上面的 sql 查询中，我可以填充与我的目标相同的内容，但是，在计算SUM(SALES_VALUE)时有一个非常重要的逻辑

什么时候，

CUR_TIMESTAMP=20200101 
Then,
SUM(SALES_VALUE) will be calculated for 
TIMESTAMP_WID IN (20200101,20191201,20191101,20191001,20190901,20191001,20190901,20190801)

什么时候，

CUR_TIMESTAMP=20201201 
Then,
SUM(SALES_VALUE) will be calculated for 
TIMESTAMP_WID IN (20191201,20191101,20191001,20190901,20191001,20190901,20190801,20190701)

什么时候，

CUR_TIMESTAMP=20201101 
Then,
SUM(SALES_VALUE) will be calculated for 
TIMESTAMP_WID IN (20191101,20191001,20190901,20191001,20190901,20190801,20190701,20190601)

任何人请指导我

标签： sqloracle

Answer 1

这听起来像是一个累积和：

select t.*
from (select t.*,
             sum(sales_value) over (partition by outlet order by timestamp_wid) as running_sales_value
      from some_table st
     ) t

然后，您可以添加一个where子句来过滤您想要的行。我认为：

select t.*
from (select t.*,
             sum(sales_value) over (partition by outlet order by timestamp_wid) as running_sales_value
      from some_table st
     ) t
where timestamp_wid >= TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MON'), -4), 'YYYYMMDD')) and
      timestamp_wid < TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MON'), -2), 'YYYYMMDD')) and