目录

目标

空气质量指数计算对照表

函数

调用测试

目标

        根据空气质量指数标准，通过调用MySQL函数来计算：二氧化硫（SO2）、二氧化氮（NO2）、一氧化碳（CO）、臭氧（O3）的1小时平均浓度对应的IAQI。

空气质量指数计算对照表

函数

DELIMITER $$

USE `数据库名称`$$

DROP FUNCTION IF EXISTS `IAQI_1H`$$

CREATE DEFINER=`登录账号`@`%` FUNCTION `IAQI_1H`(param_factor_type VARCHAR(10),param_factor_value DECIMAL(10,5)) RETURNS VARCHAR(1024) CHARSET utf8mb4
BEGIN
IF
param_factor_type IS NULL OR  LENGTH(REPLACE(param_factor_type,' ',''))=0
OR param_factor_value IS NULL OR param_factor_value IS NULL
THEN 
RETURN NULL;
END IF;
#二氧化硫（SO2），单位：μg/m³
IF
param_factor_type ="二氧化硫" OR LOWER(param_factor_type) ="so2"
THEN 
	IF
		param_factor_value BETWEEN 0 AND 150
	THEN
		RETURN CEILING((50-0)/(150-0)*(param_factor_value-0)+0);
	ELSEIF
		param_factor_value BETWEEN 150 AND 500
	THEN
		RETURN CEILING((100-50)/(500-150)*(param_factor_value-150)+50);
	ELSEIF
		param_factor_value BETWEEN 500 AND 650
	THEN
		RETURN CEILING((150-100)/(650-500)*(param_factor_value-500)+100);
	ELSEIF
		param_factor_value BETWEEN 650 AND 800
	THEN
		RETURN CEILING((200-150)/(800-650)*(param_factor_value-650)+150);
	ELSE
		#SO2高于800，1h平均浓度按照24小时平均浓度计算的分指数报告。
		RETURN NULL;	
	END IF;
		
END IF;

#二氧化氮（NO2），单位：μg/m³
IF
param_factor_type ="二氧化氮" OR LOWER(param_factor_type) ="no2"
THEN 
	IF
		param_factor_value BETWEEN 0 AND 100
	THEN
		RETURN CEILING((50-0)/(100-0)*(param_factor_value-0)+0);
	ELSEIF
		param_factor_value BETWEEN 100 AND 200
	THEN
		RETURN CEILING((100-50)/(200-100)*(param_factor_value-100)+50);
	ELSEIF
		param_factor_value BETWEEN 200 AND 700
	THEN
		RETURN CEILING((150-100)/(700-200)*(param_factor_value-200)+100);
	ELSEIF
		param_factor_value BETWEEN 700 AND 1200
	THEN
		RETURN CEILING((200-150)/(1200-700)*(param_factor_value-700)+150);
	ELSEIF
		param_factor_value BETWEEN 1200 AND 2340
	THEN
		RETURN CEILING((300-200)/(2340-1200)*(param_factor_value-1200)+150);	
	ELSEIF
		param_factor_value BETWEEN 2340 AND 3090
	THEN
		RETURN CEILING((400-300)/(3090-2340)*(param_factor_value-2340)+150);
	ELSEIF
		param_factor_value BETWEEN 3090 AND 3840
	THEN
		RETURN CEILING((500-400)/(3840-3090)*(param_factor_value-3090)+150);
	ELSE
		RETURN NULL;
	END IF;
		
END IF;


#一氧化碳（CO），单位：mg/m³
IF
param_factor_type ="一氧化碳" OR LOWER(param_factor_type) ="co"
THEN 
	IF
		param_factor_value BETWEEN 0 AND 5
	THEN
		RETURN CEILING((50-0)/(5-0)*(param_factor_value-0)+0);
	ELSEIF
		param_factor_value BETWEEN 5 AND 10
	THEN
		RETURN CEILING((100-50)/(10-5)*(param_factor_value-5)+50);
	ELSEIF
		param_factor_value BETWEEN 10 AND 35
	THEN
		RETURN CEILING((150-100)/(35-10)*(param_factor_value-10)+100);
	ELSEIF
		param_factor_value BETWEEN 35 AND 60
	THEN
		RETURN CEILING((200-150)/(60-35)*(param_factor_value-35)+150);
	ELSEIF
		param_factor_value BETWEEN 60 AND 90
	THEN
		RETURN CEILING((300-200)/(90-60)*(param_factor_value-60)+150);	
	ELSEIF
		param_factor_value BETWEEN 90 AND 120
	THEN
		RETURN CEILING((400-300)/(120-90)*(param_factor_value-90)+150);
	ELSEIF
		param_factor_value BETWEEN 120 AND 150
	THEN
		RETURN CEILING((500-400)/(150-120)*(param_factor_value-120)+150);
	ELSE
		RETURN NULL;
	END IF;	
END IF;

#臭氧（O3），单位：μg/m³
IF
param_factor_type ="臭氧" OR LOWER(param_factor_type) ="o3"
THEN 
	IF
		param_factor_value BETWEEN 0 AND 160
	THEN
		RETURN CEILING((50-0)/(160-0)*(param_factor_value-0)+0);
	ELSEIF
		param_factor_value BETWEEN 160 AND 200
	THEN
		RETURN CEILING((100-50)/(200-160)*(param_factor_value-160)+50);
	ELSEIF
		param_factor_value BETWEEN 200 AND 300
	THEN
		RETURN CEILING((150-100)/(300-200)*(param_factor_value-200)+100);
	ELSEIF
		param_factor_value BETWEEN 300 AND 400
	THEN
		RETURN CEILING((200-150)/(400-300)*(param_factor_value-300)+150);
	ELSEIF
		param_factor_value BETWEEN 400 AND 800
	THEN
		RETURN CEILING((300-200)/(800-400)*(param_factor_value-400)+150);	
	ELSEIF
		param_factor_value BETWEEN 800 AND 1000
	THEN
		RETURN CEILING((400-300)/(1000-800)*(param_factor_value-800)+150);
	ELSEIF
		param_factor_value BETWEEN 1000 AND 1200
	THEN
		RETURN CEILING((500-400)/(1200-1000)*(param_factor_value-1000)+150);
	ELSE
		RETURN NULL;
	END IF;
END IF;

    END$$

DELIMITER ;

调用测试

#o3iaqi=36
SELECT `IAQI_1H`('o3',114.00) o3iaqi
#so2iaqi=173
SELECT `IAQI_1H`('so2',718.92) so2iaqi
#no2iaqi=93
SELECT `IAQI_1H`('no2',186.00) no2iaqi
#coiaqi=26
SELECT `IAQI_1H`('co',2.600) coiaqi