下表包含电话名称、已售商品数量、月份和年份。
with table1 as(
select "iphone" as phone,3 as sold_out,"Jan" as month,2015 as year union all
select "iphone",10,"Feb",2015 union all
select "samsung",4,"March",2015 union all
select "Lava",14,"June",2016 union all
select "Lenova",8,"July",2016 union all
select "Lenova",10,"Sep",2016 union all
select "Motorola",8,"Jan",2017 union all
select "Nokia",7,"Jan",2017 union all
select "Nokia",3,"Feb",2017
)
我会得到这样的答案
-----------------------------
year Phone sales
-----------------------------
2015 iphone 13
2016 lenova 18
2017 Nokia 10
-----------------------------
我没试过因为老实说我不知道
以下是 BigQuery 标准 SQL
#standardSQL
SELECT
year,
ARRAY_AGG(STRUCT(phone, sales) ORDER BY sales DESC LIMIT 1)[OFFSET(0)].*
FROM (
SELECT year, phone, SUM(sold_out) sales
FROM `project.dataset.table1`
GROUP BY year, phone
)
GROUP BY year
您可以使用您的问题中的虚拟数据进行测试/播放，如下所示
#standardSQL
WITH `project.dataset.table1` AS(
SELECT "iphone" AS phone,3 AS sold_out,"Jan" AS month,2015 AS year UNION ALL
SELECT "iphone",10,"Feb",2015 UNION ALL
SELECT "samsung",4,"March",2015 UNION ALL
SELECT "Lava",14,"June",2016 UNION ALL
SELECT "Lenova",8,"July",2016 UNION ALL
SELECT "Lenova",10,"Sep",2016 UNION ALL
SELECT "Motorola",8,"Jan",2017 UNION ALL
SELECT "Nokia",7,"Jan",2017 UNION ALL
SELECT "Nokia",3,"Feb",2017
)
SELECT
year,
ARRAY_AGG(STRUCT(phone, sales) ORDER BY sales DESC LIMIT 1)[OFFSET(0)].*
FROM (
SELECT year, phone, SUM(sold_out) sales
FROM `project.dataset.table1`
GROUP BY year, phone
)
GROUP BY year
ORDER BY year
结果
Row year phone sales
1 2015 iphone 13
2 2016 Lenova 18
3 2017 Nokia 10
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句