代码之家 › 专栏 › 技术社区 › Adelin

如何查找表的top或max列值?

greatest-n-per-group oracle sql

Adelin · 技术社区 · 6 年前

下表是某种http请求日志:

+---------+----------+-----------+----------+
| County  |  Mobile  |    OS     | Browser  |
+---------+----------+-----------+----------+
| USA     | iPhone   | iOS       | Safar    |
| UK      | Samsung  | Android   | Chrome   |
| India   | Motorola | Android   | Chrome   |
| India   | Nokia    | Android   | Chrome   |
| Russia  | Nokia    | Microsoft | Edge     |
| Germany | Sony     | Android   | Chrome   |
| India   | Sony     | Android   | Chrome   |
| Russia  | Nokia    | Microsoft | Edge     |
| Germany | Sony     | Android   | Chrome   |
| India   | Motorola | Android   | Chrome   |
| Russia  | Nokia    | Android   | Edge     |
| Germany | Sony     | Android   | Chrome   |
+---------+----------+-----------+----------+

如何找到每个县最常用的手机、操作系统和浏览器,并得到如下结果:

+---------+----------+-----------+----------+
| County  |  Mobile  |    OS     | Browser  |
+---------+----------+-----------+----------+
| USA     | iPhone   | iOS       | Safar    |
| UK      | Samsung  | Android   | Chrome   |
| India   | Motorola | Android   | Chrome   |
| Russia  | Nokia    | Microsoft | Edge     |
| Germany | Sony     | Android   | Chrome   |
+---------+----------+-----------+----------+

所以美国最常用的手机是iPhone,最常用的操作系统是iOs,最常用的浏览器是Safar等等。。。

2 回复 | 直到 6 年前

D-Shih 6 年前

如果你的DBMS支持window函数,你可以试试这个。

尝试使用 CTE 写入子查询获取 COUNT 通过 County , Mobile , OS Browser 颜色和用途 row_number 行编号依据 cnt 然后得到 rn=1 .

CREATE TABLE T(
   County VARCHAR(50),
     Mobile VARCHAR(50),
     OS VARCHAR(50),
     Browser VARCHAR(50)
);


INSERT INTO T VALUES ('USA','iPhone', 'iOS', 'Safar');
INSERT INTO T VALUES ('UK','Samsung', 'Android' , 'Chrome');
INSERT INTO T VALUES ('India','Motorola','Android' , 'Chrome');
INSERT INTO T VALUES ('India','Nokia','Android', 'Chrome');
INSERT INTO T VALUES ('Russia','Nokia','Microsoft', 'Edge');
INSERT INTO T VALUES ('Germany','Sony','Android','Chrome');
INSERT INTO T VALUES ('India','Sony','Android','Chrome');
INSERT INTO T VALUES ('Russia','Nokia','Microsoft','Edge');
INSERT INTO T VALUES ('Germany','Sony','Android','Chrome');
INSERT INTO T VALUES ('India','Motorola','Android','Chrome');
INSERT INTO T VALUES ('Russia','Nokia','Android','Edge');
INSERT INTO T VALUES ('Germany','Sony','Android','Chrome');

查询1 :

with cte as (
  select County,Mobile,OS,Browser,COUNT(*) cnt
  from T
  GROUP BY County,Mobile,OS,Browser
)
SELECT 
    t1.County,
    t1.Mobile,
    t1.OS,
    t1.Browser 
FROM (
  select t1.*,row_number() over(partition by County order by cnt desc) rn
  from cte t1
) t1
where t1.rn = 1
ORDER BY COUNTY desc

Results :

|  COUNTY |   MOBILE |        OS | BROWSER |
|---------|----------|-----------|---------|
|     USA |   iPhone |       iOS |   Safar |
|      UK |  Samsung |   Android |  Chrome |
|  Russia |    Nokia | Microsoft |    Edge |
|   India | Motorola |   Android |  Chrome |
| Germany |     Sony |   Android |  Chrome |

Fahmi 6 年前

select * from 
(select max(rn) rn1 ,country
from
(
select *, row_number over partition by (Mobile, OS,Browser order by County) as rn
from tablename)a)x inner join 
(
select *, row_number over partition by (Mobile, OS,Browser order by County) as rn
from tablename)b on b.rn=x.rn1 and b.country=x.country