sql分组统计“groupby”

  • Z时代
  • 2024-01-10
  • 分类：综合

database

准备数据

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

按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;

以上是 sql分组统计“groupby” 的全部内容， 来源链接： utcz.com/z/532451.html

回到顶部