Changing a Query with a numbered result set (with gaps,) to return result with no gaps, containing every number.

Question

I have a select statement: select a, b, [...]; which returns the results:

a|b
---------
1|8688798
2|355744
4|457437
7|27834

I want it to return:

a|b
---------
1|8688798
2|355744
3|0
4|457437
5|0
6|0
7|27834

An example query that does not do what I would like, since it does not have the gap numbers:

select
    sub.num_of_ratings,
    count(sub.rater)
from
(
    select 
        r.rater_id as rater,
        count(r.id) as num_of_ratings
    from ratings r
    group by rater
) as sub
group by num_of_ratings;

Explanation of the query: If a user rates another user, the rating is listed in the table ratings and the id of the rating user is kept in the field rater_id. Effectively I check for all users who are referred to in ratings and count how many ratings records I find for that user, which is rater / num_of_ratings, and then I use this result to find how many users have rated a given number of times.

At the end I know how many users rated once, how many users rated twice, etc. My problem is that the numbers for count(sub.rater) start fine from 1,2,3,4,5... However, for bigger numbers there are gaps. This is because there might be one user who rated 1028 times - but no user who rated 1027 times.

I don't want to apply stored procedures looping over the result or something like that. Is it possible to fill those gaps in the result without using stored procedures, looping, or creating temporary tables?

score 3 · Answer 1 · edited May 23 '17 at 12:20

If you have a sequence of numbers, then you can do a JOIN with that table and fill in the gaps properly.

You can check out this questions on how to get the sequence: generate an integer sequence in MySQL

Here is one of the answers posted that might be easily used with the limitation that generates numbers from 1 to 10,000:

SELECT @row := @row + 1 as row FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
(SELECT @row:=0) t5

an explanation would be nice - `t - t4` each contain 10 rows of dummy data, cartesian product of these is 10^4, i.e. 10000 rows of dummy data, and the outer select is just mysql version of rownumber — Aprillion, Jun 21 '12 at 15:55

score 0 · Answer 2 · answered Jun 21 '12 at 17:20

Using a sequence of numbers, you can join your result set. For instance, assuming your number list is in a table called numbersList, with column number:

Select number, Count 
from
  numbersList left outer join
  (select
      sub.num_of_ratings,
      count(sub.rater) as Count
  from
  (
      select 
          r.rater_id as rater,
          count(r.id) as num_of_ratings
      from ratings r
      group by rater
  ) as sub
  group by num_of_ratings) as num

on num.num_of_ratings=numbersList.number
where numbersList.number<max(num.num_of_ratings)

Your numbers list must be larger than your largest value, obviously, and the restriction will allow it to not have all numbers up to the maximum. (If MySQL does not allow that type of where clause, you can either leave the where clause out to list all numbers up to the maximum, or modify the query in various ways to achieve the same result.)

Raffael · Accepted Answer · 2012-07-12T09:49:04.457

@mazzucci: the query is too magical and you are not actually explaining the query.

@David: I cannot create a table for that purpose (as stated in the question)

Basically what I need is a select that returns a gap-less list of numbers. Then I can left join on that result set and treat NULL as 0.

What I need is an arbitrary table that keeps more records than the length of the final list. I use the table user for that in the following example:

select @row := @row + 1 as index
    from (select @row := -1) r, users u
limit 101;

This query returns a set of the numbers von 0 to 100. Using it as a subquery in a left join finally fills the gap.

users is just a dummy to keep the relational engine going and hence producing the numbers incrementally.

select t1.index as a, ifnull(t2.b, 0) as b
from (
    select @row := @row + 1 as index
        from (select @row := 0) r, users u
    limit 7
) as t1
left join (
    select a, b [...]
) as t2
on t1.index = t2.a;

I didn't try this very query live, so have merci with me if there is a little flaw. but technically it works. you get my point.

EDIT:

just used this concept to gain a gapless list of dates to left join measures onto it:

select @date := date_add(@date, interval 1 day) as date
    from (select @date := '2010-10-14') d, users u
limit 700

starts from 2010/10/15 and iterates 699 more days.

Changing a Query with a numbered result set (with gaps,) to return result with no gaps, containing every number.

3 Answers3