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.
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
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
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
host="localhost"user="sqluser"password="password"database="mydatabase"# Conectar ao MySQLconnection=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)
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)
/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)
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.
/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)
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
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
/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)
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
/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)
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.
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
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
The Tidelift Subscription provides access to a continuously curated stream of human-researched and maintainer-verified data on open source packages and their licenses, releases, vulnerabilities, and development practices.