汇总值取决于其他字段

debugcn 发表于 Dev

弗朗索瓦·泽维尔·格塞尔

我有一个带有日期时间和倍数的表，该表对我进行分组和汇总的某些属性进行了查询，就像上周让我获得每位客户的收入一样。现在，我想查看请求的时间段与上一个时间段之间的变化，因此我将获得2列的收益和previous_revenue。

现在，我正在请求所请求时段的行以及上一个时段的行，并且对于每个聚合字段，我在其中添加了一个case语句，该语句返回值；如果不是我想要的时段，则返回0。

这导致与聚合字段一样多的CASE，但始终带有相同的条件语句。

我想知道这种用例是否有更好的设计...

SELECT
customer,
SUM(
  CASE TIMESTAMP_CMP('2016-07-01 00:00:00', ft.date) > 0 WHEN true THEN 
    REVENUE
  ELSE 0 END
) AS revenue,
SUM(
  CASE TIMESTAMP_CMP('2016-07-01 00:00:00', ft.date) < 0 WHEN true THEN 
    REVENUE
  ELSE 0 END
) AS previous_revenue

WHERE date_hour >= '2016-06-01 00:00:00'
AND date_hour <= '2016-07-31 23:59:59'
GROUP BY customer

（在我的实际用例中，我有很多列使其变得更加难看）

坚果

首先，我建议重构时间戳，并预先计算当前和上一时期，以备后用。但是，这并不是解决您的问题所必需的：

create temporary table _period as
    select
        '2016-07-01 00:00:00'::timestamp as curr_period_start
      , '2016-07-31 23:59:59'::timestamp as curr_period_end
      , '2016-06-01 00:00:00'::timestamp as prev_period_start
      , '2016-06-30 23:59:59'::timestamp as prev_period_end
;

现在，为了避免重复使用时间戳和CASE语句，一种可能的设计是先对句点进行分组，然后再FULL OUTER JOIN对该表本身进行操作：

with _aggregate as (
    select
        case
          when date_hour between prev_period_start and prev_period_end then 'previous'
          when date_hour between curr_period_start and curr_period_end then 'current'
        end::varchar(20) as period
      , customer
   -- < other columns to group by go here >
      , sum(revenue)     as revenue
   -- < other aggregates go here >
    from
        _revenue, _period
    where
        date_hour between prev_period_start and curr_period_end
    group by 1, 2
)
select
    customer
  , current_period.revenue  as revenue
  , previous_period.revenue as previous_revenue
from
                (select * from _aggregate where period = 'previous') previous_period
full outer join (select * from _aggregate where period = 'current')  current_period

using(customer) -- All columns which have been group by must go into the using() clause:
                -- e.g. using(customer, some_column, another_column)
;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。