只返回每个组中的最高值

x3naxklr  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(348)

给出一张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 ; ->运行,但会给我所有日期的结果,而不仅仅是最大值。

hgtggwj0

hgtggwj01#

可以使用聚合和窗口函数:

select name, date_occured, no_records
from (
    select 
        name, 
        date_occured, 
        count(*) no_records, 
        rank() over(partition by name order by count(*) desc) rn
    from mytable
    group by name, date_occured
) t
where rn = 1

子查询按聚合 name 以及 date_occured ,对每个组的记录进行计数,并对具有相同属性的记录组进行排序 name 按降序计数。然后,外部查询根据 name . 因为我们使用 rank() ,结果集中将包含可能的顶部关系(如果不需要,请使用 row_number() 相反)。

相关问题