sql分组统计“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;