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

Asked

Viewed 966 times

2

Good morning folks. 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 select, but it is not correct. Please, someone can help me?

sql oracle pl-sql

  • What you want is called "linear regression" and it’s just a matter of doing some math. I believe that the SQL code posted above is not necessary/relevant to the query (but the question itself is good).

  • @Renan Next, the above query is a part of the function that I have created, it will search in the database the values of the indices and the periods in which you want to check the trend line as well as other data such as which organization, company, sector etc belong.. then it really is necessary. I have been searching and found the "regr_slope" that I am trying to find out if it is suitable for this case. Thanks for the input.

  • The function will be held on top of a mass of data that your code returns. That way, to be able to help, we need to know at most the format of the data, and not the logic that was used to obtain them. Take a look at other questions related to linear regression in the OS, such as this, this and this ;)

  • Or, TL;DR: only select columns really matter for the problem.

  • Renan, in case the data will be dynamic and not a mass of data, can I use this function? .. each month a value is entered (vl_index) I will have to calculate a new trend.

  • Columns change?

  • Has these functions http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions151.htm#SQLRF00696 , just need to remember (or see) how to mount the RL

  • SEE TB https://docs.oracle.com/cd/B28359_01/datamine.111/b28129/regress.htm

  • My query is returning the following data: CD_MES VL_INDICE ID_MULTI_EMPRESA 200902 1068 1 200903 1288 1 ... 200912 1119 1

  • I was trying to use the REGR_SLOPE function but unsuccessfully.

  • @Renan Consegui .. SELECT value, mes, ((mes * SLOPE) + INTERCEPT) TENDENCIA, SLOPE, INTERCEPT from( select value, mes, ROUND(REGR_SLOPE(value,mes) over (Partition by id_multi_company),4)SLOPE, ROUND(REGR_INTERCEPT(value,mes) over (PARTITION by id_multi_company),4) INTERCEPT from( ... I thank everyone who gave attention and could help.

  • Post it there as an answer and earn points ;)

Show 7 more comments

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

Browser other questions tagged sql oracle pl-sql

You are not signed in. Login or sign up in order to post.