datasus_db.views.ibge_piramide_etaria 

 1import duckdb
 2
 3VIEW_DDL = """
 4CREATE OR REPLACE VIEW IBGE_PIRAMIDE_ETARIA AS with POP AS (
 5	select 
 6		MUNIC_RES, 
 7		ANO, 
 8		SEXO, 
 9		INICIO_FXETARIA, 
10		FIM_FXETARIA, 
11		SUM(POPULACAO) AS POPULACAO 
12	from main.IBGE_POP
13	group by MUNIC_RES, ANO, SEXO, INICIO_FXETARIA, FIM_FXETARIA
14),
15POP_PERCENTUAL AS (
16	select 
17		MUNIC_RES,
18		SEXO,
19		ANO,
20		INICIO_FXETARIA,
21		FIM_FXETARIA,
22		POPULACAO,
23		POPULACAO / SUM(POPULACAO) OVER (PARTITION BY MUNIC_RES, ANO) AS PERCETUAL,
24	from POP
25)
26select MUNIC_RES, SEXO, ANO, INICIO_FXETARIA, FIM_FXETARIA, POPULACAO, PERCETUAL 
27from POP_PERCENTUAL;
28"""
29
30
31def create_piramide_etaria_view(db_file="datasus.db"):
32    with duckdb.connect(db_file) as con:
33        con.execute(VIEW_DDL)
VIEW_DDL = '\nCREATE OR REPLACE VIEW IBGE_PIRAMIDE_ETARIA AS with POP AS (\n\tselect \n\t\tMUNIC_RES, \n\t\tANO, \n\t\tSEXO, \n\t\tINICIO_FXETARIA, \n\t\tFIM_FXETARIA, \n\t\tSUM(POPULACAO) AS POPULACAO \n\tfrom main.IBGE_POP\n\tgroup by MUNIC_RES, ANO, SEXO, INICIO_FXETARIA, FIM_FXETARIA\n),\nPOP_PERCENTUAL AS (\n\tselect \n\t\tMUNIC_RES,\n\t\tSEXO,\n\t\tANO,\n\t\tINICIO_FXETARIA,\n\t\tFIM_FXETARIA,\n\t\tPOPULACAO,\n\t\tPOPULACAO / SUM(POPULACAO) OVER (PARTITION BY MUNIC_RES, ANO) AS PERCETUAL,\n\tfrom POP\n)\nselect MUNIC_RES, SEXO, ANO, INICIO_FXETARIA, FIM_FXETARIA, POPULACAO, PERCETUAL \nfrom POP_PERCENTUAL;\n'
def create_piramide_etaria_view(db_file='datasus.db'): 
32def create_piramide_etaria_view(db_file="datasus.db"):
33    with duckdb.connect(db_file) as con:
34        con.execute(VIEW_DDL)