Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create local temp table sample_canonicals on commit preserve rows as
- (
- with canonicals as
- (
- select c.id as canonical_place_id,
- count(distinct place_id) as places
- from canonical_places c inner join place_mappings m on c.id = m.canonical_place_id
- where enabled
- group by c.id
- order by random()
- )
- select canonical_place_id
- from canonicals
- --where places > 10
- --limit 1000
- )
- ;
- create local temp table relevant_info on commit preserve rows as
- (
- --with sample_canonicals as (select '33717'::int as canonical_place_id)
- select c.canonical_place_id,
- m.place_id,
- m.owner_verified,
- s.priority,
- m.updated_at,
- row_number() over (partition by canonical_place_id
- order by m.owner_verified desc,
- s.priority asc,
- m.updated_at asc,
- m.place_id asc) as layer_order
- from sample_canonicals c inner join place_mappings m using (canonical_place_id)
- inner join places p on m.place_id = p.id
- inner join sources s on p.source_id = s.id
- order by c.canonical_place_id,
- m.owner_verified desc,
- s.priority asc,
- m.updated_at asc,
- m.place_id asc
- )
- ;
- create local temp table details on commit preserve rows as
- (
- select i.*,
- p.name,
- regexp_replace(p.description, ',|\n', '', 'g') as description,
- p.subtitle,
- p.alt_names,
- d.permanently_closed,
- p.enabled
- from relevant_info i inner join places p on i.place_id = p.id
- left join place_details d using (place_id)
- order by i.canonical_place_id, i.layer_order
- )
- ;
- create local temp table details_enabled on commit preserve rows as
- (
- select * from details where enabled is true
- )
- ;
- create local temp table top_values on commit preserve rows as
- (
- select canonical_place_id,
- first_value(name) over (partition by canonical_place_id order by layer_order asc, name is null, ts) as name,
- first_value(description) over (partition by canonical_place_id order by layer_order asc, description is null, ts) as description,
- first_value(subtitle) over (partition by canonical_place_id order by layer_order asc, subtitle is null, ts) as subtitle,
- first_value(alt_names) over (partition by canonical_place_id order by layer_order asc, alt_names is null, ts) as alt_names
- from details
- order by canonical_place_id
- )
- ;
Add Comment
Please, Sign In to add comment