文章目录
- 测试案例
- 定时任务相关
前提是已经安装好timescaledb插件。
测试案例
-- 1. 创建普通表CREATETABLEsensor_data(timeTIMESTAMPTZNOTNULL,-- 时间戳（必须包含，且不能为空）device_idTEXTNOTNULL,-- 设备IDtemperatureDOUBLEPRECISIONNULL,-- 温度humidityDOUBLEPRECISIONNULL,-- 湿度cpu_usageDOUBLEPRECISIONNULL-- CPU使用率);-- 2. 将普通表转换为超表（按 time 字段自动分区）-- 如果表里已经有数据，可以加上 migrate_data => true 参数SELECTcreate_hypertable('sensor_data','time');-- 模拟插入几条带有当前时间和过去时间的数据INSERTINTOsensor_data(time,device_id,temperature,humidity,cpu_usage)VALUES(NOW(),'device_001',23.5,45.2,12.5),(NOW()-INTERVAL'10 minutes','device_001',23.1,45.0,11.8),(NOW()-INTERVAL'20 minutes','device_002',25.8,50.1,35.2),(NOW(),'device_002',26.0,50.5,38.1);-- 基础查询：获取 device_001 最近 1 小时的所有数据SELECT*FROMsensor_dataWHEREdevice_id='device_001'ANDtime>NOW()-INTERVAL'1 hour'ORDERBYtimeDESC;-- 高级聚合：使用 time_bucket 计算每个设备每 10 分钟的平均温度和最高 CPU 使用率SELECTtime_bucket('10 minutes',time)AStime_interval,device_id,AVG(temperature)ASavg_temp,MAX(cpu_usage)ASmax_cpuFROMsensor_dataGROUPBYtime_interval,device_idORDERBYtime_intervalDESC;-- 实用函数：获取每个设备最近一次的温度记录SELECTdevice_id,last(temperature,time)ASlatest_tempFROMsensor_dataGROUPBYdevice_id;-- 修改：将 device_001 过去 30 分钟内的 CPU 使用率统一修正为 15.0UPDATEsensor_dataSETcpu_usage=15.0WHEREdevice_id='device_001'ANDtime>NOW()-INTERVAL'30 minutes';-- 删除：删除 humidity（湿度）小于 30 的异常数据DELETEFROMsensor_dataWHEREhumidity<30;-- 启用数据压缩（必须先设置压缩的分组和排序字段）ALTERTABLEsensor_dataSET(timescaledb.compress,timescaledb.compress_segmentby='device_id',-- 按设备ID分组压缩timescaledb.compress_orderby='time DESC'-- 按时间倒序排列);-- 添加压缩策略：自动压缩 7 天前的数据（大幅节省磁盘空间）SELECTadd_compression_policy('sensor_data',INTERVAL'7 days');-- 添加数据保留策略：自动删除 30 天前的数据（防止磁盘写满）SELECTadd_retention_policy('sensor_data',INTERVAL'30 days');
