CREATE VIEW av AS SELECT * FROM '/e3/salidas/barrido/avisos.parquet';; CREATE VIEW re AS SELECT r.*, a.notice_type, a.subtype, a.buyer_country, a.buyer_profile, a.sdk FROM '/e3/salidas/barrido/resultados.parquet' r JOIN av a USING (numero, anio);; CREATE VIEW ofe AS SELECT o.*, a.notice_type, a.subtype, a.buyer_country FROM '/e3/salidas/barrido/ofertas.parquet' o JOIN av a USING (numero, anio);; CREATE VIEW u AS SELECT * FROM re WHERE esquema='eforms' AND buyer_country='ESP' AND notice_type IN ('can-standard','can-social','can-desg','can-tran');; CREATE MACRO clase_id(ids) AS CASE WHEN ids='' OR ids IS NULL THEN 'sin_id' WHEN regexp_matches(upper(ids), '(^|[=;\s-])(ES)?[ABCDEFGHJNPQRSUVW][0-9]{7}[0-9A-J]($|[;\s-])') THEN 'nif_juridica' WHEN regexp_matches(upper(ids), '(^|[=;\s-])(ES)?([0-9]{8}[A-Z]|[XYZ][0-9]{7}[A-Z])($|[;\s-])') THEN 'nif_fisica' WHEN regexp_matches(ids, '^=[0-9]+(\.0)?$') THEN 'numerico_sin_esquema' WHEN regexp_matches(upper(ids), 'PLATAFORMA') AND NOT regexp_matches(upper(ids), 'NIF=') THEN 'solo_plataforma' ELSE 'otro' END;; CREATE VIEW uo AS SELECT o.*, regexp_extract(a.buyer_profile, 'https?://([^/]+)', 1) dominio FROM ofe o JOIN av a USING (numero, anio) WHERE o.buyer_country='ESP' AND o.notice_type IN ('can-standard','can-social','can-desg','can-tran') AND tender_id<>'TE';;