quadratic_regression_hive.hql

DROP TABLE IF EXISTS
  dados;

CREATE TEMPORARY TABLE 
  dados 
(
  cnpj INT,
  anomes INT,
  sinal FLOAT
);

INSERT INTO
  dados
VALUES
  (101, 201801, 10),
  (101, 201802, 12),
  (101, 201803, 14),
  (101, 201804, null),
  (101, 201805, 18),
  (101, 201806, 20),
  (102, 201801, null),
  (102, 201802, 11.e1),
  (102, 201803, 10.e1),
  (102, 201804,  9.e1),
  (102, 201805, 10.e1),
  (102, 201806, 10.e1),
  (103, 201801, 20.e2),
  (103, 201802, 18.e2),
  (103, 201803, 16.e2),
  (103, 201804, 14.e2),
  (103, 201805, 12.e2),
  (103, 201806, null);

DROP TABLE IF EXISTS
  sinal_acumulado;

CREATE TEMPORARY TABLE
  sinal_acumulado AS
SELECT
  cnpj, 
  x_i,
  sum(ffill) OVER w1 AS y_cum
FROM
(
  SELECT
    cnpj,
    row_number() OVER w0 - 1 AS x_i,
    nvl(LAST_VALUE(sinal, true) OVER w0, 0) AS ffill
  FROM 
    dados
  WINDOW w0 AS
  ( 
    PARTITION BY cnpj
    ORDER BY anomes
  ) 
) t0
WINDOW w1 AS
(
  PARTITION BY cnpj
  ORDER BY x_i
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

DROP TABLE IF EXISTS
  dados_normalizados;
  
CREATE TEMPORARY TABLE
  dados_normalizados AS
SELECT
  cnpj,
  x_i,
  (y_cum - y_min) / (y_max - y_min) AS y_i 
FROM
( 
  SELECT
    cnpj,
    x_i,
    y_cum,
    min(y_cum) OVER w0 AS y_min,
    max(y_cum) OVER w0 AS y_max
  FROM
    sinal_acumulado
  WINDOW w0 AS (PARTITION BY cnpj)
) t0;

DROP TABLE IF EXISTS
  regressao_quadratica;

CREATE TEMPORARY TABLE
  regressao_quadratica AS
SELECT
  cnpj,
  (
    ( xxy / xxx) - ( xy / xx)
  )
  / 
  ( 
    (xxxx / xxx) - (xxx / xx)
  )
  AS target
FROM
(
  SELECT
    cnpj,
    sum(x_i * x_i) AS xx,
    sum(x_i * y_i) AS xy,
    sum(x_i * x_i * x_i) AS xxx,
    sum(x_i * x_i * y_i) AS xxy,
    sum(x_i * x_i * x_i * x_i) AS xxxx
  FROM
    dados_normalizados
  GROUP BY cnpj
) t1
ORDER BY target DESC;

SELECT * FROM regressao_quadratica;

Steps

1. Fill null values
2. Calculate cumulative signal
3. Find min/max values for the cum. signal
4. Normalize cum. signal
5. Calculate aggregations
6. Evaluate target