sql - 复杂聚合查询

Question

create temp table tokens (
    id serial not null,
    name text not null,
    locale text not null,
    CONSTRAINT tokens_pkey PRIMARY KEY (id)
);
insert into tokens(name, locale) values 
('e47dd0440c923a77915791ffab7346f4', 'en_US'),
('e47dd0440c923a77915791ffab7346f4', 'en_US'),
('e47dd0440c923a77915791ffab7346f4', 'en_US'),
('e47dd0440c923a77915791ffab7346f4', 'en_US'),
('e47dd0440c923a77915791ffab7346f4', 'en_US'),
('e47dd0440c923a77915791ffab7346f4', 'en_GB'),
('e47dd0440c923a77915791ffab7346f4', 'en_AU'),
('8156773b9da51b7cffb4a1af1a326464', 'de_DE'),
('8156773b9da51b7cffb4a1af1a326464', 'de_DE'),
('8156773b9da51b7cffb4a1af1a326464', 'de_DE'),
('8156773b9da51b7cffb4a1af1a326464', 'de_AT')

select 
    max(cnt) max, name, locale
from (
    select count(id) cnt, name, locale
    from tokens
    group by name, 
    locale
) q
group by
    name, 
    locale
order by 
    max(cnt) desc, 
    name, 
    locale

/*
Result:
max;name;locale
5;e47dd0440c923a77915791ffab7346f4;en_US
3;8156773b9da51b7cffb4a1af1a326464;de_DE
1;8156773b9da51b7cffb4a1af1a326464;de_AT
1;e47dd0440c923a77915791ffab7346f4;en_AU
1;e47dd0440c923a77915791ffab7346f4;en_GB

Expected result:
max;name;locale
5;e47dd0440c923a77915791ffab7346f4;en_US
3;8156773b9da51b7cffb4a1af1a326464;de_DE
*/

我需要最大计数的 token 和语言环境，但问题是如果我按语言环境分组，我会得到错误的结果

Fiddle

Answer 1

select cnt, name, locale from (
    select 
        cnt, name, locale,
        row_number() over (partition by name order by cnt desc) rn
    from (
        select count(id) cnt, name, locale
        from tokens
        group by name, 
        locale
    ) t1
) q where rn = 1 order by cnt desc

http://sqlfiddle.com/#!15/33b36/5