I have the following tables:
kid
id
name
kid_workshop
id
kid_id
workshop_name
workshop_name could be only: arts, martial_arts, chess, soccer
in order to increase performance, I want to create a materialized view that will look like this:
kid_id name arts martial_arts chess soccer
1 Dann True True False True
How can I do it? I am using postgres
Try the following query:
create materialized view your_view as
select
k.id,
k.name,
max(case when kw.workshop_name = 'arts'
then 'true' else 'false' end) arts,
max(case when kw.workshop_name = 'martial_arts'
then 'true' else 'false' end) martial_arts,
max(case when kw.workshop_name = 'chess'
then 'true' else 'false' end) chess,
max(case when kw.workshop_name = 'soccer'
then 'true' else 'false' end) soccer
from kid k
inner join kid_workshop kw
on k.id = kw.kid_id
group by k.id,
k.name
This query employs a trick while doing the pivot. Specifically, it assigns the string 'true' for a positive case and 'false' to every negative case. Since 'true' is lexicographically greater than 'false', it should be retained using the MAX() function if present, otherwise false would be reported. This is precisely the logic we want.
kid_id, and you want to pivot those records into columns. The way we do this is by aggregating over each kid_id and then creating the columns we want to store each piece of data.smth like:
create materialized view mv1 as
select
k.id kidid
, name
, case when workshop_name = 'arts' then true else false end arts
, case when workshop_name = 'martial_arts' then true else false end martial_arts
, case when workshop_name = 'chess' then true else false end chess
, case when workshop_name = 'soccer' then true else false end soccer
from kid k
join kid_workshop w on w.kid_id = k.id
GROUP BY, but I feel we should not be answering this just yet.
kid_id and do a pivot. This is my feeling.Try:
select
k.id kid_id,
k.name,
a.id is not null as arts,
m.id is not null as martial_arts,
c.id is not null as chess,
s.id is not null as soccer
from kid k
outer join kid_workshop a on a.kid_id = k.id and a.workshop_name = 'arts'
outer join kid_workshop m on m.kid_id = k.id and m.workshop_name = 'martial arts'
outer join kid_workshop c on c.kid_id = k.id and c.workshop_name = 'chess'
outer join kid_workshop s on s.kid_id = k.id and s.workshop_name = 'soccer'
kid_idcolumn. I doubt this will be very performant. In any case, what have you tried so far?