Usamos cookies para medir audiência e melhorar sua experiência. Você pode aceitar ou recusar a qualquer momento. Veja sobre o iMasters.
Bom dia.
Tenho uma tabela Imoveis, uma tabela Caracteristicas, e uma tabela de relacionamento entre essas duas, chamada RelCaracImoveis
Um imóvel possui várias características, e uma característica pertence vários imóveis.
RelCaracImoveis só guarda o id do imóvel e o id da caracteristica, bem como seu valor.
Exemplo da estrutura de RelCaracImoveis:
id|imoveis_id|carac_id|valor
1| 38 | 2 | 2
2| 38 | 3 | 1
Nesse caso, eu estou dizendo que o imóvel 38 possui 2 quartos e 1 suíte.
Bom.. Pela minha estrutura, quero fazer um select na tabela de imoveis, juntamente com as caracteristicas, onde eu traga esses dados relacionados, dessa forma, semelhante a uma consulta de referência cruzada:
id|rua|salas|quartos|suites|vagas_garagem
38|xxx| 1 | 2 | 1 | 1
39|xxx| 1 | 3 | 2 | 2
Os campos id e rua, referem-se ao imóvel, já os demais, estão vindo da tabela de caracteristicas, através do relacionamento com o RelCaracImoveis e Imoveis.
Eu consegui fazer assim:
SELECT
rel_carac_imoveis.valor_carac
, rel_carac_imoveis.imoveis_id
, (case caracteristicas.id when 1 then true else false end) as sala_estar
, (case rel_carac_imoveis.caracteristicas_id when 2 then true end) as quartos
, (case rel_carac_imoveis.caracteristicas_id when 3 then true end) as suites
, (case rel_carac_imoveis.caracteristicas_id when 4 then true end) as vagas_garagemSó que ele retorna os dados assim:
id|rua|salas|quartos |suites|vagas_garagem
38|xxx| 1 | null | null | null
38|xxx| null| null | nulL | null
38|xxx| null| null | nulL | null
38|xxx| null| null | nulL | null
38|xxx| null| null | nulL | null
38|xxx| null| null | nulL | null
38|xxx| null| null | nulL | null
38|xxx| null| null | nulL | null
38|xxx| null| null | nulL | null
Para cada valor de RelCaracImoveis relacionado ao imovel 38, ela retorna uma linha, mas tudo vem com null, exceto o primeiro. Se eu tentar agrupar pelo valor do imovel, ele também não retorna da forma esperada.
É isso. Alguma dica? Como refaço essa consulta? Alguma sugestão de alteração na estrutura?
Valeu, macho. Funcionou perfeitamente.
Ainda neste ensejo, estamos criando aliases pra cada uma das verificações. No entanto, se eu tentar acrescentar "where quartos>3", o mysql vai dizer que não existe nenhuma coluna com esse nome. Eu resolvi isso colocando sua consulta como uma subquery, mais ou menos assim
select * from (
SELECT imoveis_id,
SUM(IF(caracteristicas_id = 2, valor, 0)) as quartos,
SUM(IF(caracteristicas_id = 3, valor, 0)) as suites,
SUM(IF(caracteristicas_id = 4, valor, 0)) as vagas_garagem
FROM rel_carac_imoveis
GROUP BY imoveis_id) as e
where quartos>3Você sugere outra forma?
E quanto a modificações? Você me sugere alguma que tornasse a criação dessa pivot table mais automatizada, ou alguma modificação nas tabelas?
Valeu pela resposta, por mim esse tópico já está resolvido. :)
Essa forma acredito que atenda bem. Mas claro, sempre há jeito de fazer de forma mais otimizada. Que no caso, seria pegar somente os imoveis_id que possuirem > 3 e fazer o join com a mesma tabela, pra computar as agregações. Obviamente, diminuiria a quantidade de processamento das agregações para somente os imoveis desejados. Compreende?
Quanto a criação automatizada, não há como via SQL. Somente definindo manualmente mesmo. E eu acho que essa modelagem está boa para este fim.
Compreendidíssimo!!!!
Eu só não consigo visualizar onde eu colocaria esse where, de forma a fazer o filtro antes que as agregações fossem processadas.
Será que eu deveria colocar mais uma sub-query inline? Assim ficariam 3 níveis de recursão.Correto?
Você teria que fazer apenas um self-join.
Exemplo:
SELECT r.imoveis_id,
SUM(IF(r2.caracteristicas_id = 2, r2.valor, 0)) as quartos,
SUM(IF(r2.caracteristicas_id = 3, r2.valor, 0)) as suites,
SUM(IF(r2.caracteristicas_id = 4, r2.valor, 0)) as vagas_garagem
FROM rel_carac_imoveis r
INNER JOIN rel_carac_imoveis r2
ON r2.imoveis_id = r.imoveis_id
WHERE r.caracteristicas_id = 2
AND r.valor > 3
GROUP BY r.imoveis_idValeu, assim que chegar em casa farei as adaptações.
Obrigado.
Tópico resolvido!
Ali onde tem rel_carac_imoveis r2, depois, ele não acha o r2, diz que não existe.
Eu vou fazer assim:
Vou configurar o cron para ser executado a cada 2 horas, e criar uma tabela baseada no sql acima.
algo assim
create table full_data as (select ..........)
agora, todas as buscas serão feitas a partir da tabela full_data, que será atualizada a cada 2 horas.
Pensei em criar uma view. Mas a view é apenas uma "consulta embalada", né? Ou ela oferece alguma vantagem gritante de performance em uma consulta pesada como essa?
>
Ali onde tem rel_carac_imoveis r2, depois, ele não acha o r2, diz que não existe.
Ahm? Eu testei essa query antes de postar. Cole exatamente a mensagem de erro, por favor.
>
Eu vou fazer assim:
Vou configurar o cron para ser executado a cada 2 horas, e criar uma tabela baseada no sql acima.
algo assim
create table full_data as (select ..........)
agora, todas as buscas serão feitas a partir da tabela full_data, que será atualizada a cada 2 horas.
Pensei em criar uma view. Mas a view é apenas uma "consulta embalada", né? Ou ela oferece alguma vantagem gritante de performance em uma consulta pesada como essa?
Não há vantagem em performance não. No Oracle que obtemos uma vantagem com o recurso de materializar uma view. Que no caso, ele armazena os dados obtidos da query em outra storage.
Não está havendo erros. Tá perfeito. O problema era o campo "valor", que na realidade se chama "valor_carac".
As mensagens do mysql não ajudam muito as vezes. Só vi esse erro agora.
Valeu
Olá, o processo que deseja realizar, é criar uma "pivot table" ou "crosstab report".
Como você tem características com ID fixo, você consegue fazer isso na query por meio de uma checagem dentro de uma função de agregação, onde internalmente é verificado cada registro relacionado à agregação, que no caso, seria pelo imoveis_id.
Exemplo:
+------------+---------+--------+---------------+ | imoveis_id | quartos | suites | vagas_garagem | +------------+---------+--------+---------------+ | 1 | 2 | 1 | 0 | +------------+---------+--------+---------------+ 1 row in set (0.02 sec)