关于SQL假数据生成

 

客户端连接手机数量历史记录表：

CREATE TABLE `xw_client_phone_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_user_name` varchar(255) DEFAULT NULL COMMENT '客户端用户名',
  `brand_code` varchar(255) DEFAULT NULL COMMENT '品牌编码',
  `computer_id` varchar(255) DEFAULT NULL COMMENT '电脑标识',
  `phone_num` int(11) DEFAULT NULL COMMENT '连接手机数量',
  `create_time` date DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_bcd` (`create_time`,`brand_code`,`computer_id`) USING BTREE COMMENT '每台电脑每个品牌每日唯一索引',
  KEY `idx_brand` (`brand_code`) USING BTREE COMMENT '品牌标识索引',
  KEY `idx_computer` (`computer_id`) USING BTREE COMMENT '电脑标识索引'
) ENGINE=InnoDB AUTO_INCREMENT=263167 DEFAULT CHARSET=utf8mb4 COMMENT='客户端连接手机数量历史记录表';

假数据：

SET @counter = 0;

-- Generate fake data
INSERT INTO xw_client_phone_history (client_user_name, brand_code, computer_id, phone_num, create_time, update_time, remark)
SELECT
    CONCAT('client_', FLOOR(RAND() * 100)), -- 生成随机的客户端用户名
    CONCAT('brand_', FLOOR(RAND() * 100)), -- 生成随机的品牌编码
    CONCAT('computer_', FLOOR(RAND() * 500)), -- 生成随机的电脑标识
    FLOOR(RAND() * 60), -- 生成随机的连接手机数量
    DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 90) DAY), -- 在最近三个月内生成随机日期
    NOW(), -- 当前时间戳作为更新时间
    CONCAT('Remark for record ', @counter) -- 备注
FROM
    information_schema.tables AS t1,
    information_schema.tables AS t2
ON DUPLICATE KEY UPDATE
    phone_num = VALUES(phone_num), -- 如果存在重复的唯一键，则更新连接手机数量
    update_time = NOW(), -- 更新时间戳
    remark = CONCAT('Updated remark for record ', @counter); -- 更新备注

-- Reset counter
SET @counter = 0;

 

统计表:

CREATE TABLE `xw_client_statistical` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_user_num` int(11) NOT NULL DEFAULT '0' COMMENT '新增客户端用户数量',
  `computer_num` int(11) NOT NULL DEFAULT '0' COMMENT '新增电脑数量',
  `phone_num` int(11) NOT NULL DEFAULT '0' COMMENT '新增连接手机数量',
  `stat_date` date NOT NULL COMMENT '统计数据的日期',
  `stat_type` int(11) NOT NULL DEFAULT '1' COMMENT '统计类型 1天 2月 3年',
  `create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建日期',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_stat` (`stat_date`)
) ENGINE=InnoDB AUTO_INCREMENT=262270 DEFAULT CHARSET=utf8mb4 COMMENT='后台管理服务-客户端记录统计表';

统计sql

-- 将 xw_client_phone_history 表中的数据统计到 xw_client_statistical 表中
INSERT INTO xw_client_statistical (client_user_num, computer_num, phone_num, stat_date, stat_type, create_time, remark)
SELECT
    COUNT(DISTINCT client_user_name) AS client_user_num,
    COUNT(DISTINCT computer_id) AS computer_num,
    SUM(phone_num) AS phone_num,
    create_time AS stat_date,
    1 AS stat_type,
    CURRENT_TIMESTAMP AS create_time,
    '每日统计' AS remark
FROM
    xw_client_phone_history
GROUP BY
    create_time;

-- 如果已存在统计记录，则更新现有的统计记录
INSERT INTO xw_client_statistical (client_user_num, computer_num, phone_num, stat_date, stat_type, create_time, remark)
SELECT
    COUNT(DISTINCT client_user_name) AS client_user_num,
    COUNT(DISTINCT computer_id) AS computer_num,
    SUM(phone_num) AS phone_num,
    create_time AS stat_date,
    1 AS stat_type,
    CURRENT_TIMESTAMP AS create_time,
    '每日统计' AS remark
FROM
    xw_client_phone_history
WHERE
    create_time NOT IN (SELECT stat_date FROM xw_client_statistical WHERE stat_type = 1);

-- 注意：上述查询假定 xw_client_phone_history 中的 'create_time' 是每日统计的日期。如果不是这种情况，请调整查询。