超函数 #

超函数分类：

时间桶函数
├── time_bucket
├── time_bucket_ng
└── time_bucket_gapfill

统计分析函数
├── stats_agg
├── percentile_agg
└── counter_agg

时间分析函数
├── time_weight
├── state_agg
└── interval_agg

近似计算函数
├── approx_percentile
├── approx_row_count
└── distinct_count

-- 超函数已内置在TimescaleDB 2.6+
-- 无需额外安装

-- 检查超函数是否可用
SELECT * FROM pg_proc
WHERE proname LIKE '%time_bucket%'
LIMIT 5;

-- 查看超函数扩展
SELECT * FROM pg_extension
WHERE extname LIKE '%timescaledb%';

-- 基本用法
SELECT 
    time_bucket('1 hour', time) AS hour,
    AVG(temperature) AS avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

-- 带偏移量
SELECT 
    time_bucket('1 hour', time, '30 minutes'::interval) AS hour,
    AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY hour;

-- 带起始时间
SELECT 
    time_bucket('1 day', time, NULL, '2024-01-01'::timestamptz) AS day,
    AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY day;

-- time_bucket_ng（Next Generation）
-- 支持更多时间单位和origin参数

SELECT 
    time_bucket_ng('1 hour', time) AS hour,
    AVG(temperature) AS avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

-- 使用origin参数
SELECT 
    time_bucket_ng('1 week', time, origin => '2024-01-01'::timestamptz) AS week,
    AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY week;

-- 填充时间间隙
SELECT 
    time_bucket_gapfill('1 hour', time, NOW() - INTERVAL '24 hours', NOW()) AS hour,
    sensor_id,
    locf(AVG(temperature)) AS avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
  AND sensor_id = 1
GROUP BY hour, sensor_id
ORDER BY hour;

-- gapfill参数
-- start: 开始时间
-- end: 结束时间
-- 填充缺失的时间桶

-- locf: Last Observation Carried Forward
SELECT 
    time_bucket_gapfill('1 hour', time, start, end) AS hour,
    locf(AVG(temperature)) AS avg_temp
FROM sensor_data
GROUP BY hour;

-- interpolate: 线性插值
SELECT 
    time_bucket_gapfill('1 hour', time, start, end) AS hour,
    interpolate(AVG(temperature)) AS avg_temp
FROM sensor_data
GROUP BY hour;

-- 创建统计聚合
SELECT 
    time_bucket('1 hour', time) AS hour,
    stats_agg(temperature) AS stats
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour;

-- 提取统计值
SELECT 
    time_bucket('1 hour', time) AS hour,
    average(stats_agg(temperature)) AS avg_temp,
    stddev(stats_agg(temperature)) AS std_temp,
    variance(stats_agg(temperature)) AS var_temp,
    min(stats_agg(temperature)) AS min_temp,
    max(stats_agg(temperature)) AS max_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

-- 计算百分位数
SELECT 
    time_bucket('1 hour', time) AS hour,
    percentile_agg(temperature) AS percentiles
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour;

-- 提取百分位数
SELECT 
    time_bucket('1 hour', time) AS hour,
    approx_percentile(0.5, percentile_agg(temperature)) AS median,
    approx_percentile(0.95, percentile_agg(temperature)) AS p95,
    approx_percentile(0.99, percentile_agg(temperature)) AS p99
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

-- 计数器聚合（处理重置）
SELECT 
    time_bucket('1 hour', time) AS hour,
    counter_agg(time, counter_value) AS counter_stats
FROM counter_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour;

-- 提取计数器值
SELECT 
    time_bucket('1 hour', time) AS hour,
    delta(counter_agg(time, counter_value)) AS delta_value,
    rate(counter_agg(time, counter_value)) AS rate_value
FROM counter_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

-- 时间加权平均
SELECT 
    time_bucket('1 hour', time) AS hour,
    time_weight('Linear', time, temperature) AS tw_avg
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour;

-- 提取时间加权值
SELECT 
    time_bucket('1 hour', time) AS hour,
    average(time_weight('Linear', time, temperature)) AS tw_avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

-- Linear: 线性加权
SELECT 
    time_bucket('1 hour', time) AS hour,
    average(time_weight('Linear', time, temperature)) AS avg_temp
FROM sensor_data
GROUP BY hour;

-- LOCF: Last Observation Carried Forward
SELECT 
    time_bucket('1 hour', time) AS hour,
    average(time_weight('LOCF', time, temperature)) AS avg_temp
FROM sensor_data
GROUP BY hour;

