1
顾客开门进店 → 浏览/拿取商品 → 发起结算 → 支付成功 → 离店

1
- 今天每个租户、门店的进店人数和支付人数是多少?
2
- 最近 7 天哪些商品"被浏览很多但结算很少"?
3
- 某城市支付失败是否集中在特定门店、设备型号或支付通道?
4
- 从进店到支付成功的漏斗转化率是否在某个门店突然下降?

1
tenant_id | store_id | event_time          | event_name       | device_id | properties
2
----------|----------|---------------------|------------------|-----------|-----------
3
tenant-a  | store-01 | 2026-07-21 10:00:01 | entry_detected   | door-01   | ...
4
tenant-a  | store-01 | 2026-07-21 10:00:03 | payment_success | pos-01    | ...

1
SELECT store_id, count()
2
FROM store_events
3
WHERE tenant_id = 'tenant-a'
4
  AND event_time >= '2026-07-21 00:00:00'
5
GROUP BY store_id;

1
tenant_id 列 + event_time 列 + store_id 列

1
查询请求
2
   │
3
   ├── CPU Core 1:扫描 Part 1、Part 2
4
   ├── CPU Core 2:扫描 Part 3、Part 4
5
   ├── CPU Core 3:扫描 Part 5、Part 6
6
   └── CPU Core 4:扫描 Part 7、Part 8
7
                    │
8
                    ▼
9
                汇总最终结果

1
Part 内部(按 tenant_id, event_date, event_time 排序)
2

3
tenant-a, 2026-07-21, 10:00:00  ─┐
4
tenant-a, 2026-07-21, 10:13:00   │ 一个索引粒度
5
tenant-a, 2026-07-21, 10:28:00  ─┘
6
tenant-b, 2026-07-21, 10:00:00  ─┐
7
tenant-b, 2026-07-21, 10:15:00   │ 下一个索引粒度

1
services:
2
  clickhouse:
3
    image: clickhouse/clickhouse-server:latest
4
    container_name: clickhouse-server
5
    restart: unless-stopped
6
    ports:
7
      - "8123:8123"
8
      - "9000:9000"
9
      - "9004:9004"
10
    environment:
11
      CLICKHOUSE_DB: analytics
12
      CLICKHOUSE_USER: app_user
13
      CLICKHOUSE_PASSWORD: change_me_in_production
14
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: "1"
15
    volumes:
16
      - ./clickhouse-data:/var/lib/clickhouse
17
      - ./clickhouse-logs:/var/log/clickhouse-server
18
    ulimits:
19
      nofile:
20
        soft: 262144
21
        hard: 262144

1
Row 1:
2
──────
3
version(): 25.x.x.x
4
now():     2026-07-21 17:00:00

1
CREATE DATABASE IF NOT EXISTS analytics;
2

3
USE analytics;

1
CREATE TABLE IF NOT EXISTS store_events
2
(
3
    -- 事件唯一标识,用于技术去重和问题追踪
4
    event_id UUID,
5

6
    -- SaaS 租户、门店、顾客与设备维度
7
    tenant_id LowCardinality(String),
8
    store_id LowCardinality(String),
9
    member_id Nullable(UInt64),
10
    device_id Nullable(String),
11
    city LowCardinality(String),
12

13
    -- 业务事件与关联对象
14
    event_name LowCardinality(String),
15
    order_id Nullable(UInt64),
16
    product_id Nullable(UInt64),
17
    page_code LowCardinality(String),
18

19
    -- 业务发生时间。DateTime64(3) 支持毫秒精度
20
    event_time DateTime64(3, 'Asia/Shanghai'),
21
    event_date Date MATERIALIZED toDate(event_time),
22

23
    -- 动态扩展属性,例如设备型号、支付通道、软件版本、错误码
24
    properties String,
25

26
    -- 数据写入时间,用于识别采集或消费延迟
27
    ingest_time DateTime DEFAULT now()
28
)
29
ENGINE = MergeTree
30
PARTITION BY toYYYYMM(event_date)
31
ORDER BY (tenant_id, store_id, event_date, event_time, event_name, ifNull(member_id, toUInt64(0)))
32
SETTINGS index_granularity = 8192;

1
tenant_id + store_id + 日期范围 + 时间范围 + 事件名称

1
ORDER BY (tenant_id, store_id, event_date, event_time, event_name, member_id)

1
INSERT INTO store_events
2
    (event_id, tenant_id, store_id, member_id, device_id, city, event_name,
3
     order_id, product_id, page_code, event_time, properties)
4
VALUES
5
    (generateUUIDv4(), 'tenant-a', 'store-hz-001', 10001, 'door-01', '杭州', 'entry_detected',
6
     NULL, NULL, 'store_entry', '2026-07-21 10:00:01.123', '{"device_model":"door-v2","software_version":"2.4.1"}'),
