相关文章推荐
骑白马的领带  ·  v-select中的Vuetify ...·  昨天    · 
重感情的葡萄酒  ·  sql ...·  23 小时前    · 
光明磊落的金针菇  ·  SQL 到 Kusto 查询转换 - ...·  11 小时前    · 
爱玩的人字拖  ·  如何用sql判断是唯一主键还是联合主键 - ...·  5 小时前    · 
善良的白开水  ·  oracle里面查询重复数据的方法 - ...·  5 小时前    · 
豪气的板栗  ·  vue3设置表格中复选框默认选中 ant ...·  1 年前    · 
玩足球的炒粉  ·  【Python】networkx中如何获取图 ...·  1 年前    · 
千杯不醉的上铺  ·  音频频谱 via ...·  1 年前    · 
气宇轩昂的弓箭  ·  Idea使用docker插件部署服务到远程服 ...·  1 年前    · 
善良的钥匙扣  ·  在CentOS ...·  1 年前    · 
Code  ›  MYSQL：如何計算每筆數據的移動平均值？ - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天
asc null mysql select
https://ithelp.ithome.com.tw/questions/10206152
不爱学习的风衣
1 年前 
SELECT  scdate,avg(tt.value) as avg_v FROM
SELECT t.scdate,t.value,(@rownum := @rownum + 1) - 1 AS rownum FROM (SELECT @rownum := 0) 
r,scantech_data t 
ORDER BY  scdate asc
WHERE scdate >= '2021-11-10 17:00:00' and  scdate <='2021-11-10 17:10:00'
GROUP BY tt.rownum - tt.rownum % 3
order by scdate

結果會跳筆數

scdate
avg_v
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:00',1943.98);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:18',1957.13);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:36',1953.94);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:54',1946.28);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:12',1960.29);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:29',1950.39);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:47',1942.62);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:05',1954.64);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:23',1958.21);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:41',1950.26);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:58',1945.33);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:03:16',1960.35);
-- 額外加入
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:04:33',NULL);

SELECT T1.scdate,T1.value,
CASE WHEN IFNULL(T1.value,0)+IFNULL(T2.value,0)+IFNULL(T3.value,0) > 0 THEN
ROUND((IFNULL(T1.value,0)+IFNULL(T2.value,0)+IFNULL(T3.value,0))/(
CASE WHEN T1.value IS NULL THEN 0 ELSE 1 END +
CASE WHEN T2.value IS NULL THEN 0 ELSE 1 END +
CASE WHEN T3.value IS NULL THEN 0 ELSE 1 END),2) 
ELSE NULL END AS avg_v
FROM (
SELECT t.scdate,t.value,
(@rownum_A := @rownum_A + 1) AS rownum_A FROM (SELECT @rownum_A := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_B := @rownum_B + 1) AS rownum_B FROM (SELECT @rownum_B := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T2 ON T2.rownum_B = T1.rownum_A + 1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_C := @rownum_C + 1) AS rownum_C FROM (SELECT @rownum_C := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T3 ON T3.rownum_C = T1.rownum_A + 2
ORDER BY T1.scdate
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:00',1943.98,2943.98);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:18',1957.13,2957.13);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:36',1953.94,2953.94);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:54',1946.28,2946.28);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:12',1960.29,2960.29);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:29',1950.39,2950.39);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:47',1942.62,2942.62);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:05',1954.64,2954.64);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:23',1958.21,2958.21);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:41',1950.26,2950.26);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:58',1945.33,2945.33);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:03:16',1960.35,2960.35);
-- 額外加入
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:04:33',NULL,NULL);

