Is there any function to calculate the trend line in PL SQL?

Question

Is there any function to calculate the trend line in PL SQL?

Navigation

#1 by (1 votes)

2

Good morning everyone. I need a function to calculate a trend line. I have a query (part of the function):

select round(sum(nvl(vl_indice, vl_meta))/12, 2) from (
SELECT 
  SUM (vl_indice) vl_indice, SUM (vl_meta) vl_meta
FROM
  (SELECT cd_mes, vl_indice, NULL vl_meta, dt.id_tempo,
    fi.id_multi_empresa, fi.id_setor, fi.id_indice
  FROM dbadw.fa_indice fi , dbadw.di_tempo dt ,
    dbadw.di_multi_empresa dme , dbaportal.organizacao o ,
    dbadw.di_indice di
  WHERE fi.id_tempo        = dt.id_tempo
  AND DT.CD_MES BETWEEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(PCD_MES),'YYYYMM'),- 11),'YYYYMM'))
                          AND PCD_MES
       AND DT.ANO = TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(PCD_MES),'YYYYMM'),'YYYY'))
  AND fi.id_multi_empresa  = dme.id_multi_empresa
  AND dme.cd_multi_empresa = NVL(o.cd_multi_empresa_mv2000, o.cd_organizacao)
  AND o.cd_organizacao     = PCD_ORG
  AND fi.id_setor         IS NULL
  AND fi.id_indice         = di.id_indice
  AND di.cd_indice         = PCD_IVM
  UNION ALL
  SELECT cd_mes, NULL vl_indice, vl_meta, dt.id_tempo,
    fm.id_multi_empresa, fm.id_setor, fm.id_indice
  FROM dbadw.fa_meta_indice fm , dbadw.di_tempo dt ,
    dbadw.di_multi_empresa dme , dbaportal.organizacao o ,
    dbadw.di_indice di
  WHERE fm.id_tempo        = dt.id_tempo
  AND DT.ANO = TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(PCD_MES),'YYYYMM'),'YYYY'))
  AND fm.id_multi_empresa  = dme.id_multi_empresa
  AND dme.cd_multi_empresa = NVL(o.cd_multi_empresa_mv2000, o.cd_organizacao)
  AND o.cd_organizacao     = PCD_ORG
  AND fm.id_setor         IS NULL
  AND fm.id_indice         = di.id_indice
  AND di.cd_indice         = PCD_IVM
  )
GROUP BY cd_mes, id_tempo, id_multi_empresa, id_setor, id_indice
ORDER BY cd_mes);

Where I tried to calculate the trend line in the first row of the select, but it is not correct. Please, can anyone help me?

sql oracle pl-sql

asked by anonymous 11.05.2016 / 16:29

1 answer

Problems with HTML images sent to email How to make MYSQL Reverse Bank Engineering with SqlAlchemy?

score 1 · Answer 1

The solution is:

SELECT valor, mes, ((mes * SLOPE) + INTERCEPT) TENDENCIA, SLOPE, INTERCEPT from( 
SELECT valor, mes, ROUND(REGR_SLOPE(valor,mes) over (partition by id_multi_empresa),4)SLOPE, 
ROUND(REGR_INTERCEPT(valor,mes) over (PARTITION by id_multi_empresa),4) INTERCEPT from( -- O select inicial