FR动态多表查询(一个比较清奇的数据集写法方案)

FR动态多表查询解决方案

业务场景

问题描述： 数据按天存储在不同表中，表名格式为 TableName_YYYY_MM_DD，需要查询指定日期范围的数据。

挑战： 无法提前确定需要查询哪些表，传统的多表 UNION ALL 方式不适用。

核心解决思路

通过动态获取表名列表，使用字符串替换技术生成 UNION ALL 查询语句。

实现步骤

1. 查询符合条件的表名
2. 使用 REPLACE 函数动态生成 UNION ALL
3. 执行最终的聚合查询

完整示例

SELECT  a, b, d,
        MIN(c) AS minc,
        MAX(c) AS maxc,
        DATEDIFF(mi,MIN(c),MAX(c))+1 AS 时长
FROM (
    SELECT  a, b, c,
            ROW_NUMBER() OVER (PARTITION BY a ORDER BY c) - 
            ROW_NUMBER() OVER (PARTITION BY a,b ORDER BY c) AS d
    FROM (
        SELECT  id AS a,
                CASE WHEN CAST(value AS FLOAT) >= 20 
                     THEN '运行' ELSE '停机' END AS b,
                acptime AS c
        FROM ${REPLACE(
            SQL("JDBC5","
                SELECT name
                FROM sysobjects
                WHERE xtype='u'
                  AND name LIKE 'ValueTable_FLOAT_%'
                  AND REPLACE(name,'ValueTable_FLOAT_','') >= '" + FORMAT(KSRQ,"yyyy_MM_dd") + "'
                  AND REPLACE(name,'ValueTable_FLOAT_','') <= '" + FORMAT(JSRQ,"yyyy_MM_dd") + "'
            ", 1),
            ",",
            " WHERE 1=1 " + IF(LEN(ID)==0,""," AND ID IN ('"+ID+"')") + "
            UNION ALL
            SELECT ID,
                   CASE WHEN CAST(value AS FLOAT) >= 20 
                        THEN '运行' ELSE '停机' END AS Value,
                   AcpTime
            FROM "
        )}
        WHERE 1=1 ${IF(LEN(ID)==0,""," AND ID IN ('"+ID+"')")}
    ) t1
) t2
GROUP BY a, b, d

核心技术解析

1. 动态表名查询

-- 查询系统表获取符合条件的表名
SELECT name
FROM sysobjects
WHERE xtype='u'                                          -- 用户表
  AND name LIKE 'ValueTable_FLOAT_%'                     -- 表名模式
  AND REPLACE(name,'ValueTable_FLOAT_','') >= '开始日期'   -- 日期范围过滤
  AND REPLACE(name,'ValueTable_FLOAT_','') <= '结束日期'

2. 字符串替换技术

-- 将逗号分隔的表名替换为 UNION ALL 语句
REPLACE(
    "table1,table2,table3",     -- 表名列表
    ",",                        -- 分隔符
    " WHERE 条件 UNION ALL SELECT ... FROM "  -- 替换内容
)

3. 结果示例

原始：table1,table2,table3
替换后：
SELECT ... FROM table1 WHERE 条件
UNION ALL SELECT ... FROM table2 WHERE 条件  
UNION ALL SELECT ... FROM table3 WHERE 条件

适用场景

✅ 适合的场景：

• 按日期分表存储的历史数据
• 表名规律性强
• 查询频率不高的分析报表

❌ 不适合的场景：

• 高并发实时查询
• 表结构不一致
• 数据量极大的场景

优缺点分析

优点

• 灵活性强：无需预知具体表名
• 维护简单：新增表无需修改查询逻辑
• 适应性好：可处理任意日期范围

缺点

• 性能较差：动态SQL执行效率低
• 调试困难：生成的SQL不易查看调试
• 安全风险：字符串拼接可能存在注入风险

替代方案

1. 分区表方案

-- 创建按日期分区的表
CREATE TABLE data_table (
    id INT,
    value FLOAT,
    acptime DATETIME
) PARTITION BY RANGE (TO_DAYS(acptime)) (
    PARTITION p20230101 VALUES LESS THAN (TO_DAYS('2023-01-02')),
    PARTITION p20230102 VALUES LESS THAN (TO_DAYS('2023-01-03')),
    ...
);

2. 视图联合方案

-- 创建联合视图
CREATE VIEW all_data_view AS
SELECT * FROM ValueTable_FLOAT_2023_01_01
UNION ALL
SELECT * FROM ValueTable_FLOAT_2023_01_02
-- ... 更多表

性能优化建议

1. 添加索引：在查询条件字段上创建索引
2. 限制日期范围：避免查询过多表
3. 缓存结果：对查询结果进行缓存
4. 异步处理：大数据量查询采用异步方式

最佳实践

1. 表名规范：确保表名格式严格统一
2. 日期验证：添加日期范围有效性检查
3. 错误处理：增加异常情况的处理逻辑
4. 性能监控：监控查询执行时间和资源消耗

总结

这种动态多表查询方案在FR报表中还是比较清奇的解决思路。

适用建议：

• 小到中等数据量的分析报表
• 对实时性要求不高的场景
• 表结构相对稳定的环境

长期建议： 考虑数据架构重构，采用分区表或数据仓库方案来根本解决问题。

---

参考来源： FR论坛问题