95 lines
4.5 KiB
SQL
95 lines
4.5 KiB
SQL
-- =====================================================
|
||
-- 模拟传感器数据生成脚本
|
||
-- 为单个用户和设备生成高体量生命体征数据
|
||
-- 用户ID: eed3824b-bba1-4309-8048-19d17367c084
|
||
-- 设备ID: 12345678-1234-5678-9abc-123456789012
|
||
-- 按分钟级别生成过去7天内的数据
|
||
-- =====================================================
|
||
|
||
DO $$
|
||
DECLARE
|
||
v_user_id UUID := 'eed3824b-bba1-4309-8048-19d17367c084';
|
||
v_device_id UUID := '12345678-1234-5678-9abc-123456789012';
|
||
BEGIN
|
||
-- 已直接初始化用户ID和设备ID
|
||
|
||
-- 步数数据 (每日1440条，共 7 天)
|
||
INSERT INTO public.ss_sensor_measurements (id, device_id, user_id, measurement_type, measured_at, unit, raw_data, created_at)
|
||
SELECT
|
||
uuid_generate_v4(), v_device_id, v_user_id, 'steps', ts, 'count', jsonb_build_object('count', (floor(random()*2000 + 1000))::int), now()
|
||
FROM generate_series(now() - interval '7 days', now(), '1 minute') AS ts;
|
||
|
||
INSERT INTO public.ss_steps (measurement_id, step_count, created_at)
|
||
SELECT id, (raw_data->> 'count')::bigint, created_at
|
||
FROM public.ss_sensor_measurements
|
||
WHERE measurement_type = 'steps'
|
||
AND measured_at >= now() - interval '7 days';
|
||
|
||
-- 心率数据
|
||
INSERT INTO public.ss_sensor_measurements (id, device_id, user_id, measurement_type, measured_at, unit, raw_data, created_at)
|
||
SELECT
|
||
uuid_generate_v4(), v_device_id, v_user_id, 'heart_rate', ts, 'bpm', jsonb_build_object('bpm', (floor(random()*40 + 60))::int), now()
|
||
FROM generate_series(now() - interval '7 days', now(), '1 minute') AS ts;
|
||
|
||
INSERT INTO public.ss_heart_rate (measurement_id, heart_rate_bpm, created_at)
|
||
SELECT id, (raw_data->> 'bpm')::int, created_at
|
||
FROM public.ss_sensor_measurements
|
||
WHERE measurement_type = 'heart_rate'
|
||
AND measured_at >= now() - interval '7 days';
|
||
|
||
-- 血氧 (SpO2)
|
||
INSERT INTO public.ss_sensor_measurements (id, device_id, user_id, measurement_type, measured_at, unit, raw_data, created_at)
|
||
SELECT
|
||
uuid_generate_v4(), v_device_id, v_user_id, 'spo2', ts, '%', jsonb_build_object('spo2', (floor(random()*5 + 92))::int), now()
|
||
FROM generate_series(now() - interval '7 days', now(), '5 minutes') AS ts;
|
||
|
||
INSERT INTO public.ss_spo2 (measurement_id, spo2_percent, created_at)
|
||
SELECT id, (raw_data->> 'spo2')::int, created_at
|
||
FROM public.ss_sensor_measurements
|
||
WHERE measurement_type = 'spo2'
|
||
AND measured_at >= now() - interval '7 days';
|
||
|
||
-- 血压
|
||
INSERT INTO public.ss_sensor_measurements (id, device_id, user_id, measurement_type, measured_at, unit, raw_data, created_at)
|
||
SELECT
|
||
uuid_generate_v4(), v_device_id, v_user_id, 'bp', ts, 'mmHg',
|
||
jsonb_build_object('systolic', (floor(random()*20 + 110))::int,
|
||
'diastolic', (floor(random()*10 + 70))::int), now()
|
||
FROM generate_series(now() - interval '7 days', now(), '1 hour') AS ts;
|
||
|
||
INSERT INTO public.ss_blood_pressure (measurement_id, systolic, diastolic, created_at)
|
||
SELECT id,
|
||
(raw_data->> 'systolic')::int,
|
||
(raw_data->> 'diastolic')::int,
|
||
created_at
|
||
FROM public.ss_sensor_measurements
|
||
WHERE measurement_type = 'bp'
|
||
AND measured_at >= now() - interval '7 days';
|
||
|
||
-- 体温
|
||
INSERT INTO public.ss_sensor_measurements (id, device_id, user_id, measurement_type, measured_at, unit, raw_data, created_at)
|
||
SELECT
|
||
uuid_generate_v4(), v_device_id, v_user_id, 'temp', ts, 'C', jsonb_build_object('temp', (random()*0.5 + 36.5)::numeric), now()
|
||
FROM generate_series(now() - interval '7 days', now(), '30 minutes') AS ts;
|
||
|
||
INSERT INTO public.ss_temperature (measurement_id, temperature_c, created_at)
|
||
SELECT id, (raw_data->> 'temp')::float, created_at
|
||
FROM public.ss_sensor_measurements
|
||
WHERE measurement_type = 'temp'
|
||
AND measured_at >= now() - interval '7 days';
|
||
|
||
-- 步幅
|
||
INSERT INTO public.ss_sensor_measurements (id, device_id, user_id, measurement_type, measured_at, unit, raw_data, created_at)
|
||
SELECT
|
||
uuid_generate_v4(), v_device_id, v_user_id, 'stride', ts, 'cm', jsonb_build_object('stride', (random()*30 + 50)::numeric), now()
|
||
FROM generate_series(now() - interval '7 days', now(), '5 minutes') AS ts;
|
||
|
||
INSERT INTO public.ss_stride_length (measurement_id, stride_cm, created_at)
|
||
SELECT id, (raw_data->> 'stride')::float, created_at
|
||
FROM public.ss_sensor_measurements
|
||
WHERE measurement_type = 'stride'
|
||
AND measured_at >= now() - interval '7 days';
|
||
|
||
RAISE NOTICE '✔️ 模拟数据生成完成';
|
||
END $$;
|