给出一张table:
NAME DATE_OCCURRED
a 2020-05-14 23:48.07
a 2020-05-14 23:48.07
a 2020-05-14 23:48.08
b 2020-05-14 23:48.08
b 2020-05-14 23:48.08
b 2020-05-14 23:48.08
b 2020-05-14 23:48.09
我想回来,为每一个 NAME
,记录最多的时间;当时有多少记录:
NAME MAXCOUNT_PER_SECOND DATE_OCCURRED
a 2 2020-05-14 23:48.07
b 3 2020-05-14 23:48.08
我找到了按发生日期和名称对计数进行分组的sql: SELECT COUNT(*) AS COUNT_PER_SECOND, NAME, DATE_OCCURRED FROM TABLE GROUP BY NAME, DATE_OCCURRED ORDER BY NAME ASC, COUNT_PER_SECOND DESC
但我现在要再次选择,只为每个名称选择最大值。我试过: SELECT MAX(COUNT_PER_SECOND) AS MAXCOUNT_PER_SECOND, NAME FROM (the above query) GROUP BY NAME;
这给了我 MAXCOUNT_PER_SECOND
以及 NAME
; 但一旦我试着也得到了 DATE_OCCURRED
结果呢 MAXCOUNT_PER_SECOND
值,则在运行sql时会出现分组错误;或者我没有得到我期望的结果。
即: SELECT MAX(COUNT_PER_SECOND) AS MAXCOUNT_PER_SECOND, NAME, DATE_OCCURRED FROM (the above query) GROUP BY NAME;
->
Not a GROUP BY expression SELECT MAX(COUNT_PER_SECOND) AS MAXCOUNT_PER_SECOND, NAME, DATE_OCCURRED FROM (the above query) GROUP BY NAME, DATE_OCCURRED ;
->运行,但会给我所有日期的结果,而不仅仅是最大值。
1条答案
按热度按时间hgtggwj01#
可以使用聚合和窗口函数:
子查询按聚合
name
以及date_occured
,对每个组的记录进行计数,并对具有相同属性的记录组进行排序name
按降序计数。然后,外部查询根据name
. 因为我们使用rank()
,结果集中将包含可能的顶部关系(如果不需要,请使用row_number()
相反)。