aiData/Doc/5、统计表结构说明.md

充电桩数据仓库设计说明文档

本文档详细描述了充电桩爬虫项目的数据仓库设计思路、表结构定义、常见查询场景及应对策略。该设计基于 Doris 数据库，采用拉链表（SCD Type 2）思想，旨在完整保留历史数据变更轨迹，支持时间旅行查询（Time Travel Query）。

1. 设计思路：为何选择 SCD Type 2 拉链表？

在充电桩业务中，数据具有不同的更新频率和业务含义：

1. 基础信息（Profile）：如场站名称、地址。这些信息相对稳定，偶尔变化（如更名、迁址）。
2. 实时状态（Status）：如空闲桩数、总桩数。这些信息变化极快（分钟级），是分析热度的关键。
3. 价格策略（Price）：如分时电价。通常按天或月调整，但一旦调整需要保留历史价格以核对账单或分析价格趋势。

如果仅保存“最新状态”，我们将丢失所有历史轨迹，无法回答“上个月该站点的平均空闲率是多少”或“价格调整前后的流量对比”等问题。

因此，我们采用 SCD Type 2（Slowly Changing Dimension Type 2） 拉链表设计：

• 每条记录都有 valid_start_time（生效时间）和 valid_end_time（失效时间）。
• 当前有效的记录，valid_end_time 通常设为极大值（如 9999-12-31）。
• 当数据发生变化时：
  1. 将旧记录的 valid_end_time 更新为当前时间（闭链）。
  2. 插入一条新记录，valid_start_time 为当前时间，valid_end_time 为极大值（开链）。

2. 表结构定义

2.1 充电站基础信息表 (station_profile_scd)

• 用途：存储场站名称、地址等相对稳定的属性。
• 业务主键：station_hash (名称的MD5，确保唯一性)。

字段名	类型	说明
id	VARCHAR(50)	物理主键 (UUID)，每条历史记录唯一
station_hash	VARCHAR(32)	业务主键，同一场站的所有历史记录此值相同
station_name	VARCHAR(255)	场站名称
address	VARCHAR(500)	详细地址
valid_start_time	DATETIME	记录生效时间
valid_end_time	DATETIME	记录失效时间 (默认 9999-12-31)
is_current	TINYINT	是否为当前最新记录 (1=是, 0=否)

2.2 充电站实时状态表 (station_status_scd)

• 用途：高频存储场站的桩数、空闲情况。
• 特点：数据量大，通过拉链表仅记录“状态发生变化”的时刻，节省空间。

字段名	类型	说明
id	VARCHAR(50)	物理主键 (UUID)
station_hash	VARCHAR(32)	业务主键
total_piles	INT	总桩数
free_piles	INT	空闲桩数
piles_detail_json	TEXT	桩详情JSON (如 {"快": {"free": 2, "total": 10}, "慢": ...})
current_price	DECIMAL	抓取时的当前电价快照
valid_start_time	DATETIME	生效时间
valid_end_time	DATETIME	失效时间
is_current	TINYINT	是否最新

2.3 充电站价格时段表 (station_price_schedule_scd)

• 用途：存储全天的分时电价表。

字段名	类型	说明
id	VARCHAR(50)	物理主键 (UUID)
station_hash	VARCHAR(32)	业务主键
schedule_json	TEXT	价格时段JSON数组
valid_start_time	DATETIME	生效时间
valid_end_time	DATETIME	失效时间
is_current	TINYINT	是否最新

3. 常见查询场景及 SQL 写法

场景一：查询所有场站的“当前”最新状态

这是最常用的查询，用于实时展示。

SELECT 
    p.station_name,
    p.address,
    s.total_piles,
    s.free_piles,
    s.current_price
FROM 
    station_profile_scd p
JOIN 
    station_status_scd s ON p.station_hash = s.station_hash
WHERE 
    p.is_current = 1 
    AND s.is_current = 1;

场景二：查询“指定时间点”的历史快照

例如：查询 2023-10-01 14:00:00 这一刻，所有场站的状态。 这是拉链表的杀手锏功能。

SET @query_time = '2023-10-01 14:00:00';

SELECT 
    p.station_name,
    s.free_piles,
    s.current_price
FROM 
    station_profile_scd p
JOIN 
    station_status_scd s ON p.station_hash = s.station_hash
WHERE 
    -- 核心逻辑：查询时间落在生效区间内
    (@query_time >= p.valid_start_time AND @query_time < p.valid_end_time)
    AND (@query_time >= s.valid_start_time AND @query_time < s.valid_end_time);

场景三：分析某场站过去24小时的空闲桩变化趋势

我们需要按时间顺序拉出该场站的所有状态变更记录。

SELECT 
    valid_start_time AS change_time,
    free_piles,
    total_piles,
    ROUND(free_piles / total_piles * 100, 2) AS free_rate_percent
FROM 
    station_status_scd
WHERE 
    station_hash = 'TARGET_STATION_MD5'
    AND valid_start_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY 
    valid_start_time ASC;

场景四：统计过去一个月，价格发生过变动的场站

通过查找 station_price_schedule_scd 表中同一个 station_hash 有多条记录的情况。

SELECT 
    p.station_name,
    COUNT(pr.id) AS price_change_count
FROM 
    station_price_schedule_scd pr
JOIN 
    station_profile_scd p ON pr.station_hash = p.station_hash AND p.is_current = 1
WHERE 
    pr.valid_start_time >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY 
    p.station_name
HAVING 
    COUNT(pr.id) > 1
ORDER BY 
    price_change_count DESC;

4. 数据写入与维护策略

为了维护 SCD2 结构，写入程序（Crawler/ETL）需要遵循以下逻辑：

1. 计算 Hash：对抓取到的场站名称计算 MD5，作为 station_hash。
2. 查询最新状态：根据 station_hash 查询 is_current=1 的记录。
3. 比对差异：
   • Profile: 比对名称、地址是否变化。
   • Status: 比对总桩数、空闲数、价格是否变化。
   • Price: 比对价格时段 JSON 是否变化。
4. 无变化：跳过，不进行任何数据库操作。
5. 有变化：
   • Step 1 (Close): 更新旧记录：UPDATE table SET valid_end_time = NOW(), is_current = 0 WHERE id = 'old_uuid'。
   • Step 2 (Insert): 插入新记录：INSERT INTO table (..., valid_start_time=NOW(), is_current=1)。
   • 注意：Step 1 和 Step 2 最好在一个事务中完成，或者保证最终一致性。

5. 性能优化建议

1. 分桶 (Bucketing)：建表时使用了 DISTRIBUTED BY HASH(station_hash) BUCKETS 10。这保证了同一个场站的所有历史记录都落在同一个 Tablet 上，极大地优化了基于 station_hash 的 Join 和聚合查询性能。
2. 分区 (Partitioning)：当前未显式使用时间分区。如果数据量达到亿级，建议按 valid_start_time 进行 Range 分区（如按月），以便冷热数据分离和快速清理过期历史数据。
3. 索引: Doris 自动为前缀列创建索引。由于我们常按 station_hash 查询，它被放在表定义的列首（或紧跟 ID），查询效率很高。

---

文档维护人: AI Assistant 最后更新: 2026-01-08