clickhouse策略-利用合并表的合并特性来记录每行数据的变更历史
clickhouse版本为 version 25.3.3.42 (official build)
用 ClickHouse 的 ReplacingMergeTree 做“6 小时快照差”，追踪亚马逊订单数据变更
场景：订单表、订单明细表实时写入 ClickHouse，不做 UPDATE，只做 INSERT。希望在不依赖业务侧快照表的前提下，追踪“数据状态在 6 小时内发生了多少变化”（例如销量 quantity_ordered 的增量/回撤），并能按 SKU 汇总。对于 黑五、退货量大的数据能进行实时统计（这里6小时变化可以修改为3小时或者1小时）
需求提出：为什么不是“按下单时间统计”？
很多“销量趋势”其实有两类口径：
口径A（事件口径）：按 purchase_date / station_purchase_date 统计最近 6 小时新增下单量
适合做经营趋势，不关心数据后续被改写/补写，但是需要业务提前处理相关的数据，编写对应的快照逻辑代码
口径B（状态口径，本文重点）：对同一批订单/订单项，比较“6 小时前的表内状态”与“当前表内状态”
适合做数据质量、补写/回补、纠错、状态变更追踪，关注的是“表里这条记录现在长什么样 vs 6 小时前长什么样”，特别是没有时间编写代码临时使用
你这里明确满足口径B的前提条件：
id 是唯一键（订单项唯一 / 订单唯一）；version 单调递增并真实生效；每次写入会更新 update_time；ReplacingMergeTree 的合并窗口 保留约 2 天（意味着你允许在 2 天内做“回看”）
ClickHouse 的关键点：ReplacingMergeTree 是“最终态”，不是“历史库”
ReplacingMergeTree(version) 的语义是：
相同主键（这里是 id）会存在多版本行（在 merge 前）
后台 merge 后，最终只保留 version 最大 的一行
因此它天然不保证长期历史；它更像“可短期回看”的近实时 UPSERT （核验数据就很有效。。）
设置 merge 约 2 天，本质上就是，注意这个本质也是很重要的：
2 天内：旧版本行大概率还在 → 可以用 “as-of 查询” 拼出历史快照；超过 2 天：旧版本被合并掉 → “6 小时前”还能不能还原取决于是否被 merge 清掉（不稳定）
结论：口径B可用，但它依赖“旧版本仍存在”的窗口。
数据设计与写入规范：让“历史快照”可还原
你的表结构里，以下字段对口径B至关重要：
id：唯一键（Replacing 的主键）
version：版本号（ReplacingMergeTree(version) 的判定依据）
update_time：写入时间/变更时间（用于做 “t0 时刻的快照”）
is_delete：软删（用插入一条新版本来表达删除）
业务过滤字段（order_status / is_vine / is_replacement_order / seller_order_id 等）
写入要求（必须满足）：
只 INSERT，不 UPDATE、不 DELETE
每次订单/订单项有变化，插入一条同 id 的新行，version 必须单调递增且越新越大
推荐：version = toUnixTimestamp64Milli(now64(3)) 或 Snowflake/序列
不要回退，不要重放同 version
update_time 每次写入都要刷新
update_time = now64(3)，update_time 是“快照时间”的锚点，缺它就无法 as-of
软删也用新版本表达
删除：插入新行，is_delete=1，version 更大，update_time 更新；合并/保留窗口要覆盖你的回看周期
对应 API 的使用：如何驱动“多版本写入”
以 Amazon SP-API（Orders API）为例，典型拉取流程是：
1、拉订单列表（增量窗口，比如最近 N 小时 / lastUpdatedAfter）
2、拉订单详情（补齐状态、金额、买家信息等）
3、拉订单项列表（Order Items）
落 ClickHouse：每次拉取都插入新版本，同一个订单/订单项，可能因为：
1、状态变更（Pending → Shipped → …）
2、回补字段（title、tax、promotion 等）
3、纠错/对账（quantity、价格字段修正）
本质就是：对比 t0 与 now 两个时刻的表内状态。时刻可以拉的很细
SQL 设计思路：用 argMax / argMaxIf 拼两个快照
两个快照的定义:
1、now 快照：对每个 id 取 version 最大的一行（最新状态）
→ argMax(field, version)
2、t0 快照：对每个 id 在 update_time <= t0 的约束下，取 version 最大的一行（t0 时刻最新）
→ argMaxIf(field, version, update_time <= t0)
now64(3) AS now_ts,
(now_ts - INTERVAL 6 HOUR) AS t0,
这里可以用 "- INTERVAL 6 HOUR" 这种手段来做时间偏移
完整用例sql如下
WITH
now64(3) AS now_ts,
(now_ts - INTERVAL 6 HOUR) AS t0,
/* 订单快照：按 (amazon_order_id, seller_id, market_place_id) 拼 now 与 t0 */
o AS (
SELECT
amazon_order_id, seller_id, market_place_id,
argMax(order_status, version) AS order_status_now,
argMaxIf(order_status, version, isNotNull(update_time) AND update_time <= t0) AS order_status_t0,
argMax(is_delete, version) AS is_delete_now,
argMaxIf(is_delete, version, isNotNull(update_time) AND update_time <= t0) AS is_delete_t0,
argMax(is_replacement_order, version) AS is_repl_now,
argMaxIf(is_replacement_order, version, isNotNull(update_time) AND update_time <= t0) AS is_repl_t0,
argMax(seller_order_id, version) AS seller_order_id_now,
argMaxIf(seller_order_id, version, isNotNull(update_time) AND update_time <= t0) AS seller_order_id_t0
FROM tunan_dw_prd.amzn_order
WHERE isNotNull(update_time)
AND update_time >= t0 - INTERVAL 2 DAY
GROUP BY amazon_order_id, seller_id, market_place_id
),
/* 订单项快照：按 id 拼 now 与 t0（你这里 id 唯一，最关键） */
i AS (
SELECT
id,
amazon_order_id, seller_id, market_place_id,
argMax(seller_sku, version) AS seller_sku,
argMax(quantity_ordered, version) AS qty_now,
argMaxIf(quantity_ordered, version, isNotNull(update_time) AND update_time <= t0) AS qty_t0,
argMax(is_delete, version) AS is_delete_now,
argMaxIf(is_delete, version, isNotNull(update_time) AND update_time <= t0) AS is_delete_t0,
argMax(is_vine, version) AS is_vine_now,
argMaxIf(is_vine, version, isNotNull(update_time) AND update_time <= t0) AS is_vine_t0
FROM tunan_dw_prd.amzn_order_item
WHERE isNotNull(update_time)
AND update_time >= t0 - INTERVAL 2 DAY
GROUP BY id, amazon_order_id, seller_id, market_place_id
)
SELECT
seller_sku,
qty_6h_ago,
qty_now,
qty_now - qty_6h_ago AS qty_delta_6h
FROM
(
SELECT
i.seller_sku AS seller_sku,
/* t0 口径过滤：必须用 *_t0 字段 */
sumIf(ifNull(i.qty_t0, 0),
coalesce(i.is_delete_t0,0)=0
AND coalesce(i.is_vine_t0,0)=0
AND coalesce(o.is_delete_t0,0)=0
AND coalesce(o.order_status_t0,'')!='Canceled'
AND coalesce(o.is_repl_t0,0)=0
-- 这里可以补充其他的业务sql过滤条件, 具体看业务情况而定
) AS qty_6h_ago,
/* now 口径过滤：必须用 *_now 字段 */
sumIf(ifNull(i.qty_now, 0),
coalesce(i.is_delete_now,0)=0
AND coalesce(i.is_vine_now,0)=0
AND coalesce(o.is_delete_now,0)=0
AND coalesce(o.order_status_now,'')!='Canceled'
AND coalesce(o.is_repl_now,0)=0
-- 这里可以补充其他的业务sql过滤条件, 具体看业务情况而定
) AS qty_now
FROM i
JOIN o USING (amazon_order_id, seller_id, market_place_id)
GROUP BY i.seller_sku
)
ORDER BY qty_delta_6h DESC;
结果怎么解读？
qty_6h_ago：按 t0 快照口径汇总出的 SKU 总销量状态；qty_now：按最新快照口径汇总出的 SKU 总销量状态；qty_delta_6h：两者差值
正数：6小时内销量状态“净增长”；负数：6小时内发生了回撤（例如取消、纠错覆盖、软删等）；0：状态没有净变化（但不代表没有发生过变更，可能进出抵消）
这里可以在这个基础上进行改进比如查看订单canceled、pending等状态切换的情况来分析退货的情况
这套方案的本质是：
1、用 ReplacingMergeTree 存“多版本事实”
2、在 merge 窗口内用 argMax / argMaxIf 临时拼出“as-of 快照”
3、通过两个快照的差值，追踪订单数据状态变化
它在“实时写入 + 近两天可追溯”的场景里非常实用，且不用额外建历史表；但要说明：它依赖 merge 窗口，不适合长期审计。
.preview-wrapper pre::before { position: absolute; top: 0; right: 0; color: #ccc; text-align: center; font-size: 0.8em; padding: 5px 10px 0; line-height: 15px; height: 15px; font-weight: 600; } .hljs.code__pre > .mac-sign { display: flex; } .code__pre { padding: 0 !important; } .hljs.code__pre code { display: -webkit-box; padding: 0.5em 1em 1em; overflow-x: auto; text-indent: 0; } h2 strong { color: inherit !important; }
本文使用 文章同步助手 同步
