SQL排名和求和

Question

我在一个SQL查询中遇到了一些问题：

目前我有两个表。第一个表列出了按供应商和国家划分的销售额，还有更多行，但这只是要点。

Country id     Sale
US      1      100
UK      2      1000
US      3      150 
UK      2      200

在第二个表格中，我有链接到供应商名称的ID，例如：

id name
1  john
2  david 
3  tom 

我需要获取每个国家的最高销售额供应商，但是要求不考虑总销售额。输出结果应该类似于这样：country id name sum_sales。

你能帮忙吗？我目前只能使用groupby和sum函数，无法获得每个国家的最高销售额供应商。谢谢！

我是在BigQuery SQL上运行此查询。

- user13082186

Answer 1

在BigQuery中，您可以使用窗口函数进行聚合：

select id, name, country, sum_sales
from (select s.id, v.name, s.country, sum(sales) as sum_sales
             row_number() over (partition by s.country order by sum(sales) desc) as seqnum
      from sales s join
           vendors v
           on v.id = v.id
      group by s.id, v.name, s.country
     ) sv
where seqnum = 1;

- Gordon Linoff

---

嗨戈登，谢谢你。然而我遇到了这个错误 - SELECT列表表达式引用了v.name，但它既没有进行分组也没有聚合[2:15]。你能帮忙解决吗？ - user13082186

@NatashaLakhpaty . . . 哎呀，所有这些列都应该在 group by 中。 - Gordon Linoff

Answer 2

使用聚合函数 dense_rank() ：

select yr, Country, id, name, total_sales
from (select extract(year from s.date) as yr,
             s.Country, s.id, v.name, sum(s.sales) as total_sales,
             dense_rank() over (partition by s.date, s.country order by sum(s.sales) desc) as seq
      from sales s inner join
           vendors v
           on v.id = v.id
      group by s.date, s.Country, s.id, v.name
     ) t
where seq <= 2;

编辑：要使用特定年份格式，请使用FORMAT_DATETIME。

FORMAT_DATETIME("%Y", DATETIME "2020-03-19")

通过这种方式，您将获得每个国家的销售额更高的供应商。

注意：这将显示两个或更多具有相同总销售额的供应商。如果您只想要其中一个，请使用row_number()而不是dense_rank()。

- Yogesh Sharma

---

谢谢，这很有帮助。我希望可以问一个后续问题。在同一张表中，我还有一个日期列表，并尝试找到每个国家每年销售额前两名的供应商。我已经添加了“extract(year from date)”作为年份，并将“seq”替换为“<=2”，但我没有得到我需要的结果。您能帮忙吗？谢谢！ - user13082186

示例数据为 国家 日期 ID 销售额 英国 15/12/2010 1 100 美国 12/1/2011 2 200供应商表与之前相同 - user13082186

使用的代码是：select Country, id, name, total_sales from (select s.Country, s.id, v.name, sum(s.sales) as total_sales, extract(year from date) as year, dense_rank() over (partition by s.country order by year, sum(s.sales) desc) as seq from sales s inner join vendors v on v.id = v.id group by s.Country, s.id, v.name, extract(year from date) ) t where seq <=2;错误：在第3行未识别名称年份。 - user13082186

@NatashaLakhpaty. . . . 我做了修改。 - Yogesh Sharma

是的，我尝试过了，但出现了错误，无法将int 64转换为datetime。很遗憾。 - user13082186

Answer 3

以下内容适用于BigQuery标准SQL。

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY sum_sale DESC LIMIT 1)[OFFSET(0)]
FROM (
  SELECT country, id, name, SUM(sale) sum_sale
  FROM `project.dataset.vendors`
  JOIN `project.dataset.sales`
  USING(id)
  GROUP BY id, name, country
) t
GROUP BY country

- Mikhail Berlyant

---

你有机会尝试了吗？ - Mikhail Berlyant