java分组还是sql分组_sql分组统计“group by”

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

准备数据

先准备数据，数据如图所示：

按app字段分组

select

fun(logs.id) as c, -- fun表示聚合函数

logs.app

from public.logs as logs

group by logs.app;

计数 count

select

count(logs.id) as c,

logs.app

from public.logs as logs

group by logs.app

order by c desc

limit 10;

求和 sum

select

sum(logs.packets) as s,

logs.app

from public.logs as logs

group by logs.app

order by s desc

limit 10;

求均值 avg

select

avg(logs.packets) as s,

logs.app

from public.logs as logs

group by logs.app

order by s desc

limit 10;

取最大值 max

select

max(logs.packets) as s,

logs.app

from public.logs as logs

group by logs.app

order by s desc

limit 10;

取最小值 max

select

min(logs.packets) as s,

logs.app

from public.logs as logs

group by logs.app

order by s desc

limit 10;

取平均值大于11710, having

select

avg(logs.packets) as s,

logs.app

from public.logs as logs

group by logs.app

having avg(logs.packets) > 11710

order by s desc

limit 10;

大小写 upper 和 lower

select upper(logs.d_region), lower(logs.s_region)

from public.logs as logs

limit 10;

字符串长度

select length(logs.d_region) as length_of_region

from public.logs as logs

limit 10;

取平均值大于11710，并且四舍五入 round

select

round(avg(logs.packets), 2) as s,

logs.app,

now() as date

from public.logs as logs

group by logs.app

HAVING avg(logs.packets) > 11710

order by s desc

limit 10;