从表中查找搜索次数最多的国家/地区和 ip

Question

我有一个表格"用户"，其中包含ip，os，国家和浏览器列。我想找到最大计数的ip，os，国家和浏览器。PostgreSQL中是否有任何查询

我正在使用的当前查询是

SELECT *
FROM
(
SELECT COUNT(ip),ip FROM user GROUP BY ip 
UNION ALL
SELECT COUNT(os),os FROM user GROUP BY os 
UNION ALL
SELECT COUNT(country),country FROM user GROUP BY country 
UNION ALL
SELECT COUNT(browser),browser FROM user GROUP BY browser
) user 

它显示所有IP，操作系统，国家和浏览器及其数量 我真正想要的是一个列名，该列的最大计数

是否可以在单个查询中执行此操作？

我期待这样的事情

os      count           ip              count
linux     50         xx:xx:xx:xx      95

Answer 1

SELECT *
FROM
(SELECT COUNT(ip) as cnt_ip, ip FROM user GROUP BY ip ORDER BY 1 DESC LIMIT 1) as t_ip,
(SELECT COUNT(os) as cnt_os, os FROM user GROUP BY os ORDER BY 1 DESC LIMIT 1) as t_os,
(SELECT COUNT(country) as cnt_country, country FROM user GROUP BY country ORDER BY 1 DESC LIMIT 1) as t_country,
(SELECT COUNT(browser) as cnt_browser, browser FROM user GROUP BY browser ORDER BY 1 DESC LIMIT 1) as t_browser

Answer 2

您可以使用HAVING和ALL。出于可读性目的，我将只显示一列

SELECT COUNT(ip),ip 
FROM user 
GROUP BY ip 
HAVING COUNT(ip) >= all
(
SELECT COUNT(ip)
FROM user 
GROUP BY ip 
)