Create features with sql
Project description
featsql
from featsql.featsqlite import *
from featsql.featmysql import *
from featsql.featsnow import *
Imports
import pandas as pd
import mysql.connector
from sqlalchemy import create_engine
pd.set_option('display.max_columns', None)
Install
pip install featsql
1. SQLITE
Configurando a engine
url_db = "sqlite:///../../data/mydatabase.db"
engine = create_engine(url_db)
Visão inicial do público
Primeiro vamos observar o formato da tabela spine
df_spine = pd.read_sql("SELECT * FROM tb_spine", engine)
df_spine.head()
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA_REF | Target | |
|---|---|---|---|
| 0 | 4 | 2023-02-01 | 0 |
| 1 | 5 | 2023-02-01 | 0 |
| 2 | 6 | 2023-02-01 | 0 |
| 3 | 7 | 2023-02-01 | 0 |
| 4 | 10 | 2023-02-01 | 0 |
Visão inicial da tabela de variáveis
A tabela de variáveis contém 4 variáveis, duas sendo numéricas e duas categórica. Perceba que existem mais ID’s únicos e datas disponíveis nessa tabela do que na tabela spine, caso que ocorre no dia a dia.
df_data = pd.read_sql("SELECT * FROM tb_feat", engine)
df_data.head()
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA | FEAT_NUM1 | FEAT_NUM2 | FEAT_CAT1 | FEAT_CAT2 | |
|---|---|---|---|---|---|---|
| 0 | 1 | 2023-01-01 | -97 | -44 | A | C |
| 1 | 2 | 2023-01-01 | 89 | 67 | C | B |
| 2 | 3 | 2023-01-01 | 53 | 24 | A | B |
| 3 | 4 | 2023-01-01 | -40 | 62 | B | C |
| 4 | 5 | 2023-01-01 | 41 | 62 | B | B |
Criação de variáveis numéricas
A função sqlite_create_query_num() cria um texto com a query para a criação de variáveis com as operações soma, mínimo, máximo e média das variáveis listadas em feat_num_lista e com a janela de tempo listada em lista_janela.
tb_publico = 'tb_spine'
tb_feat = 'tb_feat'
id = 'ID'
safra_ref = 'SAFRA_REF'
safra = 'SAFRA'
feat_num_lista = ['FEAT_NUM1','FEAT_NUM2']
lista_janela = [1,2,3]
query_final_num_sqlite = sqlite_create_query_num(tb_publico, tb_feat, lista_janela,feat_num_lista, id, safra_ref, safra)
df_num_sqlite = pd.read_sql(query_final_num_sqlite, engine)
df_num_sqlite.head()
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA_REF | FEAT_NUM1_SUM_1M | FEAT_NUM1_MIN_1M | FEAT_NUM1_MAX_1M | FEAT_NUM1_AVG_1M | FEAT_NUM2_SUM_1M | FEAT_NUM2_MIN_1M | FEAT_NUM2_MAX_1M | FEAT_NUM2_AVG_1M | FEAT_NUM1_SUM_2M | FEAT_NUM1_MIN_2M | FEAT_NUM1_MAX_2M | FEAT_NUM1_AVG_2M | FEAT_NUM2_SUM_2M | FEAT_NUM2_MIN_2M | FEAT_NUM2_MAX_2M | FEAT_NUM2_AVG_2M | FEAT_NUM1_SUM_3M | FEAT_NUM1_MIN_3M | FEAT_NUM1_MAX_3M | FEAT_NUM1_AVG_3M | FEAT_NUM2_SUM_3M | FEAT_NUM2_MIN_3M | FEAT_NUM2_MAX_3M | FEAT_NUM2_AVG_3M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 4 | 2023-02-01 | -40.0 | -40.0 | -40.0 | -40.0 | 62.0 | 62.0 | 62.0 | 62.0 | -40 | -40 | -40 | -40.0 | 62 | 62 | 62 | 62.0 | -40 | -40 | -40 | -40.0 | 62 | 62 | 62 | 62.0 |
| 1 | 5 | 2023-02-01 | 41.0 | 41.0 | 41.0 | 41.0 | 62.0 | 62.0 | 62.0 | 62.0 | 41 | 41 | 41 | 41.0 | 62 | 62 | 62 | 62.0 | 41 | 41 | 41 | 41.0 | 62 | 62 | 62 | 62.0 |
| 2 | 6 | 2023-02-01 | 36.0 | 36.0 | 36.0 | 36.0 | 63.0 | 63.0 | 63.0 | 63.0 | 36 | 36 | 36 | 36.0 | 63 | 63 | 63 | 63.0 | 36 | 36 | 36 | 36.0 | 63 | 63 | 63 | 63.0 |
| 3 | 7 | 2023-02-01 | 47.0 | 47.0 | 47.0 | 47.0 | 44.0 | 44.0 | 44.0 | 44.0 | 47 | 47 | 47 | 47.0 | 44 | 44 | 44 | 44.0 | 47 | 47 | 47 | 47.0 | 44 | 44 | 44 | 44.0 |
| 4 | 10 | 2023-02-01 | 29.0 | 29.0 | 29.0 | 29.0 | -7.0 | -7.0 | -7.0 | -7.0 | 29 | 29 | 29 | 29.0 | -7 | -7 | -7 | -7.0 | 29 | 29 | 29 | 29.0 | -7 | -7 | -7 | -7.0 |
Ajustar para não necessariamente criar um dataframe por conta do tamanho
print(query_final_num_sqlite)
WITH
tb_public AS (
SELECT
*
FROM tb_spine
),
-- Criação de variáveis de janela de 1M
tb_janela_1M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM1 para a janela 1
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_SUM_1M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MIN_1M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MAX_1M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_AVG_1M,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM2 para a janela 1
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_SUM_1M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MIN_1M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MAX_1M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_AVG_1M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+1 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis de janela de 2M
tb_janela_2M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM1 para a janela 2
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_SUM_2M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MIN_2M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MAX_2M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_AVG_2M,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM2 para a janela 2
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_SUM_2M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MIN_2M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MAX_2M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_AVG_2M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+2 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis de janela de 3M
tb_janela_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM1 para a janela 3
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_SUM_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MIN_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MAX_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_AVG_3M,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM2 para a janela 3
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_SUM_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MIN_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MAX_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_AVG_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+3 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_join AS (
SELECT
*
FROM tb_public
LEFT JOIN tb_janela_1M
ON tb_public.ID = tb_janela_1M.ID
AND tb_public.SAFRA_REF = tb_janela_1M.SAFRA_REF
LEFT JOIN tb_janela_2M
ON tb_public.ID = tb_janela_2M.ID
AND tb_public.SAFRA_REF = tb_janela_2M.SAFRA_REF
LEFT JOIN tb_janela_3M
ON tb_public.ID = tb_janela_3M.ID
AND tb_public.SAFRA_REF = tb_janela_3M.SAFRA_REF
)
SELECT
tb_join.ID,
tb_join.SAFRA_REF,
tb_join.FEAT_NUM1_SUM_1M,
tb_join.FEAT_NUM1_MIN_1M,
tb_join.FEAT_NUM1_MAX_1M,
tb_join.FEAT_NUM1_AVG_1M,
tb_join.FEAT_NUM2_SUM_1M,
tb_join.FEAT_NUM2_MIN_1M,
tb_join.FEAT_NUM2_MAX_1M,
tb_join.FEAT_NUM2_AVG_1M,
tb_join.FEAT_NUM1_SUM_2M,
tb_join.FEAT_NUM1_MIN_2M,
tb_join.FEAT_NUM1_MAX_2M,
tb_join.FEAT_NUM1_AVG_2M,
tb_join.FEAT_NUM2_SUM_2M,
tb_join.FEAT_NUM2_MIN_2M,
tb_join.FEAT_NUM2_MAX_2M,
tb_join.FEAT_NUM2_AVG_2M,
tb_join.FEAT_NUM1_SUM_3M,
tb_join.FEAT_NUM1_MIN_3M,
tb_join.FEAT_NUM1_MAX_3M,
tb_join.FEAT_NUM1_AVG_3M,
tb_join.FEAT_NUM2_SUM_3M,
tb_join.FEAT_NUM2_MIN_3M,
tb_join.FEAT_NUM2_MAX_3M,
tb_join.FEAT_NUM2_AVG_3M
FROM tb_join
Criação de variáveis categóricas
A função sqlite_create_query_cat() cria um texto com a query para a criação de variáveis com a moda de cada uma das variáveis listadas em feat_num_lista na janela de tempo fornecida em lista_janela.
tb_publico = 'tb_spine'
tb_feat = 'tb_feat'
id = 'ID'
safra_ref = 'SAFRA_REF'
safra = 'SAFRA'
feat_num_lista = ['FEAT_CAT1', 'FEAT_CAT2']
lista_janela = [1, 3, 6]
query_final_cat_sqlite = sqlite_create_query_cat(tb_publico, tb_feat, lista_janela,feat_num_lista, id, safra_ref, safra)
df_cat_sqlite_sqlite = pd.read_sql(query_final_cat_sqlite, engine)
df_cat_sqlite_sqlite.head()
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA_REF | FEAT_CAT1_MODA_1M | FEAT_CAT2_MODA_1M | FEAT_CAT1_MODA_3M | FEAT_CAT2_MODA_3M | FEAT_CAT1_MODA_6M | FEAT_CAT2_MODA_6M | |
|---|---|---|---|---|---|---|---|---|
| 0 | 4 | 2023-02-01 | B | C | B | C | B | C |
| 1 | 5 | 2023-02-01 | B | B | B | B | B | B |
| 2 | 6 | 2023-02-01 | A | A | A | A | A | A |
| 3 | 7 | 2023-02-01 | C | B | C | B | C | B |
| 4 | 10 | 2023-02-01 | A | B | A | B | A | B |
print(query_final_cat_sqlite)
WITH
tb_public as (
SELECT
ID,
SAFRA_REF
FROM tb_spine
),
tb_janela_FEAT_CAT1_1M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT1,
COUNT(*) AS frequency_FEAT_CAT1
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+1 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT1
),
tb_row_FEAT_CAT1_1M as (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT1 DESC
) as row_num_FEAT_CAT1_1M
FROM tb_janela_FEAT_CAT1_1M
),
tb_moda_FEAT_CAT1_1M AS(
SELECT
tb_row_FEAT_CAT1_1M .ID,
tb_row_FEAT_CAT1_1M .SAFRA_REF,
tb_row_FEAT_CAT1_1M.FEAT_CAT1 AS FEAT_CAT1_MODA_1M
FROM tb_row_FEAT_CAT1_1M
WHERE row_num_FEAT_CAT1_1M = 1
),
tb_janela_FEAT_CAT2_1M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT2,
COUNT(*) AS frequency_FEAT_CAT2
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+1 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT2
),
tb_row_FEAT_CAT2_1M as (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT2 DESC
) as row_num_FEAT_CAT2_1M
FROM tb_janela_FEAT_CAT2_1M
),
tb_moda_FEAT_CAT2_1M AS(
SELECT
tb_row_FEAT_CAT2_1M .ID,
tb_row_FEAT_CAT2_1M .SAFRA_REF,
tb_row_FEAT_CAT2_1M.FEAT_CAT2 AS FEAT_CAT2_MODA_1M
FROM tb_row_FEAT_CAT2_1M
WHERE row_num_FEAT_CAT2_1M = 1
),
tb_janela_FEAT_CAT1_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT1,
COUNT(*) AS frequency_FEAT_CAT1
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+3 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT1
),
tb_row_FEAT_CAT1_3M as (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT1 DESC
) as row_num_FEAT_CAT1_3M
FROM tb_janela_FEAT_CAT1_3M
),
tb_moda_FEAT_CAT1_3M AS(
SELECT
tb_row_FEAT_CAT1_3M .ID,
tb_row_FEAT_CAT1_3M .SAFRA_REF,
tb_row_FEAT_CAT1_3M.FEAT_CAT1 AS FEAT_CAT1_MODA_3M
FROM tb_row_FEAT_CAT1_3M
WHERE row_num_FEAT_CAT1_3M = 1
),
tb_janela_FEAT_CAT2_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT2,
COUNT(*) AS frequency_FEAT_CAT2
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+3 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT2
),
tb_row_FEAT_CAT2_3M as (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT2 DESC
) as row_num_FEAT_CAT2_3M
FROM tb_janela_FEAT_CAT2_3M
),
tb_moda_FEAT_CAT2_3M AS(
SELECT
tb_row_FEAT_CAT2_3M .ID,
tb_row_FEAT_CAT2_3M .SAFRA_REF,
tb_row_FEAT_CAT2_3M.FEAT_CAT2 AS FEAT_CAT2_MODA_3M
FROM tb_row_FEAT_CAT2_3M
WHERE row_num_FEAT_CAT2_3M = 1
),
tb_janela_FEAT_CAT1_6M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT1,
COUNT(*) AS frequency_FEAT_CAT1
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+6 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT1
),
tb_row_FEAT_CAT1_6M as (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT1 DESC
) as row_num_FEAT_CAT1_6M
FROM tb_janela_FEAT_CAT1_6M
),
tb_moda_FEAT_CAT1_6M AS(
SELECT
tb_row_FEAT_CAT1_6M .ID,
tb_row_FEAT_CAT1_6M .SAFRA_REF,
tb_row_FEAT_CAT1_6M.FEAT_CAT1 AS FEAT_CAT1_MODA_6M
FROM tb_row_FEAT_CAT1_6M
WHERE row_num_FEAT_CAT1_6M = 1
),
tb_janela_FEAT_CAT2_6M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT2,
COUNT(*) AS frequency_FEAT_CAT2
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+6 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT2
),
tb_row_FEAT_CAT2_6M as (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT2 DESC
) as row_num_FEAT_CAT2_6M
FROM tb_janela_FEAT_CAT2_6M
),
tb_moda_FEAT_CAT2_6M AS(
SELECT
tb_row_FEAT_CAT2_6M .ID,
tb_row_FEAT_CAT2_6M .SAFRA_REF,
tb_row_FEAT_CAT2_6M.FEAT_CAT2 AS FEAT_CAT2_MODA_6M
FROM tb_row_FEAT_CAT2_6M
WHERE row_num_FEAT_CAT2_6M = 1
)
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_moda_FEAT_CAT1_1M.FEAT_CAT1_MODA_1M,
tb_moda_FEAT_CAT2_1M.FEAT_CAT2_MODA_1M,
tb_moda_FEAT_CAT1_3M.FEAT_CAT1_MODA_3M,
tb_moda_FEAT_CAT2_3M.FEAT_CAT2_MODA_3M,
tb_moda_FEAT_CAT1_6M.FEAT_CAT1_MODA_6M,
tb_moda_FEAT_CAT2_6M.FEAT_CAT2_MODA_6M
FROM tb_public
LEFT JOIN tb_moda_FEAT_CAT1_1M
ON tb_moda_FEAT_CAT1_1M.ID = tb_public.ID
AND tb_moda_FEAT_CAT1_1M.SAFRA_REF = tb_public.SAFRA_REF
LEFT JOIN tb_moda_FEAT_CAT2_1M
ON tb_moda_FEAT_CAT2_1M.ID = tb_public.ID
AND tb_moda_FEAT_CAT2_1M.SAFRA_REF = tb_public.SAFRA_REF
LEFT JOIN tb_moda_FEAT_CAT1_3M
ON tb_moda_FEAT_CAT1_3M.ID = tb_public.ID
AND tb_moda_FEAT_CAT1_3M.SAFRA_REF = tb_public.SAFRA_REF
LEFT JOIN tb_moda_FEAT_CAT2_3M
ON tb_moda_FEAT_CAT2_3M.ID = tb_public.ID
AND tb_moda_FEAT_CAT2_3M.SAFRA_REF = tb_public.SAFRA_REF
LEFT JOIN tb_moda_FEAT_CAT1_6M
ON tb_moda_FEAT_CAT1_6M.ID = tb_public.ID
AND tb_moda_FEAT_CAT1_6M.SAFRA_REF = tb_public.SAFRA_REF
LEFT JOIN tb_moda_FEAT_CAT2_6M
ON tb_moda_FEAT_CAT2_6M.ID = tb_public.ID
AND tb_moda_FEAT_CAT2_6M.SAFRA_REF = tb_public.SAFRA_REF
Criação de variáveis agragadas
tb_publico = 'tb_spine'
lista_janela = [3, 6]
lista_feat_num = ['FEAT_NUM1', 'FEAT_NUM2']
feat_cat = 'FEAT_CAT1'
lista_valor_agregador = ['A', 'B']
id = 'ID'
safra_ref = 'SAFRA_REF'
tb_feat = 'tb_feat'
safra = 'SAFRA'
query = sqlite_create_query_agregada(tb_publico, tb_feat, lista_janela, lista_feat_num, id, safra_ref, safra, feat_cat, lista_valor_agregador)
print(query)
WITH
tb_public as(
SELECT
ID,
SAFRA_REF
FROM tb_spine
),
tb_agrupada_FEAT_CAT1_A_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_A_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_A_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_A_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_A_3M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_A_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_A_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_A_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_A_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+3 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'A'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT1_A_6M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_A_6M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_A_6M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_A_6M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_A_6M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_A_6M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_A_6M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_A_6M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_A_6M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+6 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'A'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT1_B_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_B_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_B_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_B_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_B_3M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_B_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_B_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_B_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_B_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+3 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT1_B_6M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_B_6M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_B_6M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_B_6M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_B_6M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_B_6M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_B_6M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_B_6M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_B_6M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+6 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_join AS (
SELECT
*
FROM tb_public
LEFT JOIN tb_agrupada_FEAT_CAT1_A_3M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_A_3M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_A_3M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_A_6M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_A_6M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_A_6M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_B_3M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_B_3M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_B_3M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_B_6M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_B_6M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_B_6M.SAFRA_REF
)
SELECT
tb_join.ID,
tb_join.SAFRA_REF,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_B_6M
FROM tb_join
df_sqlite_agregada = pd.read_sql(query, engine)
df_sqlite_agregada.head()
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA_REF | SUM_FEAT_NUM1_FEAT_CAT1_A_3M | MAX_FEAT_NUM1_FEAT_CAT1_A_3M | MIN_FEAT_NUM1_FEAT_CAT1_A_3M | AVG_FEAT_NUM1_FEAT_CAT1_A_3M | SUM_FEAT_NUM2_FEAT_CAT1_A_3M | MAX_FEAT_NUM2_FEAT_CAT1_A_3M | MIN_FEAT_NUM2_FEAT_CAT1_A_3M | AVG_FEAT_NUM2_FEAT_CAT1_A_3M | SUM_FEAT_NUM1_FEAT_CAT1_A_6M | MAX_FEAT_NUM1_FEAT_CAT1_A_6M | MIN_FEAT_NUM1_FEAT_CAT1_A_6M | AVG_FEAT_NUM1_FEAT_CAT1_A_6M | SUM_FEAT_NUM2_FEAT_CAT1_A_6M | MAX_FEAT_NUM2_FEAT_CAT1_A_6M | MIN_FEAT_NUM2_FEAT_CAT1_A_6M | AVG_FEAT_NUM2_FEAT_CAT1_A_6M | SUM_FEAT_NUM1_FEAT_CAT1_B_3M | MAX_FEAT_NUM1_FEAT_CAT1_B_3M | MIN_FEAT_NUM1_FEAT_CAT1_B_3M | AVG_FEAT_NUM1_FEAT_CAT1_B_3M | SUM_FEAT_NUM2_FEAT_CAT1_B_3M | MAX_FEAT_NUM2_FEAT_CAT1_B_3M | MIN_FEAT_NUM2_FEAT_CAT1_B_3M | AVG_FEAT_NUM2_FEAT_CAT1_B_3M | SUM_FEAT_NUM1_FEAT_CAT1_B_6M | MAX_FEAT_NUM1_FEAT_CAT1_B_6M | MIN_FEAT_NUM1_FEAT_CAT1_B_6M | AVG_FEAT_NUM1_FEAT_CAT1_B_6M | SUM_FEAT_NUM2_FEAT_CAT1_B_6M | MAX_FEAT_NUM2_FEAT_CAT1_B_6M | MIN_FEAT_NUM2_FEAT_CAT1_B_6M | AVG_FEAT_NUM2_FEAT_CAT1_B_6M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 4 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | -40.0 | -40.0 | -40.0 | -40.0 | 62.0 | 62.0 | 62.0 | 62.0 | -40.0 | -40.0 | -40.0 | -40.0 | 62.0 | 62.0 | 62.0 | 62.0 |
| 1 | 5 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 41.0 | 41.0 | 41.0 | 41.0 | 62.0 | 62.0 | 62.0 | 62.0 | 41.0 | 41.0 | 41.0 | 41.0 | 62.0 | 62.0 | 62.0 | 62.0 |
| 2 | 6 | 2023-02-01 | 36.0 | 36.0 | 36.0 | 36.0 | 63.0 | 63.0 | 63.0 | 63.0 | 36.0 | 36.0 | 36.0 | 36.0 | 63.0 | 63.0 | 63.0 | 63.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 3 | 7 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 4 | 10 | 2023-02-01 | 29.0 | 29.0 | 29.0 | 29.0 | -7.0 | -7.0 | -7.0 | -7.0 | 29.0 | 29.0 | 29.0 | 29.0 | -7.0 | -7.0 | -7.0 | -7.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
tb_publico = 'tb_spine'
lista_janela = [3, 6]
lista_feat_num = ['FEAT_NUM1', 'FEAT_NUM2']
feat_cat = 'FEAT_CAT1'
lista_valor_agragador = ['A', 'B']
id = 'ID'
safra_ref = 'SAFRA_REF'
tb_feat = 'tb_feat'
safra = 'SAFRA'
query = sqlite_create_query_agregada(tb_publico, tb_feat, lista_janela, lista_feat_num, id, safra_ref, safra, feat_cat, lista_valor_agragador)
print(query)
WITH
tb_public as(
SELECT
ID,
SAFRA_REF
FROM tb_spine
),
tb_agrupada_FEAT_CAT1_A_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_A_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_A_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_A_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_A_3M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_A_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_A_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_A_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_A_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+3 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'A'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT1_A_6M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_A_6M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_A_6M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_A_6M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_A_6M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_A_6M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_A_6M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_A_6M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_A_6M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+6 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'A'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT1_B_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_B_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_B_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_B_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_B_3M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_B_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_B_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_B_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_B_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+3 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT1_B_6M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_B_6M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_B_6M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_B_6M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_B_6M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_B_6M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_B_6M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_B_6M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_B_6M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (strftime('%Y-%m-%d', date(tb_feat.SAFRA, '+6 months')) >= tb_public.SAFRA_REF)
AND (tb_feat.SAFRA < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_join AS (
SELECT
*
FROM tb_public
LEFT JOIN tb_agrupada_FEAT_CAT1_A_3M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_A_3M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_A_3M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_A_6M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_A_6M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_A_6M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_B_3M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_B_3M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_B_3M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_B_6M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_B_6M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_B_6M.SAFRA_REF
)
SELECT
tb_join.ID,
tb_join.SAFRA_REF,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_B_6M
FROM tb_join
df_sqlite_agregada = pd.read_sql(query, engine)
df_sqlite_agregada.head()
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA_REF | SUM_FEAT_NUM1_FEAT_CAT1_A_3M | MAX_FEAT_NUM1_FEAT_CAT1_A_3M | MIN_FEAT_NUM1_FEAT_CAT1_A_3M | AVG_FEAT_NUM1_FEAT_CAT1_A_3M | SUM_FEAT_NUM2_FEAT_CAT1_A_3M | MAX_FEAT_NUM2_FEAT_CAT1_A_3M | MIN_FEAT_NUM2_FEAT_CAT1_A_3M | AVG_FEAT_NUM2_FEAT_CAT1_A_3M | SUM_FEAT_NUM1_FEAT_CAT1_A_6M | MAX_FEAT_NUM1_FEAT_CAT1_A_6M | MIN_FEAT_NUM1_FEAT_CAT1_A_6M | AVG_FEAT_NUM1_FEAT_CAT1_A_6M | SUM_FEAT_NUM2_FEAT_CAT1_A_6M | MAX_FEAT_NUM2_FEAT_CAT1_A_6M | MIN_FEAT_NUM2_FEAT_CAT1_A_6M | AVG_FEAT_NUM2_FEAT_CAT1_A_6M | SUM_FEAT_NUM1_FEAT_CAT1_B_3M | MAX_FEAT_NUM1_FEAT_CAT1_B_3M | MIN_FEAT_NUM1_FEAT_CAT1_B_3M | AVG_FEAT_NUM1_FEAT_CAT1_B_3M | SUM_FEAT_NUM2_FEAT_CAT1_B_3M | MAX_FEAT_NUM2_FEAT_CAT1_B_3M | MIN_FEAT_NUM2_FEAT_CAT1_B_3M | AVG_FEAT_NUM2_FEAT_CAT1_B_3M | SUM_FEAT_NUM1_FEAT_CAT1_B_6M | MAX_FEAT_NUM1_FEAT_CAT1_B_6M | MIN_FEAT_NUM1_FEAT_CAT1_B_6M | AVG_FEAT_NUM1_FEAT_CAT1_B_6M | SUM_FEAT_NUM2_FEAT_CAT1_B_6M | MAX_FEAT_NUM2_FEAT_CAT1_B_6M | MIN_FEAT_NUM2_FEAT_CAT1_B_6M | AVG_FEAT_NUM2_FEAT_CAT1_B_6M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 4 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | -40.0 | -40.0 | -40.0 | -40.0 | 62.0 | 62.0 | 62.0 | 62.0 | -40.0 | -40.0 | -40.0 | -40.0 | 62.0 | 62.0 | 62.0 | 62.0 |
| 1 | 5 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 41.0 | 41.0 | 41.0 | 41.0 | 62.0 | 62.0 | 62.0 | 62.0 | 41.0 | 41.0 | 41.0 | 41.0 | 62.0 | 62.0 | 62.0 | 62.0 |
| 2 | 6 | 2023-02-01 | 36.0 | 36.0 | 36.0 | 36.0 | 63.0 | 63.0 | 63.0 | 63.0 | 36.0 | 36.0 | 36.0 | 36.0 | 63.0 | 63.0 | 63.0 | 63.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 3 | 7 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 4 | 10 | 2023-02-01 | 29.0 | 29.0 | 29.0 | 29.0 | -7.0 | -7.0 | -7.0 | -7.0 | 29.0 | 29.0 | 29.0 | 29.0 | -7.0 | -7.0 | -7.0 | -7.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
2. MySQL
Configurando conexão
host = "localhost"
user = "sqluser"
password = "password"
database = "mydatabase"
# Conectar ao MySQL
connection = mysql.connector.connect(
host=host,
user=user,
password=password,
database=database
)
Visão inicial do público
df_spine = pd.read_sql("SELECT * FROM tb_spine", connection)
df_spine.head()
/tmp/ipykernel_4407/661881290.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
df_spine = pd.read_sql("SELECT * FROM tb_spine", connection)
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA_REF | Target | |
|---|---|---|---|
| 0 | 4 | 2023-02-01 | 1 |
| 1 | 5 | 2023-02-01 | 0 |
| 2 | 6 | 2023-02-01 | 0 |
| 3 | 7 | 2023-02-01 | 0 |
| 4 | 10 | 2023-02-01 | 0 |
Visão inicial da tabela de variáveis
df_data = pd.read_sql("SELECT * FROM tb_feat", connection)
df_data.head()
/tmp/ipykernel_4407/1780842963.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
df_data = pd.read_sql("SELECT * FROM tb_feat", connection)
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA | FEAT_NUM1 | FEAT_NUM2 | FEAT_CAT1 | FEAT_CAT2 | |
|---|---|---|---|---|---|---|
| 0 | 1 | 2023-01-01 | 73 | 23 | B | B |
| 1 | 3 | 2023-01-01 | 15 | 1 | B | B |
| 2 | 5 | 2023-01-01 | 75 | 71 | A | A |
| 3 | 7 | 2023-01-01 | 73 | 82 | B | C |
| 4 | 9 | 2023-01-01 | 61 | 8 | C | B |
Criação de variáveis numéricas
A função mysql_create_query_num() cria um texto com a query para a criação de variáveis com as operações soma, mínimo, máximo e média das variáveis listadas em feat_num_lista e com a janela de tempo listada em lista_janela.
tb_publico = 'tb_spine'
tb_feat = 'tb_feat'
id = 'ID'
safra_ref = 'SAFRA_REF'
safra = 'SAFRA'
feat_num_lista = ['FEAT_NUM1','FEAT_NUM2']
lista_janela = [1,2,3]
query_final_num_mysql = mysql_create_query_num(tb_publico, tb_feat, lista_janela,feat_num_lista, id, safra_ref, safra)
df_num_mysql = pd.read_sql(query_final_num_mysql, connection)
df_num_mysql.head()
/tmp/ipykernel_4407/2119439562.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
df_num_mysql = pd.read_sql(query_final_num_mysql, connection)
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA_REF | FEAT_NUM1_SUM_1M | FEAT_NUM1_MIN_1M | FEAT_NUM1_MAX_1M | FEAT_NUM1_AVG_1M | FEAT_NUM2_SUM_1M | FEAT_NUM2_MIN_1M | FEAT_NUM2_MAX_1M | FEAT_NUM2_AVG_1M | FEAT_NUM1_SUM_2M | FEAT_NUM1_MIN_2M | FEAT_NUM1_MAX_2M | FEAT_NUM1_AVG_2M | FEAT_NUM2_SUM_2M | FEAT_NUM2_MIN_2M | FEAT_NUM2_MAX_2M | FEAT_NUM2_AVG_2M | FEAT_NUM1_SUM_3M | FEAT_NUM1_MIN_3M | FEAT_NUM1_MAX_3M | FEAT_NUM1_AVG_3M | FEAT_NUM2_SUM_3M | FEAT_NUM2_MIN_3M | FEAT_NUM2_MAX_3M | FEAT_NUM2_AVG_3M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 4 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 1 | 5 | 2023-02-01 | 75.0 | 75.0 | 75.0 | 75.0 | 71.0 | 71.0 | 71.0 | 71.0 | 75.0 | 75.0 | 75.0 | 75.0 | 71.0 | 71.0 | 71.0 | 71.0 | 75.0 | 75.0 | 75.0 | 75.0 | 71.0 | 71.0 | 71.0 | 71.0 |
| 2 | 6 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 3 | 7 | 2023-02-01 | 73.0 | 73.0 | 73.0 | 73.0 | 82.0 | 82.0 | 82.0 | 82.0 | 73.0 | 73.0 | 73.0 | 73.0 | 82.0 | 82.0 | 82.0 | 82.0 | 73.0 | 73.0 | 73.0 | 73.0 | 82.0 | 82.0 | 82.0 | 82.0 |
| 4 | 10 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
print(query_final_num_mysql)
WITH
tb_public AS (
SELECT
*
FROM tb_spine
),
-- Criação de variáveis de janela de 1M
tb_janela_1M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM1 para a janela 1
SUM(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_SUM_1M,
MIN(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MIN_1M,
MAX(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MAX_1M,
AVG(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_AVG_1M,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM2 para a janela 1
SUM(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_SUM_1M,
MIN(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MIN_1M,
MAX(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MAX_1M,
AVG(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_AVG_1M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 1 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis de janela de 2M
tb_janela_2M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM1 para a janela 2
SUM(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_SUM_2M,
MIN(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MIN_2M,
MAX(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MAX_2M,
AVG(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_AVG_2M,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM2 para a janela 2
SUM(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_SUM_2M,
MIN(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MIN_2M,
MAX(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MAX_2M,
AVG(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_AVG_2M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 2 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis de janela de 3M
tb_janela_3M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM1 para a janela 3
SUM(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_SUM_3M,
MIN(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MIN_3M,
MAX(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MAX_3M,
AVG(IFNULL(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_AVG_3M,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM2 para a janela 3
SUM(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_SUM_3M,
MIN(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MIN_3M,
MAX(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MAX_3M,
AVG(IFNULL(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_AVG_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 3 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_join AS (
SELECT
tb_public.*,
tb_janela_1M.FEAT_NUM1_SUM_1M,
tb_janela_1M.FEAT_NUM1_MIN_1M,
tb_janela_1M.FEAT_NUM1_MAX_1M,
tb_janela_1M.FEAT_NUM1_AVG_1M,
tb_janela_1M.FEAT_NUM2_SUM_1M,
tb_janela_1M.FEAT_NUM2_MIN_1M,
tb_janela_1M.FEAT_NUM2_MAX_1M,
tb_janela_1M.FEAT_NUM2_AVG_1M,
tb_janela_2M.FEAT_NUM1_SUM_2M,
tb_janela_2M.FEAT_NUM1_MIN_2M,
tb_janela_2M.FEAT_NUM1_MAX_2M,
tb_janela_2M.FEAT_NUM1_AVG_2M,
tb_janela_2M.FEAT_NUM2_SUM_2M,
tb_janela_2M.FEAT_NUM2_MIN_2M,
tb_janela_2M.FEAT_NUM2_MAX_2M,
tb_janela_2M.FEAT_NUM2_AVG_2M,
tb_janela_3M.FEAT_NUM1_SUM_3M,
tb_janela_3M.FEAT_NUM1_MIN_3M,
tb_janela_3M.FEAT_NUM1_MAX_3M,
tb_janela_3M.FEAT_NUM1_AVG_3M,
tb_janela_3M.FEAT_NUM2_SUM_3M,
tb_janela_3M.FEAT_NUM2_MIN_3M,
tb_janela_3M.FEAT_NUM2_MAX_3M,
tb_janela_3M.FEAT_NUM2_AVG_3M
FROM tb_public
LEFT JOIN tb_janela_1M
ON tb_public.ID = tb_janela_1M.ID
AND tb_public.SAFRA_REF = tb_janela_1M.SAFRA_REF
LEFT JOIN tb_janela_2M
ON tb_public.ID = tb_janela_2M.ID
AND tb_public.SAFRA_REF = tb_janela_2M.SAFRA_REF
LEFT JOIN tb_janela_3M
ON tb_public.ID = tb_janela_3M.ID
AND tb_public.SAFRA_REF = tb_janela_3M.SAFRA_REF
)
SELECT
tb_join.ID,
tb_join.SAFRA_REF,
tb_join.FEAT_NUM1_SUM_1M,
tb_join.FEAT_NUM1_MIN_1M,
tb_join.FEAT_NUM1_MAX_1M,
tb_join.FEAT_NUM1_AVG_1M,
tb_join.FEAT_NUM2_SUM_1M,
tb_join.FEAT_NUM2_MIN_1M,
tb_join.FEAT_NUM2_MAX_1M,
tb_join.FEAT_NUM2_AVG_1M,
tb_join.FEAT_NUM1_SUM_2M,
tb_join.FEAT_NUM1_MIN_2M,
tb_join.FEAT_NUM1_MAX_2M,
tb_join.FEAT_NUM1_AVG_2M,
tb_join.FEAT_NUM2_SUM_2M,
tb_join.FEAT_NUM2_MIN_2M,
tb_join.FEAT_NUM2_MAX_2M,
tb_join.FEAT_NUM2_AVG_2M,
tb_join.FEAT_NUM1_SUM_3M,
tb_join.FEAT_NUM1_MIN_3M,
tb_join.FEAT_NUM1_MAX_3M,
tb_join.FEAT_NUM1_AVG_3M,
tb_join.FEAT_NUM2_SUM_3M,
tb_join.FEAT_NUM2_MIN_3M,
tb_join.FEAT_NUM2_MAX_3M,
tb_join.FEAT_NUM2_AVG_3M
FROM tb_join
Criação de variáveis categóricas
A função mysql_create_query_cat() cria um texto com a query para a criação de variáveis com a moda de cada uma das variáveis listadas em feat_num_lista na janela de tempo fornecida em lista_janela.
tb_publico = 'tb_spine'
tb_feat = 'tb_feat'
id = 'ID'
safra_ref = 'SAFRA_REF'
safra = 'SAFRA'
feat_num_lista = ['FEAT_CAT1','FEAT_CAT2']
lista_janela = [1,2,3]
query_final_cat_mysql = mysql_create_query_cat(tb_publico, tb_feat, lista_janela, feat_num_lista, id, safra_ref, safra)
df_cat_sqlite_mysql = pd.read_sql(query_final_cat_mysql, connection)
df_cat_sqlite_mysql.head()
/tmp/ipykernel_4407/3114069227.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
df_cat_sqlite_mysql = pd.read_sql(query_final_cat_mysql, connection)
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA_REF | FEAT_CAT1_MODA_1M | FEAT_CAT2_MODA_1M | FEAT_CAT1_MODA_2M | FEAT_CAT2_MODA_2M | FEAT_CAT1_MODA_3M | FEAT_CAT2_MODA_3M | |
|---|---|---|---|---|---|---|---|---|
| 0 | 4 | 2023-02-01 | None | None | None | None | None | None |
| 1 | 5 | 2023-02-01 | A | A | A | A | A | A |
| 2 | 6 | 2023-02-01 | None | None | None | None | None | None |
| 3 | 7 | 2023-02-01 | B | C | B | C | B | C |
| 4 | 10 | 2023-02-01 | None | None | None | None | None | None |
print(query_final_cat_mysql)
WITH
tb_public AS (
SELECT
ID,
SAFRA_REF
FROM tb_spine
),
tb_janela_FEAT_CAT1_1M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT1,
COUNT(*) AS frequency_FEAT_CAT1
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 1 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT1
),
tb_row_FEAT_CAT1_1M AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT1 DESC
) AS row_num_FEAT_CAT1_1M
FROM tb_janela_FEAT_CAT1_1M
),
tb_moda_FEAT_CAT1_1M AS (
SELECT
tb_row_FEAT_CAT1_1M.ID,
tb_row_FEAT_CAT1_1M.SAFRA_REF,
tb_row_FEAT_CAT1_1M.FEAT_CAT1 AS FEAT_CAT1_MODA_1M
FROM tb_row_FEAT_CAT1_1M
WHERE row_num_FEAT_CAT1_1M = 1
),
tb_janela_FEAT_CAT2_1M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT2,
COUNT(*) AS frequency_FEAT_CAT2
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 1 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT2
),
tb_row_FEAT_CAT2_1M AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT2 DESC
) AS row_num_FEAT_CAT2_1M
FROM tb_janela_FEAT_CAT2_1M
),
tb_moda_FEAT_CAT2_1M AS (
SELECT
tb_row_FEAT_CAT2_1M.ID,
tb_row_FEAT_CAT2_1M.SAFRA_REF,
tb_row_FEAT_CAT2_1M.FEAT_CAT2 AS FEAT_CAT2_MODA_1M
FROM tb_row_FEAT_CAT2_1M
WHERE row_num_FEAT_CAT2_1M = 1
),
tb_janela_FEAT_CAT1_2M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT1,
COUNT(*) AS frequency_FEAT_CAT1
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 2 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT1
),
tb_row_FEAT_CAT1_2M AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT1 DESC
) AS row_num_FEAT_CAT1_2M
FROM tb_janela_FEAT_CAT1_2M
),
tb_moda_FEAT_CAT1_2M AS (
SELECT
tb_row_FEAT_CAT1_2M.ID,
tb_row_FEAT_CAT1_2M.SAFRA_REF,
tb_row_FEAT_CAT1_2M.FEAT_CAT1 AS FEAT_CAT1_MODA_2M
FROM tb_row_FEAT_CAT1_2M
WHERE row_num_FEAT_CAT1_2M = 1
),
tb_janela_FEAT_CAT2_2M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT2,
COUNT(*) AS frequency_FEAT_CAT2
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 2 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT2
),
tb_row_FEAT_CAT2_2M AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT2 DESC
) AS row_num_FEAT_CAT2_2M
FROM tb_janela_FEAT_CAT2_2M
),
tb_moda_FEAT_CAT2_2M AS (
SELECT
tb_row_FEAT_CAT2_2M.ID,
tb_row_FEAT_CAT2_2M.SAFRA_REF,
tb_row_FEAT_CAT2_2M.FEAT_CAT2 AS FEAT_CAT2_MODA_2M
FROM tb_row_FEAT_CAT2_2M
WHERE row_num_FEAT_CAT2_2M = 1
),
tb_janela_FEAT_CAT1_3M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT1,
COUNT(*) AS frequency_FEAT_CAT1
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 3 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT1
),
tb_row_FEAT_CAT1_3M AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT1 DESC
) AS row_num_FEAT_CAT1_3M
FROM tb_janela_FEAT_CAT1_3M
),
tb_moda_FEAT_CAT1_3M AS (
SELECT
tb_row_FEAT_CAT1_3M.ID,
tb_row_FEAT_CAT1_3M.SAFRA_REF,
tb_row_FEAT_CAT1_3M.FEAT_CAT1 AS FEAT_CAT1_MODA_3M
FROM tb_row_FEAT_CAT1_3M
WHERE row_num_FEAT_CAT1_3M = 1
),
tb_janela_FEAT_CAT2_3M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_feat.FEAT_CAT2,
COUNT(*) AS frequency_FEAT_CAT2
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 3 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
GROUP BY tb_public.ID, tb_public.SAFRA_REF, tb_feat.FEAT_CAT2
),
tb_row_FEAT_CAT2_3M AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
ID,
SAFRA_REF
ORDER BY frequency_FEAT_CAT2 DESC
) AS row_num_FEAT_CAT2_3M
FROM tb_janela_FEAT_CAT2_3M
),
tb_moda_FEAT_CAT2_3M AS (
SELECT
tb_row_FEAT_CAT2_3M.ID,
tb_row_FEAT_CAT2_3M.SAFRA_REF,
tb_row_FEAT_CAT2_3M.FEAT_CAT2 AS FEAT_CAT2_MODA_3M
FROM tb_row_FEAT_CAT2_3M
WHERE row_num_FEAT_CAT2_3M = 1
)
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_moda_FEAT_CAT1_1M.FEAT_CAT1_MODA_1M,
tb_moda_FEAT_CAT2_1M.FEAT_CAT2_MODA_1M,
tb_moda_FEAT_CAT1_2M.FEAT_CAT1_MODA_2M,
tb_moda_FEAT_CAT2_2M.FEAT_CAT2_MODA_2M,
tb_moda_FEAT_CAT1_3M.FEAT_CAT1_MODA_3M,
tb_moda_FEAT_CAT2_3M.FEAT_CAT2_MODA_3M
FROM tb_public
LEFT JOIN tb_moda_FEAT_CAT1_1M
ON tb_moda_FEAT_CAT1_1M.ID = tb_public.ID
AND tb_moda_FEAT_CAT1_1M.SAFRA_REF = tb_public.SAFRA_REF
LEFT JOIN tb_moda_FEAT_CAT2_1M
ON tb_moda_FEAT_CAT2_1M.ID = tb_public.ID
AND tb_moda_FEAT_CAT2_1M.SAFRA_REF = tb_public.SAFRA_REF
LEFT JOIN tb_moda_FEAT_CAT1_2M
ON tb_moda_FEAT_CAT1_2M.ID = tb_public.ID
AND tb_moda_FEAT_CAT1_2M.SAFRA_REF = tb_public.SAFRA_REF
LEFT JOIN tb_moda_FEAT_CAT2_2M
ON tb_moda_FEAT_CAT2_2M.ID = tb_public.ID
AND tb_moda_FEAT_CAT2_2M.SAFRA_REF = tb_public.SAFRA_REF
LEFT JOIN tb_moda_FEAT_CAT1_3M
ON tb_moda_FEAT_CAT1_3M.ID = tb_public.ID
AND tb_moda_FEAT_CAT1_3M.SAFRA_REF = tb_public.SAFRA_REF
LEFT JOIN tb_moda_FEAT_CAT2_3M
ON tb_moda_FEAT_CAT2_3M.ID = tb_public.ID
AND tb_moda_FEAT_CAT2_3M.SAFRA_REF = tb_public.SAFRA_REF
Criação de variáveis agragadas
tb_publico = 'tb_spine'
lista_janela = [3, 6]
lista_feat_num = ['FEAT_NUM1', 'FEAT_NUM2']
feat_cat = 'FEAT_CAT1'
lista_valor_agragador = ['A', 'B']
id = 'ID'
safra_ref = 'SAFRA_REF'
tb_feat = 'tb_feat'
safra = 'SAFRA'
query = mysql_create_query_agregada(tb_publico, tb_feat, lista_janela, lista_feat_num, id, safra_ref, safra, feat_cat, lista_valor_agragador)
print(query)
WITH
tb_public as(
SELECT
ID,
SAFRA_REF
FROM tb_spine
),
tb_agrupada_FEAT_CAT1_A_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_A_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_A_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_A_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_A_3M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_A_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_A_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_A_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_A_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 3 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
AND tb_feat.FEAT_CAT1 = 'A'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT1_A_6M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_A_6M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_A_6M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_A_6M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_A_6M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_A_6M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_A_6M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_A_6M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_A_6M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 6 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
AND tb_feat.FEAT_CAT1 = 'A'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT1_B_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_B_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_B_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_B_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_B_3M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_B_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_B_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_B_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_B_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 3 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
AND tb_feat.FEAT_CAT1 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT1_B_6M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_B_6M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_B_6M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_B_6M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_B_6M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_B_6M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_B_6M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_B_6M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_B_6M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 6 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
AND tb_feat.FEAT_CAT1 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_join AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_agrupada_FEAT_CAT1_A_3M.SUM_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_agrupada_FEAT_CAT1_A_3M.MAX_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_agrupada_FEAT_CAT1_A_3M.MIN_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_agrupada_FEAT_CAT1_A_3M.AVG_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_agrupada_FEAT_CAT1_A_3M.SUM_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_agrupada_FEAT_CAT1_A_3M.MAX_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_agrupada_FEAT_CAT1_A_3M.MIN_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_agrupada_FEAT_CAT1_A_3M.AVG_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_agrupada_FEAT_CAT1_A_6M.SUM_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_agrupada_FEAT_CAT1_A_6M.MAX_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_agrupada_FEAT_CAT1_A_6M.MIN_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_agrupada_FEAT_CAT1_A_6M.AVG_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_agrupada_FEAT_CAT1_A_6M.SUM_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_agrupada_FEAT_CAT1_A_6M.MAX_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_agrupada_FEAT_CAT1_A_6M.MIN_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_agrupada_FEAT_CAT1_A_6M.AVG_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_agrupada_FEAT_CAT1_B_3M.SUM_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.MAX_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.MIN_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.AVG_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.SUM_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.MAX_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.MIN_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.AVG_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_6M.SUM_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_agrupada_FEAT_CAT1_B_6M.MAX_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_agrupada_FEAT_CAT1_B_6M.MIN_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_agrupada_FEAT_CAT1_B_6M.AVG_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_agrupada_FEAT_CAT1_B_6M.SUM_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_agrupada_FEAT_CAT1_B_6M.MAX_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_agrupada_FEAT_CAT1_B_6M.MIN_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_agrupada_FEAT_CAT1_B_6M.AVG_FEAT_NUM2_FEAT_CAT1_B_6M
FROM tb_public
LEFT JOIN tb_agrupada_FEAT_CAT1_A_3M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_A_3M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_A_3M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_A_6M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_A_6M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_A_6M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_B_3M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_B_3M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_B_3M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_B_6M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_B_6M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_B_6M.SAFRA_REF
)
SELECT
tb_join.ID,
tb_join.SAFRA_REF,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_A_3M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_A_3M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_A_6M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_A_6M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT1_B_6M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT1_B_6M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT1_B_6M
FROM tb_join
df_mysql_agregada = pd.read_sql(query, connection)
df_mysql_agregada.head()
/tmp/ipykernel_4407/724425866.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
df_mysql_agregada = pd.read_sql(query, connection)
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA_REF | SUM_FEAT_NUM1_FEAT_CAT1_A_3M | MAX_FEAT_NUM1_FEAT_CAT1_A_3M | MIN_FEAT_NUM1_FEAT_CAT1_A_3M | AVG_FEAT_NUM1_FEAT_CAT1_A_3M | SUM_FEAT_NUM2_FEAT_CAT1_A_3M | MAX_FEAT_NUM2_FEAT_CAT1_A_3M | MIN_FEAT_NUM2_FEAT_CAT1_A_3M | AVG_FEAT_NUM2_FEAT_CAT1_A_3M | SUM_FEAT_NUM1_FEAT_CAT1_A_6M | MAX_FEAT_NUM1_FEAT_CAT1_A_6M | MIN_FEAT_NUM1_FEAT_CAT1_A_6M | AVG_FEAT_NUM1_FEAT_CAT1_A_6M | SUM_FEAT_NUM2_FEAT_CAT1_A_6M | MAX_FEAT_NUM2_FEAT_CAT1_A_6M | MIN_FEAT_NUM2_FEAT_CAT1_A_6M | AVG_FEAT_NUM2_FEAT_CAT1_A_6M | SUM_FEAT_NUM1_FEAT_CAT1_B_3M | MAX_FEAT_NUM1_FEAT_CAT1_B_3M | MIN_FEAT_NUM1_FEAT_CAT1_B_3M | AVG_FEAT_NUM1_FEAT_CAT1_B_3M | SUM_FEAT_NUM2_FEAT_CAT1_B_3M | MAX_FEAT_NUM2_FEAT_CAT1_B_3M | MIN_FEAT_NUM2_FEAT_CAT1_B_3M | AVG_FEAT_NUM2_FEAT_CAT1_B_3M | SUM_FEAT_NUM1_FEAT_CAT1_B_6M | MAX_FEAT_NUM1_FEAT_CAT1_B_6M | MIN_FEAT_NUM1_FEAT_CAT1_B_6M | AVG_FEAT_NUM1_FEAT_CAT1_B_6M | SUM_FEAT_NUM2_FEAT_CAT1_B_6M | MAX_FEAT_NUM2_FEAT_CAT1_B_6M | MIN_FEAT_NUM2_FEAT_CAT1_B_6M | AVG_FEAT_NUM2_FEAT_CAT1_B_6M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 4 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 1 | 5 | 2023-02-01 | 75.0 | 75.0 | 75.0 | 75.0 | 71.0 | 71.0 | 71.0 | 71.0 | 75.0 | 75.0 | 75.0 | 75.0 | 71.0 | 71.0 | 71.0 | 71.0 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2 | 6 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 3 | 7 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 73.0 | 73.0 | 73.0 | 73.0 | 82.0 | 82.0 | 82.0 | 82.0 | 73.0 | 73.0 | 73.0 | 73.0 | 82.0 | 82.0 | 82.0 | 82.0 |
| 4 | 10 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
tb_publico = 'tb_spine'
lista_janela = [3, 6]
lista_feat_num = ['FEAT_NUM1', 'FEAT_NUM2']
feat_cat = 'FEAT_CAT2'
lista_valor_agragador = ['B', 'C']
id = 'ID'
safra_ref = 'SAFRA_REF'
tb_feat = 'tb_feat'
safra = 'SAFRA'
query = mysql_create_query_agregada(tb_publico, tb_feat, lista_janela, lista_feat_num, id, safra_ref, safra, feat_cat, lista_valor_agragador)
print(query)
WITH
tb_public as(
SELECT
ID,
SAFRA_REF
FROM tb_spine
),
tb_agrupada_FEAT_CAT2_B_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT2_B_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT2_B_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT2_B_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT2_B_3M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT2_B_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT2_B_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT2_B_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT2_B_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 3 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
AND tb_feat.FEAT_CAT2 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT2_B_6M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT2_B_6M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT2_B_6M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT2_B_6M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT2_B_6M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT2_B_6M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT2_B_6M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT2_B_6M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT2_B_6M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 6 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
AND tb_feat.FEAT_CAT2 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT2_C_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT2_C_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT2_C_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT2_C_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT2_C_3M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT2_C_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT2_C_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT2_C_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT2_C_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 3 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
AND tb_feat.FEAT_CAT2 = 'C'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_agrupada_FEAT_CAT2_C_6M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT2_C_6M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT2_C_6M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT2_C_6M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT2_C_6M,
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT2_C_6M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT2_C_6M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT2_C_6M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT2_C_6M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND DATE_ADD(tb_feat.SAFRA, INTERVAL 6 MONTH) >= tb_public.SAFRA_REF
AND tb_feat.SAFRA < tb_public.SAFRA_REF
AND tb_feat.FEAT_CAT2 = 'C'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_join AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_agrupada_FEAT_CAT2_B_3M.SUM_FEAT_NUM1_FEAT_CAT2_B_3M,
tb_agrupada_FEAT_CAT2_B_3M.MAX_FEAT_NUM1_FEAT_CAT2_B_3M,
tb_agrupada_FEAT_CAT2_B_3M.MIN_FEAT_NUM1_FEAT_CAT2_B_3M,
tb_agrupada_FEAT_CAT2_B_3M.AVG_FEAT_NUM1_FEAT_CAT2_B_3M,
tb_agrupada_FEAT_CAT2_B_3M.SUM_FEAT_NUM2_FEAT_CAT2_B_3M,
tb_agrupada_FEAT_CAT2_B_3M.MAX_FEAT_NUM2_FEAT_CAT2_B_3M,
tb_agrupada_FEAT_CAT2_B_3M.MIN_FEAT_NUM2_FEAT_CAT2_B_3M,
tb_agrupada_FEAT_CAT2_B_3M.AVG_FEAT_NUM2_FEAT_CAT2_B_3M,
tb_agrupada_FEAT_CAT2_B_6M.SUM_FEAT_NUM1_FEAT_CAT2_B_6M,
tb_agrupada_FEAT_CAT2_B_6M.MAX_FEAT_NUM1_FEAT_CAT2_B_6M,
tb_agrupada_FEAT_CAT2_B_6M.MIN_FEAT_NUM1_FEAT_CAT2_B_6M,
tb_agrupada_FEAT_CAT2_B_6M.AVG_FEAT_NUM1_FEAT_CAT2_B_6M,
tb_agrupada_FEAT_CAT2_B_6M.SUM_FEAT_NUM2_FEAT_CAT2_B_6M,
tb_agrupada_FEAT_CAT2_B_6M.MAX_FEAT_NUM2_FEAT_CAT2_B_6M,
tb_agrupada_FEAT_CAT2_B_6M.MIN_FEAT_NUM2_FEAT_CAT2_B_6M,
tb_agrupada_FEAT_CAT2_B_6M.AVG_FEAT_NUM2_FEAT_CAT2_B_6M,
tb_agrupada_FEAT_CAT2_C_3M.SUM_FEAT_NUM1_FEAT_CAT2_C_3M,
tb_agrupada_FEAT_CAT2_C_3M.MAX_FEAT_NUM1_FEAT_CAT2_C_3M,
tb_agrupada_FEAT_CAT2_C_3M.MIN_FEAT_NUM1_FEAT_CAT2_C_3M,
tb_agrupada_FEAT_CAT2_C_3M.AVG_FEAT_NUM1_FEAT_CAT2_C_3M,
tb_agrupada_FEAT_CAT2_C_3M.SUM_FEAT_NUM2_FEAT_CAT2_C_3M,
tb_agrupada_FEAT_CAT2_C_3M.MAX_FEAT_NUM2_FEAT_CAT2_C_3M,
tb_agrupada_FEAT_CAT2_C_3M.MIN_FEAT_NUM2_FEAT_CAT2_C_3M,
tb_agrupada_FEAT_CAT2_C_3M.AVG_FEAT_NUM2_FEAT_CAT2_C_3M,
tb_agrupada_FEAT_CAT2_C_6M.SUM_FEAT_NUM1_FEAT_CAT2_C_6M,
tb_agrupada_FEAT_CAT2_C_6M.MAX_FEAT_NUM1_FEAT_CAT2_C_6M,
tb_agrupada_FEAT_CAT2_C_6M.MIN_FEAT_NUM1_FEAT_CAT2_C_6M,
tb_agrupada_FEAT_CAT2_C_6M.AVG_FEAT_NUM1_FEAT_CAT2_C_6M,
tb_agrupada_FEAT_CAT2_C_6M.SUM_FEAT_NUM2_FEAT_CAT2_C_6M,
tb_agrupada_FEAT_CAT2_C_6M.MAX_FEAT_NUM2_FEAT_CAT2_C_6M,
tb_agrupada_FEAT_CAT2_C_6M.MIN_FEAT_NUM2_FEAT_CAT2_C_6M,
tb_agrupada_FEAT_CAT2_C_6M.AVG_FEAT_NUM2_FEAT_CAT2_C_6M
FROM tb_public
LEFT JOIN tb_agrupada_FEAT_CAT2_B_3M
ON tb_public.ID = tb_agrupada_FEAT_CAT2_B_3M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT2_B_3M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT2_B_6M
ON tb_public.ID = tb_agrupada_FEAT_CAT2_B_6M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT2_B_6M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT2_C_3M
ON tb_public.ID = tb_agrupada_FEAT_CAT2_C_3M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT2_C_3M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT2_C_6M
ON tb_public.ID = tb_agrupada_FEAT_CAT2_C_6M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT2_C_6M.SAFRA_REF
)
SELECT
tb_join.ID,
tb_join.SAFRA_REF,
tb_join.SUM_FEAT_NUM1_FEAT_CAT2_B_3M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT2_B_3M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT2_B_3M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT2_B_3M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT2_B_3M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT2_B_3M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT2_B_3M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT2_B_3M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT2_B_6M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT2_B_6M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT2_B_6M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT2_B_6M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT2_B_6M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT2_B_6M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT2_B_6M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT2_B_6M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT2_C_3M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT2_C_3M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT2_C_3M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT2_C_3M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT2_C_3M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT2_C_3M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT2_C_3M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT2_C_3M,
tb_join.SUM_FEAT_NUM1_FEAT_CAT2_C_6M,
tb_join.MAX_FEAT_NUM1_FEAT_CAT2_C_6M,
tb_join.MIN_FEAT_NUM1_FEAT_CAT2_C_6M,
tb_join.AVG_FEAT_NUM1_FEAT_CAT2_C_6M,
tb_join.SUM_FEAT_NUM2_FEAT_CAT2_C_6M,
tb_join.MAX_FEAT_NUM2_FEAT_CAT2_C_6M,
tb_join.MIN_FEAT_NUM2_FEAT_CAT2_C_6M,
tb_join.AVG_FEAT_NUM2_FEAT_CAT2_C_6M
FROM tb_join
df_mysql_agregada = pd.read_sql(query, connection)
df_mysql_agregada.head()
/tmp/ipykernel_4407/724425866.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
df_mysql_agregada = pd.read_sql(query, connection)
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
</style>
| ID | SAFRA_REF | SUM_FEAT_NUM1_FEAT_CAT2_B_3M | MAX_FEAT_NUM1_FEAT_CAT2_B_3M | MIN_FEAT_NUM1_FEAT_CAT2_B_3M | AVG_FEAT_NUM1_FEAT_CAT2_B_3M | SUM_FEAT_NUM2_FEAT_CAT2_B_3M | MAX_FEAT_NUM2_FEAT_CAT2_B_3M | MIN_FEAT_NUM2_FEAT_CAT2_B_3M | AVG_FEAT_NUM2_FEAT_CAT2_B_3M | SUM_FEAT_NUM1_FEAT_CAT2_B_6M | MAX_FEAT_NUM1_FEAT_CAT2_B_6M | MIN_FEAT_NUM1_FEAT_CAT2_B_6M | AVG_FEAT_NUM1_FEAT_CAT2_B_6M | SUM_FEAT_NUM2_FEAT_CAT2_B_6M | MAX_FEAT_NUM2_FEAT_CAT2_B_6M | MIN_FEAT_NUM2_FEAT_CAT2_B_6M | AVG_FEAT_NUM2_FEAT_CAT2_B_6M | SUM_FEAT_NUM1_FEAT_CAT2_C_3M | MAX_FEAT_NUM1_FEAT_CAT2_C_3M | MIN_FEAT_NUM1_FEAT_CAT2_C_3M | AVG_FEAT_NUM1_FEAT_CAT2_C_3M | SUM_FEAT_NUM2_FEAT_CAT2_C_3M | MAX_FEAT_NUM2_FEAT_CAT2_C_3M | MIN_FEAT_NUM2_FEAT_CAT2_C_3M | AVG_FEAT_NUM2_FEAT_CAT2_C_3M | SUM_FEAT_NUM1_FEAT_CAT2_C_6M | MAX_FEAT_NUM1_FEAT_CAT2_C_6M | MIN_FEAT_NUM1_FEAT_CAT2_C_6M | AVG_FEAT_NUM1_FEAT_CAT2_C_6M | SUM_FEAT_NUM2_FEAT_CAT2_C_6M | MAX_FEAT_NUM2_FEAT_CAT2_C_6M | MIN_FEAT_NUM2_FEAT_CAT2_C_6M | AVG_FEAT_NUM2_FEAT_CAT2_C_6M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 4 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 1 | 5 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2 | 6 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 3 | 7 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 73.0 | 73.0 | 73.0 | 73.0 | 82.0 | 82.0 | 82.0 | 82.0 | 73.0 | 73.0 | 73.0 | 73.0 | 82.0 | 82.0 | 82.0 | 82.0 |
| 4 | 10 | 2023-02-01 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
3. Snowflake
Criação de variáveis numéricas
A função snow_create_query_num() cria um texto com a query para a criação de variáveis com as operações soma, mínimo, máximo e média das variáveis listadas em feat_num_lista e com a janela de tempo listada em lista_janela.
tb_publico = 'tb_spine'
tb_feat = 'tb_feat'
id = 'ID'
safra_ref = 'SAFRA_REF'
safra = 'SAFRA'
feat_num_lista = ['FEAT_NUM1','FEAT_NUM2']
lista_janela = [1,2,3]
query_final_num_snow = snow_create_query_num(tb_publico, tb_feat, lista_janela,feat_num_lista, id, safra_ref, safra)
print(query_final_num_snow)
WITH
tb_public AS (
SELECT
*
FROM tb_spine
),
-- Criação de variáveis de janela de 1M
tb_janela_1M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM1 para a janela 1
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_SUM_1M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MIN_1M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MAX_1M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_AVG_1M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MEDIAN_1M,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM2 para a janela 1
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_SUM_1M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MIN_1M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MAX_1M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_AVG_1M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MEDIAN_1M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 1 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis de janela de 2M
tb_janela_2M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM1 para a janela 2
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_SUM_2M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MIN_2M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MAX_2M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_AVG_2M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MEDIAN_2M,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM2 para a janela 2
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_SUM_2M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MIN_2M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MAX_2M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_AVG_2M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MEDIAN_2M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 2 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis de janela de 3M
tb_janela_3M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM1 para a janela 3
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_SUM_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MIN_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MAX_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_AVG_3M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM1,0)) AS FEAT_NUM1_MEDIAN_3M,
-- Criação de variáveis numéricas a partir da coluna FEAT_NUM2 para a janela 3
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_SUM_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MIN_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MAX_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_AVG_3M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM2,0)) AS FEAT_NUM2_MEDIAN_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 3 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_join AS (
SELECT
tb_public.*,
tb_janela_1M.FEAT_NUM1_SUM_1M,
tb_janela_1M.FEAT_NUM1_MIN_1M,
tb_janela_1M.FEAT_NUM1_MAX_1M,
tb_janela_1M.FEAT_NUM1_AVG_1M,
tb_janela_1M.FEAT_NUM1_MEDIAN_1M,
tb_janela_1M.FEAT_NUM2_SUM_1M,
tb_janela_1M.FEAT_NUM2_MIN_1M,
tb_janela_1M.FEAT_NUM2_MAX_1M,
tb_janela_1M.FEAT_NUM2_AVG_1M,
tb_janela_1M.FEAT_NUM2_MEDIAN_1M,
tb_janela_2M.FEAT_NUM1_SUM_2M,
tb_janela_2M.FEAT_NUM1_MIN_2M,
tb_janela_2M.FEAT_NUM1_MAX_2M,
tb_janela_2M.FEAT_NUM1_AVG_2M,
tb_janela_2M.FEAT_NUM1_MEDIAN_2M,
tb_janela_2M.FEAT_NUM2_SUM_2M,
tb_janela_2M.FEAT_NUM2_MIN_2M,
tb_janela_2M.FEAT_NUM2_MAX_2M,
tb_janela_2M.FEAT_NUM2_AVG_2M,
tb_janela_2M.FEAT_NUM2_MEDIAN_2M,
tb_janela_3M.FEAT_NUM1_SUM_3M,
tb_janela_3M.FEAT_NUM1_MIN_3M,
tb_janela_3M.FEAT_NUM1_MAX_3M,
tb_janela_3M.FEAT_NUM1_AVG_3M,
tb_janela_3M.FEAT_NUM1_MEDIAN_3M,
tb_janela_3M.FEAT_NUM2_SUM_3M,
tb_janela_3M.FEAT_NUM2_MIN_3M,
tb_janela_3M.FEAT_NUM2_MAX_3M,
tb_janela_3M.FEAT_NUM2_AVG_3M,
tb_janela_3M.FEAT_NUM2_MEDIAN_3M
FROM tb_public
LEFT JOIN tb_janela_1M
ON tb_public.ID = tb_janela_1M.ID
AND tb_public.SAFRA_REF = tb_janela_1M.SAFRA_REF
LEFT JOIN tb_janela_2M
ON tb_public.ID = tb_janela_2M.ID
AND tb_public.SAFRA_REF = tb_janela_2M.SAFRA_REF
LEFT JOIN tb_janela_3M
ON tb_public.ID = tb_janela_3M.ID
AND tb_public.SAFRA_REF = tb_janela_3M.SAFRA_REF
)
SELECT
tb_join.ID,
tb_join.SAFRA_REF,
tb_join.FEAT_NUM1_SUM_1M,
tb_join.FEAT_NUM1_MIN_1M,
tb_join.FEAT_NUM1_MAX_1M,
tb_join.FEAT_NUM1_AVG_1M,
tb_join.FEAT_NUM1_MEDIAN_1M,
tb_join.FEAT_NUM2_SUM_1M,
tb_join.FEAT_NUM2_MIN_1M,
tb_join.FEAT_NUM2_MAX_1M,
tb_join.FEAT_NUM2_AVG_1M,
tb_join.FEAT_NUM2_MEDIAN_1M,
tb_join.FEAT_NUM1_SUM_2M,
tb_join.FEAT_NUM1_MIN_2M,
tb_join.FEAT_NUM1_MAX_2M,
tb_join.FEAT_NUM1_AVG_2M,
tb_join.FEAT_NUM1_MEDIAN_2M,
tb_join.FEAT_NUM2_SUM_2M,
tb_join.FEAT_NUM2_MIN_2M,
tb_join.FEAT_NUM2_MAX_2M,
tb_join.FEAT_NUM2_AVG_2M,
tb_join.FEAT_NUM2_MEDIAN_2M,
tb_join.FEAT_NUM1_SUM_3M,
tb_join.FEAT_NUM1_MIN_3M,
tb_join.FEAT_NUM1_MAX_3M,
tb_join.FEAT_NUM1_AVG_3M,
tb_join.FEAT_NUM1_MEDIAN_3M,
tb_join.FEAT_NUM2_SUM_3M,
tb_join.FEAT_NUM2_MIN_3M,
tb_join.FEAT_NUM2_MAX_3M,
tb_join.FEAT_NUM2_AVG_3M,
tb_join.FEAT_NUM2_MEDIAN_3M
FROM tb_join
Criação de variáveis categóricas
A função query_final_cat_snow() cria um texto com a query para a criação de variáveis com a moda de cada uma das variáveis listadas em feat_num_lista na janela de tempo fornecida em lista_janela.
tb_publico = 'tb_spine'
tb_feat = 'tb_feat'
id = 'ID'
safra_ref = 'SAFRA_REF'
safra = 'SAFRA'
feat_num_lista = ['FEAT_CAT1','FEAT_CAT2']
lista_janela = [1,2,3]
query_final_cat_snow = snow_create_query_cat(tb_publico, tb_feat, lista_janela, feat_num_lista, id, safra_ref, safra)
print(query_final_cat_snow)
WITH
tb_public AS (
SELECT
ID,
SAFRA_REF
FROM tb_spine
),
tb_janela_1M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
MODE(FEAT_CAT1) AS MODE_FEAT_CAT1_1M,
MODE(FEAT_CAT2) AS MODE_FEAT_CAT2_1M
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 1 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_janela_2M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
MODE(FEAT_CAT1) AS MODE_FEAT_CAT1_2M,
MODE(FEAT_CAT2) AS MODE_FEAT_CAT2_2M
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 2 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
tb_janela_3M AS (
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
MODE(FEAT_CAT1) AS MODE_FEAT_CAT1_3M,
MODE(FEAT_CAT2) AS MODE_FEAT_CAT2_3M
FROM tb_public
LEFT JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 3 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
GROUP BY tb_public.ID, tb_public.SAFRA_REF
)
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_janela_1M.MODE_FEAT_CAT1_1M,
tb_janela_1M.MODE_FEAT_CAT2_1M,
tb_janela_2M.MODE_FEAT_CAT1_2M,
tb_janela_2M.MODE_FEAT_CAT2_2M,
tb_janela_3M.MODE_FEAT_CAT1_3M,
tb_janela_3M.MODE_FEAT_CAT2_3M
FROM tb_public
LEFT JOIN tb_janela_1M
ON tb_public.ID = JOIN tb_janela_1M.ID
AND tb_public.SAFRA_REF = JOIN tb_janela_1M.SAFRA_REF
LEFT JOIN tb_janela_2M
ON tb_public.ID = JOIN tb_janela_2M.ID
AND tb_public.SAFRA_REF = JOIN tb_janela_2M.SAFRA_REF
LEFT JOIN tb_janela_3M
ON tb_public.ID = JOIN tb_janela_3M.ID
AND tb_public.SAFRA_REF = JOIN tb_janela_3M.SAFRA_REF
Criação de variáveis agragadas
lista_feat_num = ['FEAT_NUM1', 'FEAT_NUM2']
feat_cat = 'FEAT_CAT1'
lista_valor_agragador = ['B', 'C']
id = 'ID'
safra_ref = 'SAFRA_REF'
tb_feat = 'tb_feat'
safra = 'SAFRA'
janelas = [1, 2, 3]
tb_publico = 'tb_spine'
print(snow_create_query_agregada(tb_publico, tb_feat, janelas, lista_feat_num, id, safra_ref, safra, feat_cat, lista_valor_agragador))
WITH
tb_public as(
SELECT
ID,
SAFRA_REF
FROM tb_spine
),
-- Criação de variáveis agrupadas com janela de 1M
tb_agrupada_FEAT_CAT1_B_1M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM1 para a janela 1
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_B_1M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_B_1M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_B_1M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_B_1M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MEDIAN_FEAT_NUM1_FEAT_CAT1_B_1M,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM2 para a janela 1
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_B_1M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_B_1M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_B_1M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_B_1M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MEDIAN_FEAT_NUM2_FEAT_CAT1_B_1M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 1 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis agrupadas com janela de 2M
tb_agrupada_FEAT_CAT1_B_2M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM1 para a janela 2
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_B_2M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_B_2M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_B_2M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_B_2M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MEDIAN_FEAT_NUM1_FEAT_CAT1_B_2M,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM2 para a janela 2
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_B_2M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_B_2M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_B_2M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_B_2M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MEDIAN_FEAT_NUM2_FEAT_CAT1_B_2M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 2 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis agrupadas com janela de 3M
tb_agrupada_FEAT_CAT1_B_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM1 para a janela 3
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_B_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_B_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_B_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_B_3M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MEDIAN_FEAT_NUM1_FEAT_CAT1_B_3M,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM2 para a janela 3
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_B_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_B_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_B_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_B_3M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MEDIAN_FEAT_NUM2_FEAT_CAT1_B_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 3 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'B'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis agrupadas com janela de 1M
tb_agrupada_FEAT_CAT1_C_1M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM1 para a janela 1
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_C_1M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_C_1M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_C_1M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_C_1M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MEDIAN_FEAT_NUM1_FEAT_CAT1_C_1M,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM2 para a janela 1
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_C_1M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_C_1M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_C_1M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_C_1M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MEDIAN_FEAT_NUM2_FEAT_CAT1_C_1M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 1 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'C'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis agrupadas com janela de 2M
tb_agrupada_FEAT_CAT1_C_2M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM1 para a janela 2
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_C_2M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_C_2M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_C_2M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_C_2M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MEDIAN_FEAT_NUM1_FEAT_CAT1_C_2M,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM2 para a janela 2
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_C_2M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_C_2M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_C_2M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_C_2M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MEDIAN_FEAT_NUM2_FEAT_CAT1_C_2M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 2 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'C'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
),
-- Criação de variáveis agrupadas com janela de 3M
tb_agrupada_FEAT_CAT1_C_3M as(
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM1 para a janela 3
SUM(COALESCE(tb_feat.FEAT_NUM1,0)) AS SUM_FEAT_NUM1_FEAT_CAT1_C_3M,
MAX(COALESCE(tb_feat.FEAT_NUM1,0)) AS MAX_FEAT_NUM1_FEAT_CAT1_C_3M,
MIN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MIN_FEAT_NUM1_FEAT_CAT1_C_3M,
AVG(COALESCE(tb_feat.FEAT_NUM1,0)) AS AVG_FEAT_NUM1_FEAT_CAT1_C_3M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM1,0)) AS MEDIAN_FEAT_NUM1_FEAT_CAT1_C_3M,
-- Criação de variáveis agrupadas a partir da coluna FEAT_CAT1 e FEAT_NUM2 para a janela 3
SUM(COALESCE(tb_feat.FEAT_NUM2,0)) AS SUM_FEAT_NUM2_FEAT_CAT1_C_3M,
MAX(COALESCE(tb_feat.FEAT_NUM2,0)) AS MAX_FEAT_NUM2_FEAT_CAT1_C_3M,
MIN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MIN_FEAT_NUM2_FEAT_CAT1_C_3M,
AVG(COALESCE(tb_feat.FEAT_NUM2,0)) AS AVG_FEAT_NUM2_FEAT_CAT1_C_3M,
MEDIAN(COALESCE(tb_feat.FEAT_NUM2,0)) AS MEDIAN_FEAT_NUM2_FEAT_CAT1_C_3M
FROM tb_public
INNER JOIN tb_feat
ON tb_public.ID = tb_feat.ID
AND (DATEADD('month', 3 , TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD')) >= tb_public.SAFRA_REF)
AND (TO_DATE(tb_feat.SAFRA, 'YYYY-MM-DD') < tb_public.SAFRA_REF)
AND tb_feat.FEAT_CAT1 = 'C'
GROUP BY tb_public.ID, tb_public.SAFRA_REF
)
SELECT
tb_public.ID,
tb_public.SAFRA_REF,
tb_agrupada_FEAT_CAT1_B_1M.SUM_FEAT_NUM1_FEAT_CAT1_B_1M,
tb_agrupada_FEAT_CAT1_B_1M.MAX_FEAT_NUM1_FEAT_CAT1_B_1M,
tb_agrupada_FEAT_CAT1_B_1M.MIN_FEAT_NUM1_FEAT_CAT1_B_1M,
tb_agrupada_FEAT_CAT1_B_1M.AVG_FEAT_NUM1_FEAT_CAT1_B_1M,
tb_agrupada_FEAT_CAT1_B_1M.MEDIAN_FEAT_NUM1_FEAT_CAT1_B_1M,
tb_agrupada_FEAT_CAT1_B_1M.SUM_FEAT_NUM2_FEAT_CAT1_B_1M,
tb_agrupada_FEAT_CAT1_B_1M.MAX_FEAT_NUM2_FEAT_CAT1_B_1M,
tb_agrupada_FEAT_CAT1_B_1M.MIN_FEAT_NUM2_FEAT_CAT1_B_1M,
tb_agrupada_FEAT_CAT1_B_1M.AVG_FEAT_NUM2_FEAT_CAT1_B_1M,
tb_agrupada_FEAT_CAT1_B_1M.MEDIAN_FEAT_NUM2_FEAT_CAT1_B_1M,
tb_agrupada_FEAT_CAT1_B_2M.SUM_FEAT_NUM1_FEAT_CAT1_B_2M,
tb_agrupada_FEAT_CAT1_B_2M.MAX_FEAT_NUM1_FEAT_CAT1_B_2M,
tb_agrupada_FEAT_CAT1_B_2M.MIN_FEAT_NUM1_FEAT_CAT1_B_2M,
tb_agrupada_FEAT_CAT1_B_2M.AVG_FEAT_NUM1_FEAT_CAT1_B_2M,
tb_agrupada_FEAT_CAT1_B_2M.MEDIAN_FEAT_NUM1_FEAT_CAT1_B_2M,
tb_agrupada_FEAT_CAT1_B_2M.SUM_FEAT_NUM2_FEAT_CAT1_B_2M,
tb_agrupada_FEAT_CAT1_B_2M.MAX_FEAT_NUM2_FEAT_CAT1_B_2M,
tb_agrupada_FEAT_CAT1_B_2M.MIN_FEAT_NUM2_FEAT_CAT1_B_2M,
tb_agrupada_FEAT_CAT1_B_2M.AVG_FEAT_NUM2_FEAT_CAT1_B_2M,
tb_agrupada_FEAT_CAT1_B_2M.MEDIAN_FEAT_NUM2_FEAT_CAT1_B_2M,
tb_agrupada_FEAT_CAT1_B_3M.SUM_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.MAX_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.MIN_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.AVG_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.MEDIAN_FEAT_NUM1_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.SUM_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.MAX_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.MIN_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.AVG_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_B_3M.MEDIAN_FEAT_NUM2_FEAT_CAT1_B_3M,
tb_agrupada_FEAT_CAT1_C_1M.SUM_FEAT_NUM1_FEAT_CAT1_C_1M,
tb_agrupada_FEAT_CAT1_C_1M.MAX_FEAT_NUM1_FEAT_CAT1_C_1M,
tb_agrupada_FEAT_CAT1_C_1M.MIN_FEAT_NUM1_FEAT_CAT1_C_1M,
tb_agrupada_FEAT_CAT1_C_1M.AVG_FEAT_NUM1_FEAT_CAT1_C_1M,
tb_agrupada_FEAT_CAT1_C_1M.MEDIAN_FEAT_NUM1_FEAT_CAT1_C_1M,
tb_agrupada_FEAT_CAT1_C_1M.SUM_FEAT_NUM2_FEAT_CAT1_C_1M,
tb_agrupada_FEAT_CAT1_C_1M.MAX_FEAT_NUM2_FEAT_CAT1_C_1M,
tb_agrupada_FEAT_CAT1_C_1M.MIN_FEAT_NUM2_FEAT_CAT1_C_1M,
tb_agrupada_FEAT_CAT1_C_1M.AVG_FEAT_NUM2_FEAT_CAT1_C_1M,
tb_agrupada_FEAT_CAT1_C_1M.MEDIAN_FEAT_NUM2_FEAT_CAT1_C_1M,
tb_agrupada_FEAT_CAT1_C_2M.SUM_FEAT_NUM1_FEAT_CAT1_C_2M,
tb_agrupada_FEAT_CAT1_C_2M.MAX_FEAT_NUM1_FEAT_CAT1_C_2M,
tb_agrupada_FEAT_CAT1_C_2M.MIN_FEAT_NUM1_FEAT_CAT1_C_2M,
tb_agrupada_FEAT_CAT1_C_2M.AVG_FEAT_NUM1_FEAT_CAT1_C_2M,
tb_agrupada_FEAT_CAT1_C_2M.MEDIAN_FEAT_NUM1_FEAT_CAT1_C_2M,
tb_agrupada_FEAT_CAT1_C_2M.SUM_FEAT_NUM2_FEAT_CAT1_C_2M,
tb_agrupada_FEAT_CAT1_C_2M.MAX_FEAT_NUM2_FEAT_CAT1_C_2M,
tb_agrupada_FEAT_CAT1_C_2M.MIN_FEAT_NUM2_FEAT_CAT1_C_2M,
tb_agrupada_FEAT_CAT1_C_2M.AVG_FEAT_NUM2_FEAT_CAT1_C_2M,
tb_agrupada_FEAT_CAT1_C_2M.MEDIAN_FEAT_NUM2_FEAT_CAT1_C_2M,
tb_agrupada_FEAT_CAT1_C_3M.SUM_FEAT_NUM1_FEAT_CAT1_C_3M,
tb_agrupada_FEAT_CAT1_C_3M.MAX_FEAT_NUM1_FEAT_CAT1_C_3M,
tb_agrupada_FEAT_CAT1_C_3M.MIN_FEAT_NUM1_FEAT_CAT1_C_3M,
tb_agrupada_FEAT_CAT1_C_3M.AVG_FEAT_NUM1_FEAT_CAT1_C_3M,
tb_agrupada_FEAT_CAT1_C_3M.MEDIAN_FEAT_NUM1_FEAT_CAT1_C_3M,
tb_agrupada_FEAT_CAT1_C_3M.SUM_FEAT_NUM2_FEAT_CAT1_C_3M,
tb_agrupada_FEAT_CAT1_C_3M.MAX_FEAT_NUM2_FEAT_CAT1_C_3M,
tb_agrupada_FEAT_CAT1_C_3M.MIN_FEAT_NUM2_FEAT_CAT1_C_3M,
tb_agrupada_FEAT_CAT1_C_3M.AVG_FEAT_NUM2_FEAT_CAT1_C_3M,
tb_agrupada_FEAT_CAT1_C_3M.MEDIAN_FEAT_NUM2_FEAT_CAT1_C_3M
FROM tb_public
LEFT JOIN tb_agrupada_FEAT_CAT1_B_1M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_B_1M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_B_1M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_B_2M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_B_2M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_B_2M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_B_3M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_B_3M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_B_3M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_C_1M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_C_1M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_C_1M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_C_2M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_C_2M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_C_2M.SAFRA_REF
LEFT JOIN tb_agrupada_FEAT_CAT1_C_3M
ON tb_public.ID = tb_agrupada_FEAT_CAT1_C_3M.ID
AND tb_public.SAFRA_REF = tb_agrupada_FEAT_CAT1_C_3M.SAFRA_REF
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
featsql-0.0.1.tar.gz
(39.4 kB
view hashes)
Built Distribution
featsql-0.0.1-py3-none-any.whl
(22.2 kB
view hashes)
