BigQuery中结合unnest(split)分组统计客户提问数及高频响应

实现思路 #

• 总提问数是基于每个customer+state分组的原始记录行数统计，和response拆分后的行数无关，不能直接在拆完响应的结果集里统计，需要单独计算或提前用窗口函数固化
• 拆分response_displayed字段后统计各响应在对应分组的出现次数，按次数倒序排序取Top1；遇到并列第一的情况，将所有并列响应用逗号拼接，匹配你给出的样例输出要求

可运行SQL示例 #

WITH
-- 统计每个分组的总提问数
total_question_stats AS (
  SELECT
    customer,
    LOWER(state) AS state,
    COUNT(questions) AS total_question
  FROM `你的业务表名`
  GROUP BY customer, state
),
-- 拆分响应并统计每个响应的出现次数
response_count AS (
  SELECT
    t.customer,
    LOWER(t.state) AS state,
    resp,
    COUNT(*) AS resp_cnt
  FROM `你的业务表名` t,
  UNNEST(SPLIT(response_displayed, ',')) AS resp
  GROUP BY customer, state, resp
),
-- 对分组内的响应按出现次数排名，RANK函数会保留并列排名
response_ranked AS (
  SELECT
    customer,
    state,
    resp,
    RANK() OVER (PARTITION BY customer, state ORDER BY resp_cnt DESC) AS rnk
  FROM response_count
)
-- 关联总提问数，拼接所有排名第一的响应
SELECT
  t1.customer,
  t1.state,
  t1.total_question,
  STRING_AGG(t2.resp, ',') AS frequent_response_displayed
FROM total_question_stats t1
LEFT JOIN response_ranked t2
  ON t1.customer = t2.customer AND t1.state = t2.state
WHERE t2.rnk = 1
GROUP BY t1.customer, t1.state, t1.total_question
ORDER BY t1.customer

简化版本（无需处理并列排名场景） #

如果不需要保留并列的响应，只需取任意一个出现次数最高的响应，可以用更简洁的写法：

SELECT
  customer,
  LOWER(state) AS state,
  COUNT(questions) AS total_question,
  ARRAY_AGG(resp ORDER BY resp_cnt DESC LIMIT 1)[OFFSET(0)] AS frequent_response_displayed
FROM (
  SELECT
    customer,
    state,
    questions,
    resp,
    COUNT(*) OVER (PARTITION BY customer, state, resp) AS resp_cnt
  FROM `你的业务表名`,
  UNNEST(SPLIT(response_displayed, ',')) AS resp
)
GROUP BY customer, state

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