如何对SQL分组后的用户标签频率结果计算百分位数？

首先，修正你的基础查询：你之前的GROUP BY "tag"是错误的，应该按userid分组，才能得到每个用户的标签设置频率。正确的基础查询如下：

SELECT "userid", COUNT(*) AS frequency
FROM "tag"
GROUP BY "userid"

执行后会得到完整的用户频率数据：

userid | frequency
123    | 2
211    | 1
213    | 1
215    | 1

接下来，基于这个结果计算百分位数，不同数据库的实现略有差异，以下是几种主流数据库的方案：

PostgreSQL #

使用PERCENTILE_CONT（连续型百分位数）或PERCENTILE_DISC（离散型百分位数）函数，示例计算25th、50th、75th百分位数：

SELECT
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY frequency) AS p25,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY frequency) AS p50,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY frequency) AS p75
FROM (
  SELECT COUNT(*) AS frequency
  FROM "tag"
  GROUP BY "userid"
) AS user_frequencies;

如果需要离散型结果，将PERCENTILE_CONT替换为PERCENTILE_DISC即可。

MySQL 8.0+ #

可以使用PERCENTILE_CONT计算整体百分位数，或者用PERCENT_RANK获取每个用户频率对应的百分位排名：

计算整体百分位数

SELECT
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY frequency) OVER () AS p25,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY frequency) OVER () AS p50,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY frequency) OVER () AS p75
FROM (
  SELECT COUNT(*) AS frequency
  FROM `tag`
  GROUP BY `userid`
) AS user_frequencies
LIMIT 1;

获取每个用户的百分位排名

SELECT
  userid,
  frequency,
  PERCENT_RANK() OVER (ORDER BY frequency) * 100 AS percentile_rank
FROM (
  SELECT userid, COUNT(*) AS frequency
  FROM `tag`
  GROUP BY userid
) AS user_frequencies;

SQL Server #

使用PERCENTILE_CONT函数计算指定百分位数：

SELECT
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY frequency) AS p25,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY frequency) AS p50,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY frequency) AS p75
FROM (
  SELECT COUNT(*) AS frequency
  FROM [tag]
  GROUP BY [userid]
) AS user_frequencies;

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