Subtraindo colunas de consultas separadas do BigQuery

Question

Tenho duas consultas separadas do BigQuery em que peguei a soma dos casos confirmados para uma determinada data e os agrupei por região e os ordenei em ordem decrescente por casos.

SELECT region, SUM(confirmed_cases) AS total_cases FROM provincedata WHERE date BETWEEN '2020-08-01' AND '2020-08-02' GROUP BY region ORDER BY total_cases DESC
SELECT region, SUM(confirmed_cases) AS total_cases FROM provincedata WHERE date BETWEEN '2020-08-31' AND '2020-09-01' GROUP BY region ORDER BY total_cases DESC

Quero calcular a diferença entre total_casesa primeira e a segunda consultas e agrupar e ordenar por região e ordem decrescente pela diferença em ordem decrescente.

Answer 1

Abaixo está o BigQuery Standard SQL

A maneira mais simples é apenas reutilizar suas consultas com as quais você já se sente confortável (em vez de reescrever coisas)

#standardSQL
WITH `project.dataset.query1` AS (
  SELECT region, SUM(confirmed_cases) AS total_cases 
  FROM provincedata 
  WHERE DATE BETWEEN '2020-08-01' AND '2020-08-02' 
  GROUP BY region 
), `project.dataset.query2` AS (
  SELECT region, SUM(confirmed_cases) AS total_cases 
  FROM provincedata 
  WHERE DATE BETWEEN '2020-08-31' AND '2020-09-01' 
  GROUP BY region 
)
SELECT region, q1.total_cases - q2.total_cases AS total_cases_difference
FROM `project.dataset.query1` q1 
JOIN `project.dataset.query2` q2
USING(region)
ORDER BY total_cases_difference DESC

Answer 2

Isso pode ser expresso de forma mais eficiente com agregação condicional:

select
    region,
    sum(case when date between '2020-08-01' and '2020-08-02' then confirmed_cases else 0 end) total_cases_1,
    sum(case when date between '2020-08-31' and '2020-09-02' then confirmed_cases else 0 end) total_cases_2,
    sum(case when date between '2020-08-01' and '2020-08-02' then confirmed_cases else - confirmed_cases end) diff
from provincedata
where 
    date between '2020-08-01' and '2020-08-02'
    or date between '2020-08-31' and '2020-09-01' 
group by region
order by diff desc