如何用单个连接列替代Snowflake中日期范围BETWEEN子句

针对你提到的用单等值查询替代BETWEEN时间范围查询的需求，以下是三种适配不同场景的解决方案：

方案1：固定时间粒度离散化（适合查询粒度固定、区间不跨粒度的场景） #

如果业务查询的<input_date>统一为固定粒度（如天、周、月），且事实行的生效/失效周期均为该粒度的整数倍（无跨粒度区间），可以将生成列定义为区间起始时间的粒度标识，查询时将输入日期转换为相同标识做等值匹配。

操作代码：

-- 添加天级粒度的生成列
ALTER TABLE your_slowly_changing_fact_table
ADD COLUMN GENERATED_COL STRING
AS (TO_CHAR(DATE_TRUNC('DAY', EFFECTIVE_TS), 'YYYYMMDD'))
COMMENT '天级生效日期标识，用于等值查询';

-- 优化后的查询语句
SELECT *
FROM your_slowly_changing_fact_table
WHERE GENERATED_COL = TO_CHAR(<input_date>, 'YYYYMMDD');

方案2：区间唯一编码+辅助映射表（适用于任意区间、查询频繁的场景） #

若事实行的时间区间无固定规律，可将每个区间编码为唯一值，同时预构建日期到区间编码的映射表，查询时通过映射表获取输入日期对应的编码，再匹配生成列。

操作代码：

-- 添加区间唯一编码生成列
ALTER TABLE your_slowly_changing_fact_table
ADD COLUMN GENERATED_COL STRING
AS (CONCAT(TO_CHAR(EFFECTIVE_TS, 'YYYYMMDDHH24MISS'), '_', TO_CHAR(EXPIRATION_TS, 'YYYYMMDDHH24MISS')))
COMMENT '生效-失效区间的唯一编码';

-- 构建日期到区间编码的映射表（以天粒度为例）
CREATE OR REPLACE TABLE date_interval_map AS
SELECT
    d.calendar_date AS input_date,
    f.GENERATED_COL
FROM
    -- 生成业务覆盖范围内的所有日期
    (SELECT DATEADD(DAY, SEQ4(), '2020-01-01') AS calendar_date FROM TABLE(GENERATOR(ROWCOUNT => 10950))) d
LEFT JOIN
    your_slowly_changing_fact_table f
ON
    d.calendar_date BETWEEN f.EFFECTIVE_TS AND f.EXPIRATION_TS;

-- 优化后的查询语句
SELECT f.*
FROM your_slowly_changing_fact_table f
JOIN date_interval_map m
ON f.GENERATED_COL = m.GENERATED_COL
WHERE m.input_date = <input_date>;

方案3：时间分片哈希数组（适用于任意区间、容忍非严格等值的场景） #

将时间轴划分为固定大小的分片（如每小时），生成列存储事实行覆盖的所有分片的哈希数组，查询时将输入日期转换为对应分片的哈希，用数组包含判断替代范围查询（性能接近等值查询）。

操作代码：

-- 创建UDF生成区间内所有小时分片的哈希数组
CREATE OR REPLACE FUNCTION GET_INTERVAL_HOUR_HASHES(start_ts TIMESTAMP, end_ts TIMESTAMP)
RETURNS ARRAY
LANGUAGE JAVASCRIPT
AS $$
    let hashes = [];
    let current = new Date(start_ts);
    const end = new Date(end_ts);
    // 遍历区间内的每小时分片
    while (current <= end) {
        // 生成小时分片的字符串标识
        const hourStr = current.toISOString().slice(0, 13).replace(/[-T]/g, '');
        // 调用Snowflake内置SHA256函数生成哈希
        const hash = snowflake.execute({sqlText: `SELECT SHA2('${hourStr}', 256)`}).next().getColumnValue(0);
        hashes.push(hash);
        current.setHours(current.getHours() + 1);
    }
    return hashes;
$$;

-- 添加分片哈希数组生成列
ALTER TABLE your_slowly_changing_fact_table
ADD COLUMN GENERATED_COL ARRAY
AS (GET_INTERVAL_HOUR_HASHES(EFFECTIVE_TS, EXPIRATION_TS))
COMMENT '区间覆盖的小时分片哈希数组';

-- 优化后的查询语句
SELECT *
FROM your_slowly_changing_fact_table
WHERE ARRAY_CONTAINS(
    SHA2(TO_CHAR(DATE_TRUNC('HOUR', <input_date>), 'YYYYMMDDHH24'), 256),
    GENERATED_COL
);

注意事项 #

• 生成列的表达式必须是确定性的，Snowflake才允许创建（即相同输入必须返回相同结果）。
• 若使用数组类型生成列，建议配合Snowflake的搜索优化服务（Search Optimization Service），进一步提升查询性能。
• 方案3中，若区间跨度极大，生成列会占用较多存储，需根据业务实际权衡存储成本与查询性能。

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