MySQL 聚合函数、分组查询、时间函数详解

目录

• 一、题目：每位教师所教授的科目种类的数量
• 1 准备工作
• 2 分析
• 3 实现 
• 二 、查询近30天活跃用户数
• 1 准备工作
• 2 分析 
• 3 实现 

一、题目：每位教师所教授的科目种类的数量

本题主要考验聚合函数count（）求和以及分组查询。

1 准备工作

Create table If Not Exists Teacher (teacher_id int, subject_id int, dept_id int);
Truncate table Teacher;
insert into Teacher (teacher_id, subject_id, dept_id) values ('1', '2', '3');
insert into Teacher (teacher_id, subject_id, dept_id) values ('1', '2', '4');
insert into Teacher (teacher_id, subject_id, dept_id) values ('1', '3', '3');
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '1', '1');
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '2', '1');
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '3', '1');
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '4', '1');

查询每位老师在大学里教授的科目种类的数量；

示例 1：

输入：

Teacher 表：

输出： 

2 分析

3 实现 

select
    activity_date day,
    count(distinct user_id) act编程客栈ive_users
from activity
where datediff('2019-07-27',activity_date) between 0 and 29
group by activity_date;

二 、查询近30天活跃用户数

本题主要考验

• 分组查询

• 字符串函数count统计人数

• 时间函数定义时间

1 准备工作

Create table If Not Exists Activity (user_id int, session_id int, activity_date date, activity_type ENUM('open_session', 'end_session', 'scroll_down', 'send_message'));
Truncate tabwww.devze.comle Activity;
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'scroll_down');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-20', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'send_message');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'open_sesandroidsion');
insert into Activity (user_id, session_id, activity_date, activiandroidty_type) values ('3', '2', '2019-07-21', js'send_message');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'end_session');

统计截至 2019-07-27（包含07-27），近30天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）

示例 2 ：

输入：

输出：

2 分析 

首先需要查询的字段为时间和用户登录数，对时间分组并且对用户人数统计查询出每日活跃的用户。（含重复id）

对用户的id进行去重查出每日活跃用户

3 实现 

select
    activity_date day,
    count(distinct user_id) active_users
from activity
where datediff('2019-07-27',activity_date) between 0 and 29
group by activity_date;

到此这篇关于mysql 聚合函数、分组查询、时间函数 的文章就介绍到这了,更多相关mysql聚合函数、分组查询、时间函数 内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！