PostgreSQl数据库几个查询问题

最新推荐文章于 2024-12-08 11:20:34 发布

fcy0306

最新推荐文章于 2024-12-08 11:20:34 发布

阅读量359

点赞数 1

分类专栏： PostgreSQL

本文链接：https://blog.youkuaiyun.com/jingzhe0306/article/details/108533593

版权

PostgreSQL 专栏收录该内容

1 篇文章

订阅专栏

微服务是根据project_name区分的，用户是用user_id区分的。

1.各个微服务每天请求量统计；

 SELECT project_name,COUNT(project_name) FROM log_info
            WHERE gmt_create between '2020-09-04 00:00:00' and '2020-09-04 23:59:59'
            GROUP BY project_name
            having count(project_name)>=0
            order by count(project_name) desc;

在这里插入图片描述

2.各个微服务每天的活跃用户量统计；

 select project_name,count(distinct(user_id)) from log_info
             WHERE gmt_create between '2020-09-04 00:00:00' and '2020-09-04 23:59:59'
             group by project_name
             having count(project_name)>=0
             order by count(distinct(user_id)) desc;

在这里插入图片描述

3.各个微服务每小时请求量统计；

  select project_name,to_char(gmt_create,'hh24')as hourly,count(project_name)
      from  log_info
      WHERE gmt_create between '2020-09-04 00:00:00' and '2020-09-04 23:59:59'
      group by project_name,to_char(gmt_create,'hh24')
      having count(project_name)>=0

在这里插入图片描述

4.各个微服务每小时的活跃用户量统计；

 select project_name,to_char(gmt_create,'hh24') as hourly,count(DISTINCT(user_id))
    from  log_info
    WHERE gmt_create between '2020-09-04 00:00:00' and '2020-09-04 23:59:59'
    group by project_name,to_char(gmt_create,'hh24');

在这里插入图片描述

5.所有数据里每天最慢的30个接口；

 select  class_name,max(log_info.gmt_create-log_info.gmt_end) AS times
    FROM log_info
    WHERE gmt_create between '2020-09-04 00:00:00' and '2020-09-04 23:59:59'
    GROUP BY class_name
    ORDER BY times desc limit 30;

在这里插入图片描述

6.所有数据里每个微服务每天最慢的30个接口；

 select * from (select project_name,class_name,Max(log_info.gmt_create-log_info.gmt_end) AS times
      FROM log_info WHERE gmt_create between '2020-09-04 00:00:00' and '2020-09-04 23:59:59'
      GROUP BY project_name,class_name) a
      where (select count(1) from
      (select project_name,class_name,Max(log_info.gmt_create-log_info.gmt_end) AS times
      FROM log_info WHERE gmt_create between '2020-09-04 00:00:00' and '2020-09-04 23:59:59' GROUP BY
      project_name,class_name) b where a.project_name=b.project_name and b.times>a.times)<![CDATA[ < ]]>30
      ORDER BY a.project_name,a.times desc