如何在BigQuery查询中高效筛选特定词汇的出现数量

一、先优化原查询的日期筛选（提升效率的基础） #

原查询里把日期转成字符串用LIKE匹配的方式效率极低，无法利用日期类型的索引，改成直接基于日期范围筛选：

WHERE lines.chart_of_accounts_id = "ID_account"
-- 方式1：直接指定日期范围，性能最优
AND lines.accounting_date BETWEEN '2022-12-01' AND '2022-12-31'
-- 方式2：按月份截断匹配，适合需要批量匹配整月数据的场景
-- AND DATE_TRUNC(lines.accounting_date, MONTH) = DATE('2022-12-01')

二、筛选包含特定词汇的记录 #

针对目标词汇（比如workers），推荐两种高效匹配方式：

1. 大小写敏感匹配 #

如果严格区分大小写，两种写法任选：

• LIKE写法：LOWER(lines.line_description) LIKE '%workers%'
• 正则写法：REGEXP_CONTAINS(lines.line_description, r'workers')

2. 大小写不敏感匹配 #

如果不需要区分大小写（比如Workers/WORKERS都算有效匹配），用带忽略大小写标记的正则：

REGEXP_CONTAINS(lines.line_description, r'(?i)workers')

三、统计特定词汇的出现次数 #

分两种常用场景给出实现：

场景1：筛选含关键词的记录，同时显示单条记录内的关键词出现次数 #

以统计line_description字段中workers的次数为例：

SELECT 
    -- 保留原查询的所有字段
    lines.accounting_book_id      AS Livro
    ,lines.book_name               AS Nome_Livro
    ,lines.period_name             AS Periodo
    ,lines.effective_period_num    AS Num_Periodo  
    ,lines.company_code AS Empresa
    ,lines.brand_code AS Bandeira
    ,lines.product_code AS Produto
    ,lines.account_unit AS Conta_Contabil
    ,lines.account_name_ptb AS Descri_Conta_Contabil
    ,lines.cost_center AS Centro_Custo
    ,lines.intercompany_code AS Intercompany
    ,lines.business_unit AS Unidade_Negocio
    ,lines.future_code AS Futuro2
    ,lines.accounting_date AS Data_Contabil
    ,lines.posted_date AS Data_Postada
    ,lines.currency_code AS Moeda
    ,lines.line_description AS Descri_Linha
    ,lines.batch_name AS Journal_Batch
    ,lines.header_name AS Journal_Entry
    ,lines.supplier AS Fornecedor
    ,lines.item AS Item
    ,lines.period_num AS Mes
    ,lines.created_by AS Criado_Por
    ,lines.line_num AS Num_Lin
    ,lines.posted_by AS Postado_Por
    ,lines.initial_balance AS Saldo_Inicial
    ,lines.entered_debit AS Total_Debito
    ,lines.entered_credit AS Total_Credito
    ,lines.accounted_debit AS Total_Debito_Conv
    ,lines.accounted_credit AS Total_Credito_Conv
    ,lines.final_balance AS Saldo_Final
    ,lines.dre_classification AS Class_DRE
    ,lines.dre6 AS DRE
    -- 新增：统计当前记录中workers的出现次数（大小写不敏感）
    ,ARRAY_LENGTH(REGEXP_EXTRACT_ALL(lines.line_description, r'(?i)workers')) AS workers_count
FROM `my_project` lines
WHERE lines.chart_of_accounts_id  = "ID_account"
AND lines.accounting_date BETWEEN '2022-12-01' AND '2022-12-31'
-- 筛选出至少包含一次workers的记录
AND REGEXP_CONTAINS(lines.line_description, r'(?i)workers')

场景2：按维度分组统计关键词总出现次数 #

比如按公司代码（Empresa）分组，统计各公司下workers的总次数：

SELECT 
    lines.company_code AS Empresa
    ,SUM(ARRAY_LENGTH(REGEXP_EXTRACT_ALL(lines.line_description, r'(?i)workers'))) AS total_workers_count
FROM `my_project` lines
WHERE lines.chart_of_accounts_id  = "ID_account"
AND lines.accounting_date BETWEEN '2022-12-01' AND '2022-12-31'
AND REGEXP_CONTAINS(lines.line_description, r'(?i)workers')
GROUP BY lines.company_code
ORDER BY total_workers_count DESC

四、效率小贴士 #

• 优先用日期原生函数筛选日期，避免类型转换后模糊匹配
• 仅匹配固定词汇时，LIKE性能略优于正则；需复杂匹配规则时用正则更灵活
• 需在多字段中匹配关键词时，可拼接字段后统一匹配：

  REGEXP_CONTAINS(lines.line_description || lines.account_name_ptb, r'(?i)workers')
  

内容的提问来源于stack exchange，提问作者nand_core