物化视图看到的和漏掉的

目标

在源表上挂一个物化视图，确认 INSERT 流向目标表，并用数字重现视图看不到的三件事——视图之前的行、合并之前的重复键、JOIN 右侧表的变化。

为什么重要

ClickHouse 的物化视图不是“保存下来的查询”，而是 INSERT 触发器。所以漏掉回填，过去就是空的；回填两次，就会翻倍；目标表不累加就读取，数字会随合并时机而波动；而维度表连接，则会永远错过来得晚的维度。本实验的评分器不会相信你写的数字——它会把目标表与用源表（或测试数据生成表达式）重新计算的值逐格核对，并把你保存的 SELECT 以只读方式重新运行，测量结果和读取的行数。

步骤

1. 创建数据库 mv 和表 mv.orders——列 order_id UInt64, ts DateTime, shop LowCardinality(String), product_id UInt32, user_id UInt32, qty UInt32, price UInt32（按此顺序），引擎 MergeTree，ORDER BY (shop, ts)。然后一次写入 /opt/lab/fixtures/mv/orders_1.sql（9 月 1–15 日，20 万条）。
2. 用 SummingMergeTree、ORDER BY (shop, day) 创建目标表 mv.daily_sales (day Date, shop LowCardinality(String), orders UInt64, revenue UInt64)，并以 TO mv.daily_sales 创建物化视图 mv.daily_sales_mv——toDate(ts) AS day, shop, count() AS orders, sum(qty * price) AS revenue，GROUP BY day, shop。
3. 在写入 /opt/lab/fixtures/mv/orders_2.sql（9 月 16–30 日，20 万条）之后立刻，把 mv.orders 的行数作为 source_orders，mv.daily_sales 的 sum(orders) 作为 view_orders，写入 /root/ch/mv/gap.json。
4. 把视图没有看到的第 1 批回填到 mv.daily_sales，使目标表按（shop, day）准确包含全部源数据。不能再写入第 2 批。
5. 写出在 mv.daily_sales 中求 shop-3 的每日订单数、销售额的 /root/ch/mv/q_daily.sql——列 day, orders, revenue，按日期排序。必须累加后读取，以便在合并之前也是对的。
6. 用 AggregatingMergeTree、ORDER BY (shop, day) 创建目标表 mv.shop_stats (shop LowCardinality(String), day Date, orders AggregateFunction(count), buyers AggregateFunction(uniqExact, UInt32))，以 TO mv.shop_stats 创建视图 mv.shop_stats_mv（countState()、uniqExactState(user_id)），并把已有的源数据回填一次。然后写出在 mv.shop_stats 中求每家商店一个月的不同购买者数的 /root/ch/mv/q_buyers.sql——列 shop, buyers，按商店排序。
7. 用 ENGINE = Null 创建在 mv.orders 的列之后末尾多出 status LowCardinality(String) 的 mv.raw，创建只把 status = 'paid' 的行发送到 mv.orders 的视图 mv.raw_mv，然后一次写入 /opt/lab/fixtures/mv/raw.sql。
8. 创建 mv.products (product_id UInt32, category LowCardinality(String))（MergeTree，ORDER BY product_id）和 mv.cat_sales (category LowCardinality(String), orders UInt64)（SummingMergeTree，ORDER BY category），以及把 mv.orders 与 mv.products 做 INNER JOIN、按类别发送 count() 的视图 mv.cat_sales_mv。然后按 products.sql → late.sql → products_new.sql（都在 /opt/lab/fixtures/mv/ 中）的顺序写入，把迟到订单数（order_id >= 2000000）作为 late_orders，mv.cat_sales 的 sum(orders) 作为 joined_orders，两者之差作为 lost_orders，写入 /root/ch/mv/join.json。

参考

• 服务器已经在运行（clickhouse-client）。如果停了，就运行 ch-up。
• 测量读取的行数时，请加上 --use_query_condition_cache 0。同一条件的查询运行第二次时，条件缓存会跳过颗粒，数字可能不同。评分器也是关闭缓存后测量的。
• 测试数据按订单号划分区间——第 1 批从 0 开始，第 2 批从 200000 开始，原始数据从 1000000 开始，迟到订单从 2000000 开始。
• 常见错误：回填时把第 2 批也写进去，使 9 月 16–30 日翻倍；把 JOIN 视图做成 LEFT JOIN，产生空类别；把测试数据写入了两次。
• 如果弄坏了，对目标表先 TRUNCATE TABLE，再把整个源数据重新写入一次即可（视图保持不动）。
• 官方文档：Incremental materialized view · CREATE VIEW · Use materialized views · AggregatingMergeTree · Refreshable materialized view

创建源表并写入第 1 批

创建数据库 mv 和表 mv.orders。列依次为 order_id UInt64, ts DateTime, shop LowCardinality(String), product_id UInt32, user_id UInt32, qty UInt32, price UInt32，引擎 MergeTree，ORDER BY (shop, ts)。然后运行一次 /opt/lab/fixtures/mv/orders_1.sql，写入 20 万条。

