在Amazon Redshift中计算varchar数组元素和并插入表的方法

问题描述 #

在Amazon Redshift中，weather.precipitation_data表的hourly_precipitation列以varchar类型存储[0.0, 1.0, 2.8]格式的数组字符串，需要将该列的数组元素求和转为浮点类型后，插入到weather.daily_summary表中生成每日降水量汇总。

表结构与数据示例 #

表1：weather.precipitation_data

CREATE TABLE IF NOT EXISTS weather.precipitation_data
(
    record_import_datetime        timestamp,
    location_id                   varchar(10),
    hourly_precipitation          varchar(1000)
)
SORTKEY (location_id);

数据示例：

record_import_datetime     | location_id | hourly_precipitation
2023-04-25 19:00:00.000000 | STATION001  | [0.0, 1.0, 2.8]
2023-04-25 19:00:00.000000 | STATION002  | [0.0, 0.0, 1.2]

表2：weather.daily_summary
注意：原表定义中daily_precipitation为int4，但期望结果是带小数的数值（如3.8），需将该列类型修改为float或numeric避免丢失小数：

CREATE TABLE IF NOT EXISTS weather.daily_summary
(
    record_import_datetime        timestamp,
    location_id                   varchar(10),
    daily_precipitation           float -- 修改为浮点类型以保留小数精度
)
SORTKEY (location_id);

期望插入后的数据：

record_import_datetime     | location_id | daily_precipitation
2023-04-25 19:00:00.000000 | STATION001  | 3.8
2023-04-25 19:00:00.000000 | STATION002  | 1.2

实现SQL语句 #

基础插入语句（无重复数据场景） #

无需关联目标表，直接从源表处理数据后插入：

INSERT INTO weather.daily_summary (
    record_import_datetime,
    location_id,
    daily_precipitation
)
SELECT
    t1.record_import_datetime,
    t1.location_id,
    SUM(CAST(split_part(trimmed_str, ',', n) AS float)) AS daily_precipitation
FROM (
    -- 去除数组首尾的方括号，得到纯逗号分隔的数值字符串
    SELECT
        record_import_datetime,
        location_id,
        TRIM(hourly_precipitation, '[]') AS trimmed_str,
        -- 计算数组元素的数量
        REGEXP_COUNT(hourly_precipitation, ',') + 1 AS element_count
    FROM weather.precipitation_data
) t1
-- 生成连续数字序列，用于拆分每个数组元素
JOIN (
    SELECT ROW_NUMBER() OVER () AS n
    FROM weather.precipitation_data
    LIMIT 100 -- 根据实际数组最大长度调整数值
) nums ON nums.n <= t1.element_count
GROUP BY t1.record_import_datetime, t1.location_id;

关键逻辑解释 #

1. 清理数组格式：用TRIM(hourly_precipitation, '[]')将[0.0,1.0,2.8]转换为0.0,1.0,2.8。
2. 统计元素数量：通过REGEXP_COUNT(hourly_precipitation, ',') + 1获取数组内元素的个数，比如含2个逗号的字符串对应3个元素。
3. 拆分并转换数值：利用生成的数字序列，通过split_part拆分出每个位置的数值字符串，再转为float类型。
4. 分组求和：按时间和地点分组，对拆分后的数值求和得到每日总降水量。

重复数据更新（Upsert）场景 #

如果需要覆盖目标表中已存在的同时间同地点数据，可使用Redshift的INSERT ... ON CONFLICT语法（需先给目标表定义主键/唯一约束）：

INSERT INTO weather.daily_summary (
    record_import_datetime,
    location_id,
    daily_precipitation
)
SELECT
    t1.record_import_datetime,
    t1.location_id,
    SUM(CAST(split_part(trimmed_str, ',', n) AS float)) AS daily_precipitation
FROM (
    SELECT
        record_import_datetime,
        location_id,
        TRIM(hourly_precipitation, '[]') AS trimmed_str,
        REGEXP_COUNT(hourly_precipitation, ',') + 1 AS element_count
    FROM weather.precipitation_data
) t1
JOIN (
    SELECT ROW_NUMBER() OVER () AS n
    FROM weather.precipitation_data
    LIMIT 100
) nums ON nums.n <= t1.element_count
GROUP BY t1.record_import_datetime, t1.location_id
ON CONFLICT (record_import_datetime, location_id)
DO UPDATE SET daily_precipitation = EXCLUDED.daily_precipitation;

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