-- 状态聚合
SELECT 
    time_bucket('1 hour', time) AS hour,
    state_agg(time, status) AS state_stats
FROM status_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour;

-- 提取状态持续时间
SELECT 
    time_bucket('1 hour', time) AS hour,
    duration_in(state_agg(time, status), 'active') AS active_duration,
    duration_in(state_agg(time, status), 'inactive') AS inactive_duration
FROM status_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

-- 区间聚合
SELECT 
    time_bucket('1 day', time) AS day,
    interval_agg(time, end_time) AS interval_stats
FROM interval_data
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY day;

-- 提取区间统计
SELECT 
    time_bucket('1 day', time) AS day,
    range(interval_agg(time, end_time)) AS total_range,
    duration(interval_agg(time, end_time)) AS total_duration
FROM interval_data
GROUP BY day;

-- 近似百分位数
SELECT 
    time_bucket('1 hour', time) AS hour,
    approx_percentile(0.5, temperature) AS median,
    approx_percentile(0.95, temperature) AS p95,
    approx_percentile(0.99, temperature) AS p99
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

-- 近似行数
SELECT approx_row_count('sensor_data');

-- 比较精确计数和近似计数
SELECT 
    count(*) as exact_count,
    approx_row_count('sensor_data') as approx_count;

-- 近似去重计数
SELECT 
    time_bucket('1 day', time) AS day,
    distinct_count(sensor_id) AS unique_sensors
FROM sensor_data
WHERE time > NOW() - INTERVAL '30 days'
GROUP BY day
ORDER BY day;

-- 小时聚合
WITH hourly_stats AS (
    SELECT 
        time_bucket('1 hour', time) AS hour,
        sensor_id,
        stats_agg(temperature) AS temp_stats
    FROM sensor_data
    WHERE time > NOW() - INTERVAL '7 days'
    GROUP BY hour, sensor_id
)
-- 日聚合
SELECT 
    time_bucket('1 day', hour) AS day,
    sensor_id,
    average(rollup(temp_stats)) AS avg_temp,
    stddev(rollup(temp_stats)) AS std_temp
FROM hourly_stats
GROUP BY day, sensor_id
ORDER BY day, sensor_id;

-- 使用rollup组合多个聚合
SELECT 
    time_bucket('1 day', time) AS day,
    sensor_id,
    average(rollup(stats_agg(temperature))) AS avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '30 days'
GROUP BY day, sensor_id
ORDER BY day, sensor_id;

-- 系统监控统计
SELECT 
    time_bucket('5 minutes', time) AS bucket,
    average(stats_agg(cpu_usage)) AS avg_cpu,
    approx_percentile(0.95, percentile_agg(cpu_usage)) AS p95_cpu,
    max(cpu_usage) AS max_cpu,
    average(stats_agg(memory_usage)) AS avg_memory
FROM system_metrics
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY bucket
ORDER BY bucket;

-- 传感器数据分析
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    average(time_weight('Linear', time, temperature)) AS avg_temp,
    approx_percentile(0.5, percentile_agg(temperature)) AS median_temp,
    approx_percentile(0.95, percentile_agg(temperature)) AS p95_temp,
    stddev(stats_agg(temperature)) AS std_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour, sensor_id
ORDER BY hour, sensor_id;

-- 股票价格分析
SELECT 
    time_bucket('1 day', time) AS day,
    symbol,
    min(price) AS low,
    max(price) AS high,
    average(time_weight('Linear', time, price)) AS vwap,
    sum(volume) AS total_volume
FROM stock_data
WHERE time > NOW() - INTERVAL '30 days'
GROUP BY day, symbol
ORDER BY day, symbol;

-- 创建连续聚合预计算
CREATE MATERIALIZED VIEW hourly_sensor_stats
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    stats_agg(temperature) AS temp_stats,
    percentile_agg(temperature) AS temp_percentiles
FROM sensor_data
GROUP BY hour, sensor_id;

-- 查询预计算结果
SELECT 
    hour,
    sensor_id,
    average(temp_stats) AS avg_temp,
    approx_percentile(0.95, temp_percentiles) AS p95_temp
FROM hourly_sensor_stats
WHERE hour > NOW() - INTERVAL '24 hours'
ORDER BY hour, sensor_id;

-- 为超函数查询创建索引
CREATE INDEX idx_sensor_time ON sensor_data (sensor_id, time DESC);

-- 使用时间范围查询
SELECT 
    time_bucket('1 hour', time) AS hour,
    average(stats_agg(temperature)) AS avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
  AND sensor_id = 1
GROUP BY hour;