Блог

Я хотел бы создать запрос, который выполняет следующее:

Используя regex_mapping таблицу, найдите все строки в sample data том, что REGEXP_MATCH на x

 WITH sample_data AS (
SELECT x, y
FROM (SELECT "asd rmkt asdf" AS x, true AS y UNION ALL  -- should map to remekartier
      SELECT "as asdf", true UNION ALL -- should map to ali sneider
      SELECT "asdafsd", false)  -- should map to NULL
),

regex_mapping AS (
SELECT regex, map
FROM (SELECT "as" AS regex, "ali sneider" AS map UNION ALL
      SELECT "rmkt" AS regex, "remekartier" AS map )
)

SELECT sample_data.*, mapped_item
FROM sample_data
-- but here, use multiple REGEXP_MATCH with CASE WHEN looping over the regex_mappings.
-- e.g. CASE WHEN REGEXP_MATCH(x, "as") THEN "ali sneider"
             WHEN REGEXP_MATCH(x, "rmkt") THEN "remakrtier" END AS mapped_item)

  

Попробуйте это —

 WITH sample_data AS (
SELECT x, y
FROM (SELECT "asd rmkt asdf" AS x, true AS y UNION ALL  -- should map to remekartier
      SELECT "as asdf", true UNION ALL -- should map to ali sneider
      SELECT "asdafsd", false)
),
regex_mapping AS (
SELECT regex, map
FROM (SELECT "as" AS regex, "ali sneider" AS map UNION ALL
      SELECT "rmkt" AS regex, "remekartier" AS map )
)
SELECT s.*, r.map 
FROM sample_data s, regex_mapping r
WHERE regexp_contains(s.x,concat('\b',r.regex,'\b')) 
  

Результаты ->

Второй способ: вместо перекрестного соединения используйте скалярный подзапрос. Я использовал limit, чтобы подзапрос не возвращал более 1 строки, и если совпадают несколько регулярных выражений, он вернет только одно из них

 --- same WITH clause as above query ---
SELECT s.*, (SELECT r.map 
               FROM regex_mapping r 
              WHERE regexp_contains(s.x,concat('\b',r.regex,'\b')) 
              LIMIT 1) as map
FROM sample_data s
  

Результаты ->

Третий способ: дедуплицированные данные

 WITH sample_data AS (
  SELECT campaign_name, placement_name
  FROM (SELECT "as_rmkt_asdf" AS campaign_name, "xdd" AS placement_name UNION ALL  -- should map to remekartier
        SELECT "as_asdf", "sdfsdf" UNION ALL -- should map to ali sneider
        SELECT "as_rmkt_dafsd", "sdfg" UNION ALL -- should map to rmkt
        SELECT "asf_adsdf", "gdf" -- should map to NULL (because higher priority)
        )
),
regex_mapping AS (
  SELECT regex, map, priority
  FROM (SELECT "rmkt" AS regex, "remekartier" AS map, 1 AS priority UNION ALL
        SELECT "as" AS regex, "ali sneider" AS map, 2 AS priority)
),

X AS (
SELECT s.*,
       CASE WHEN regexp_contains(s.campaign_name, concat('(^|_)',r.regex,'($|_)')) THEN r.map ELSE NULL END AS map,
       ROW_NUMBER() OVER (PARTITION BY s.campaign_name ORDER BY regexp_contains(s.campaign_name, concat('(^|_)',r.regex,'($|_)')) DESC, r.priority) AS rn
FROM sample_data s
CROSS JOIN regex_mapping r

)

SELECT * EXCEPT (rn)
FROM X
WHERE rn = 1