如何在ClickHouse中高效存储时间-影响元组数组以支持查询

背景说明 #

你需要存储和特定时段绑定的「影响等级」条目，每个条目包含多个(时段, 等级)元组，示例数据如下：

(8:30 AM, "High"),
(8:30 AM, "Medium"),
(9:30 AM, "High"),
(10:00 AM, "Medium")

之前采用字符串拼接（如0830-H_0830-M_0930-H_1000-M）的存储方式，在判断条目是否包含/排除指定元组时逻辑繁琐，且性能不佳。考虑到这类数据变体最多只有几十种，以下是几种在ClickHouse中适配的高效存储方案，可支持你提出的三类查询需求。

方案1：数组存储结构化元组（通用灵活） #

利用ClickHouse的数组+元组类型，直接存储结构化的时段-等级对，同时把时段转成数值类型（比如一天内的分钟数，节省空间且查询更快），等级用枚举类型替代字符串，进一步提升性能。

表结构定义 #

CREATE TABLE impact_items (
    item_id UInt64,
    impact_pairs Array(Tuple(UInt16, Enum8('Low'=1, 'Medium'=2, 'High'=3)))
) ENGINE = MergeTree()
ORDER BY item_id;

• UInt16存储时段：比如8:30 AM转为8*60+30=510，9:30 AM转为570，10:00 AM转为600
• 枚举类型Enum8比字符串更节省存储空间，且对比操作更快

插入示例数据 #

INSERT INTO impact_items VALUES
(1, [(510, 'High'), (510, 'Medium'), (570, 'High'), (600, 'Medium')]);

对应三类查询的SQL实现 #

查询1：条目包含8:30 AM High和9:30 AM High，允许存在其他指定元组

SELECT item_id
FROM impact_items
WHERE has(impact_pairs, (510, 'High')) 
  AND has(impact_pairs, (570, 'High'));

查询2：条目仅包含8:30 AM High和9:30 AM High，不允许有其他元组

-- 方式1：通过数组排序后完全匹配
SELECT item_id
FROM impact_items
WHERE arraySort(impact_pairs) = arraySort([(510, 'High'), (570, 'High')]);

-- 方式2：数组长度+存在判断（适合无重复元组的场景）
SELECT item_id
FROM impact_items
WHERE length(impact_pairs) = 2
  AND has(impact_pairs, (510, 'High')) 
  AND has(impact_pairs, (570, 'High'));

查询3：条目包含8:30 AM High和9:30 AM High，且不存在10:00 AM High

SELECT item_id
FROM impact_items
WHERE has(impact_pairs, (510, 'High')) 
  AND has(impact_pairs, (570, 'High'))
  AND NOT has(impact_pairs, (600, 'High'));

方案2：预生成变体编码（极致性能） #

因为数据变体最多几十种，可提前给每种唯一的变体分配一个编码（比如UInt8足够），查询时直接通过编码过滤，性能最优。

步骤1：创建主表与变体映射表 #

-- 主表：存储条目ID和对应的变体编码，可选保留原始数据用于验证
CREATE TABLE impact_items (
    item_id UInt64,
    variant_code UInt8,
    impact_pairs Array(Tuple(UInt16, Enum8('Low'=1, 'Medium'=2, 'High'=3)))
) ENGINE = MergeTree()
ORDER BY variant_code;

-- 变体映射表：维护编码和对应元组的映射，可额外添加查询条件标记
CREATE TABLE variant_mappings (
    variant_code UInt8,
    impact_pairs Array(Tuple(UInt16, Enum8('Low'=1, 'Medium'=2, 'High'=3))),
    has_0830_high Bool,
    has_0930_high Bool,
    has_1000_high Bool
) ENGINE = MergeTree()
ORDER BY variant_code;

步骤2：预填充映射表并关联查询 #

比如提前计算好每个变体是否符合各类查询条件，查询时直接关联映射表过滤：

-- 查询2：仅包含8:30 AM High和9:30 AM High的条目
SELECT i.item_id
FROM impact_items i
JOIN variant_mappings m ON i.variant_code = m.variant_code
WHERE m.has_0830_high = 1
  AND m.has_0930_high = 1
  AND length(m.impact_pairs) = 2;

这种方式把复杂的数组判断提前预计算，查询时只需做简单的等值过滤，性能拉满。

方案3：宽表存储（固定时段场景） #

如果涉及的时段是固定的（比如只有几个特定时间点），可以将每个时段的等级做成独立的数组字段，查询逻辑更直观。

表结构定义 #

CREATE TABLE impact_items (
    item_id UInt64,
    time_0830 Array(Enum8('Low'=1, 'Medium'=2, 'High'=3)),
    time_0930 Array(Enum8('Low'=1, 'Medium'=2, 'High'=3)),
    time_1000 Array(Enum8('Low'=1, 'Medium'=2, 'High'=3))
) ENGINE = MergeTree()
ORDER BY item_id;

插入示例数据 #

INSERT INTO impact_items VALUES
(1, ['High', 'Medium'], ['High'], ['Medium']);

对应查询2的SQL实现 #

SELECT item_id
FROM impact_items
WHERE time_0830 = ['High'] 
  AND time_0930 = ['High']
  AND time_1000 = [];

内容的提问来源于stack exchange，提问作者Sean