How to get columns when using buckets (width

I would like to know which row were moved to a bucket.

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;

My actual return:

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

What I expect to return:

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

How can I get, for example, the column 'id' of table scores?

CodePudding user response：

I believe you can include the id in an array with array_agg. If I recreate your case with

create table test (id serial, score int);
insert into test(score) values (10),(9),(5),(4),(10),(2),(5),(7),(8),(10);

The data is

id | score
---- -------
 1 |    10
 2 |     9
 3 |     5
 4 |     4
 5 |    10
 6 |     2
 7 |     5
 8 |     7
 9 |     8
10 |    10
(10 rows)

Using the following and aggregating the id with array_agg

SELECT
   width_bucket(score, 0, 10, 11) as buckets,
   COUNT(*) nr_ids,
   array_agg(id) agg_ids
FROM test s
GROUP BY 1
ORDER BY 1;

You get

buckets | nr_ids | agg_ids
--------- -------- ----------
      3 |      1 | {6}
      5 |      1 | {4}
      6 |      2 | {3,7}
      8 |      1 | {8}
      9 |      1 | {9}
     10 |      1 | {2}
     12 |      3 | {1,5,10}