7
    (generateUUIDv4(), 'tenant-a', 'store-hz-001', 10001, 'shelf-03', '杭州', 'product_view',
8
     NULL, 1001, 'product_detail', '2026-07-21 10:01:03.456', '{"device_model":"shelf-v3"}'),
9
    (generateUUIDv4(), 'tenant-a', 'store-hz-001', 10001, 'pos-01', '杭州', 'payment_success',
10
     200001, 1001, 'checkout', '2026-07-21 10:02:20.000', '{"payment_channel":"wechat","cost_ms":680}');

1
SELECT
2
    event_time,
3
    member_id,
4
    store_id,
5
    city,
6
    event_name,
7
    device_id,
8
    order_id,
9
    product_id
10
FROM store_events
11
ORDER BY event_time;

1
INSERT 批次 1  ──► Part_1
2
INSERT 批次 2  ──► Part_2
3
INSERT 批次 3  ──► Part_3
4
                         │
5
                         ▼
6
                后台自动 Merge
7
                         │
8
                         ▼
9
                    大的有序 Part

1
CREATE TABLE order_snapshot
2
(
3
    order_id UInt64,
4
    member_id UInt64,
5
    order_status LowCardinality(String),
6
    amount Decimal(18, 2),
7
    update_time DateTime,
8
    version UInt64
9
)
10
ENGINE = ReplacingMergeTree(version)
11
PARTITION BY toYYYYMM(update_time)
12
ORDER BY order_id;

1
INSERT INTO order_snapshot VALUES
2
    (100001, 20001, 'CREATED', 199.00, '2026-07-21 10:00:00', 1),
3
    (100001, 20001, 'PAID',    199.00, '2026-07-21 10:05:00', 2);

1
SELECT *
2
FROM order_snapshot FINAL
3
WHERE order_id = 100001;

1
Query id: 57b8981f-274c-4d6d-8a36-187e9b348534
2

3
   ┌─order_id─┬─member_id─┬─order_status─┬─amount─┬──────────update_time─┬─version─┐
4
1. │  100001  │     20001 │ PAID         │ 199.00 │ 2026-07-21 10:05:00  │       2 │
5
   └──────────┴───────────┴──────────────┴────────┴──────────────────────┴─────────┘
6

7
1 row in set. Elapsed: 0.008 sec.

1
SELECT
2
    event_date,
3
    countIf(event_name = 'entry_detected') AS entry_count,
4
    uniqCombined64If(member_id, event_name = 'entry_detected') AS entry_uv,
5
    countIf(event_name = 'payment_success') AS payment_count,
6
    round(payment_count / nullIf(entry_count, 0), 4) AS entry_to_payment_rate
7
FROM store_events
8
WHERE tenant_id = 'tenant-a'
9
  AND event_date BETWEEN '2026-07-01' AND '2026-07-21'
10
GROUP BY event_date
11
ORDER BY event_date;

1
Query id: df511fb3-bb28-47ef-9e90-573397be0c07
2

3
   ┌─event_date─┬─entry_count─┬─entry_uv─┬─payment_count─┬─entry_to_payment_rate─┐
4
1. │ 2026-07-21 │           1 │        1 │             1 │                     1 │
5
   └────────────┴─────────────┴──────────┴───────────────┴───────────────────────┘
6

7
1 row in set. Elapsed: 0.004 sec.

1
SELECT
2
    tenant_id,
3
    store_id,
4
    city,
5
    countIf(event_name = 'entry_detected') AS entry_count,
6
    countIf(event_name = 'payment_success') AS payment_count,
7
    round(payment_count / nullIf(entry_count, 0), 4) AS conversion_rate
8
FROM store_events
9
WHERE tenant_id = 'tenant-a'
10
  AND event_date = '2026-07-21'
11
GROUP BY tenant_id, store_id, city
12
ORDER BY conversion_rate ASC
13
LIMIT 20;

1
Query id: 70b7aebb-ad81-45a1-ab5d-3164162faa23
2

3
   ┌─tenant_id─┬─store_id─────┬─city─┬─entry_count─┬─payment_count─┬─conversion_rate─┐
4
1. │ tenant-a  │ store-hz-001 │ 杭州 │           1 │             1 │               1 │
5
   └───────────┴──────────────┴──────┴─────────────┴───────────────┴─────────────────┘
6

7
1 row in set. Elapsed: 0.004 sec.

1
SELECT
2
    product_id,
3
    countIf(event_name = 'product_view') AS view_count,
4
    countIf(event_name = 'product_taken') AS taken_count,
5
    countIf(event_name = 'payment_success') AS payment_count,
6
    round(payment_count / nullIf(view_count, 0), 4) AS conversion_rate
7
FROM store_events
8
WHERE tenant_id = 'tenant-a'
9
  AND event_date BETWEEN '2026-07-15' AND '2026-07-21'
