数据采集完了数据也存了但老板问昨天UV多少你还要去数据库跑SQL你需要一个数据看板。这篇文章我会从0到1教你搭建一个可用的数据看板。一、看板架构1.1 整体架构code复制┌──────────────────────────────────────────────────┐ │ 前端展示 │ │ Vue/React ECharts/D3 │ ├──────────────────────────────────────────────────┤ │ API层 │ │ Node.js/Python RESTful API │ ├──────────────────────────────────────────────────┤ │ 数据层 │ │ ClickHouse实时 MySQL配置 │ ├──────────────────────────────────────────────────┤ │ 采集层 │ │ 前端埋点 → 上报网关 → Kafka │ └──────────────────────────────────────────────────┘1.2 看板功能规划模块功能优先级概览UV/PV/留存/新增P0趋势日/周/月趋势图P0页面分析页面PV/UV排行P1来源分析场景值/渠道分布P1用户画像设备/地域/版本分布P2漏斗分析转化漏斗P2留存分析次日/7日/30日留存P2二、数据库设计2.1 汇总表设计sql复制-- 日汇总表 CREATE TABLE daily_summary ( stat_date Date, app_id String, uv UInt64, pv UInt64, new_user UInt64, session_count UInt64, avg_duration Float64, bounce_rate Float64, retention_1d Float64 DEFAULT 0, retention_7d Float64 DEFAULT 0, retention_30d Float64 DEFAULT 0, created_at DateTime DEFAULT now(), updated_at DateTime DEFAULT now() ) ENGINE ReplacingMergeTree() PARTITION BY toYYYYMM(stat_date) ORDER BY (app_id, stat_date); -- 页面汇总表 CREATE TABLE page_daily_summary ( stat_date Date, app_id String, page_path String, uv UInt64, pv UInt64, avg_stay_time Float64, exit_rate Float64, created_at DateTime DEFAULT now() ) ENGINE ReplacingMergeTree() PARTITION BY toYYYYMM(stat_date) ORDER BY (app_id, stat_date, page_path); -- 来源汇总表 CREATE TABLE source_daily_summary ( stat_date Date, app_id String, scene UInt32, source_name String, uv UInt64, pv UInt64, new_user UInt64, created_at DateTime DEFAULT now() ) ENGINE ReplacingMergeTree() PARTITION BY toYYYYMM(stat_date) ORDER BY (app_id, stat_date, scene); -- 设备汇总表 CREATE TABLE device_daily_summary ( stat_date Date, app_id String, platform String, brand String, model String, uv UInt64, pv UInt64, created_at DateTime DEFAULT now() ) ENGINE ReplacingMergeTree() PARTITION BY toYYYYMM(stat_date) ORDER BY (app_id, stat_date, platform, brand);2.2 汇总任务javascript复制const { ClickHouse } require(clickhouse); const ch new ClickHouse({ url: http://clickhouse:8123, basicAuth: { username: default, password: }, }); async function runDailySummary(date) { // 1. 日UV/PV汇总 await ch.query( INSERT INTO daily_summary (stat_date, app_id, uv, pv, new_user, session_count, avg_duration, bounce_rate) SELECT event_date, app_id, uniq(user_id) AS uv, count() AS pv, countIf(is_first_visit 1) AS new_user, uniq(session_id) AS session_count, avg(session_duration) AS avg_duration, countIf(session_pv 1) / count() AS bounce_rate FROM ( SELECT event_date, app_id, user_id, session_id, min(event_time) OVER (PARTITION BY user_id) event_time AS is_first_visit, max(event_time) OVER (PARTITION BY session_id) - min(event_time) OVER (PARTITION BY session_id) AS session_duration, count() OVER (PARTITION BY session_id) AS session_pv FROM track_events WHERE event_date ${date} AND event_name IN (app_launch, page_view) ) GROUP BY event_date, app_id ).toPromise(); // 2. 页面汇总 await ch.query( INSERT INTO page_daily_summary (stat_date, app_id, page_path, uv, pv, avg_stay_time, exit_rate) SELECT event_date, app_id, JSONExtractString(properties, pagePath) AS page_path, uniq(user_id) AS uv, count() AS pv, avg(toFloat64(JSONExtractString(properties, stayDuration))) / 1000 AS avg_stay_time, exit_count / count() AS exit_rate FROM track_events WHERE event_date ${date} AND event_name page_view GROUP BY event_date, app_id, page_path ).toPromise(); // 3. 来源汇总 await ch.query( INSERT INTO source_daily_summary (stat_date, app_id, scene, source_name, uv, pv, new_user) SELECT event_date, app_id, toUInt32(JSONExtractString(properties, scene)) AS scene, getSceneName(toUInt32(JSONExtractString(properties, scene))) AS source_name, uniq(user_id) AS uv, count() AS pv, countIf(is_first_visit 1) AS new_user FROM track_events WHERE event_date ${date} AND event_name app_launch GROUP BY event_date, app_id, scene, source_name ).toPromise(); console.log(日汇总完成: ${date}); }三、API层开发3.1 API设计方法路径说明GET/api/overview概览数据GET/api/trend趋势数据GET/api/pages页面分析GET/api/sources来源分析GET/api/devices设备分析GET/api/retention留存分析GET/api/funnel漏斗分析3.2 核心API实现javascript复制const express require(express); const app express(); const APP_ID your_app_id; // 概览数据 app.get(/api/overview, async (req, res) { const { date getToday() } req.query; const result await ch.query( SELECT uv, pv, new_user, session_count, avg_duration, bounce_rate, retention_1d, retention_7d, retention_30d FROM daily_summary WHERE app_id ${APP_ID} AND stat_date ${date} ).toPromise(); const yesterday getYesterday(date); const yesterdayResult await ch.query( SELECT uv, pv, new_user FROM daily_summary WHERE app_id ${APP_ID} AND stat_date ${yesterday} ).toPromise(); const today result[0] || {}; const yesterdayData yesterdayResult[0] || {}; res.json({ date, uv: today.uv || 0, uv_change: calcChange(today.uv, yesterdayData.uv), pv: today.pv || 0, pv_change: calcChange(today.pv, yesterdayData.pv), new_user: today.new_user || 0, new_user_change: calcChange(today.new_user, yesterdayData.new_user), avg_duration: today.avg_duration || 0, bounce_rate: today.bounce_rate || 0, retention_1d: today.retention_1d || 0, retention_7d: today.retention_7d || 0, retention_30d: today.retention_30d || 0, }); }); // 趋势数据 app.get(/api/trend, async (req, res) { const { start_date, end_date, metric uv } req.query; const result await ch.query( SELECT stat_date, uv, pv, new_user, avg_duration, bounce_rate FROM daily_summary WHERE app_id ${APP_ID} AND stat_date ${start_date} AND stat_date ${end_date} ORDER BY stat_date ).toPromise(); res.json({ dates: result.map(r r.stat_date), [metric]: result.map(r r[metric]), }); }); // 页面分析 app.get(/api/pages, async (req, res) { const { date getToday(), sort pv, limit 20 } req.query; const result await ch.query( SELECT page_path, uv, pv, avg_stay_time, exit_rate FROM page_daily_summary WHERE app_id ${APP_ID} AND stat_date ${date} ORDER BY ${sort} DESC LIMIT ${limit} ).toPromise(); res.json(result); }); // 留存分析 app.get(/api/retention, async (req, res) { const { start_date, end_date } req.query; const result await ch.query( SELECT cohort_date, day_n, cohort_size, retained, retained / cohort_size AS retention_rate FROM retention_table WHERE app_id ${APP_ID} AND cohort_date ${start_date} AND cohort_date ${end_date} ORDER BY cohort_date, day_n ).toPromise(); res.json(result); }); function getToday() { return new Date().toISOString().split(T)[0]; } function getYesterday(date) { const d new Date(date); d.setDate(d.getDate() - 1); return d.toISOString().split(T)[0]; } function calcChange(current, previous) { if (!previous || previous 0) return current 0 ? 100 : 0; return ((current - previous) / previous * 100).toFixed(1); } app.listen(8080);四、前端看板开发4.1 技术选型技术选择理由框架Vue 3轻量组件化图表ECharts功能强大中文文档完善UIElement PlusVue 3生态构建Vite快4.2 概览页面vue复制template div classdashboard h1小程序数据看板/h1 el-date-picker v-modeldate typedate / !-- 核心指标卡片 -- div classmetric-cards MetricCard titleUV :valueoverview.uv :changeoverview.uv_change icon / MetricCard titlePV :valueoverview.pv :changeoverview.pv_change icon️ / MetricCard title新增用户 :valueoverview.new_user :changeoverview.new_user_change icon / MetricCard title平均时长 :valueformatDuration(overview.avg_duration) icon⏱️ / MetricCard title跳出率 :valueformatPercent(overview.bounce_rate) icon / MetricCard title次日留存 :valueformatPercent(overview.retention_1d) icon / /div !-- 趋势图 -- div classtrend-chart h2UV趋势/h2 div reftrendChart styleheight: 400px;/div /div !-- 页面排行 -- div classpage-ranking h2页面排行/h2 el-table :datapages stripe el-table-column proppage_path label页面路径 / el-table-column propuv labelUV sortable / el-table-column proppv labelPV sortable / el-table-column propavg_stay_time label平均停留(秒) sortable / el-table-column propexit_rate label退出率 sortable / /el-table /div /div /template script setup import { ref, onMounted, watch } from vue; import * as echarts from echarts; const date ref(new Date()); const overview ref({}); const pages ref([]); const trendChart ref(null); async function fetchOverview() { const dateStr date.value.toISOString().split(T)[0]; const res await fetch(/api/overview?date${dateStr}); overview.value await res.json(); } async function fetchTrend() { const endDate date.value.toISOString().split(T)[0]; const startDate new Date(date.value); startDate.setDate(startDate.getDate() - 29); const res await fetch(/api/trend?start_date${startDate.toISOString().split(T)[0]}end_date${endDate}); const data await res.json(); const chart echarts.init(trendChart.value); chart.setOption({ tooltip: { trigger: axis }, xAxis: { type: category, data: data.dates }, yAxis: { type: value }, series: [{ data: data.uv, type: line, smooth: true, areaStyle: { opacity: 0.3 } }], }); } async function fetchPages() { const dateStr date.value.toISOString().split(T)[0]; const res await fetch(/api/pages?date${dateStr}); pages.value await res.json(); } watch(date, () { fetchOverview(); fetchTrend(); fetchPages(); }); onMounted(() { fetchOverview(); fetchTrend(); fetchPages(); }); /script4.3 留存分析热力图vue复制template div classretention-heatmap h2留存分析/h2 div refheatmapChart styleheight: 600px;/div /div /template script setup import { ref, onMounted } from vue; import * as echarts from echarts; const heatmapChart ref(null); async function fetchRetention() { const endDate new Date().toISOString().split(T)[0]; const startDate new Date(); startDate.setDate(startDate.getDate() - 30); const res await fetch(/api/retention?start_date${startDate.toISOString().split(T)[0]}end_date${endDate}); const data await res.json(); const dates [...new Set(data.map(d d.cohort_date))]; const days [...new Set(data.map(d d.day_n))].sort((a, b) a - b); const heatmapData data.map(d [dates.indexOf(d.cohort_date), d.day_n, (d.retention_rate * 100).toFixed(1)]); const chart echarts.init(heatmapChart.value); chart.setOption({ tooltip: { formatter: (p) ${dates[p.value[0]]}br/第${p.value[1]}天留存: ${p.value[2]}% }, xAxis: { type: category, data: dates, axisLabel: { rotate: 45 } }, yAxis: { type: category, data: days.map(d 第${d}天) }, visualMap: { min: 0, max: 100, inRange: { color: [#ebedf0, #9be9a8, #40c463, #30a14e, #216e39] } }, series: [{ type: heatmap, data: heatmapData, label: { show: true, formatter: (p) p.value[2] % } }], }); } onMounted(fetchRetention); /script五、实时看板5.1 WebSocket实时推送javascript复制// 服务端 const WebSocket require(ws); const wss new WebSocket.Server({ port: 8081 }); const Redis require(ioredis); const sub new Redis(); sub.subscribe(uv:realtime); sub.on(message, (channel, message) { const data JSON.parse(message); wss.clients.forEach((client) { if (client.readyState WebSocket.OPEN) { client.send(JSON.stringify({ type: uv_update, data })); } }); });vue复制!-- 客户端 -- template div classrealtime-uv h3实时UV/h3 div classuv-number{{ realtimeUV }}/div /div /template script setup import { ref, onMounted, onUnmounted } from vue; const realtimeUV ref(0); let ws null; onMounted(() { ws new WebSocket(ws://localhost:8081); ws.onmessage (event) { const data JSON.parse(event.data); if (data.type uv_update) realtimeUV.value data.data.uv; }; }); onUnmounted(() { if (ws) ws.close(); }); /script六、部署方案规模配置月成本 10万UV1台 2核4G MySQL Redis~200元10万-100万UVNginx Node×2 ClickHouse Redis Kafka~2000元 100万UVK8s集群 ClickHouse集群 Kafka集群 Flink~10000元起七、看板避坑指南坑解决方案数据延迟对齐统计口径、时区允许5%误差查询慢预聚合 Redis缓存 降采样数据不准和微信官方对比 异常检测 根因下钻写在最后数据看板是数据价值的最后一公里。搭建看板的关键从简到繁先做概览和趋势再做页面和来源最后做漏斗和留存预聚合不要每次查询都算提前算好汇总数据缓存热点数据用Redis缓存5分钟过期校准和微信官方数据对比确保口径一致记住看板是给人看的不是给机器看的。用户体验和数据准确性同样重要。