sql纵向，横向，去Null值，去0值求平均数

最新推荐文章于 2023-07-11 12:07:16 发布

流浪羽先生

最新推荐文章于 2023-07-11 12:07:16 发布

阅读量858

点赞数 2

分类专栏： sql 文章标签： sql

本文链接：https://blog.csdn.net/liulangyude/article/details/124258035

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

sql纵向，横向，去Null值，去0值求平均数

sql纵向，横向，去Null值，去0值求平均数

sql纵向，横向，去Null值，去0值求平均数

SELECT
	round( avg( nullif( temperature, 0 )), 2 ) temperature,
	sum( electricity_sum + electricity_1 + electricity_2 + electricity_3 ) AS electricity_sum,
	sum( power_sum ) AS power_sum,
	round((
		IF
			(
				avg(
				nullif( voltage_sum, 0 )) IS NULL,
				0,
				avg(
				nullif( voltage_sum, 0 )))+
		IF
			(
				avg(
				nullif( voltage_1, 0 )) IS NULL,
				0,
				avg(
				nullif( voltage_1, 0 )))+
		IF
			(
				avg(
				nullif( voltage_2, 0 )) IS NULL,
				0,
				avg(
				nullif( voltage_2, 0 )))+
		IF
			(
				avg(
				nullif( voltage_3, 0 )) IS NULL,
				0,
				avg(
				nullif( voltage_3, 0 ))))/(
		IF
			( avg( nullif( voltage_1, 0 )) = 0 OR avg( nullif( voltage_1, 0 )) IS NULL, 0, 1 )+
		IF
			( avg( nullif( voltage_2, 0 )) = 0 OR avg( nullif( voltage_2, 0 )) IS NULL, 0, 1 )+
		IF
			( avg( nullif( voltage_2, 0 )) = 0 OR avg( nullif( voltage_3, 0 )) IS NULL, 0, 1 )+
		IF
			( avg( nullif( voltage_sum, 0 )) = 0 OR avg( nullif( voltage_sum, 0 )) IS NULL, 0, 1 )),
		2 
	) AS voltage_sum 
FROM
	power 
WHERE
	room_id = 214