Base de dados transacionais para OLAP

mysql-gasta_muito.sql

-- 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;

mysql-vendas_dynamic.sql

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;

pg-function_to_generate_sql.sql

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;

pg-gasta_muito.sql

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;

pg-vendas_not_dynamic.sql

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;

para usar sim e não:

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 ''''Sim'''' ELSE ''''Não'''' 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;