如何在BigQuery中构建星级评分（Star Rating）报表？0-5分转星级方案

嘿，我来帮你搞定这两个BigQuery里的星级报表需求！咱们一步步来拆解实现。

要实现这个效果，咱们可以结合BigQuery的字符串函数、可视化函数（COLOR_SCALE、SPARKLINE）来完成。假设你有一张包含商品/服务名称、用户评分的表your_project.your_dataset.ratings，结构大概是item_name STRING, score FLOAT64, review_date DATE。

实现SQL示例 #

SELECT
  item_name,
  -- 生成星级展示：★代表已获得的星级，☆代表未获得的
  CONCAT(
    REPEAT('★', ROUND(score)),
    REPEAT('☆', 5 - ROUND(score))
  ) AS star_rating,
  -- 颜色渐变：根据score值从0（浅）到5（深）着色
  COLOR_SCALE(score, 0, 5) AS score_color_gradient,
  -- Sparklines：展示该item的评分趋势（按日期排序）
  SPARKLINE(ARRAY_AGG(score ORDER BY review_date)) AS score_trend_sparkline,
  -- 辅助统计值：平均评分、总评数
  ROUND(AVG(score), 2) AS avg_score,
  COUNT(*) AS total_reviews
FROM `your_project.your_dataset.ratings`
GROUP BY item_name
ORDER BY avg_score DESC

关键细节说明 #

• 星级生成：用ROUND(score)把0-5的评分转成整数星级，再用REPEAT生成对应数量的★和☆。如果需要支持半星（比如3.5星），可以调整逻辑为REPEAT('★', FLOOR(score)) || IF(score - FLOOR(score) >=0.5, '½', '') || REPEAT('☆', 5 - CEIL(score))。
• 颜色渐变：COLOR_SCALE函数会根据score的数值范围自动生成渐变背景色，在BigQuery控制台的结果里会直接显示着色效果。
• Sparklines：SPARKLINE接收一个有序的评分数组，这里按review_date排序，生成迷你折线图展示评分变化趋势。

假设你的样本输入是包含name和score的数据集（score范围0-5），咱们需要先把评分映射到具体星级，再统计每个名称下各星级的数量。

实现SQL示例（含样本数据） #

WITH sample_data AS (
  -- 替换成你的实际数据来源
  SELECT '商品A' AS name, 4.8 AS score UNION ALL
  SELECT '商品A' AS name, 5.0 AS score UNION ALL
  SELECT '商品A' AS name, 3.2 AS score UNION ALL
  SELECT '商品B' AS name, 2.5 AS score UNION ALL
  SELECT '商品B' AS name, 4.0 AS score UNION ALL
  SELECT '商品B' AS name, 4.0 AS score UNION ALL
  SELECT '商品B' AS name, 1.8 AS score
),
star_mapping AS (
  SELECT
    name,
    -- 把score映射到1-5星级（这里用自定义分段规则，可按需调整）
    CASE
      WHEN score >= 4.5 THEN 5
      WHEN score >= 3.5 THEN 4
      WHEN score >= 2.5 THEN 3
      WHEN score >= 1.5 THEN 2
      WHEN score >= 0.5 THEN 1
      ELSE 0
    END AS star_rating
  FROM sample_data
)
-- 统计每个名称各星级的数量，并用PIVOT转成列展示
SELECT
  name,
  COALESCE(`1`, 0) AS star_1_count,
  COALESCE(`2`, 0) AS star_2_count,
  COALESCE(`3`, 0) AS star_3_count,
  COALESCE(`4`, 0) AS star_4_count,
  COALESCE(`5`, 0) AS star_5_count,
  COUNT(*) AS total_reviews
FROM star_mapping
PIVOT (
  COUNT(star_rating) FOR star_rating IN (1,2,3,4,5)
)
GROUP BY name, `1`, `2`, `3`, `4`, `5`
ORDER BY total_reviews DESC

关键细节说明 #

• 星级映射规则：这里用了自定义分段（比如4.5及以上算5星），你也可以直接用ROUND(score)得到整数星级，完全根据业务需求调整。
• PIVOT转换：把行式的星级统计转成列，让报表更直观，方便快速查看每个名称的星级分布。用COALESCE处理没有数据的星级，避免出现NULL值。

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