10
  AND product_id IS NOT NULL
11
GROUP BY product_id
12
ORDER BY payment_count DESC
13
LIMIT 10;

1
Query id: 46c915ee-917d-4a11-9fbd-efd84c9e21db
2

3
   ┌─product_id─┬─view_count─┬─taken_count─┬─payment_count─┬─conversion_rate─┐
4
1. │       1001 │          1 │           0 │             1 │               1 │
5
   └────────────┴────────────┴─────────────┴───────────────┴─────────────────┘
6

7
1 row in set. Elapsed: 0.006 sec.

1
SELECT
2
    countIf(event_name = 'entry_detected') AS entry_uv,
3
    countIf(event_name = 'product_view') AS view_uv,
4
    countIf(event_name = 'checkout_started') AS checkout_uv,
5
    countIf(event_name = 'payment_success') AS payment_uv
6
FROM
7
(
8
    SELECT member_id, event_name
9
    FROM store_events
10
    WHERE tenant_id = 'tenant-a'
11
      AND store_id = 'store-hz-001'
12
      AND event_date = '2026-07-21'
13
      AND member_id IS NOT NULL
14
    GROUP BY member_id, event_name
15
);

1
Query id: eccf1816-edbb-4078-bc66-f705a2f725e1
2

3
   ┌─entry_uv─┬─view_uv─┬─checkout_uv─┬─payment_uv─┐
4
1. │        1 │       1 │           0 │           1 │
5
   └──────────┴─────────┴─────────────┴────────────┘
6

7
1 row in set. Elapsed: 0.004 sec.

1
SELECT
2
    funnel_step,
3
    count() AS member_count
4
FROM
5
(
6
    SELECT
7
        member_id,
8
        windowFunnel(3600)(
9
            event_time,
10
            event_name = 'entry_detected',
11
            event_name = 'checkout_started',
12
            event_name = 'payment_success'
13
        ) AS funnel_step
14
    FROM store_events
15
    WHERE tenant_id = 'tenant-a'
16
      AND store_id = 'store-hz-001'
17
      AND event_date = '2026-07-21'
18
      AND member_id IS NOT NULL
19
    GROUP BY member_id
20
)
21
GROUP BY funnel_step
22
ORDER BY funnel_step;

1
SELECT
2
    event_date,
3
    device_id,
4
    quantile(0.50)(toFloat64(JSONExtractFloat(properties, 'cost_ms'))) AS p50_ms,
5
    quantile(0.95)(toFloat64(JSONExtractFloat(properties, 'cost_ms'))) AS p95_ms,
6
    quantile(0.99)(toFloat64(JSONExtractFloat(properties, 'cost_ms'))) AS p99_ms
7
FROM store_events
8
WHERE tenant_id = 'tenant-a'
9
  AND event_name = 'api_request'
10
  AND event_date BETWEEN '2026-07-15' AND '2026-07-21'
11
GROUP BY event_date, device_id
12
ORDER BY event_date, p99_ms DESC;

1
┌───────────────┐                        ┌──────────────────┐
2
│   MySQL / PG   │  ──── CDC / Kafka ───► │   ClickHouse     │
3
│  订单、库存、    │      同步明细 / 快照       │  分析、报表、大盘   │
4
│  事务与一致性   │                        │  追加写、聚合读    │
5
└───────────────┘                        └──────────────────┘
6
   强事务、频繁更新                         海量数据、快速聚合

1
UPDATE store_events
2
SET store_id = 'store-hz-001'
3
WHERE event_id = '550e8400-e29b-41d4-a716-446655440000';

1
收到一条 Kafka 消息 → HTTP INSERT 一次 → 产生一个小 Part

1
Kafka 批量消费 / 应用内缓存攒批 → 每批数千~数万条写入 → 后台高效合并

1
SELECT count()
2
FROM store_events
3
WHERE event_name = 'entry_detected';

1
SELECT count()
2
FROM store_events
3
WHERE tenant_id = 'tenant-a'
4
  AND event_date >= today() - 7
5
  AND event_name = 'entry_detected';

1
PARTITION BY store_id

1
要做事务、要频繁改数据、要点查一条       →  MySQL
2
要做海量数据的聚合分析、报表、大盘        →  ClickHouse
3
要做全文检索、相关性排序                  →  Elasticsearch
4
三者各司其职,组合使用才是常态

1
MySQL / PostgreSQL:订单、库存、租户和门店配置等事务事实
2
Kafka:门禁、设备、交易和服务事件的传输与削峰
3
Flink:实时清洗、关联、口径计算
4
ClickHouse:门店事件沉淀、设备分析和经营指标
5
Redis:热点门店状态与低延迟缓存
6
Elasticsearch:商品、工单等全文检索