如何用APPROX_QUANTILES在SQL中做用户分群？BigQuery查询优化求助

原查询存在的问题 #

1. 冗余语法：SELECT DISTINCT fullVisitorId 完全多余，因为 GROUP BY fullVisitorId 已经确保每个访客ID唯一，重复去重无意义。
2. 未实现分群需求：原查询仅将分位数阈值附加到每一行数据，但没有为每个用户标记所属的分群区间，无法直接得到用户分群结果。
3. 分位数结果集中的原因：样本数据中绝大多数用户的交易次数为0或1，导致低百分位（20/40/60/80分位）的阈值集中在1，这是数据的真实分布特征，可通过过滤无交易用户优化。

优化后的查询方案 #

方案一：基于APPROX_QUANTILES阈值标记分群

该方案先计算分位数阈值，再通过条件判断为每个用户标记所属分群，同时保留阈值供参考：

WITH transdata AS (
SELECT
  fullVisitorId AS VisitorId,
  COUNT(DISTINCT FORMAT('%s%i', fullVisitorId, visitId)) AS uniqueVisits,
  SUM(totals.transactions) AS total_transactions,
  SUM(totals.totalTransactionRevenue) AS total_transaction_revenue
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _table_suffix BETWEEN '20160801' AND '20170801'
GROUP BY 1
),
percentile_thresholds AS (
  SELECT 
    APPROX_QUANTILES(total_transactions, 100) AS percentiles
  FROM transdata
)
SELECT
  a.*,
  CASE
    WHEN a.total_transactions <= b.percentiles[OFFSET(20)] THEN '0-20分位'
    WHEN a.total_transactions <= b.percentiles[OFFSET(40)] THEN '20-40分位'
    WHEN a.total_transactions <= b.percentiles[OFFSET(60)] THEN '40-60分位'
    WHEN a.total_transactions <= b.percentiles[OFFSET(80)] THEN '60-80分位'
    ELSE '80-100分位'
  END AS transaction_percentile_group,
  b.percentiles[OFFSET(20)] AS v20,
  b.percentiles[OFFSET(40)] AS v40,
  b.percentiles[OFFSET(60)] AS v60,
  b.percentiles[OFFSET(80)] AS v80,
  b.percentiles[OFFSET(100)] AS v100
FROM transdata a
CROSS JOIN percentile_thresholds b
-- 可选：过滤无交易用户，聚焦有下单行为的群体
-- WHERE a.total_transactions > 0
ORDER BY a.total_transactions DESC

方案二：用NTILE直接等分用户群体

如果不需要精确的分位数阈值，可直接用NTILE将用户等分为指定数量的群体，操作更简洁：

WITH transdata AS (
SELECT
  fullVisitorId AS VisitorId,
  COUNT(DISTINCT FORMAT('%s%i', fullVisitorId, visitId)) AS uniqueVisits,
  SUM(totals.transactions) AS total_transactions,
  SUM(totals.totalTransactionRevenue) AS total_transaction_revenue
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _table_suffix BETWEEN '20160801' AND '20170801'
GROUP BY 1
)
SELECT
  *,
  -- 将用户等分为5个群体（对应0-20、20-40等区间）
  NTILE(5) OVER(ORDER BY total_transactions) AS transaction_group,
  -- 可选：查看用户的具体百分位排名
  ROUND(PERCENT_RANK() OVER(ORDER BY total_transactions) * 100, 2) AS transaction_percent_rank
FROM transdata
-- 可选：过滤无交易用户
-- WHERE total_transactions > 0
ORDER BY total_transactions DESC

关键说明 #

• 若想让分群阈值更分散，可添加WHERE total_transactions > 0过滤无交易用户，此时会聚焦有下单行为的用户群体，分位数区间差异更明显。
• APPROX_QUANTILES的第二个参数为分位数数量，返回结果包含从0分位到100分位的共101个值，因此OFFSET(20)对应20分位阈值，OFFSET(100)对应最大值。

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