Last active
May 6, 2016 23:12
-
-
Save brunoocasali/ab3504fc5639dcd29eb9 to your computer and use it in GitHub Desktop.
Base de dados transacionais para OLAP
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Criei uma função para melhorar a legibilidade: | |
CREATE FUNCTION `age_group_from` (birthday DATE) | |
RETURNS TEXT | |
BEGIN | |
DECLARE age_group VARCHAR(5); | |
SET age_group = CASE | |
WHEN TIMESTAMPDIFF(YEAR, birthday, NOW()) >= 40 THEN '≥ 40' | |
WHEN TIMESTAMPDIFF(YEAR, birthday, NOW()) BETWEEN 20 AND 39 THEN '20-39' | |
WHEN TIMESTAMPDIFF(YEAR, birthday, NOW()) <= 19 THEN '≤ 19' | |
END; | |
RETURN age_group; | |
END | |
-- Abaixo o código de transformação: | |
SELECT | |
c.idcliente AS 'Id', | |
c.sexo, | |
AGE_GROUP_FROM(c.nascimento) AS 'Idade', | |
IF(c.num_dependentes > 0, 'Sim', 'Não') AS 'Filhos', | |
IF(SUM(valor_total) >= 1000, 'Sim', 'Não') AS 'total' | |
FROM | |
vendas v | |
JOIN | |
clientes c USING (idcliente) | |
GROUP BY c.idcliente | |
ORDER BY c.idcliente; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET @sql = NULL; | |
SELECT | |
GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(p.idproduto = ', idproduto, ', "Sim", "Não")) AS ', nome)) | |
INTO @sql FROM produtos; | |
SET @sql = CONCAT('SELECT | |
vp.idvenda, ', | |
@sql, | |
' FROM | |
venda_produto vp | |
JOIN | |
produtos p ON p.idproduto = vp.idproduto | |
GROUP BY vp.idvenda | |
ORDER BY vp.idvenda;'); | |
PREPARE stmt FROM @sql; | |
EXECUTE stmt; | |
DEALLOCATE PREPARE stmt; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION generate_sql () RETURNS VARCHAR AS $$ | |
DECLARE | |
get_column_names varchar; | |
columnlist varchar; | |
BEGIN | |
get_column_names = 'SELECT string_agg(''MAX(CASE WHEN p.idproduto = '' || idproduto || '' THEN 1 ELSE 0 END) AS '' || nome, '','' ORDER BY idproduto) FROM produtos;'; | |
EXECUTE get_column_names INTO columnlist; | |
RETURN 'SELECT vp.idvenda, '|| columnlist ||' FROM venda_produto vp JOIN produtos p ON p.idproduto = vp.idproduto GROUP BY vp.idvenda ORDER BY vp.idvenda;'; | |
END; | |
$$ LANGUAGE plpgsql; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
DISTINCT(c.idcliente) AS Id, | |
c.sexo, | |
CASE WHEN EXTRACT(YEAR FROM AGE(c.nascimento)) >= 40 THEN '≥ 40' | |
WHEN EXTRACT(YEAR FROM AGE(c.nascimento)) BETWEEN 20 AND 39 THEN '20-39' | |
WHEN EXTRACT(YEAR FROM AGE(c.nascimento)) <= 19 THEN '≤ 19' | |
END AS Idade, | |
CASE WHEN c.num_dependentes > 0 THEN 'Sim' | |
ELSE 'Não' END AS Filhos, | |
CASE WHEN SUM(valor_total) >= 1000 THEN 'Sim' | |
ELSE 'Não' | |
END AS total | |
FROM vendas v | |
JOIN clientes c USING (idcliente) | |
GROUP BY c.idcliente ORDER BY c.idcliente; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT vp.idvenda, | |
MAX(CASE WHEN p.idproduto = 1 THEN 'Sim' ELSE 'Não' END) AS Leite, --p.nome | |
MAX(CASE WHEN p.idproduto = 2 THEN 'Sim' ELSE 'Não' END) AS Café, | |
MAX(CASE WHEN p.idproduto = 3 THEN 'Sim' ELSE 'Não' END) AS Cerveja, | |
MAX(CASE WHEN p.idproduto = 4 THEN 'Sim' ELSE 'Não' END) AS Pão, | |
MAX(CASE WHEN p.idproduto = 5 THEN 'Sim' ELSE 'Não' END) AS Manteiga, | |
MAX(CASE WHEN p.idproduto = 6 THEN 'Sim' ELSE 'Não' END) AS Arroz, | |
MAX(CASE WHEN p.idproduto = 7 THEN 'Sim' ELSE 'Não' END) AS Feijão | |
FROM venda_produto vp | |
JOIN produtos p ON p.idproduto = vp.idproduto | |
GROUP BY vp.idvenda ORDER BY vp.idvenda; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
para usar sim e não: