时间戳

首页 > 解决方案 > 如何找到表格的顶部或最大列值？

sql - 如何找到表格的顶部或最大列值？

问题描述

下表是某种 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 等等......

标签： sqloraclegreatest-n-per-group

解决方案

如果你的 DBMS 支持窗口功能，你可以试试这个。

尝试使用CTEwrite subquery get the COUNTby County, Mobile, OS, Browsercolnums 并使用row_numbermake row number by cntthen get 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

结果：

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

推荐阅读