使用最大日期和间隔汇总前一天的值 [英] Using max date and interval to aggregate values for previous day

问题描述

我正尝试在案例陈述中使用Max（date）汇总当天的数字，并使用-interval汇总前一天的数字。

I am trying to aggregate current day's numbers using Max(date) and previous day's numbers using -interval all in a case statement as follows.

select  product
      , sum(
            case
            when date_started = max(date_started)
            then volume
            end )
      as "Current day's Volume"
      , sum(
            case
            when date_started = max(date_started)
            then revenue
            end )
      as "Current day's Revenue"
      , sum(
            case
            when date_started = (max(date_started) - interval '1 day' 
            then volume 
             end ) as "previous day's Volume"
      , sum(
            case
            when date_started = (max(date_started) - interval '1 day' 
            then revenue 
             end ) as "Previous day's Revenue"
  from  lifetime_data
 group by 1

这是在PostgreSQL上。

This is on PostgreSQL. It's not working.

示例数据

预期结果

EXPECTED RESULTS

推荐答案

我认为您可以使用子查询和窗口函数来完成所需的操作：

I think you can do what you want with a subquery and window functions:

select product,
       sum(volume) filter (where date_started = max_date_started) as current_day_volume,
       sum(revenue) filter (where date_started = max_date_started) as current_day_revenue,
       sum(volume) filter (where date_started = max_date_started - interval '1 day') as previous_day_volume,
       sum(revenue) filter (where date_started = max_date_started - interval '1 day') as previous_day_revenue
from (select ld.*,
             max(date_started) over (partition by product) as max_date_started
      from lifetime_data ld
     ) ld
group by product;

这篇关于使用最大日期和间隔汇总前一天的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！