질문

스키마의 모든 열을 동일한 열의 추세와 비교해야하는 작업이 있습니다. 이를 위해 대량으로 삽입 문을 생성하는 쿼리를 작성했습니다. 총 삽입 문의 수는 50K를 초과합니다. 내가 작성한 SQL 생성 쿼리는 다음과 같습니다.

select ' insert into global.PM_COUNTER_AUDIT  select  ''' || column_name ||
       '''  COUNTER_NAME,   pre.country,pre.city, pre.stddev_col_name , pre.cnt_wk,post.counter_last_day,var_wk from (select  country, city,stddev(nvl(' || column_name ||
       ',0)) stddev_col_name,  variance(nvl(' || column_name || ',0)) var_wk, avg(nvl(' || column_name || ',0)) cnt_wk, count(*) from  ' || owner || '.' || table_name ||
       ' where datetime>=trunc(sysdate)-14  and  upper(trim(tO_char(datetime,''DAY''))) NOT IN (''FRIDAY'',''SATURDAY'') group by country, city)pre left outer join (select country, city,sum(nvl(' ||
       column_name || ',0)) counter_last_day  from  ' || owner || '.' || table_name ||
       ' where datetime>=trunc(sysdate)-1 group by country, city )post  on  pre.country=post.country and pre.city=post.city where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))  and pre.cnt_wk+(3*(pre.stddev_col_name)) '
from   all_tab_cols
where  owner = 'HUAWEI_EUTRAN_PM'
and    upper(table_name) in ('TABLE_X', 'TABLE_Y', table_z)
and    nullable = 'Y'

이제 커서 또는 즉시 실행을 사용하여 생성 된 모든 문을 실행하고 싶지만이를 수행 할 수 없습니다. 어떻게 할 수 있는지 친절하게 조언하십시오.

TIA

답변1

코드를 약간 정리하면 다음과 같이 할 수 있습니다.

begin
  for r in (
    select 'insert into table_name
select ''' || COLUMN_NAME || ''' counter_name, pre.country, pre.city,
  pre.stddev_col_name, pre.cnt_wk, post.counter_last_day, pre.var_wk
from (
  select country, city, stddev(nvl(' || COLUMN_NAME || ', 0)) stddev_col_name, 
    variance(nvl(' || COLUMN_NAME || ', 0)) var_wk,
    avg(nvl(' || COLUMN_NAME || ',0)) cnt_wk,
    count(*)
  from ' || OWNER || '.' || TABLE_NAME ||'
  where datetime >= trunc(sysdate) - 14
  and to_char(datetime, ''FMDAY'', ''NLS_DATE_LANGUAGE=ENGLISH'') NOT IN (''FRIDAY'', ''SATURDAY'')
  group by country, city
) pre
left outer join (
  select country, city, sum(nvl(' || COLUMN_NAME || ', 0)) counter_last_day 
  from ' || OWNER || '.' || TABLE_NAME || '
  where datetime >= trunc(sysdate) - 1
  group by country, city
) post
on pre.country = post.country
and pre.city = post.city
where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))
and pre.cnt_wk+(3*(pre.stddev_col_name))' as stmt
from all_tab_cols where owner = 'HEXA'
and table_name in ('Z_STATS', 'Y_STATS', 'X_STATS')
and nullable = 'Y'
and rownum <= 10
  ) loop

    dbms_output.put_line(r.stmt);
    execute immediate r.stmt;

  end loop;
end;
/

불필요한 부분 몇 개를 꺼내서 일일 확인을 단순화하고 해당 부분에 사용할 명시적인 언어를 추가했습니다.

다음과 같은 명령문을 생성하고 실행합니다.

insert into table_name
select 'ABC' counter_name, pre.country, pre.city,
  pre.stddev_col_name, pre.cnt_wk, post.counter_last_day, pre.var_wk
from (
  select country, city, stddev(nvl(ABC, 0)) stddev_col_name, 
    variance(nvl(ABC, 0)) var_wk,
    avg(nvl(ABC,0)) cnt_wk,
    count(*)
  from HEXA.Z_STATS
  where datetime >= trunc(sysdate) - 14
  and to_char(datetime, 'FMDAY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('FRIDAY', 'SATURDAY')
  group by country, city
) pre
left outer join (
  select country, city, sum(nvl(ABC, 0)) counter_last_day 
  from HEXA.Z_STATS
  where datetime >= trunc(sysdate) - 1
  group by country, city
) post
on pre.country = post.country
and pre.city = post.city
where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))
and pre.cnt_wk+(3*(pre.stddev_col_name))

... 여기서 z_stats 에는 abc 라는 nullable 열이 있습니다.

db < > fiddle ( user대신 HEXA 가 실행되므로 실행됩니다).

내가 꺼낸 것 중 하나는 UPPER (TABLE_NAME) 였습니다. 테이블 이름이 따옴표로 묶인 식별자로 만든 대소문자가 혼합 된 경우에만 필요합니다. 그것들이 있다면 코드의 다른 참조는 그것들을 인용해야 할 것이므로 아마도 노이즈 일 것입니다. 추가하고 싶은 것은 데이터 유형 검사이므로 숫자 열만 선택합니다.

답변2

당신의 진술은 거의 읽지 않는 것 외에도 무의미합니다. 다음과 유사한 절차를 실행하려고한다고 가정합니다.

DECLARE
    sqlstr VARCHAR2(10000);
    
    CURSOR SourceTables IS
    SELECT OWNER, TABLE_NAME, COLUMN_NAME
    FROM ALL_TAB_COLS 
    WHERE OWNER = 'HEXA' 
        AND TABLE_NAME IN ('Z_STATS','Y_STATS','X_STATS') 
        AND NULLABLE = 'Y' 
        AND ROWNUM <= 10;
    
BEGIN

    FOR aTable IN SourceTables LOOP
        sqlstr := 
            'INSERT INTO table_name 
            WITH pre AS (
                SELECT country, city,
                    STDDEV(NVL( '||aTable.COLUMN_NAME||',0)) stddev_col_name,  
                    VARIANCE(NVL('||aTable.COLUMN_NAME||',0)) var_wk, 
                    AVG(NVL('||aTable.COLUMN_NAME||',0)) cnt_wk, 
                    COUNT(*) AS N
                FROM '||aTable.OWNER||'.'||aTable.TABLE_NAME||'
                WHERE datetime >= TRUNC(SYSDATE)-14 
                    AND TO_CHAR(datetime,''DY'', ''NLS_DATE_LANGUAGE = American'') NOT IN (''FRI'',''SAT'')
                GROUP BY country, city),
            post AS (
                SELECT country, city,
                    SUM(NVL('||aTable.OLUMN_NAME||',0)) counter_last_day  
                FROM  '||aTable.OWNER||'.'||aTable.TABLE_NAME||' 
                WHERE datetime >= TRUNC(SYSDATE)-1 
                GROUP BY country, city)
            SELECT '||aTable.COLUMN_NAME||' COUNTER_NAME, 
                pre.country,
                pre.city, 
                pre.stddev_col_name, 
                pre.cnt_wk,
                post.counter_last_day,
                var_wk
            FROM pre
                LEFT OUTER JOIN post ON  pre.country = post.country AND pre.city = post.city 
            WHERE counter_last_day NOT BETWEEN pre.cnt_wk-(3*(pre.stddev_col_name)) AND pre.cnt_wk+(3*(pre.stddev_col_name))';

        EXECUTE IMMEDIATE sqlstr;
    END LOOP;
END;

쿼리는별로 의미가 없으며 PL / SQL이 즉시 작동하지 않을 가능성이 높지만 작성 방법에 대한 아이디어를 얻었 으면합니다.

창 조항 을 살펴볼 수 있습니다. 하위 쿼리없이 쿼리를 실행할 수 있어야합니다. CTE. 다음과 유사 할 수 있습니다.

SELECT
    country, 
    city,
    STDDEV(COLUMN_NAME) OVER (PARTITION BY  country, city ORDER BY datetime RANGE INTERVAL '14' DAY PRECEDING) stddev_col_name,  
    VARIANCE(COLUMN_NAME) OVER (PARTITION BY  country, city ORDER BY datetime RANGE INTERVAL '14' DAY PRECEDING) var_wk, 
    AVG(COLUMN_NAME) OVER (PARTITION BY country, city ORDER BY datetime RANGE INTERVAL '14' DAY PRECEDING) nt_wk, 
    SUM(COLUMN_NAME) OVER (PARTITION BY country, city ORDER BY datetime RANGE INTERVAL '1' DAY PRECEDING) counter_last_day, 
    COUNT(*) AS N
FROM TABLE_NAME
WHERE datetime >= TRUNC(SYSDATE)-14 
    AND TO_CHAR(datetime,'DY', 'NLS_DATE_LANGUAGE = American') NOT IN ('FRI','SAT');