快捷導(dǎo)航

MySQL?聚合函數(shù)、分組查詢、時(shí)間函數(shù)詳解

更新時(shí)間：2024年08月01日 10:41:59 作者：不止萩

這篇文章主要介紹了MySQL?聚合函數(shù)、分組查詢、時(shí)間函數(shù)?,主要考驗(yàn)聚合函數(shù)count（）求和以及分組查詢,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),需要的朋友可以參考下

一、題目：每位教師所教授的科目種類的數(shù)量

本題主要考驗(yàn)聚合函數(shù)count（）求和以及分組查詢。

1 準(zhǔn)備工作

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');

查詢每位老師在大學(xué)里教授的科目種類的數(shù)量；

示例 1：

輸入：

Teacher 表：

輸出：

2 分析

3 實(shí)現(xiàn)

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;

二、查詢近30天活躍用戶數(shù)

本題主要考驗(yàn)

分組查詢
字符串函數(shù)count統(tǒng)計(jì)人數(shù)
時(shí)間函數(shù)定義時(shí)間

1 準(zhǔn)備工作

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 table 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_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', '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');

統(tǒng)計(jì)截至 2019-07-27（包含07-27），近30天的每日活躍用戶數(shù)（當(dāng)天只要有一條活動(dòng)記錄，即為活躍用戶）

示例 2 ：

輸入：

輸出：

2 分析

首先需要查詢的字段為時(shí)間和用戶登錄數(shù)，對(duì)時(shí)間分組并且對(duì)用戶人數(shù)統(tǒng)計(jì)查詢出每日活躍的用戶。（含重復(fù)id）

對(duì)用戶的id進(jìn)行去重查出每日活躍用戶

3 實(shí)現(xiàn)

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;

到此這篇關(guān)于MySQL 聚合函數(shù)、分組查詢、時(shí)間函數(shù) 的文章就介紹到這了,更多相關(guān)mysql聚合函數(shù)、分組查詢、時(shí)間函數(shù) 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: