sql - 有差距的运行总和

Question

我有下表，其中包含除黄色列以外的所有列

基本上，该表包含客户的 ID、销售发生的日期以及客户当天花费的总金额(销售额)。现在我必须计算当天每个客户在某个时间范围内的累计销售额，包括当天的销售额。例如，将时间范围设置为 3 天客户 2233 购买了两次(14 日什么也没买)，因此他在 15 日的累计销售额为 26，而在 13 日为 25。

我无法创建新表，所以我尝试了这种方法，但速度很慢

SELECT t.dt,

Count(CASE WHEN t.running_sale < 1.99 THEN 1 ELSE NULL END) as "Low spender",
Count(CASE WHEN t.running_sale BETWEEN 1.99 and 4.99 THEN 1 ELSE NULL END) as "Medium spender",
Count(CASE WHEN t.running_sale > 4.99 THEN 1 ELSE NULL END) as "High spender"

FROM  ( SELECT dt, channel, id, (
     SELECT SUM(revenue)
     FROM  myTable rd
     WHERE CAST(rd.dt AS DATE) 
             BETWEEN (CAST(rd.dt AS DATE) - INTERVAL '3' DAY) AND CAST(rd.dt AS DATE) AND 
           rd.id = r.id 
  ) running_sale from myTable r) t

WHERE channel = 'retail' 
AND dt BETWEEN '2017-06-01' AND '2017-06-15'

GROUP BY dt
limit 100

Answer 1

我会为此使用子查询

select *,
  (
     select sum(sales)
     from your_table dd
     where cast(dd.dates as date) 
             between cast(your_table.dates as date) - interval '3' day and 
                     cast(your_table.dates as date) and 
           dd.id = your_table.id
  ) running_sales
from your_table

demo

上面的查询可以重写成一个更高效的查询，它只使用自连接和group by

 select dd.id, dd.dates, dd.sales, sum(d.sales) running_sales
 from your_table dd
 join your_table d on cast(d.dates as date) 
         between (cast(dd.dates as date) - interval '3' day) and cast(dd.dates as date) and 
       dd.id = d.id
 group by dd.id, dd.dates, dd.sales

group by demo

您可以考虑创建以下索引来支持上述查询:

create index ix_your_table on your_table(id, dates, sales)