SQL实现dplyr summarise等价功能：统计颜色占比生成汇总表

报错原因说明 #

你之前的SQL执行报错是因为使用了count(*)聚合函数时，SELECT后声明的type、category为非聚合字段，且没有添加对应的GROUP BY子句，不符合SQL聚合查询的语法要求；同时你预期的输出结果不需要返回type、category字段，无需在查询中声明这两个字段。

通用实现方案 #

支持窗口函数的现代数据库（MySQL 8.0+、PostgreSQL、SQL Server、Hive等） #

用窗口函数直接计算全表总记录数，写法更简洁：

SELECT
  color,
  COUNT(*) AS count,
  ROUND(COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (), 2) AS prop
FROM df
GROUP BY color
ORDER BY count DESC;

• 语句说明：
  • GROUP BY color按颜色字段分组，COUNT(*)统计每个颜色对应的记录数
  • SUM(COUNT(*)) OVER ()为窗口函数，直接计算所有分组的计数总和，即全表总记录数
  • 乘以1.0是为了避免部分数据库的整数除法规则导致结果取整的问题
  • ROUND(...,2)控制比例保留2位小数，可根据需求调整精度或直接移除

不支持窗口函数的低版本数据库（如MySQL 5.x） #

通过子查询先获取全表总记录数再计算比例：

SELECT
  color,
  COUNT(*) AS count,
  ROUND(COUNT(*) * 1.0 / total_cnt, 2) AS prop
FROM df, (SELECT COUNT(*) AS total_cnt FROM df) AS total
GROUP BY color
ORDER BY count DESC;

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