loops - 如何在 BigQuery 中引用另一个表作为案例中多个喜欢的来源？

Question

所以我有一张表格，列出了所有类型的 url 类型（例如：Facebook、Instagram、Twitter 等）我想获得类似于以下结果的结果：

select
  url
  ,(case
    when url like '%facebook%' then 'Facebook'
    when url like '%instagram%' then 'Instagram'
    ....
  end) as type

但是问题是列表继续包含大约 30-40 个项目，我不想手动列出它们。有什么办法可以做到这一点？我正在考虑使用循环，但无法正确使用。

谢谢你。

标签： loopsgoogle-bigquery

Answer 1

不完全是您问题的答案，但也许您可以使用REGEXP_EXTRACT作为替代方案：

WITH test_table AS (
  SELECT "https://www.facebook.com/qwe" AS url UNION ALL
  SELECT "http://abc.instagram.com/zxc"
)
SELECT
  url,
  REGEXP_EXTRACT(url, r"^https?:\/\/([^\/?]+)") AS type
FROM test_table

编辑：

与 Michel 的绝妙建议类似，您可以创建包含域列表及其搜索字符串的附加表。但请确保单个 url 不会在多个条件下出现，否则这样的 url 将在输出中重复。

WITH test_table AS (
  SELECT "https://www.facebook.com/qwe" AS url UNION ALL
  SELECT "http://abc.instagram.com/zxc"
),
social_networks AS (
  SELECT '%facebook%' as search_string, 'Facebook' AS type UNION ALL
  SELECT '%instagram%', 'Instagram'
)
SELECT
  url, social_networks.type
FROM test_table JOIN social_networks 
ON test_table.url LIKE social_networks.search_string