--取分组中出现频率最高的值或表达式, 如果最高频率的值有多个, 则随机取一个.
mode() WITHIN GROUP (ORDER BYsort_expression)
postgres=# create table test(id int, info text);
CREATE TABLE
postgres=# insert into test values (1,'test1');
INSERT 0 1
postgres=# insert into test values (1,'test1');
INSERT 0 1
postgres=# insert into test values (1,'test2');
INSERT 0 1
postgres=# insert into test values (1,'test3');
INSERT 0 1
postgres=# insert into test values (2,'test1');
INSERT 0 1
postgres=# insert into test values (2,'test1');
INSERT 0 1
postgres=# insert into test values (2,'test1');
INSERT 0 1
postgres=# insert into test values (3,'test4');
INSERT 0 1
postgres=# insert into test values (3,'test4');
INSERT 0 1
postgres=# insert into test values (3,'test4');
INSERT 0 1
postgres=# insert into test values (3,'test4');
INSERT 0 1
postgres=# insert into test values (3,'test4');
INSERT 0 1
postgres=# select * from test;
id | info
----+-------
1 | test1
1 | test1
1 | test2
1 | test3
2 | test1
2 | test1
2 | test1
3 | test4
3 | test4
3 | test4
3 | test4
3 | test4
(12 rows)
取出所有数据中, 出现频率最高的info, 有可能是test1也有可能是test4, 因为他们的出现频率一致.
mode的返回结果数据类型和order by后面的表达式一致.
postgres=# select mode() within group (order by info) from test;
mode
-------
test1
(1 row)
如果按INFO来分组的话, 取出出现频率最高的info, 实际上这个操作是没有任何意义的, 返回值就是所有记录的info的唯一值.
postgres=# select mode() within group (order by info) from test group by info;
mode
-------
test1
test2
test3
test4
(4 rows)
按id来分组, 取出组内出现频率最高的info值, 这个是有意义的.
postgres=# select mode() within group (order by info) from test group by id;
mode
-------
test1
test1
test4
(3 rows)
id=1 , 出现频率最高的info是test1. 出现2次.
如下 :
postgres=# select id,info,count(*) from test group by id,info;
id | info | count
----+-------+-------
1 | test1 | 2
1 | test3 | 1
3 | test4 | 5
1 | test2 | 1
2 | test1 | 3
(5 rows)
如果要返回mode()并返回频率次数. 可以使用row_number()窗口来实现. 如下.
postgres=# select id,info,cnt from (select id,info,cnt,row_number() over(partition by id order by cnt desc) as rn from (select id,info,count(*) cnt from test group by id,info) t) t where t.rn=1;
id | info | cnt
----+-------+-----
1 | test1 | 2
2 | test1 | 3
3 | test4 | 5
(3 rows)
其他, mode的返回结果数据类型和order by后面的表达式一致.
postgres=# select mode() within group (order by id) from test;
mode
------
3
(1 row)
postgres=# select mode() within group (order by id+1) from test;
mode
------
4
(1 row)
