可视化与 OLAP 数仓集成

用户操作 → BI 生成 SQL → 数仓解析/优化 → 扫描/聚合 → 返回结果 → BI 变换 → 渲染
   ↑                                                              ↓
   └──────────────── 缓存（可命中于任意环节）────────────────────┘

-- ClickHouse：找出最近 1 小时最慢的 20 条查询
SELECT query_duration_ms, read_rows,
       formatReadableSize(memory_usage) AS mem, query
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC LIMIT 20;

# 语义层定义示例（dbt metrics 风格）
metrics:
  - { name: gmv, label: 成交额, type: sum, timestamp: order_time,
      sql: "CASE WHEN pay_status = 'paid' THEN amount ELSE 0 END",
      dimensions: [region, channel, category, is_new_customer],
      filters: ["order_status != 'cancelled'"] }
  - { name: active_users, label: 活跃用户数, type: count_distinct,
      sql: "user_id", timestamp: event_time }
  - { name: arpu, label: 客单价, type: derived,
      expression: "gmv / NULLIF(active_users, 0)" }

-- 面向 BI 的宽表视图：把常用维度预先 JOIN 好
CREATE VIEW bi.v_orders_wide AS
SELECT o.order_id, o.order_time, o.amount, o.pay_status,
       r.region_name AS region, c.channel_name AS channel,
       p.category_l1, p.category_l2, u.user_level, u.register_date,
       if(u.register_date = toDate(o.order_time), 1, 0) AS is_new_user
FROM dw.fact_orders o
LEFT JOIN dw.dim_region r ON o.region_id = r.region_id
LEFT JOIN dw.dim_channel c ON o.channel_id = c.channel_id
LEFT JOIN dw.dim_product p ON o.product_id = p.product_id
LEFT JOIN dw.dim_user u ON o.user_id = u.user_id;

CREATE DICTIONARY dw.dict_region (region_id UInt32, region_name String)
PRIMARY KEY region_id
SOURCE(CLICKHOUSE(TABLE 'dw.dim_region'))
LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);    -- 5~10 分钟自动刷新

-- 查询时用 dictGet 替代 JOIN
SELECT dictGet('dw.dict_region', 'region_name', region_id) AS region, sum(amount) AS gmv
FROM dw.fact_orders GROUP BY region;

