Google BigQuery-根据另一列中的值减去一列的SUM

Question

嗨，我需要1个查询来获得2019年至2020年间goods_type药物[总量(import(-总量(export(]最大的前10个country。

数据样本如下：

year | trading_type | country | goods_type | amount
2020   import         ABC       medicines    12345.67
2017   import         ABC       medicines    null
2019   export         DEF       foods        987.65
2018   export         ABC       foods        2345.6
2016   export         DEF       medicines    120.3
2019   export         ABC       medicines    345.67
2020   import         DEF       foods        321.04
...    ...            ...       ...          ...

返回的数据应包括country、goods_type和[total(imports(-total(export(]的值。

我提出了下面的查询，但我不知道它是对是错，我很难扩展它以获得其他列。我在Google BigQuery控制台中告诉select expression column ... not grouped or aggregated...时出错。

SELECT country, year FROM `trading_records` T    <--- error here for the year
WHERE
T.product_type = 'medicines' AND
(T.year = 2019 OR T.year = 2020)
GROUP BY T.country
ORDER BY (
(SELECT SUM(amount) FROM `trading_records`
WHERE trading_type = 'import' AND country = T.country)
-
(SELECT SUM(amount) FROM `trading_records`
WHERE trading_type = 'export' AND country = T.country)
) DESC
LIMIT 10;

我感谢任何帮助！谢谢

Answer 1

您可以将其表示为具有GROUP BY、筛选和条件聚合的单个查询：

SELECT country, 
SUM(CASE WHEN trading_type = 'import' THEN amount ELSE - amount END) as total 
FROM data 
WHERE trading_type in ('import', 'export') AND
goods_type = 'medicines' AND
year >= 2019 AND
year <= 2020
GROUP BY country
ORDER BY total DESC
LIMIT 10;

注意这不包括CCD_ 12中的CCD_；聚集离开"；。

Answer 2

我相信还有其他方法，但这个查询可以满足您的需求：

WITH data as (
SELECT 2020 as year, "import" as trading_type, "ABC" as country, 
"medicines" as goods_type, 12345.67 as amount UNION ALL
SELECT 2019, "import", "ABC", "medicines", null UNION ALL
SELECT 2019, "export", "DEF", "foods", 987.65 UNION ALL
SELECT 2018, "export", "ABC", "foods", 2345.6 UNION ALL
SELECT 2016, "export", "DEF", "medicines", 120.3 UNION ALL
SELECT 2019, "export", "ABC", "medicines", 345.67 UNION ALL
SELECT 2020, "import", "DEF", "foods", 321.04)
,agg_data as ( 
SELECT year, 
country, 
IF(trading_type = "import", amount, amount * -1) as total 
FROM data 
WHERE goods_type = "medicines" AND year in (2019,2020) 
)
SELECT country, SUM(total) as total 
FROM agg_data
GROUP BY country
LIMIT 1

您应该将最后一个LIMIT 1更改为10

结果：ABC 12000.0

Answer 3

您可以尝试使用条件和。例如SUM(IF(Condition，true_value，false_value(。这将首先评估您的状况。如果为True，则True_value(在本例中为金额(将包含在SUM中。如果计算结果为False，则0将被添加到SUM中。

这将给你想要的

SELECT country, goods_type, SUM(IF(trading_type='import', amount, 0)) - SUM(IF(trading_type='export', amount, 0)) as import_minus_export
FROM `trading_records`
WHERE goods_type='medicines' AND year IN (2019, 2020)
GROUP BY 1, 2
ORDER BY 2 DESC 
LIMIT 10