SELECT T1.scdate,
T1.value1,
CASE WHEN IFNULL(T1.value1,0)+IFNULL(T2.value1,0)+IFNULL(T3.value1,0) > 0 THEN
ROUND((IFNULL(T1.value1,0)+IFNULL(T2.value1,0)+IFNULL(T3.value1,0))/(
CASE WHEN T1.value1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T2.value1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T3.value1 IS NULL THEN 0 ELSE 1 END),2) 
ELSE NULL END AS avg_v1,
-- Copy 上面的 value1
T1.value2,
CASE WHEN IFNULL(T1.value2,0)+IFNULL(T2.value2,0)+IFNULL(T3.value2,0) > 0 THEN
ROUND((IFNULL(T1.value2,0)+IFNULL(T2.value2,0)+IFNULL(T3.value2,0))/(
CASE WHEN T1.value2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T2.value2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T3.value2 IS NULL THEN 0 ELSE 1 END),2) 
ELSE NULL END AS avg_v2
FROM (
SELECT *,(@rownum_A := @rownum_A + 1) AS rownum_A FROM (SELECT @rownum_A := 0) r,scantech_data t 
ORDER BY scdate asc) AS T1
LEFT JOIN (
SELECT *,(@rownum_B := @rownum_B + 1) AS rownum_B FROM (SELECT @rownum_B := 0) r,scantech_data t 
ORDER BY scdate asc) AS T2 ON T2.rownum_B = T1.rownum_A + 1
LEFT JOIN (
SELECT *,(@rownum_C := @rownum_C + 1) AS rownum_C FROM (SELECT @rownum_C := 0) r,scantech_data t 
ORDER BY scdate asc) AS T3 ON T3.rownum_C = T1.rownum_A + 2
ORDER BY T1.scdate
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:00',1943.98,2943.98);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:18',1957.13,2957.13);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:36',1953.94,2953.94);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:00:54',1946.28,2946.28);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:12',1960.29,2960.29);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:29',1950.39,2950.39);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:01:47',1942.62,2942.62);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:05',1954.64,2954.64);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:23',1958.21,2958.21);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:41',1950.26,2950.26);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:02:58',1945.33,2945.33);
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:03:16',1960.35,2960.35);
-- 額外加入
INSERT INTO `scantech_data` VALUES ('2021-11-10 17:04:33',NULL,NULL);
SELECT T1.scdate,
T1.value1,
CASE WHEN IFNULL(T1.value1,0)+IFNULL(T2.value1,0)+IFNULL(T3.value1,0) > 0 THEN
ROUND((IFNULL(T1.value1,0)+IFNULL(T2.value1,0)+IFNULL(T3.value1,0))/(
CASE WHEN T1.value1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T2.value1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T3.value1 IS NULL THEN 0 ELSE 1 END),2) 
ELSE NULL END AS avg_v1,
-- Copy 上面的 value1
T1.value2,
CASE WHEN IFNULL(T1.value2,0)+IFNULL(T2.value2,0)+IFNULL(T3.value2,0) > 0 THEN
ROUND((IFNULL(T1.value2,0)+IFNULL(T2.value2,0)+IFNULL(T3.value2,0))/(
CASE WHEN T1.value2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T2.value2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN T3.value2 IS NULL THEN 0 ELSE 1 END),2) 
ELSE NULL END AS avg_v2
FROM (
SELECT *,(@rownum_A := @rownum_A + 1) AS rownum_A FROM (SELECT @rownum_A := 0) r,scantech_data t 
ORDER BY scdate asc) AS T1
LEFT JOIN (
SELECT *,(@rownum_B := @rownum_B + 1) AS rownum_B FROM (SELECT @rownum_B := 0) r,scantech_data t 
ORDER BY scdate asc) AS T2 ON T2.rownum_B = T1.rownum_A + 1
LEFT JOIN (
SELECT *,(@rownum_C := @rownum_C + 1) AS rownum_C FROM (SELECT @rownum_C := 0) r,scantech_data t 
ORDER BY scdate asc) AS T3 ON T3.rownum_C = T1.rownum_A + 2
ORDER BY T1.scdate
[Demo](https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=55a5d3a34c1b545157de6c1bd59d51f0)
scdate	            |  value  | >1945 數量| 
--------------------|---------|------|
2021/11/10 17:00:00 | 1943.98 | 2    |
2021/11/10 17:00:18 | 1957.13 | 3    |
2021/11/10 17:00:36 | 1953.94 | 3    |
2021/11/10 17:00:54 | 1946.28 | 3    |
2021/11/10 17:01:12 | 1960.29 | 2    |
2021/11/10 17:01:29 | 1950.39 | 2    |
2021/11/10 17:01:47 | 1942.62 | NULL |
2021/11/10 17:02:05 | 1954.64 | NULL |
                            
