批量生成传感器数据
DROP TEMPORARY TABLE IF EXISTS temp_numbers;
-- Create a temporary numbers table to help with generation
CREATE TEMPORARY TABLE IF NOT EXISTS temp_numbers (n INT);
INSERT INTO temp_numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);-- Create a procedure to generate the data
DELIMITER //
CREATE PROCEDURE GenerateSensorData()
BEGINDECLARE sensor_count INT DEFAULT 25;DECLARE days_in_month INT DEFAULT 31;DECLARE hours_in_day INT DEFAULT 24;DECLARE intervals_per_hour INT DEFAULT 6; -- 10-minute intervals-- Generate data for each sensorSET @sensor = 1;WHILE @sensor <= sensor_count DO-- Generate data for each daySET @day = 0;WHILE @day < days_in_month DO-- Generate data for each hourSET @hour = 0;WHILE @hour < hours_in_day DO-- Generate data for each 10-minute intervalSET @interval = 0;WHILE @interval < intervals_per_hour DO-- Calculate current timestampSET @current_time = TIMESTAMPADD(MINUTE, @interval*10, TIMESTAMPADD(HOUR, @hour, TIMESTAMPADD(DAY, @day, '2025-01-01 00:00:00')));-- Generate realistic sensor values with daily patternsSET @temp = 15.0 + 10.0 * SIN(@hour * PI() / 12) + (RAND() * 4 - 2);SET @humidity = 50.0 + 30.0 * SIN(@hour * PI() / 12 + PI()) + (RAND() * 10 - 5);SET @pressure = 1013.0 + (RAND() * 20 - 10);SET @is_daytime = @hour BETWEEN 6 AND 18;SET @pollution_factor = IF(@is_daytime, 1.5 + RAND(), 0.5 + RAND() * 0.5);-- Insert the sensor readingINSERT INTO sensor_data (sensor_id, timestamp,temperature, humidity, pressure,pm25, pm10, co, no2, o3, so2,wind_speed, wind_direction, rainfall,light_intensity, sound_level,battery_voltage, signal_strength) VALUES (@sensor, @current_time,-- Environmental measurements (constrained to valid ranges)LEAST(GREATEST(@temp, -40), 85), -- TemperatureLEAST(GREATEST(@humidity, 0), 100), -- HumidityLEAST(GREATEST(@pressure, 950), 1050), -- Pressure-- Air quality (higher during daytime)LEAST(5.0 + @pollution_factor * (10 + RAND() * 30), 500), -- PM2.5LEAST(10.0 + @pollution_factor * (15 + RAND() * 40), 600), -- PM10LEAST(0.3 + @pollution_factor * (0.5 + RAND() * 2), 50), -- COLEAST(5.0 + @pollution_factor * (10 + RAND() * 30), 500), -- NO2LEAST(10.0 + @pollution_factor * (15 + RAND() * 30), 500), -- O3LEAST(2.0 + @pollution_factor * (5 + RAND() * 15), 500), -- SO2-- Wind measurementsLEAST(1.0 + RAND() * 10, 60), -- Wind speedFLOOR(RAND() * 360), -- Wind direction-- Rainfall (occasional)IF(RAND() > 0.98, ROUND(RAND() * 1.5, 2), 0), -- Rainfall-- Light intensity (day/night pattern)IF(@is_daytime, ROUND(30000 * (1 + SIN((@hour-12) * PI() / 12)) + RAND() * 5000, 2),ROUND(10 + RAND() * 5, 2)),-- Sound level (higher during day)ROUND(40 + IF(@is_daytime, 20 + RAND() * 10, 5 + RAND() * 5), 2),-- Device metricsROUND(3.5 + RAND() * 0.6, 2), -- Battery voltageFLOOR(-80 - RAND() * 40) -- Signal strength);SET @interval = @interval + 1;END WHILE;SET @hour = @hour + 1;END WHILE;SET @day = @day + 1;END WHILE;SET @sensor = @sensor + 1;END WHILE;
END //
DELIMITER ;-- Execute the data generation procedure
CALL GenerateSensorData();-- Clean up
DROP TEMPORARY TABLE IF EXISTS temp_numbers;
DROP PROCEDURE IF EXISTS GenerateSensorData;
