平均值
SELECT
device_code AS deviceCode,
DATE_FORMAT(
CONCAT(
DATE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
' ',
HOUR (DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
':',
FLOOR(MINUTE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)) / 10) * 10 + 8 ,
':15'
),
'%Y-%m-%d %H:%i:%s'
) AS time,
AVG(pm) AS pm,
AVG(lampblack) AS lampblack,
AVG(nmhc) AS nmhc
FROM
smk_data_histories
WHERE
timepoint BETWEEN '2021-12-31 16:08:15'
AND '2021-12-31 16:59:59'
GROUP BY
time;
最大值
SELECT
device_code AS deviceCode,
DATE_FORMAT(
CONCAT(
DATE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
' ',
HOUR (DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
':',
FLOOR(MINUTE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)) / 10) * 10 + 8 ,
':15'
),
'%Y-%m-%d %H:%i:%s'
) AS time,
MAX(pm) AS pm,
MAX(lampblack) AS lampblack,
MAX(nmhc) AS nmhc
FROM
smk_data_histories
WHERE
timepoint BETWEEN '2021-12-31 16:08:15'
AND '2021-12-31 16:59:59'
GROUP BY
time;
最小值
SELECT
device_code AS deviceCode,
DATE_FORMAT(
CONCAT(
DATE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
' ',
HOUR (DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
':',
FLOOR(MINUTE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)) / 10) * 10 + 8 ,
':15'
),
'%Y-%m-%d %H:%i:%s'
) AS time,
MIN(pm) AS pm,
MIN(lampblack) AS lampblack,
MIN(nmhc) AS nmhc
FROM
smk_data_histories
WHERE
timepoint BETWEEN '2021-12-31 16:08:15'
AND '2021-12-31 16:59:59'
GROUP BY
time;
总和
SELECT
device_code AS deviceCode,
DATE_FORMAT(
CONCAT(
DATE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
' ',
HOUR (DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
':',
FLOOR(MINUTE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)) / 10) * 10 + 8 ,
':15'
),
'%Y-%m-%d %H:%i:%s'
) AS time,
SUM(pm) AS pm,
SUM(lampblack) AS lampblack,
SUM(nmhc) AS nmhc
FROM
smk_data_histories
WHERE
timepoint BETWEEN '2021-12-31 16:08:15'
AND '2021-12-31 16:59:59'
GROUP BY
time;
总记录数
SELECT
device_code AS deviceCode,
DATE_FORMAT(
CONCAT(
DATE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
' ',
HOUR (DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
':',
FLOOR(MINUTE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)) / 10) * 10 + 8 ,
':15'
),
'%Y-%m-%d %H:%i:%s'
) AS time,
COUNT(pm) AS pm,
COUNT(lampblack) AS lampblack,
COUNT(nmhc) AS nmhc
FROM
smk_data_histories
WHERE
timepoint BETWEEN '2021-12-31 16:08:15'
AND '2021-12-31 16:59:59'
GROUP BY
time;
差值
SELECT b.device_code AS deviceCode, a.time,b.pm -c.pm AS pm,b.lampblack - c.lampblack AS lampblack,b.nmhc -c.nmhc AS nmhc FROM (
SELECT
device_code,
DATE_FORMAT(
CONCAT(
DATE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
' ',
HOUR (DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)),
':',
FLOOR(MINUTE(DATE_SUB(timepoint,INTERVAL '08:15' MINUTE_SECOND)) / 10) * 10 + 8,
':15'
),
'%Y-%m-%d %H:%i:%s'
) AS time,
MAX(timepoint) AS maxTime,
MIN(timepoint) AS minTime
FROM
smk_data_histories
WHERE
timepoint BETWEEN '2021-12-31 16:08:15'
AND '2021-12-31 16:59:59'
GROUP BY
time) AS a,smk_data_histories b,smk_data_histories c
WHERE a.maxTime = b.timepoint AND a.minTime = c.timepoint AND a.device_code = b.device_code AND a.device_code = c.device_code;
天
SELECT
device_code AS deviceCode,
DATE_FORMAT(
CONCAT(
DATE(DATE_SUB(timepoint,INTERVAL DATEDIFF(timepoint,'2021-12-31 16:08:15') - FLOOR(DATEDIFF(timepoint,'2021-12-31 16:08:15')/30)*30 DAY)),
' ','16:08:15'
),
'%Y-%m-%d %H:%i:%s'
) AS time,
AVG(pm) AS pm,
AVG(lampblack) AS lampblack,
AVG(nmhc) AS nmhc
FROM
smk_data_histories
WHERE
timepoint BETWEEN '2021-12-31 16:08:15'
AND '2022-04-01 17:59:59'
GROUP BY
time;
第二种方式
SELECT
device_code AS deviceCode,
DATE_FORMAT(
CONCAT(
DATE(DATE_SUB(timepoint,INTERVAL TIMESTAMPDIFF(DAY,'2021-12-31 16:08:15',timepoint) - FLOOR(TIMESTAMPDIFF(DAY,'2021-12-31 16:08:15',timepoint)/30)*30 DAY)),
' ','16:08:15'
),
'%Y-%m-%d %H:%i:%s'
) AS time,
AVG(pm) AS pm,
AVG(lampblack) AS lampblack,
AVG(nmhc) AS nmhc
FROM
smk_data_histories
WHERE
timepoint BETWEEN '2021-12-31 16:08:15'
AND '2022-04-01 17:59:59'
GROUP BY
time;