欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間：2024年08月01日 10:41:59   作者：不止萩  
這篇文章主要介紹了MySQL?聚合函數(shù)、分組查詢、時間函數(shù)?,主要考驗聚合函數(shù)count（）求和以及分組查詢,本文結(jié)合實例代碼給大家介紹的非常詳細,需要的朋友可以參考下

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

本題主要考驗聚合函數(shù)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');

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

示例 1：

輸入：

Teacher 表：

輸出： 

2 分析

3 實現(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ù)

本題主要考驗

  • 分組查詢

  • 字符串函數(shù)count統(tǒng)計人數(shù)

  • 時間函數(shù)定義時間

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 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)計截至 2019-07-27（包含07-27），近30天的每日活躍用戶數(shù)（當天只要有一條活動記錄，即為活躍用戶）

示例 2 ：

輸入：

輸出：

2 分析 

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

對用戶的id進行去重查出每日活躍用戶

3 實現(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;

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

相關文章

最新評論