CREATE DATABASE、CREATE TABLE 之后，用 clickhouse-client --queries-file 传入测试数据。测试数据是用哈希生成的，所以不管运行多少次，得到的行都相同，但 MergeTree 不会阻止重复，写两次就会变成 40 万条。

创建目标表和物化视图

用 SummingMergeTree、ORDER BY (shop, day) 创建目标表 mv.daily_sales (day Date, shop LowCardinality(String), orders UInt64, revenue UInt64)，并以 TO mv.daily_sales 创建物化视图 mv.daily_sales_mv。视图的 SELECT 是 toDate(ts) AS day, shop, count() AS orders, sum(qty * price) AS revenue FROM mv.orders GROUP BY day, shop。创建之后，立刻确认 mv.daily_sales 的行数。

视图不直接保存结果，而是写入用 TO 指定的表。目标表的排序键要与视图的 GROUP BY 对齐，合并时才会把相同的（shop, day）合并起来。刚创建的视图，其目标表为什么是空的，这就是本模块的第一个教训。

把视图看到的与源表比较

在写入一次 /opt/lab/fixtures/mv/orders_2.sql 之后立刻，把 mv.orders 的行数作为 source_orders，mv.daily_sales 的 sum(orders) 作为 view_orders，写入 /root/ch/mv/gap.json。

视图是以 INSERT 块为输入的触发器。第 2 批的 INSERT 是在视图存在时进来的，而第 1 批是在视图创建之前进来的。两个数字的差，就是需要回填的量。

只回填缺失的区间

用 INSERT INTO mv.daily_sales SELECT ... 回填视图没有看到的第 1 批（9 月 1–15 日），使 mv.daily_sales 按（shop, day）累加的值，与对整个源表重新聚合的值逐格相同。

要点是照用视图的 SELECT，而在源表上用 WHERE 限定区间。如果把视图已经写入的天也再写一遍，SummingMergeTree 会把这些天加两次。边界是第 2 批开始的时刻。

读取目标表，使合并之前也是对的

写出在 mv.daily_sales 中求 shop = 'shop-3' 的每日订单数、销售额的 /root/ch/mv/q_daily.sql。结果列是 day, orders, revenue，按日期排序。只能读取目标表。

视图的 GROUP BY 只在一个块内合并。所以在合并结束之前，同一个（shop, day）会有多行。在查询时再次 sum 并 GROUP BY day，无论合并时机如何，都会得到相同的答案。

把无法相加的值存成状态

用 AggregatingMergeTree、ORDER BY (shop, day) 创建 mv.shop_stats (shop LowCardinality(String), day Date, orders AggregateFunction(count), buyers AggregateFunction(uniqExact, UInt32))，以 TO mv.shop_stats 创建视图 mv.shop_stats_mv（countState() AS orders, uniqExactState(user_id) AS buyers，GROUP BY shop, day），并把已有的源数据回填一次。然后写出在 mv.shop_stats 中求每家商店一个月的不同购买者数的 /root/ch/mv/q_buyers.sql——列 shop, buyers，按商店排序。

把每天的不同购买者数相加，跨越多天购买的人会被重复计数。-State 存的是可以合并的中间状态，而不是结果，查询时用同一个函数的 -Merge 来合并。回填做两次，countMerge 就会翻倍。

从 Null 表开始的级联视图

用 ENGINE = Null 创建在 mv.orders 的列之后加上 status LowCardinality(String) 的表 mv.raw，并创建只把 status = 'paid' 的行的七个源列发送到 mv.orders 的视图 mv.raw_mv。然后一次写入 /opt/lab/fixtures/mv/raw.sql。

Null 引擎的表什么也不存，但它接收 INSERT 并唤醒视图。raw_mv 写入 mv.orders 的块，又会唤醒 daily_sales_mv 和 shop_stats_mv——一次 INSERT 触及三张表。对 mv.raw 做 SELECT，永远是 0 行。

JOIN 视图不知道右侧表的变化

创建 mv.products (product_id UInt32, category LowCardinality(String))（MergeTree，ORDER BY product_id）、mv.cat_sales (category LowCardinality(String), orders UInt64)（SummingMergeTree，ORDER BY category），以及把 mv.orders AS o INNER JOIN mv.products AS p ON o.product_id = p.product_id 按类别的 count() AS orders 发送到 mv.cat_sales 的视图 mv.cat_sales_mv。然后按 /opt/lab/fixtures/mv/ 中 products.sql → late.sql → products_new.sql 的顺序写入，把迟到订单数（order_id >= 2000000）作为 late_orders，mv.cat_sales 的 sum(orders) 作为 joined_orders，差值作为 lost_orders，写入 /root/ch/mv/join.json。

在带有 JOIN 的视图中，触发条件只有最左侧的表（源表）的 INSERT，右侧表只是把那一刻的内容整个读取。迟到订单进来的那一刻，商品表中没有的商品的订单会在 INNER JOIN 中被丢弃，商品之后登记，它们也不会复活。