Oracle SQL动态周度员工统计查询自动更新方案咨询

嘿，这个需求我太熟悉了——手动改SQL加列确实烦得很，咱们用动态SQL+存储过程就能完美解决自动更新报表视图的问题，一步步来：

核心思路 #

因为你的表每周会全量替换数据，所以我们可以每次上传后，自动获取当前表中的最大周数，然后动态生成包含所有周列的PIVOT查询，再创建/替换统计视图。这样就不用手动修改SQL了。

具体实现步骤 #

首先假设你的表名叫employee_activity（如果不是，替换成你的表名就行），我们写一个存储过程来自动刷新视图：

CREATE OR REPLACE PROCEDURE refresh_employee_week_stats_view AS
    v_max_week NUMBER;
    v_col_defs VARCHAR2(4000); -- 存储周列的定义，比如'1 AS week1, 2 AS week2'
    v_sql CLOB; -- 用CLOB避免长SQL超出VARCHAR2限制
BEGIN
    -- 第一步：获取当前表中的最大周数
    SELECT MAX(WEEK) INTO v_max_week FROM employee_activity;

    -- 第二步：循环生成所有周的列定义
    FOR week_num IN 1..v_max_week LOOP
        v_col_defs := v_col_defs || 
                      CASE WHEN v_col_defs IS NOT NULL THEN ',' END ||
                      week_num || ' AS week' || week_num;
    END LOOP;

    -- 第三步：构建动态PIVOT SQL，用NVL把NULL转成0（更直观显示是否有活动）
    v_sql := 'CREATE OR REPLACE VIEW employee_week_stats AS
              SELECT 
                  EMPLOYEE,
                  ' || REPLACE(v_col_defs, ' AS week', ', NVL(week', ') AS week') || '
              FROM (
                  -- 基础子查询：标记员工每周是否有活动（1表示有）
                  SELECT EMPLOYEE, WEEK, 1 AS has_activity
                  FROM employee_activity
              )
              PIVOT (
                  -- 取MAX确保同一员工同一周多条记录只显示1
                  MAX(has_activity) FOR WEEK IN (' || v_col_defs || ')
              )
              ORDER BY EMPLOYEE';

    -- 第四步：执行动态SQL，更新视图
    EXECUTE IMMEDIATE v_sql;

    DBMS_OUTPUT.PUT_LINE('统计视图已更新！当前包含第1至第' || v_max_week || '周的列');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('提示：表中没有数据，请先上传本周的CSV文件');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('更新视图出错：' || SQLERRM);
END;
/

怎么用？ #

每次上传完新一周的CSV数据后，只需要执行这条命令：

EXEC refresh_employee_week_stats_view;

执行完后，employee_week_stats视图就会自动新增最新的周列（比如上传第7周数据后，视图里就会有week7列）。

效果示例 #

用你给的测试数据：

执行存储过程后，视图employee_week_stats的结果会是：

额外提示 #

1. 如果你的周数超过52周（比如跨年度），VARCHAR2(4000)可能不够用，我们已经用CLOB存储动态SQL，不用担心长度问题；
2. 可以把调用存储过程的步骤加到你的CSV上传脚本里（比如用SQL*Loader上传完数据后自动执行），实现完全自动化；
3. 确保执行存储过程的用户有CREATE VIEW权限和对employee_activity表的SELECT权限。

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