基于列条件的和

Question

我正在试着计算下一个30天的销售额。

这是我的数据。

<表类> 日期 销售 tbody><<tr>2021-08-08352021-08-08142021-08-11352021-09-09222021-09-21442021-10-16462021-10-2592021-10-2512021-10-2522021-10-2562021-11-0412021-11-071

这里有一种使用递归cte的方法。

with data
as (select start_dt,sales,row_number() over(order by start_dt) as rnk       
from t1
)
,cte_data
as ( select d.start_dt,d.sales,d.rnk,d.start_dt as grp_dt
from data d
where rnk=1
union all
select d.start_dt,d.sales,d.rnk,case when datediff(day,d2.grp_dt,d.start_dt)>30 then 
d.start_dt
else d2.grp_dt
end
from cte_data d2
join data d
on d.rnk=d2.rnk+1
)
select min(start_dt) start_range,max(start_dt) end_range,sum(sales) as sum_sales
from cte_data
group by grp_dt
+-------------+------------+-----------+
| start_range | end_range  | sum_sales |
+-------------+------------+-----------+
| 2021-08-08  | 2021-08-11 |        84 |
| 2021-09-09  | 2021-09-21 |        66 |
| 2021-10-16  | 2021-11-07 |        66 |
+-------------+------------+-----------+

sql fiddle linkhttps://dbfiddle.uk/2LmQe5n5

详细说明一下。我们需要某种递归过程来做到这一点，因为下一行的结果取决于前一个输入日期，所以我怀疑我们可以使用窗口函数来做到这一点。

似乎您有一个重叠组的问题，您可以使用自连接和聚合如下:

SELECT T.date, T.sales,
SUM(D.sales) AS total_30d 
FROM
test_table T JOIN test_table D
ON D.date BETWEEN T.date AND T.date + interval '30' day
GROUP BY T.date, T.sales
ORDER BY T.date, T.sales

查看演示

直接使用标量子查询:

select ext.*, 
(
select sum(sales) from the_table
where "date" between ext.date and ext.date + 30
) as total_30d 
from the_table as ext;

DB-fiddle演示。

<表类>日期销售和tbody><<tr>2021-08-0835842021-08-0814842021-08-1135572021-09-0922662021-09-2144902021-10-1646662021-10-259202021-10-251202021-10-252202021-10-256202021-11-04122021-11-0711