根据聚合函数选择最高值-SQL

Question

我有一个GiftSales表，它包含项目的id(giftId(和该项目的类别(categoryId(我需要得到每个类别最畅销的商品。现在我的查询看起来像这个

SELECT giftId, categoryId, COUNT(giftId) as Total
FROM GiftSales
GROUP BY giftId, categoryId

它给了我

==================================
|| giftId || categoryId || Total||
==================================
||    1   ||      1     ||   8  ||
==================================
||    2   ||      1     ||   5  ||
==================================
||   23   ||      2     ||   12 ||
==================================

我只需要显示每个类别的最高值，所以基本上，表不应该包含第二项。

Answer 1

我建议使用窗口函数，当按类别查看最畅销的产品时，dense_rank会很有帮助，因为您可能希望包括任何联系。

架构(MySQL v8.0(

CREATE TABLE IDs (
`gift_id` INTEGER,
`category_id` INTEGER
);
INSERT INTO IDs
(`gift_id`, `category_id`)
VALUES
('1', '1'),
('1', '1'),
('1', '1'),
('1', '1'),
('1', '1'),
('1', '1'),
('1', '1'),
('1', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('2', '1');

---

查询#1

select a.category_id,a.gift_id,a.total from (
select
category_id,
gift_id,
count(gift_id) as total,
dense_rank() over (partition by category_id order by count(gift_id) desc) as ranking
from IDs group by 1,2) as a where ranking = 1;

总计8

category_id	gift_id
1	2

每个类别使用MAX OVER等窗口函数：

select giftid, categoryid, total
from
(
select 
giftid,
categoryid,
count(*) as total,
max(count(*)) over (partition by categoryid) as category_max
from giftsales
group by giftid, categoryid
) aggregated
where total = category_max;

SELECT DISTINCT categoryId, MAX(Total) as total FROM(
SELECT giftId, categoryId, COUNT(giftId) as Total FROM GiftSales GROUP BY giftId, categoryId
) AS T GROUP BY giftId, categoryId;

我通过使用带有categoryId的distinct来实现它，由于你需要按类别计算总数，我删除了giftId，一切都很好：(我用了一个操场来测试这个，操场可以在这里找到->https://www.db-fiddle.com/f/qsGLKUZyos2ZKTftykkazd/0