一、mysql怎么查詢連續時間段的最大值
按儀器與時間（處理成小時）group by，計算值的數量與和，再根據結果判斷值數量是否有缺失值，以及和的最大值。首先要明確采集標準，比如說一分鐘采集一條記錄，那么可以group by 小時。
– Step1 創建表
CREATE TABLE monitor(
id int not null auto_increment,
seq_no int,
add_time DATETIME,
stat int,
primary key(id)
);
— Step2 初始化記錄，這里的6點和7點的數據完整，其中6點的有重復記錄。
INSERT INTO monitor(seq_no,add_time,stat)
SELECT 1,’2021-6-10 6:0′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:1′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:2′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:3′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:4′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:5′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:6′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:7′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:8′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:9′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:10′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:11′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:12′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:13′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:14′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:15′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:16′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:17′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:18′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:19′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:20′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:21′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:22′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:23′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:24′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:25′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:26′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:27′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:28′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:29′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:30′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:31′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:32′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:33′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:34′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:35′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:36′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:37′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:38′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:39′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:40′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:41′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:42′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:43′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:44′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:45′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:46′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:47′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:48′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:49′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:50′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:51′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:52′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:53′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:54′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:55′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:56′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:57′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:58′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:58′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 6:59′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:0′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:1′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:2′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:3′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:4′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:5′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:6′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:7′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:8′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:9′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:10′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:11′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:12′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:13′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:14′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:15′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:16′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:17′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:18′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:19′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:20′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:21′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:22′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:23′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:24′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:25′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:26′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:27′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:28′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:29′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:30′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:31′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:32′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:33′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:34′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:35′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:36′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:37′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:38′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:39′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:40′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:41′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:42′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:43′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:44′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:45′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:46′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:47′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:48′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:49′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:50′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:51′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:52′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:53′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:54′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:55′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:56′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:57′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:58′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 1,’2021-6-10 7:59′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:1′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:2′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:3′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:4′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:5′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:6′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:7′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:8′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:9′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:10′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:11′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:12′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:13′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:14′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:15′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:16′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:17′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:18′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:19′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:20′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:21′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:22′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:23′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:24′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:25′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:26′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:27′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:28′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:29′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:30′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:31′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:32′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:33′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:34′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:35′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:36′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:37′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:38′ ,FLOOR(1 + (RAND() * 101)) UNION ALL
SELECT 2,’2021-6-10 8:39′ ,FLOOR(1 + (RAND() * 101)) ;
— Step3 查詢
— scenario1 查詢不完整的，這里加去重是為了剔除重復記錄。
SELECT seq_no,CONCAT(DATE(add_time),’#’,HOUR(add_time))date_hour,COUNT(DISTINCT add_time) record_cnt
FROM monitor A
GROUP BY seq_no,CONCAT(DATE(add_time),’#’,HOUR(add_time))
HAVING COUNT(DISTINCT add_time)<60
/*
結果
seq_no date_hour record_cnt
2?? 2021-06-10#8? 39
*/
— scenario1，按天查詢固定小時周期內總和的最大值，如果有重復數據需加邏輯去重（當前未考慮）
SELECT SUBSTR(date_hour,1,INSTR(date_hour,’#’)-1) date_only,MAX(sum_hour) max_daily
FROM
??? (
?????? SELECT A.seq_no,B.date_hour,SUM(A.stat) sum_hour
?????? FROM monitor A
?????? JOIN(
?????????? SELECT seq_no,CONCAT(DATE(add_time),’#’,HOUR(add_time)) date_hour,COUNT(DISTINCT add_time) record_cnt
?????????? FROM monitor A
?????????? GROUP BY seq_no,CONCAT(DATE(add_time),’ ‘,HOUR(add_time))
?????????? HAVING COUNT(DISTINCT add_time)=60
?????????? )B
?????????? ON A.seq_no = B.seq_no
?????????? AND CONCAT(DATE(add_time),’#’,HOUR(add_time)) = B.date_hour
?????? GROUP BY A.seq_no,B.date_hour
??? )C
GROUP BY SUBSTR(date_hour,1,INSTR(date_hour,’#’)-1)
/*
結果
date_only max_daily
2021-06-10 3289
*/
— 3 針對任意小時的，建議通過存儲過程(定義起始時間、時間比較跨度)結合窗口函數（:= 模擬窗口函數）處理
— 補注 1 當前腳本用了隨機數,關于字段state的統計結果不固定。
—????? 2 當前演示數據庫是mysql 5.6.14。
延伸閱讀：
二、什么是數據庫和數據庫管理系統
數據庫的應用非常廣泛，舉個例子，我們平時在瀏覽器上搜索內容，就要用到數據庫去檢索我們的關鍵字。以前我們可能會用數組、集合、文件等來存儲數據，但是接下來我們就會面臨一個問題，當存儲的數據或內容過多的時候，我們如何去精準的找到我們需要的東西，這時候數據庫管理系統就派上了用場。除此之外，數據庫管理系統還能永久的儲存我們的數據。
為了便于大家理解，這里先給大家講解幾個概念
DB數據庫（database)：存儲數據的“倉庫”。它保存了一系列有組織的數據。
DBMS數據庫管理系統（Database Management System)：數據庫是通過DBMS創建和操作的容器。
SQL，結構化查詢語言（Structured Query Language）用一句話概括，SQL是一種特殊目的的編程語言，一種專門用來與數據庫通信的語言。在數據庫中，數據被結構化并存儲在不同的表中，從而簡化了訪問，更新和操作數據的過程。該表由列和行組成。數據庫中的表可以在關系的幫助下進行連接。要在數據庫中執行與數據相關的任務，可以使用SQL。SQL代表結構化查詢語言，旨在在特定RDBMS內創建，修改和管理數據庫中的數據。
SQL優點：
1、不是某個特定數據庫供應商專有的語言，幾乎所有DBMS（數據庫管理系統）都支持SQL
2、簡單易學
3、雖然簡單，但實際上是一種強有力的語言，靈活使用其語言元素，可以進行非常復雜和高級的數據庫操作。
京公網安備 11010802030320號