Go Wind UBA 拆解系列 - OLAP 与 SQL 硬核25 个分析模型怎么落地本文回答一个问题25 个分析模型漏斗 / 留存 / LTV / 归因 / 路径……在 ClickHouse 和 Doris 上到底是用什么 SQL 写出来的双引擎又是怎么做到一份业务模型两份实现的一、为什么是双引擎先说结论GoWind UBA 支持ClickHouse 和 Apache Doris 二选一部署时按需选其一运行时通过一个开关切换。这是一个很有意思的工程选择——为什么不让用户自己写 SQL而要维护两份因为25 个分析模型 × 2 套方言 50 份 SQL但其中只有少量函数是不同的。平台的赌注是把相同的 SQL 骨架抽出来只换少数分歧的函数和驱动 API就能让同一套业务逻辑跑在两个引擎上给用户选择权。让我们看看这个赌注怎么兑现的。二、双引擎切换一个编译期常量切换开关在backend/app/core/service/internal/data/data.go// UseClickHouse 是否使用ClickHouse作为数据存储否则使用Doris。constUseClickHouseboolfalse注意这是一个const bool不是运行期配置。想换引擎改这一行重编译。这是个诚实的取舍——双引擎在运行期动态切换会让代码复杂度爆炸要同时维护两套连接池、做事务一致性等而绝大多数生产部署只会选一个引擎。用编译期常量换来的是另一个引擎的 client 连接都不会建立。// client/clickhouse_client.gofuncNewClickHouseClient(ctx*bootstrap.Context)(*clickhouseCrud.Client,func(),error){if!data.UseClickHouse{returnnil,func(){},nil// 永远不连 ClickHouse}// ...建立连接}// client/doris_client.gofuncNewDorisClient(ctx*bootstrap.Context)(*dorisCrud.Client,func(),error){ifdata.UseClickHouse{returnnil,func(){},nil// 永远不连 Doris}// ...}两个 repo 在 Wire 里都注册但只有被选中那个的 client 会真连。Service 层持两个 repo 指针按常量分支func(s*AnalyticsService)repo()interface{/* 24 个分析方法的接口 */}{ifdata.UseClickHouse{returns.ckRepo}returns.dorisRepo}这个repo()返回一个内联接口——列出所有 24 个分析方法签名。两套 repo 都实现它service 层无感。if data.UseClickHouse这个分支模式在 10 个 service 文件里重复出现behavior_event_service/risk_event_service/session_service……。三、Schema同一张表两种 DDL最基础的事实表是events_fact。在 Go 代码里它有两份镜像定义internal/data/doris/schema/events_fact.go—— 用db:taginternal/data/clickhouse/schema/events_fact.go—— 用ch:tag注意作者故意没有用一个 struct 两套 tag的统一方案而是维护两份。原因是两个引擎对字段的语义差异不小比如 ClickHouse 有MATERIALIZED物化列、原生 map 类型Doris 有GENERATED ALWAYS列、自定义 Map 别名硬塞一个 struct 反而别扭。字段大概有 50 个事件主体event_id/tenant_id/user_id、时间event_time/event_ts/server_time、行为event_category/event_name、客体object_*、上下文session_id/platform/os/channel、地图列context/metrics/properties 都是 map、企业字段risk_level/trace_id、点击热力图click_x/click_y/element_xpath/page_url、游戏专属server_id/level。3.1 ClickHouse DDLsql/clickhouse/1_base_tables.sqlCREATETABLEIFNOTEXISTSgw_uba.events_fact(event_id String,tenant_id UInt32COMMENT租户 IDSaaS 多租户隔离所有查询必须带此条件,user_id UInt32,...event_time DateTime64(3),event_dateDateMATERIALIZED toDate(event_time),-- 物化列分区TTL 用event_ts Int64 MATERIALIZED toUnixTimestamp64Milli(event_time),...event_category LowCardinality(String),-- 低基数优化event_name LowCardinality(String),...context Map(String,String),metrics Map(String,Float64),properties Map(String,String),-- 跳数索引data-skippingINDEXidx_object_id object_idTYPEbloom_filter(0.01)GRANULARITY4,INDEXidx_context_keys mapKeys(context)TYPEbloom_filter(0.01)GRANULARITY2,INDEXidx_element_xpath element_xpathTYPEngrambf_v1(3,5,2,0)GRANULARITY4,-- ngram 分词子串搜索INDEXidx_risk risk_levelTYPEset(4)GRANULARITY1,...)ENGINEMergeTreePARTITIONBYtoYYYYMM(event_date)-- 按月分区ORDERBY(tenant_id,event_category,event_date,event_name,event_ts)-- tenant_id 是首列TTL event_dateINTERVAL180DAYSETTINGS index_granularity8192,ttl_only_drop_parts1;要点tenant_id是 ORDER BY 首列——租户的事件在排序的 part 里物理连续租户级查询能跳过无关 granule。物化列event_date和event_ts由event_time推导写入不重复存储读取时计算。TTL ttl_only_drop_parts过期按整 part 删除而非行级效率高。跳数索引bloom_filter精确等值、ngrambf_v1XPath 子串搜索热力图用、set极低基数。LowCardinality(String)对event_name这类枚举字段用字典编码省存储加速扫描。3.2 Doris DDLsql/doris/1_base_tables.sqlCREATETABLEIFNOTEXISTSevents_fact(event_idVARCHAR(128)NOTNULL,tenant_idINTNOTNULL,event_time DATETIMEV2(3)NOTNULL,...event_tsBIGINTGENERATED ALWAYSAS(UNIX_TIMESTAMP(event_time)*1000),-- 生成列...context MAPSTRING,STRING,metrics MAPSTRING,DOUBLE,properties MAPSTRING,STRING,...INDEXidx_event_name(event_name)USINGINVERTED,-- 倒排索引INDEXidx_user_id(user_id)USINGINVERTED,...-- 12 个倒排索引)UNIQUEKEY(event_id,tenant_id,event_time)-- 唯一键去重PARTITIONBYRANGE(event_time)()-- 静态分区为空DISTRIBUTEDBYHASH(event_id,tenant_id)BUCKETS16-- tenant_id 参与 hash租户数据同桶聚集PROPERTIES(dynamic_partition.enabletrue,-- 动态分区dynamic_partition.time_unitDAY,dynamic_partition.start-180,-- 保留 180 天dynamic_partition.end3,-- 预创建未来 3 天enable_unique_key_merge_on_writetrue-- MoW 模式按 event_id 去重);要点UNIQUE KEY MoWDoris 的唯一键模型 写时合并按event_id去重ClickHouse 用的是纯 append 的 MergeTree靠 ReplacingMergeTree 在别处实现 upsert。动态分区start-180, end3Doris 自动按天创建/裁剪分区等价于 ClickHouse 的 TTL但机制完全不同。倒排索引Doris 的二级索引机制跟 ClickHouse 的 bloom/skip 索引是两套东西。tenant_id参与 hash 分桶跟 ClickHouse 把tenant_id放 ORDER BY 首列是同一个意图——让租户数据物理聚集加速租户级扫描。3.3 双引擎对比一览维度ClickHouseDoris引擎模型MergeTreeappend-onlyUNIQUE KEY merge-on-write去重分区toYYYYMM(event_date)按月动态 RANGE 按 DAYstart-180保留期TTL 180 DAY整 part 删dynamic_partition.start -180自动裁剪排序/分桶键ORDER BY (tenant_id, ...)DISTRIBUTED BY HASH(event_id, tenant_id)二级索引8 个跳数索引bloom/ngram/set12 个倒排索引派生时间列MATERIALIZED toDate(event_time)GENERATED ALWAYS AS (UNIX_TIMESTAMP(event_time)*1000)低基数优化LowCardinality(String)无纯 VARCHAR同一个业务意图租户聚集 时间分区 二级索引 派生列两个引擎用完全不同的机制实现。这就是双引擎的代价——也是它的价值用户可以根据自己的运维栈选熟悉的引擎。四、方言映射分歧到底有多大跑过 25 个模型后我总结出 ClickHouse 和 Doris 的分歧集中在 6~7 个函数上。SQL 骨架完全一样换这些函数就行用途DorisClickHouse计数COUNT(*)count()去重计数COUNT(DISTINCT user_id)count(DISTINCT user_id)ms 时间戳 → 日期FROM_UNIXTIME(event_ts/1000, %Y-%m-%d)toDate(event_ts / 1000)路径拼接GROUP_CONCAT(event_name SEPARATOR → )arrayStringConcat(groupArray(event_name), → )条件计数SUM(CASE WHEN ... )countIf(...)Map 字段访问列名直接取context[stars]、metrics[score]金额转换CAST(amount AS DOUBLE)、ROUND(SUM(...),2)toFloat64OrZero(toString(amount))驱动 API 也不同这是更麻烦的部分// Dorissqlx 风格r.db.GetContext(ctx,cnt,q LIMIT 1,args...)// 标量r.db.SelectContext(ctx,rows,q,args...)// 切片// ClickHouseclickhouse-go 风格r.db.QueryRow(ctx,cnt,q,args...)// 标量r.db.Select(ctx,rows,q,args...)// 切片struct tag 也得双份Doris 用db:...ClickHouse 用ch:...部分字段ch:-排除物化列。结论维护成本是可控的。一旦你接受了两份 repo 文件每个新模型的增量工作就是写一份 Doris SQL → 改 6~7 个函数和驱动调用 → 得到 ClickHouse 版本。骨架可复用分歧点局部化。五、四个有技术含量的模型下面挑四个最能体现SQL 巧思的模型把真实 SQL 贴出来。5.1 归因分析AttributionCTE 窗口函数的教科书归因要解决“用户最终转化了但中间是哪个渠道/来源把他带来的” 经典的 first-touch / last-touch 模型。这个实现用了两个 CTE ROW_NUMBER 窗口函数干净到可以写进教材doris/analytics_repo.goWITHconvertersAS(-- 先圈出在时间窗内完成转化事件的所有用户SELECTDISTINCTuser_idFROMevents_factWHEREtenant_id?ANDevent_name?ANDevent_time?ANDevent_time?),touchpointAS(-- 回到这些转化用户的所有事件按时间排序打行号SELECTe.user_id,e.channelASdim_val,ROW_NUMBER()OVER(PARTITIONBYe.user_idORDERBYe.event_timeDESC)ASrnFROMevents_fact eJOINconverters cONc.user_ide.user_idWHEREe.tenant_id?ANDe.event_time?ANDe.event_time?)-- rn1 即每个用户最后一次last-touch或第一次first-touch触点SELECTdim_val,COUNT(DISTINCTuser_id)ASconverter_uvFROMtouchpointWHERErn1ANDdim_valISNOTNULLANDdim_valGROUPBYdim_valORDERBYconverter_uvDESCLIMIT20精妙之处ORDER BY e.event_time DESC还是ASC决定 last 还是 first touch。这一点用fmt.Sprintf在 Go 层切换逻辑零改动。rn 1把每个用户折叠成一个触点避免重复计算。全程用ROW_NUMBER不依赖任何厂商专有函数——所以 Doris 和 ClickHouse 都能跑。这是最优雅的一个模型。5.2 LTVCASE 阶梯分桶 Go 侧累积LTV生命周期价值要算“某批同期群用户在第 0/1/3/7/14/30/60/90 天平均每人累计贡献了多少收入” 这是一个单调递增的累积曲线。实现分两步doris/analytics_repo.go第一步固定观测阶梯maxDays:[]uint32{0,1,3,7,14,30,60,90}第二步用 CASE WHEN 把付费事件按距注册天数分桶SELECTu.register_channelASlabel,CASEWHENDATEDIFF(e.event_time,u.register_time)0THEN0WHENDATEDIFF(e.event_time,u.register_time)1THEN1WHENDATEDIFF(e.event_time,u.register_time)3THEN3WHENDATEDIFF(e.event_time,u.register_time)7THEN7WHENDATEDIFF(e.event_time,u.register_time)14THEN14WHENDATEDIFF(e.event_time,u.register_time)30THEN30WHENDATEDIFF(e.event_time,u.register_time)60THEN60ELSE90ENDASday_n,ROUND(SUM(e.amount),2)AStotal_amountFROMevents_fact eJOINusers_dim uONu.tenant_ide.tenant_idANDu.user_ide.user_idWHEREu.tenant_id?ANDu.register_time?ANDu.register_time?ANDe.amount0ANDe.user_id0GROUPBYlabel,day_n第三步Go 侧走阶梯累加SQL 返回的是每个桶的总额Go 按天0→1→3→...→90升序走累加cumulative bucketSum[label][dayN]再ltv cumulative / cohortSize。最终得到 LTV(0)、LTV(1)、LTV(3)…… 一条单调递增的累计每获客收入曲线。技巧点评分桶用DATEDIFF(event_time, register_time)事件时间减注册时间的天数差 CASE 阶梯而不是用 SQL 窗口函数 SUM OVER——因为分桶点是不规则的{0,1,3,7,14,30,60,90}CASE 比窗口灵活。累积曲线故意放在 Go 里不在 SQL 里做——这样同一个 SQL 结果可以同时算累计 LTV和分桶 LTV复用性好。可选维度dimension channel时 SELECT 改成u.register_channel AS label就能看每个渠道的 LTV 曲线。5.3 路径桑基PathSankey三层嵌套 GROUP_CONCAT要把用户在每个会话里的事件序列聚合成 TOP N 路径喂给桑基图。实现是三层嵌套doris/analytics_repo.goSELECTevent_sequence,COUNT(*)ASsupport_count,COUNT(DISTINCTuser_id)ASunique_users,0ASconversion_rateFROM(-- 第三层每个(用户,会话)拼成 事件A → 事件B → 事件C 字符串SELECTuser_id,session_id,GROUP_CONCAT(event_name SEPARATOR → )ASevent_sequenceFROM(-- 第二层保证时间顺序SELECTuser_id,session_id,event_name,event_timeFROMevents_factWHEREtenant_id?ANDevent_time?ANDevent_time?ANDsession_id!ANDuser_id0ORDERBYuser_id,session_id,event_time)orderedGROUPBYuser_id,session_id)pathsWHEREevent_sequenceISNOTNULLANDevent_sequence!GROUPBYevent_sequenceORDERBYsupport_countDESCLIMIT20ClickHouse 版只换一个函数-- Doris: GROUP_CONCAT(event_name SEPARATOR → )-- CH: arrayStringConcat(groupArray(event_name), → )关键赌注内层ORDER BY在聚合时是否被保留Doris 在GROUP_CONCAT里通常保留内层顺序ClickHouse 的groupArray也倾向于保留插入顺序虽然不是契约保证。这是这个查询能 work 的隐含前提。topN被夹在[1, 200]默认 20。注conversion_rate这里硬编码成0——没有 join 目标事件。这是个已知的小局限源码里也这么写着。5.4 漏斗Funnel诚实的非严格实现漏斗分析要算“从步骤 1 到步骤 N每一步的转化率是多少”这里有个必须说清楚的取舍。源码注释明说doris/analytics_repo.go:105-106统计口径每个步骤 在时间范围内完成该事件的去重用户数不做严格顺序穿透这是 Doris 上的近实时实现严格漏斗需事件级顺序匹配留作后续优化。也就是说它不是真正的有序漏斗。它做的是每个步骤独立查一次时间窗内做过该事件的去重用户数然后在 Go 里做除法-- 每个步骤发一次循环 N 次SELECTCOUNT(DISTINCTuser_id)AScntFROMevents_factWHEREevent_time?ANDevent_time?ANDevent_name?[ANDtenant_id?]LIMIT1Go 侧算转化率// 步骤 i 的转化率 步骤 i 的人数 / 步骤 i-1 的人数// 步骤 i 的总体转化率 步骤 i 的人数 / 步骤 0 的人数为什么不严格因为严格有序漏斗需要同一个用户在时间上依次触发 step1→step2→step3。ClickHouse 有windowFunnel()函数能干这事Doris 没有等价物。为了双引擎行为一致作者选择了两个引擎都能跑的简化口径代价是漏斗退化为各步骤独立去重用户数 算术。这是一个用精度换双引擎一致性 近实时性能的典型取舍。源码诚实标注了这点值得尊敬。如果你需要严格漏斗要么只用 ClickHouse 走windowFunnel要么自己加一层。六、维度白名单防 SQL 注入的正解分析查询里维度字段dimension是用户传的字符串比如channel/platform。这里有个经典安全问题SQL 标识符不能参数化SELECT ? FROM ...不行但又不能直接拼用户输入。怎么破项目用了一个两层白名单doris/analytics_repo.go:2255-2272、clickhouse/analytics_helpers.go:32-48funcallowedDimension(dimstring)(string,bool){m:map[string]string{platform:platform,channel:channel,country:country,app_version:app_version,event_name:event_name,event_category:event_category,os:os,network:network,user_level:user_level,vip_level:vip_level,}v,ok:m[dim]returnv,ok}用法col,ok:allowedDimension(req.GetDimension())if!ok{returnnil,ubaV1.ErrorBadRequest(fmt.Sprintf(unsupported dimension: %s,req.GetDimension()))}// 返回的 col 来自 map 的 value服务端控制的字面量不是用户输入q:fmt.Sprintf(SELECT %s AS label, %s AS value FROM events_fact %s WHERE ...,col,metricExpr,joinClause)机制要点白名单 map只有这 10 个已知安全的列名能通过返回值是map 的 value服务端字面量不是原始用户输入。未知维度直接400 Bad Request。metric 也有白名单switchCOUNT/UNIQUE_USER/SUM_AMOUNT从不拼接用户文本。identifier 拼接 value 绑定白名单解析后的列名确实用fmt.Sprintf拼进 SQL因为标识符不能参数化但因为它来自固定 map值保证安全用户传入的数值一律用?绑定。user_level/vip_level触发 JOIN这两个维度在users_dim表而不是events_fact代码会自动构造JOIN users_dim u ON ...并给列名加u.前缀。这是标准且正确的防注入模式标识符用白名单解析后拼接值用参数绑定。每个分析方法的租户条件tenant_id ?也都走绑定从不字符串拼接。一个小细节Attribution没走allowedDimension而是自己写了if d referer { dim referer } else { channel }的硬编码分支——更简单但用了不同的 guard 风格。代码里这种一致性瑕疵在真实项目里很常见。七、25 个模型的全景把所有方法列出来感受下规模都在analytics_repo.goDoris 版 2293 行ClickHouse 版镜像通用行为分析10EventTrend、Funnel、Retention、GroupBy、ActiveUsers、Attribution、Distribution、BehaviorSequence、Segmentation、Click用户深度洞察9Lifecycle、Churn、Interval、Matrix、Revenue、SessionAnalysis、Anomaly、NewVsOld、PathSankey游戏专项6LevelAnalysis、WhaleTier、LTV、ServerRetention、OnlineStats、Economy每个都是func (r *AnalyticsRepo) Xxx(ctx, req) (resp, error)Doris/CH 各一份。最有算法味的几个Anomaly异常检测事件环比涨跌 7 日基线预警发现埋点丢失/故障。WhaleTier付费分层把付费用户按金额分大/中/小课长验证二八定律。Economy经济系统代币产出/消耗平衡监控防通胀和刷币。LevelAnalysis关卡分析通过率/卡关率/满星率游戏数值平衡。这些模型的 SQL 风格大同小异时间窗 tenant 条件 GROUP BY 维度 聚合函数区别在聚合的具体表达CASE 分桶 / 窗口函数 / DATEDIFF。八、小结双引擎值不值得回到开头的赌注。维护两份 OLAP repo、两份 DDL、两套方言映射值得吗我的看法是——对开源项目值得对单一公司项目未必开源项目用户运维栈各异有人吃 Doris兼容 MySQL 协议、运维熟有人吃 ClickHouse极致 scan 性能。给选择权 拓宽用户面。维护成本可控如前述分歧只在 6~7 个函数。单一公司除非真有切换引擎的需求否则选一个深挖更划算。双引擎的代码分支会渗入每个 serviceif data.UseClickHouse增加心智负担。GoWind UBA 选择双引擎更多是给社区一个选择的姿态。而它把双引擎分歧控制得足够局部repo 层 驱动 API 6~7 个函数是一个值得学习的工程克制。本文 SQL 全部出自 go-wind-uba 仓库backend/app/core/service/internal/data/{doris,clickhouse}/analytics_repo.goDDL 出自backend/sql/{doris,clickhouse}/。