SQL28 計算使用者8月每天的練題數量
描述
題目:現在運營想要計算出2021年8月每天使用者練習題目的數量,請取出相應資料。
示例:question_practice_detail
id | device_id | question_id | result | date |
1 | 2138 | 111 | wrong | 2021-05-03 |
2 | 3214 | 112 | wrong | 2021-05-09 |
3 | 3214 | 113 | wrong | 2021-06-15 |
4 | 6543 | 111 | right | 2021-08-13 |
5 | 2315 | 115 | right | 2021-08-13 |
6 | 2315 | 116 | right | 2021-08-14 |
7 | 2315 | 117 | wrong | 2021-08-15 |
根據示例,你的查詢應返回以下結果:
day | question_cnt |
13 | 5 |
14 | 2 |
15 | 3 |
16 | 1 |
18 | 1 |
select t.a as day,count(t.device_id) as question_cnt from
(select day(date) as a,device_id from question_practice_detail where date like '2021-08%') t
group by t.a;
NOW(),用於獲取當前日期和時間函式 2024-03-16 10:58:06 select Now()
CURDATE(),用於獲取當前日期,同CURRENT_DATE() 2024-03-16
CURTIME(),用於獲取當前時間,同CURRENT_TIME() 10:58:06
DATE()函式,用於提取時間欄位的日期 2024-03-16
YEAR()函式,用於提取時間欄位的年,,返回的格式為"YYYY"
MONTH()函式,用於提取時間欄位的月,返回的格式為"mm",若月份前有0會忽略
DAY()函式,用於提取時間欄位的日期,,返回的格式為"dd",若日期前有0會忽略
TIME()函式,用於提取時間欄位的時間,,返回的格式為"HH:ii:ss"
HOUR()函式,用於提取時間欄位的小時,,返回的格式為"HH",若小時前有0會忽略,如08則返回8
MINUTE()函式,用於提取時間欄位的分鐘,返回的格式為"ii",若分鐘前有0會忽略,如08則返回8
SECOND()函式,用於提取時間欄位的秒,返回的格式為"ss",若秒前有0會忽略,如08則返回8
UNIX_TIMESTAMP()函式,獲取當前時間戳,還可以將時間欄位轉換成時間戳
WEEK()函式 獲取日期對應的第幾周
QUARTER() 獲取日期對應的季度
DAYOFYEAR() 獲取當天一日為這一年的第幾天
DAYNAME() 獲取對應周幾
DAYOFMONTH() 獲取日期是當月的第幾天
TIME_TO_SEC() 獲取時間對應的秒
ADDDATE('2023-05-18 10:58:59',2) 日期加N天
計算兩個時間差
SELECT DATEDIFF('2023-05-31 10:58:59','2023-05-18 10:58:59')
日期減去N天
SELECT SUBDATE('2023-05-18 10:58:59',2)
--獲取當前日期在本週的週一
select subdate(curdate(),date_format(curdate(),'%w')-1) ;
--獲取當前日期在本週的週日
select subdate(curdate(),date_format(curdate(),'%w')-7);
返回:2023-05-21
drop table if exists `user_profile`; drop table if exists `question_practice_detail`; drop table if exists `question_detail`; CREATE TABLE `user_profile` ( `id` int NOT NULL, `device_id` int NOT NULL, `gender` varchar(14) NOT NULL, `age` int , `university` varchar(32) NOT NULL, `gpa` float, `active_days_within_30` int , `question_cnt` int , `answer_cnt` int ); CREATE TABLE `question_practice_detail` ( `id` int NOT NULL, `device_id` int NOT NULL, `question_id`int NOT NULL, `result` varchar(32) NOT NULL, `date` date NOT NULL ); CREATE TABLE `question_detail` ( `id` int NOT NULL, `question_id`int NOT NULL, `difficult_level` varchar(32) NOT NULL ); INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大學',3.4,7,2,12); INSERT INTO user_profile VALUES(2,3214,'male',null,'復旦大學',4.0,15,5,25); INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大學',3.2,12,3,30); INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大學',3.6,5,1,2); INSERT INTO user_profile VALUES(5,5432,'male',25,'山東大學',3.8,20,15,70); INSERT INTO user_profile VALUES(6,2131,'male',28,'山東大學',3.3,15,7,13); INSERT INTO user_profile VALUES(7,4321,'male',28,'復旦大學',3.6,9,6,52); INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03'); INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09'); INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15'); INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13'); INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13'); INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14'); INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15'); INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09'); INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15'); INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13'); INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13'); INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14'); INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15'); INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16'); INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18'); INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13'); INSERT INTO question_detail VALUES(1,111,'hard'); INSERT INTO question_detail VALUES(2,112,'medium'); INSERT INTO question_detail VALUES(3,113,'easy'); INSERT INTO question_detail VALUES(4,115,'easy'); INSERT INTO question_detail VALUES(5,116,'medium'); INSERT INTO question_detail VALUES(6,117,'easy');