Mysql模拟数据

模拟SQL

模拟报表数据(每日一条记录)

-- 删除现有临时表（如果存在）
DROP TEMPORARY TABLE IF EXISTS temp_citypower_dates;

-- 创建临时表用于生成模拟数据
CREATE TEMPORARY TABLE temp_citypower_dates (
    city_id INT,
    daytime DATE,
    type INT,
    power DECIMAL(10, 2),
    elecfee DECIMAL(10, 2),
    create_time DATETIME
);

-- 定义变量
SET @city_id = 65; -- 统计的城市id
SET @start_date = '2023-01-01';
SET @end_date = '2023-12-31';
SET @min_power = 10.0;
SET @max_power = 50.0;

-- 插入指定月份的每一天数据，带有随机的power值,每次只能插入100条数据(即每次三个月)
INSERT INTO temp_citypower_dates (city_id, daytime, type, power, elecfee, unit_price, create_time)
SELECT
    @city_id AS city_id,
    DATE_FORMAT(DATE_ADD(@start_date, INTERVAL (t4.i * 10 + t3.i) DAY), '%Y-%m-%d') AS daytime,
    0 AS type,
    ROUND(@min_power + (RAND() * (@max_power - @min_power)), 2) AS power, -- 生成 @min_power 到 @max_power 之间的随机浮动值
    NULL AS elecfee,
    DATE_FORMAT(DATE_ADD(@start_date, INTERVAL (t4.i * 10 + t3.i) DAY), '%Y-%m-%d 12:00:00') AS create_time
FROM 
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4,
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3
WHERE 
    DATE_ADD(@start_date, INTERVAL (t4.i * 10 + t3.i) DAY) <= @end_date;

-- 将临时表的数据插入到目标表中,ON DUPLICATE KEY UPDATE 在主键重复时
INSERT INTO `city_power_data` (`city_id`, `daytime`,  `power`,  `create_time`)
SELECT `city_id`, `daytime`,  `power`,  `create_time` FROM temp_citypower_dates ON DUPLICATE KEY UPDATE 
    power = VALUES(power),
    create_time = VALUES(create_time);

select * from temp_citypower_dates;
-- 删除临时表
DROP TEMPORARY TABLE temp_citypower_dates;