sql:如何为select语句中的特定聚合函数应用where子句过滤器?

8aqjt8rx  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(293)

对于不完整的数据集,我需要执行聚合函数,同时避免为同一个表中的特定聚合函数设置null。例如,我尝试将其放入子查询中:

WITH spend AS (
  SELECT
    (SUM(current_spend)-SUM(baseline_spend))/SUM(baseline_spend) percent_change_spend
  FROM 
   `table_one`
  WHERE
    baseline_spend IS NOT NULL
),

sales AS (
  SELECT
    (SUM(current_sales)-SUM(baseline_sales))/SUM(baseline_sales) percent_change_sales
  FROM
    `table_one`
  WHERE
    baseline_sales IS NOT NULL
)

SELECT
    percentage_change_spend,
    percentage_change_sales
FROM
    table_one ...

我不能使用常规查询作为应用程序 WHERE baseline_spend IS NOT NULL 或者 baseline_sales IS NOT NULL 会影响两个聚合函数,因为它首先过滤整个数据集。
有什么建议吗?谢谢!
编辑:
例如,我希望在聚合时,spend聚合只忽略第4行,sales聚合只忽略第2行

我希望最终结果是:

nr9pn0ug

nr9pn0ug1#

聚合函数忽略 NULL 所以这应该是你想要的:

SELECT (SUM(current_spend)-SUM(baseline_spend)) / SUM(baseline_spend) as percent_change_spend,
       (SUM(current_sales)-SUM(baseline_sales)) / SUM(baseline_sales) as percent_change_sales
FROM `table_one`

相关问题