SELECT T1.scdate,T1.value,T2.value,T3.value,
CASE WHEN T1.value>1945 THEN 1 ELSE 0 END +
CASE WHEN T2.value>1945 THEN 1 ELSE 0 END +
CASE WHEN T3.value>1945 THEN 1 ELSE 0 END AS TempCount
FROM (
SELECT t.scdate,t.value,
(@rownum_A := @rownum_A + 1) AS rownum_A FROM (SELECT @rownum_A := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_B := @rownum_B + 1) AS rownum_B FROM (SELECT @rownum_B := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T2 ON T2.rownum_B = T1.rownum_A + 1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_C := @rownum_C + 1) AS rownum_C FROM (SELECT @rownum_C := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T3 ON T3.rownum_C = T1.rownum_A + 2
ORDER BY T1.scdate
SELECT T1.scdate,T1.value,T2.value,T3.value,
CASE WHEN T1.value>1945 THEN 1 ELSE 0 END +
CASE WHEN T2.value>1945 THEN 1 ELSE 0 END +
CASE WHEN T3.value>1945 THEN 1 ELSE 0 END AS TempCount
FROM (
SELECT t.scdate,t.value,
(@rownum_A := @rownum_A + 1) AS rownum_A FROM (SELECT @rownum_A := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_B := @rownum_B + 1) AS rownum_B FROM (SELECT @rownum_B := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T2 ON T2.rownum_B = T1.rownum_A + 1
LEFT JOIN (
SELECT t.scdate,t.value,
(@rownum_C := @rownum_C + 1) AS rownum_C FROM (SELECT @rownum_C := 0) 
r,scantech_data t 
ORDER BY scdate asc) AS T3 ON T3.rownum_C = T1.rownum_A + 2
ORDER BY T1.scdate
[Demo](https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=4779a5e92fda23f2e8d41d491400fff3)

SELECT `scdate`, `value`, `avg_v` FROM (
	SELECT `scdate`, `value`, IFNULL(
		(SELECT AVG(`value`) FROM (
			SELECT * FROM (
				SELECT @no := @no + 1 `no`, `scdate`, `value` FROM (
					`scantech_data`,
					(SELECT @no := 0) `var`
				ORDER BY `scdate` ASC
			) `t4`
		) `t3`
		WHERE `t3`.`no` >= `t2`.`id`
        -- 向下查詢 3 筆資料
		AND `t3`.`no` < `t2`.`id` + 3),
	) `avg_v`
	FROM (
		SELECT *, @id := @id + 1 `id` FROM (
			`scantech_data`,
			(SELECT @id := 0) `var`
		ORDER BY `scdate` ASC
	) `t2`
) `t1`
WHERE `scdate` >= '2021-11-10 17:00:00'
AND `scdate` <='2021-11-10 17:10:00';
 
推荐文章
骑白马的领带  ·  v-select中的Vuetify 3更改事件-腾讯云开发者社区-腾讯云
昨天
重感情的葡萄酒  ·  sql 日期函数往前推当前时间指定天数_sql指定日期往前推7天-CSDN博客
23 小时前
光明磊落的金针菇  ·  SQL 到 Kusto 查询转换 - Kusto | Microsoft Learn
11 小时前
爱玩的人字拖  ·  如何用sql判断是唯一主键还是联合主键 - CSDN文库
5 小时前
善良的白开水  ·  oracle里面查询重复数据的方法 - 经典鸡翅 - 博客园
5 小时前
豪气的板栗  ·  vue3设置表格中复选框默认选中 ant design_vue多选框默认选中_尚振伟的博客-CSDN博客
1 年前
玩足球的炒粉  ·  【Python】networkx中如何获取图的邻接矩阵_兔子爱读书的博客-CSDN博客
1 年前
千杯不醉的上铺  ·  音频频谱 via FFT_音频计算频谱频率_CBlueware的博客-CSDN博客
1 年前
气宇轩昂的弓箭  ·  Idea使用docker插件部署服务到远程服务器 - Alan6 - 博客园
1 年前
善良的钥匙扣  ·  在CentOS 7中使用Sendmail通过PHP发送邮件-51CTO.COM
1 年前
今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
Code - 代码工具平台
© 2024 ~ 沪ICP备11025650号