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