如何在 CASE 语句中使用 GROUP BY 创建可能重叠的多个条件

Question

我在 BigQuery 中有一个查询：

WITH
date_params AS(
SELECT
DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -7 day), WEEK(MONDAY)) AS WeekStartDate,
DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -1 day), WEEK(SUNDAY)) AS WeekEndDate,
date "2020-01-01" AS ReportStartDate,
),
finances AS (
SELECT
Amount,
CASE
WHEN DATE (t1.Created ) BETWEEN WeekStartDate AND WeekEndDate THEN "Weelky"
WHEN DATE (t1.Created ) BETWEEN ReportStartDate
AND WeekEndDate THEN "Cumulative"
END
AS ReportPeriod
FROM
Table_1 t1,
date_params p )
SELECT
SUM(Amount) AS `Amount`
FROM
finances
GROUP BY
ReportPeriod

您能否解释一下如何在两个时间段之间对结果金额进行分组：1(上周和2(从某个日期(报告开始日期(开始，正如我试图做的那样。唯一的问题是"累积"的 CASE 语句包括"Weelky"中的日期，但我需要从 ReportStartDate 到上周日(包括上周(。

Answer 1

CASE 只会为每行返回一个值，这就是为什么Weelky中包含的行不包含在Cumulative中的原因。有多种方法可以使单个行同时在报表中都可用。我认为可以更好地保留现有查询结构的方法是以下内容，它构造一个数组，以便能够使用多个报告标记单行，然后取消嵌套该数组。我希望这对你有用！

WITH
date_params AS(
SELECT
DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -7 day), WEEK(MONDAY)) AS WeekStartDate,
DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -1 day), WEEK(SUNDAY)) AS WeekEndDate,
date "2020-01-01" AS ReportStartDate,
),
finances AS (
SELECT
Amount,
ARRAY_CONCAT(
IF (DATE (t1.Created) BETWEEN WeekStartDate AND WeekEndDate,["Weelky"],[]),
IF (DATE (t1.Created) BETWEEN ReportStartDate AND WeekEndDate,["Cumulative"],[])
) as ReportPeriod
FROM
Table_1 t1,
date_params p )
SELECT
SUM(Amount) AS `Amount`
FROM
finances
CROSS JOIN unnest(ReportPeriod) as ReportPeriod
GROUP BY
ReportPeriod

Answer 2

您可以从 ReportStartDate 开始您的"累积"，并在 WeekStartDate 之前结束。

CASE 
WHEN DATE (t1.Created ) BETWEEN WeekStartDate
AND WeekEndDate then "Weelky"
WHEN DATE (t1.Created ) BETWEEN ReportStartDate
AND WeekStartDate-1 then "Cumulative"
END 
AS ReportPeriod