添加链接 注册    登录
link之家
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
心软的松鼠  ·  发生转继承后，是否还存在代位继承? | ...·  6 月前    · 
腼腆的柠檬  ·  张梦阳：《阿Q正传》的意义--文史--中国作家网·  8 月前    · 
奔跑的啄木鸟  ·  SOLIDWORKS Simulation ...·  1 年前    · 
想表白的领结  ·  苏州大学博士研发的吸汗鞋垫！对抗99％细菌， ...·  1 年前    · 
无邪的黄花菜  ·  用一部电影，形容我的人生，搞笑漫画 - 知乎·  1 年前    · 
link之家  ›  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';
 
推荐文章
心软的松鼠  ·  发生转继承后，是否还存在代位继承? | 南海区政府网站
6 月前
腼腆的柠檬  ·  张梦阳：《阿Q正传》的意义--文史--中国作家网
8 月前
奔跑的啄木鸟  ·  SOLIDWORKS Simulation 2021增强的交互功能到底有多强？_本地交互和零部件交互的区别-CSDN博客
1 年前
想表白的领结  ·  苏州大学博士研发的吸汗鞋垫！对抗99％细菌，拒绝汗脚臭脚_腾讯新闻
1 年前
无邪的黄花菜  ·  用一部电影，形容我的人生，搞笑漫画 - 知乎
1 年前
今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
link之家 - 链接快照平台
© 2024 ~ 沪ICP备11025650号