def do_transformacao():
seg.ChecarPerfil(seg.PERFIL_Analista)
nome = getattr(request.forms, 'nome')
sql = getattr(request.forms, 'sql')
persistir = getattr(request.forms, 'pers')
descricao = 'Gerado pela transformacao'
nome = '__' + nome
tabela = imp.ObterTabela('0')
tabela.nome = nome
tabela.descricao = descricao
tabela.sql_origem = sql
tabela.id_fonte_dados = fonte_dados_transformacao
if persistir == 'N':
tabela.sql_destino = sql
else:
tabela.sql_destino = None
imp.SalvarTabela(tabela)
if persistir == 'S':
imp.CriarTabelaDoSql(sql, nome)
imp.CriarIndices(nome)
imp.GerarColunas(nome)
else:
imp.GerarColunasComSQL(nome, sql)
tabela = imp.ObterTabelaPorNome(nome)
redirect('/colunas?id=' + str(tabela.id))
def GerarSqlQuartil(tabela, coluna, tp):
tpDisc = ''
if tp == 'E':
tpDisc = 'DISTINCT'
sqlTexto = """
SELECT *,
CASE WHEN coluna <= Q1 THEN 'Q1'
WHEN coluna <= Q2 THEN 'Q2'
WHEN coluna <= Q3 THEN 'Q3'
ELSE 'Q4'
END AS QUARTIL
FROM (
SELECT *,
(SELECT COUNT(*) FROM (SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}})) AS QTD,
(SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}} ORDER BY 1 LIMIT (((SELECT COUNT(*) FROM (SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}})) / 4) * 1), 1) Q1,
(SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}} ORDER BY 1 LIMIT (((SELECT COUNT(*) FROM (SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}})) / 4) * 2), 1) Q2,
(SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}} ORDER BY 1 LIMIT (((SELECT COUNT(*) FROM (SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}})) / 4) * 3), 1) Q3
FROM (
SELECT {{tpQuartil}} {{coluna}} AS coluna
FROM {{tabela}}) AS T
ORDER BY 1
) AS T
LIMIT 1"""
sqlNumero = """
SELECT *,
CASE WHEN coluna <= Q1 THEN 'Q1'
WHEN coluna <= Q2 THEN 'Q2'
WHEN coluna <= Q3 THEN 'Q3'
ELSE 'Q4'
END AS QUARTIL
FROM (
SELECT *,
(SELECT COUNT(*) FROM (SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}})) AS QTD,
(SELECT {{tpQuartil}} CAST({{coluna}} AS FLOAT) FROM {{tabela}} ORDER BY 1 LIMIT (((SELECT COUNT(*) FROM (SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}})) / 4) * 1), 1) Q1,
(SELECT {{tpQuartil}} CAST({{coluna}} AS FLOAT) FROM {{tabela}} ORDER BY 1 LIMIT (((SELECT COUNT(*) FROM (SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}})) / 4) * 2), 1) Q2,
(SELECT {{tpQuartil}} CAST({{coluna}} AS FLOAT) FROM {{tabela}} ORDER BY 1 LIMIT (((SELECT COUNT(*) FROM (SELECT {{tpQuartil}} {{coluna}} FROM {{tabela}})) / 4) * 3), 1) Q3
FROM (
SELECT {{tpQuartil}} CAST({{coluna}} AS FLOAT) AS coluna
FROM {{tabela}}) AS T
ORDER BY 1
) AS T
LIMIT 1"""
objTabela = imp.ObterTabelaPorNome(tabela)
objColuna = meta.ObterColunaPorTabNome(objTabela.id, coluna)
if objColuna.tipo == 'N':
sql = sqlNumero.replace('{{tabela}}', tabela).replace(
'{{coluna}}', coluna).replace('{{tpQuartil}}', tpDisc)
else:
sql = sqlTexto.replace('{{tabela}}', tabela).replace(
'{{coluna}}', coluna).replace('{{tpQuartil}}', tpDisc)
resultado = db.consultarSQLDataFrame(sql)
if objColuna.tipo == 'N':
sqlResultado = 'CASE WHEN CAST(' + str(coluna) + ' AS FLOAT) <= ' + str(
resultado['Q1'][0]
) + ' THEN \'Q1\' WHEN CAST(' + str(coluna) + ' AS FLOAT) <= ' + str(
resultado['Q2'][0]) + ' THEN \'Q2\' WHEN CAST(' + str(
coluna) + ' AS FLOAT) <= ' + str(
resultado['Q3'][0]) + ' THEN \'Q3\' ELSE \'Q4\' END'
else:
sqlResultado = 'CASE WHEN ' + str(coluna) + ' <= ' + str(
resultado['Q1']
[0]) + ' THEN \'Q1\' WHEN ' + str(coluna) + ' <= ' + str(
resultado['Q2'][0]) + ' THEN \'Q2\' WHEN ' + str(
coluna) + ' <= ' + str(
resultado['Q3'][0]) + ' THEN \'Q3\' ELSE \'Q4\' END'
return sqlResultado