增量物化视图最佳实践

SELECT a, b, c, NOW() FROM t;

SELECT * FROM t WHERE ts >= NOW() - INTERVAL '1 hour';

SELECT ... FROM T WHERE key IS NOT NULL GROUP BY key

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

-- 创建基础物化视图
CREATE MATERIALIZED VIEW dept_stats AS
SELECT department, AVG(salary) as avg_salary, COUNT(*) as emp_count
FROM employees
GROUP BY department;
 
-- 查询时过滤
SELECT department, avg_salary
FROM dept_stats
WHERE emp_count > 5;

SELECT
  COUNT(DISTINCT CASE WHEN platform = 'iOS' THEN user_id END) as ios_uv,
  COUNT(DISTINCT CASE WHEN platform = 'Android' THEN user_id END) as android_uv
FROM user_events;

SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE platform = 'iOS') as ios_uv,
  COUNT(DISTINCT user_id) FILTER (WHERE platform = 'Android') as android_uv
FROM user_events;

-- 创建索引优化 JOIN 性能
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_products_product_id ON products(product_id);
 
-- 执行 JOIN 查询
SELECT o.order_id, p.product_name
FROM orders o
INNER JOIN products p ON o.product_id = p.product_id;

CREATE INDEX idx_user_daily_stats_user_id ON user_daily_stats(user_id);
CREATE INDEX idx_user_daily_stats_stat_date ON user_daily_stats(stat_date);

CREATE MATERIALIZED VIEW top_employees AS
SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
  FROM employees
) WHERE rn <= 3;

-- 系统将报错：Unsupported feature: window not in query top level
SELECT department, AVG(salary) OVER (PARTITION BY department)
FROM employees
WHERE avg_salary > 10000;

CREATE MATERIALIZED VIEW top_products AS
SELECT * FROM (
  SELECT product_id, category, sales,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn
  FROM product_sales
) WHERE rn <= 10;
-- 每个 category 取销售额前 10 的产品

CREATE MATERIALIZED VIEW imv
WITH (refresh_mode = 'incremental') AS
WITH base AS (
  SELECT department, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department
)
SELECT department, avg_salary
FROM base
WHERE avg_salary > 5000;

-- 基础层
CREATE MATERIALIZED VIEW base_mv
WITH (refresh_mode = 'incremental') AS
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

-- 应用层
CREATE MATERIALIZED VIEW app_mv
WITH (refresh_mode = 'incremental') AS
SELECT department, avg_salary
FROM base_mv
WHERE avg_salary > 5000;

SELECT COUNT(DISTINCT user_id)
FROM user_activities
WHERE pt = current_date
AND user_id not in (
    -- 统计过去30天出现过的所有用户
    SELECT DISTINCT user_id    
    FROM user_activities
    WHERE pt >= current_date - internal '30 day' AND pt < current_date AND pt)

-- 创建基础增量物化视图，按时间粒度聚合用户交易数据
CREATE MATERIALIZED VIEW user_transaction_base
WITH (refresh_mode = 'incremental') AS
SELECT 
  user_id,
  DATE_TRUNC('hour', transaction_time) as transaction_hour,
  DATE_TRUNC('day', transaction_time) as transaction_day,
  COUNT(*) as transaction_count,
  SUM(amount) as total_amount,
  AVG(amount) as avg_amount,
  MAX(amount) as max_amount,
  COUNT(DISTINCT merchant_id) as merchant_count
FROM transactions 
GROUP BY user_id, DATE_TRUNC('hour', transaction_time), DATE_TRUNC('day', transaction_time);
 
-- 创建高级物化视图，基于基础物化视图进行进一步聚合
CREATE MATERIALIZED VIEW user_risk_metrics
WITH (refresh_mode = 'incremental') AS
SELECT
  user_id,
  transaction_day,
  SUM(transaction_count) as daily_transaction_count,
  SUM(total_amount) as daily_total_amount,
  AVG(avg_amount) as daily_avg_amount,
  MAX(max_amount) as daily_max_amount,
  AVG(transaction_count) as hourly_avg_transactions,
  STDDEV(transaction_count) as transaction_count_stddev
FROM user_transaction_base
GROUP BY user_id, transaction_day;

-- 创建基础增量物化视图，按设备和时间粒度聚合传感器数据
CREATE MATERIALIZED VIEW device_sensor_base
WITH (refresh_mode = 'incremental') AS
SELECT 
  device_id,
  device_type,
  DATE_TRUNC('hour', reading_time) as reading_hour,
  DATE_TRUNC('day', reading_time) as reading_day,
  AVG(temperature) as avg_temperature,
  MAX(temperature) as max_temperature,
  MIN(temperature) as min_temperature,
  AVG(pressure) as avg_pressure,
  STDDEV(vibration) as vibration_stddev,
  COUNT(*) as reading_count,
  COUNT(CASE WHEN temperature > 80 THEN 1 END) as high_temp_count
FROM sensor_readings 
GROUP BY device_id, device_type, DATE_TRUNC('hour', reading_time), DATE_TRUNC('day', reading_time);
 
-- 创建高级物化视图，基于基础物化视图计算设备健康指标
CREATE MATERIALIZED VIEW device_health_metrics
WITH (refresh_mode = 'incremental') AS
SELECT
  device_id,
  device_type,
  reading_day,
  AVG(avg_temperature) as daily_avg_temperature,
  MAX(max_temperature) as daily_max_temperature,
  AVG(vibration_stddev) as daily_avg_vibration_stddev,
  SUM(high_temp_count) as daily_high_temp_count,
  COUNT(*) as hourly_readings_count,
  -- 计算设备异常率
  SUM(CASE WHEN high_temp_count > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as high_temp_rate
FROM device_sensor_base
GROUP BY device_id, device_type, reading_day;

-- 创建基础增量物化视图，按服务和时间粒度聚合日志数据
CREATE MATERIALIZED VIEW log_base_metrics
WITH (refresh_mode = 'incremental') AS
SELECT 
  service_name,
  DATE_TRUNC('hour', log_time) as log_hour,
  DATE_TRUNC('day', log_time) as log_day,
  log_level,
  COUNT(*) as log_count,
  COUNT(CASE WHEN log_level = 'ERROR' THEN 1 END) as error_count,
  COUNT(CASE WHEN log_level = 'WARN' THEN 1 END) as warn_count,
  COUNT(CASE WHEN log_level = 'INFO' THEN 1 END) as info_count,
  COUNT(DISTINCT host_ip) as active_hosts,
  APPROX_COUNT_DISTINCT(user_id) as affected_users
FROM application_logs 
GROUP BY service_name, DATE_TRUNC('hour', log_time), DATE_TRUNC('day', log_time), log_level;
 
-- 创建高级物化视图，基于基础物化视图计算系统健康度指标
CREATE MATERIALIZED VIEW system_health_metrics
WITH (refresh_mode = 'incremental') AS
SELECT
  service_name,
  log_day,
  log_hour,
  SUM(log_count) as total_logs,
  SUM(error_count) as total_errors,
  SUM(warn_count) as total_warnings,
  -- 计算错误率
  CASE 
    WHEN SUM(log_count) > 0 THEN SUM(error_count) * 100.0 / SUM(log_count)
    ELSE 0 
  END as error_rate,
  -- 计算警告率
  CASE 
    WHEN SUM(log_count) > 0 THEN SUM(warn_count) * 100.0 / SUM(log_count)
    ELSE 0 
  END as warning_rate,
  MAX(active_hosts) as peak_active_hosts,
  SUM(affected_users) as total_affected_users
FROM log_base_metrics
GROUP BY service_name, log_day, log_hour;