使用width_bucket分桶时，如何获取scores表对应行的id列？

我想知道哪些行被分配到了对应的桶中，当前执行的SQL查询如下：

SELECT
   width_bucket(s.score, sl.mins, sl.maxs, 9) as buckets,
   COUNT(*)
FROM scores s
CROSS JOIN scores_limits sl
GROUP BY 1
ORDER BY 1;

实际返回结果：

buckets | count 
 ---------+-------
       1 |    182
       2 |     37
       3 |     46
       4 |     15
       5 |     29
       7 |     18
       8 |     22
      10 |     11
         |     20

我期望能实现类似这样的查询效果：

SELECT buckets FROM buckets_table [...] WHERE scores.id = 1;

请问如何获取scores表中的id列信息？

解决方案 #

• 查看单条记录所属桶
  若仅需查询指定id（比如id=1）对应的桶，直接关联两张表计算即可，无需预先分组：

  SELECT
      s.id,
      width_bucket(s.score, sl.mins, sl.maxs, 9) as buckets
  FROM scores s
  CROSS JOIN scores_limits sl
  WHERE s.id = 1;
  

• 查看所有记录的桶分配情况
  要列出所有scores表的id及其对应桶，去掉分组语句，直接查询每行的计算结果：

  SELECT
      s.id,
      s.score,
      width_bucket(s.score, sl.mins, sl.maxs, 9) as buckets
  FROM scores s
  CROSS JOIN scores_limits sl
  ORDER BY buckets, s.id;
  

• 关联分组结果查看每个桶的具体id
  若需同时看到分组统计数和对应桶内的id集合，可使用数组聚合函数（以PostgreSQL为例）：

  SELECT
      width_bucket(s.score, sl.mins, sl.maxs, 9) as buckets,
      COUNT(*) as count,
      ARRAY_AGG(s.id) as bucket_ids
  FROM scores s
  CROSS JOIN scores_limits sl
  GROUP BY buckets
  ORDER BY buckets;
  

  返回结果会新增bucket_ids列，包含对应桶内的所有id列表。

• 结果异常说明

  • buckets为空的行：表示s.score小于sl.mins或大于sl.maxs，超出了指定的桶范围
  • buckets=10：因设置了9个桶，width_bucket函数会将大于sl.maxs的值分配到第10个桶（若值小于sl.mins通常返回0，你的结果为空可能需要检查sl.mins和s.score的实际数据）

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