-- ClickHouse 的 AggregatingMergeTree + 物化视图
-- 目标：按 (region, channel, 日期) 预聚合 GMV、订单数与去重用户数
CREATE TABLE dw.mv_daily_agg (
  region LowCardinality(String),
  channel LowCardinality(String),
  stat_date Date,
  gmv_state AggregateFunction(sum, Decimal(18, 2)),
  order_cnt_state AggregateFunction(count),
  user_cnt_state AggregateFunction(uniq, UInt64)      -- 去重计数的中间态
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(stat_date)
ORDER BY (stat_date, region, channel);

CREATE MATERIALIZED VIEW dw.mv_daily_agg_mv TO dw.mv_daily_agg AS
SELECT region, channel, toDate(order_time) AS stat_date,
       sumState(amount) AS gmv_state, countState() AS order_cnt_state,
       uniqState(user_id) AS user_cnt_state
FROM dw.fact_orders WHERE pay_status = 'paid' GROUP BY region, channel, stat_date;

-- 查询时用 Merge 后缀函数还原
SELECT region, channel, sumMerge(gmv_state) AS gmv,
       countMerge(order_cnt_state) AS orders, uniqMerge(user_cnt_state) AS users
FROM dw.mv_daily_agg WHERE stat_date >= today() - 30 GROUP BY region, channel;

-- Doris：创建物化视图，查询时自动匹配（透明改写）
CREATE MATERIALIZED VIEW mv_daily_agg
DISTRIBUTED BY HASH(region) BUCKETS 8
REFRESH ASYNC EVERY INTERVAL 10 MINUTE
AS SELECT region, channel, DATE(order_time) AS stat_date,
          SUM(amount) AS gmv, COUNT(*) AS orders, COUNT(DISTINCT user_id) AS users
FROM dw.fact_orders GROUP BY region, channel, stat_date;

-- 反例：BI 生成的多层嵌套，中间层没有减少数据量
SELECT region, SUM(gmv) FROM (
  SELECT region, channel, SUM(amount) AS gmv FROM fact_orders GROUP BY region, channel
) t GROUP BY region;
-- 正例：合并为单层聚合，引擎只需扫一次
SELECT region, SUM(amount) AS gmv FROM fact_orders GROUP BY region;

-- 正例：范围比较，命中分区裁剪
WHERE order_time >= '2026-01-01' AND order_time < '2026-02-01'
-- 反例：函数包装破坏裁剪，退化为全表扫描
WHERE toYYYYMM(order_time) = 202601

-- 日期维度表：预先生成所有日期及其归属的周/月/季/年
CREATE TABLE dw.dim_date (
  date_key Date, year UInt16, quarter UInt8, month UInt8, week UInt8,
  year_month String, year_quarter String, is_weekend UInt8
) ENGINE = MergeTree() ORDER BY date_key;

SELECT d.year_month AS period, sum(o.amount) AS gmv
FROM dw.fact_orders o JOIN dw.dim_date d ON toDate(o.order_time) = d.date_key
WHERE o.order_time >= today() - 365 GROUP BY period ORDER BY period;

-- 一次查询同时算出本期与去年同期
SELECT region,
  sumIf(amount, order_time >= today() - 30) AS current_gmv,
  sumIf(amount, order_time >= today() - 395 AND order_time < today() - 365) AS last_year_gmv,
  round((current_gmv - last_year_gmv) / nullIf(last_year_gmv, 0) * 100, 2) AS yoy_pct
FROM dw.fact_orders WHERE order_time >= today() - 395
GROUP BY region;

-- 精确去重：内存高、慢；近似去重：误差约 0.5%、内存低数倍
SELECT COUNT(DISTINCT user_id) FROM fact_events WHERE dt = today();
SELECT uniqCombined(user_id)   FROM fact_events WHERE dt = today();

-- 预聚合去重：按天存中间态，查询时按月上卷
SELECT toStartOfMonth(stat_date) AS month, uniqMerge(user_cnt_state) AS mau
FROM dw.mv_daily_agg GROUP BY month;

SET use_query_cache = 1;
SET query_cache_ttl = 300;        -- 5 分钟
SELECT region, sum(amount) FROM dw.fact_orders WHERE order_date = today() GROUP BY region;

# 缓存预热：在数据更新完成后主动刷新高频图表
import requests
CHART_IDS = [1, 5, 12, 23]        # 仪表盘首屏的高频图表
for chart_id in CHART_IDS:
    requests.get(f"http://superset:8088/api/v1/chart/{chart_id}/data/",
                 headers={"Authorization": f"Bearer {TOKEN}"},
                 params={"force": "false"}, timeout=60)   # 不强制，命中则跳过

实时 GMV   1,284万 （更新于 10:32:15）   ← 秒级流式
区域排行（数据截至今日 10:00）            ← 小时级物化视图
用户留存（数据截至昨日）                  ← T+1 离线

<!-- ClickHouse 用户级限制 -->
<bi_readonly>
  <max_concurrent_queries_for_user>10</max_concurrent_queries_for_user>
  <max_memory_usage>20000000000</max_memory_usage>   <!-- 20GB -->
  <max_execution_time>60</max_execution_time>
  <readonly>1</readonly>
</bi_readonly>

-- 按 BI 用户统计资源消耗，用于成本分摊与治理
SELECT user, count() AS query_count,
       sum(query_duration_ms) / 1000 AS total_seconds,
       formatReadableSize(sum(read_bytes)) AS total_read
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 DAY
GROUP BY user ORDER BY total_seconds DESC;

-- ClickHouse 建表的关键设计：分区键 + 排序键 + 低基数优化
CREATE TABLE dw.fact_orders (
  order_id UInt64, order_time DateTime,
  region LowCardinality(String), channel LowCardinality(String),  -- 低基数走字典编码
  amount Decimal(18, 2), user_id UInt64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_time)     -- 按月份分区，支持裁剪
ORDER BY (region, order_time)         -- 排序键：高频过滤列在前
TTL order_time + INTERVAL 24 MONTH;   -- 自动过期，控制存储成本

初始: 首屏 8 秒，点筛选器后 32 秒
诊断: 单查询扫描 2.4 亿行（read_rows 是结果行数的 5 万倍）；查询是 COUNT(DISTINCT
      user_id) 按 5 维分组；明细表无预聚合，排序键是 (order_id)

步骤 1（收益最大）: 建 AggregatingMergeTree 预聚合 → 扫描量降到 12 万行，32s → 1.8s
步骤 2: COUNT(DISTINCT) 换 uniqState/uniqMerge → 内存降 90%，1.8s → 900ms
步骤 3: 排序键改为 (region, channel, stat_date) → 裁剪生效，900ms → 500ms
步骤 4: BI 层缓存（TTL 10 分钟）+ 数据更新后预热 → 命中 400ms，未命中 500ms

最终: 首屏 8s → 1.2s，筛选器响应 32s → 400ms