我正在选择groupby和count中使用的列,查询看起来有点像
SELECT s.country, count(*) AS posts_ct
FROM store s
JOIN store_post_map sp ON sp.store_id = s.id
GROUP BY 1;
然而,我想从count为max的存储表中选择更多的字段,比如存储名称或存储地址,但我不想将其包含在group-by子句中。
例如,要获得每个国家/地区投递数量最高的商店:
SELECT DISTINCT ON (s.country)
s.country, s.store_id, s.name, sp.post_ct
FROM store s
JOIN (
SELECT store_id, count(*) AS post_ct
FROM store_post_map
GROUP BY store_id
) sp ON sp.store_id = s.id
ORDER BY s.country, sp.post_ct DESC
将store
中任意数量的列添加到SELECT
列表中。
此相关答案中有关此查询样式的详细信息:
- 选择每个GROUP BY组中的第一行
回复评论
这会产生每个国家的计数,并选择(其中一个)邮政计数最高的商店:
SELECT DISTINCT ON (s.country)
s.country, s.store_id, s.name
,sum(post_ct) OVER (PARTITION BY s.country) AS post_ct_for_country
FROM store s
JOIN (
SELECT store_id, count(*) AS post_ct
FROM store_post_map
GROUP BY store_id
) sp ON sp.store_id = s.id
ORDER BY s.country, sp.post_ct DESC;
这是因为根据定义,窗口函数sum()
在DISTINCT ON
之前应用。