在案例陈述中处理重叠条件

Question

SELECT (CASE WHEN ymd BETWEEN CURRENT_DATE -4 AND CURRENT_DATE -1 THEN '3day total' 
WHEN ymd BETWEEN CURRENT_DATE -11 AND CURRENT_DATE -1 THEN '10day total' 
WHEN ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1 THEN '30day total' END) AS 'Period',
SUM(cost) cost
FROM table
WHERE ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1
GROUP BY 1

结果似乎给了我一到三天，4-10天和11-30天的周期桶。我认为这是因为存在重叠条件，一旦满足第一个条件，SQL就停止处理CASE语句。

我想要的是每个水桶的总数(3天，10天和30天)。

有没有一种方法可以做到这一点而不添加额外的字段？

PS -语法与传统sql略有不同，因为它是vsql (vertica)。

Answer 1

如果您想将它保持为三行，其中有一列，下面是一种方法：

SELECT which,
       sum(CASE WHEN ymd BETWEEN CURRENT_DATE - diff AND CURRENT_DATE -1 THEN cost
                else 0
           end) as cost
FROM table t cross join
     (select '3day total' as which, 4 as diff union all
      select '10day total', 11 union all
      select '30day total', 31
     ) w
WHERE ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1
GROUP BY which;

编辑：

cross join通过为原始表中的每一行创建三行来工作。这些行中的每一行都为每个组考虑--因此重叠不是问题。这些组由它们的名称和diff值定义。

换句话说，如果数据从7天前的一行开始：

ymd                  cost
CURRENT_DATE - 7      $10

然后，它被cross join乘以3。

which         diff       ymd                 cost
3day total      4        CURRENT_DATE - 7     $10
10day total    11        CURRENT_DATE - 7     $10
30day total    31        CURRENT_DATE - 7     $10

然后，当这些值被which聚合时，计算出正确的值：

which         Total
3day total      $0
10day total    $10
30day total    $10

Answer 2

生成三个总计，而不是一个总计，并使用该条件来确定每个记录的计数位置，而不是试图为单个总计确定一个时间段：

select
  sum(case when ymd between CURRENT_DATE - 4 and CURRENT_DATE - 1 then cost else 0 end) as '3day total',
  sum(case when ymd BETWEEN CURRENT_DATE - 11 and CURRENT_DATE - 5 then cost else 0 end) as '10day total',
  sum(case when ymd BETWEEN CURRENT_DATE - 31 and CURRENT_DATE - 12 then cost else 0 end) as '30day total'
from
  table
and
  ymd between CURRENT_DATE -31 and CURRENT_DATE -1
group by
  1

注意:我不确定您想要计算在哪里的日期范围，所以我将它们设置为不重叠，因为如果您使用了between，这是最有意义的。如果您仍然希望它们重叠，那么您可以只进行比较，而不是使用between，因为任何比CURRENT_DATE - 1更晚的值都已经过滤掉了。

编辑：

要在行中获得结果，可以在选择之间进行合并：

select '3day total' as period, sum(cost) as cost
from table
where ymd between CURRENT_DATE - 4 and CURRENT_DATE - 1
union all
select '10day total', sum(cost)
from table
where ymd BETWEEN CURRENT_DATE - 11 and CURRENT_DATE - 5
union all
select '30day total', sum(cost)
from table
where ymd BETWEEN CURRENT_DATE - 31 and CURRENT_DATE - 12

Answer 3

试着为每个水桶设置一个不同的字段。我假设您想要将每个桶的成本相加，如果您想要和一个不同的字段，则可能需要更改THEN cost。

查询中还缺少一个WHERE子句。条件从一个AND语句开始，您可能想要纠正这一点。

SELECT SUM(CASE WHEN ymd BETWEEN CURRENT_DATE -4 AND CURRENT_DATE -1 THEN cost ELSE 0 END) as '3day total',
SUM(CASE WHEN ymd BETWEEN CURRENT_DATE -11 AND CURRENT_DATE -1 THEN cost ELSE 0 END) as '10day total', 
SUM(CASE WHEN ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1 THEN cost ELSE 0 END) as  '30day total' END) AS 'Period'
FROM table
WHERE ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1
GROUP BY 1