Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --select id, name from br_grability_public.products p where image != 'NO-IMAGE'
- --, 'carrefour', 'carrefour_express', 'pao_de_azucar', 'express'
- --'mambo', 'mambo_express', 'raia', 'raia_market', 'drogasil'
- WITH numeric_eans as(
- select distinct
- max(p.id) as id,
- (CASE WHEN RLIKE(ean, '^\\d+$') THEN
- CAST (ean AS INTEGER)
- ELSE null END) as ean_n
- FROM br_grability_public.products p
- join br_grability_public.product_store ps on p.id = ps.product_id
- join br_grability_public.stores s on s.store_id = ps.store_id
- where p.image != 'NO-IMAGE' and s.type in ('st_marche', 'mambo', 'mambo_express')
- group by 2),
- numeric_eans_2 as(
- select distinct
- max(p.id) as id,
- (CASE WHEN RLIKE(ean, '^\\d+$') THEN
- CAST (ean AS INTEGER)
- ELSE null END) as ean_n
- FROM br_grability_public.products p
- join br_grability_public.product_store ps on p.id = ps.product_id
- join br_grability_public.stores s on s.store_id = ps.store_id
- where p.image != 'NO-IMAGE' and s.type in ('carrefour', 'carrefour_express', 'pao_de_azucar', 'express')
- group by 2),
- numeric_eans_all as(
- select distinct
- max(p.id) as id,
- (CASE WHEN RLIKE(ean, '^\\d+$') THEN
- CAST (ean AS INTEGER)
- ELSE null END) as ean_n
- FROM br_grability_public.products p
- join br_grability_public.product_store ps on p.id = ps.product_id
- join br_grability_public.stores s on s.store_id = ps.store_id
- where p.image != 'NO-IMAGE' and s.type not in ('carrefour', 'carrefour_express', 'pao_de_azucar', 'express', 'st_marche', 'mambo', 'mambo_express')
- group by 2),
- good_photos as(
- SELECT ne2.*
- FROM numeric_eans_2 ne2
- LEFT JOIN
- numeric_eans ne
- ON ne.ean_n = ne2.ean_n
- WHERE ne.ean_n IS NULL
- and ne2.ean_n >= 100000000
- union all
- select * from numeric_eans ne
- where ne.ean_n >= 100000000
- )
- SELECT neall.id, neall.ean_n as ean
- FROM numeric_eans_all neall
- LEFT JOIN
- good_photos gp
- ON gp.ean_n = neall.ean_n
- WHERE gp.ean_n IS NULL
- and neall.ean_n >= 100000000
- union all
- select gp.id, gp.ean_n as ean from good_photos gp
- where gp.ean_n >= 100000000
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement