postgresql 查找按类别分组的不同列之和的最大值

yduiuuwa  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(149)

这是我的数据集

我想知道每个国家最受欢迎的产品是什么。
我试过

SELECT
    "Country", 
    MAX(Ft, Vg, Mt),
    SUM("Fruit") AS Ft,
    SUM("Veg") AS Vg,
    SUM("Meat") AS Mt,
FROM
    "My_Data_Set"
GROUP BY
    ("Country");

但什么都做不了。

rxztt3cl

rxztt3cl1#

Schema(MySQL v8.0)

CREATE TABLE customers (
    CustomerId SERIAL PRIMARY KEY,
    Country VARCHAR(255),
    Fruit INTEGER,
    Veg INTEGER,
    Meat INTEGER
);

INSERT INTO customers (CustomerId, Country, Fruit, Veg, Meat) VALUES
(1, 'Spain', 100, 25, 60),
(2, 'USA', 20, 50, 150),
(3, 'India', 25, 20, 15),
(4, 'Germany', 35, 46, 75),
(5, 'Spain', 22, 36, 50),
(6, 'USA', 12, 34, 56),
(7, 'India', 67, 22, 38),
(8, 'Germany', 21, 45, 18);

查询

with cus as (
  select 
    distinct country, 
    sum(fruit) over(partition by country) as fruit, 
    sum(veg) over (partition by country) as veg, 
    sum(meat) over (partition by country) as meat 
  from 
    customers
) 
select 
  country, 
  case when fruit > veg 
  AND fruit > meat THEN 'fruit' WHEN veg > fruit 
  AND veg > meat THEN 'veg' WHEN meat > fruit 
  AND meat > veg THEN 'meat' end as popular_product 
from 
  cus;

| 国家|流行产品|
| --|--|
| 德国|肉|
| 印度|水果|
| 西班牙|水果|
| 美国|肉|
View on DB Fiddle

相关问题