使用 sql 窗口函数对过去 n 天的数量求和 [英] sum last n days quantity using sql window function

问题描述

我正在尝试在 Alteryx 中创建以下逻辑，并且数据来自 Exasol 数据库.

I am trying to create following logic in Alteryx and data is coming from Exasol database.

Sum_Qty_28_days"列应汇总过去 28 天内同一文章的Qty"列的值.

Column "Sum_Qty_28_days" should sum up the values of "Qty " column for same article which falls under last 28 days.

我的示例数据如下所示:

My sample data looks like:

我想要以下输出:

例如article" = ‘A’ and date = ‘’2019-10-8" 的 Sum_Qty_28_days" 值为 8，因为它汇总了与日期(前 28 天内)相关的Qty"值，它们是:2019-09-152019-10-052019-10-08对于文章"='A'.

E.g. "Sum_Qty_28_days" value for "article" = ‘A’ and date = ‘’2019-10-8" is 8 because it is summing up the "Qty" values associated with dates (coming within previous 28 days) Which are: 2019-09-15 2019-10-05 2019-10-08 for "article" = ‘A’.

这可以使用 SQL 窗口函数吗?我尝试了以下代码:

Is this possible using SQL window function? I tried myself with following code:

SUM("Qty") OVER (PARTITION BY "article", date_trunc('month',"Date")
             ORDER BY "Date")

但是，这远不是我所需要的.它总结了同一个月内日期的数量.但是，我需要总结过去 28 天的数量.

But, it is far from what I need. It is summing up the Qty for dates falling in same month. However, I need to sum of Qty for last 28 days.

提前致谢.

推荐答案

如果您的 RDBMS 不支持 range 框架，另一种解决方案是使用内联子查询:

If your RDBMS does not support the range frame, an alternative solution is to use an inline subquery:

select 
    t.*,
    (
        select sum(t1.qty) 
        from mytable t1 
        where 
            t1.article = t.article
            and t1.date between t.date - interval 28 days and t.date
    ) sum_qty_28_days
from mytable t

这篇关于使用 sql 窗口函数对过去 n 天的数量求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！