Db2 Warehouse on Cloud按分钟采样数据并转CSV矩阵的查询问询

当然可以用单条高效的查询实现你的需求，不需要转换为其他表格式。下面分步骤给你解决方案：

你原来的递归CTE用来生成时间范围的分钟序列是可行的，我们可以在此基础上结合条件聚合来把不同NAME的数据转成列，同时完成同一分钟内的平均值计算，无数据的点自动填充NULL。

针对你示例中的test1、test2、test3三个固定NAME，可以用下面的查询：

WITH dummy(temporaer) AS (
    SELECT TIMESTAMP('2017-12-01 00:00:00') FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT temporaer + 1 MINUTES FROM dummy WHERE temporaer < TIMESTAMP('2018-01-31 23:59:00')
)
SELECT
    temporaer AS "TIMESTAMP",
    AVG(CASE WHEN NAME = 'test1' THEN VALUE END) AS test1,
    AVG(CASE WHEN NAME = 'test2' THEN VALUE END) AS test2,
    AVG(CASE WHEN NAME = 'test3' THEN VALUE END) AS test3
FROM dummy
LEFT OUTER JOIN TESTING
    ON temporaer = DATE_TRUNC('minute', TIMESTAMP) 
    AND ID = 'abc' -- 如需过滤特定ID保留此行；要所有ID则删除
GROUP BY temporaer
ORDER BY temporaer ASC;

说明： #

• 递归CTEdummy生成2017-12-01到2018-01-31的所有分钟时间点；
• CASE WHEN配合AVG实现条件聚合：每个NAME对应一列，同一分钟内的VALUE取平均，无数据时AVG(NULL)自动返回NULL，正好满足填充需求；
• 如果你的NAME是动态变化的（可能新增其他名称），可以用Db2的动态SQL或存储过程生成列，但固定NAME场景下静态查询更高效。

在Db2 Warehouse on Cloud里，有几种简单的导出方式：

方式一：用EXPORT命令（适合批量导出到存储） #

在Db2命令行或控制台执行EXPORT命令，将查询结果导出到CSV文件：

EXPORT TO '/path/to/your/output.csv' OF DEL
MODIFIED BY COLDEL, CHARDEL""
WITH dummy(temporaer) AS (
    SELECT TIMESTAMP('2017-12-01 00:00:00') FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT temporaer + 1 MINUTES FROM dummy WHERE temporaer < TIMESTAMP('2018-01-31 23:59:00')
)
SELECT
    temporaer AS "TIMESTAMP",
    AVG(CASE WHEN NAME = 'test1' THEN VALUE END) AS test1,
    AVG(CASE WHEN NAME = 'test2' THEN VALUE END) AS test2,
    AVG(CASE WHEN NAME = 'test3' THEN VALUE END) AS test3
FROM dummy
LEFT OUTER JOIN TESTING
    ON temporaer = DATE_TRUNC('minute', TIMESTAMP) 
    AND ID = 'abc'
GROUP BY temporaer
ORDER BY temporaer ASC;

方式二：Web控制台直接导出 #

如果你使用Db2 Warehouse on Cloud的Web控制台，执行完查询后，点击结果面板的导出按钮，选择CSV格式即可下载。

方式三：查询返回CSV字符串（适合程序调用） #

如果需要直接返回CSV格式的字符串，可以用LISTAGG结合字符串拼接，同时处理NULL值：

WITH dummy(temporaer) AS (
    SELECT TIMESTAMP('2017-12-01 00:00:00') FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT temporaer + 1 MINUTES FROM dummy WHERE temporaer < TIMESTAMP('2018-01-31 23:59:00')
),
aggregated AS (
    SELECT
        temporaer AS "TIMESTAMP",
        COALESCE(CAST(AVG(CASE WHEN NAME = 'test1' THEN VALUE END) AS VARCHAR(10)), 'null') AS test1,
        COALESCE(CAST(AVG(CASE WHEN NAME = 'test2' THEN VALUE END) AS VARCHAR(10)), 'null') AS test2,
        COALESCE(CAST(AVG(CASE WHEN NAME = 'test3' THEN VALUE END) AS VARCHAR(10)), 'null') AS test3
    FROM dummy
    LEFT OUTER JOIN TESTING
        ON temporaer = DATE_TRUNC('minute', TIMESTAMP) 
        AND ID = 'abc'
    GROUP BY temporaer
    ORDER BY temporaer ASC
)
SELECT LISTAGG(CONCAT_WS(',', "TIMESTAMP", test1, test2, test3), CHAR(10)) AS csv_output
FROM aggregated;

这个查询会把所有行拼接成一个CSV格式的字符串，每行用换行符分隔，列用逗号分隔，NULL值替换为字符串'null'。

• 递归CTE生成时间序列时，注意不要设置过大的时间范围，避免性能问题；2个月的时间范围完全没问题；
• 如需处理多个ID，可以将ID加入GROUP BY，或在CASE WHEN中添加ID的过滤条件；
• 导出CSV时，注意字符编码和分隔符设置，避免乱码。

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