Restar columnas de consultas de BigQuery independientes

Question

Tengo dos consultas de BigQuery separadas en las que tomé la suma de los casos confirmados para una fecha determinada, los agrupé por región y los ordené en orden descendente 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

Quiero calcular la diferencia entre total_casesla primera y la segunda consulta y agrupar y ordenar por región y orden descendente por la diferencia en orden descendente.

Answer 1

A continuación se muestra para SQL estándar de BigQuery

La forma más sencilla es reutilizar las consultas con las que ya se siente cómodo (en lugar de reescribir las cosas)

#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

Esto podría expresarse de manera más eficiente con agregación 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