What this mysql query mean?

Question

I'm trying to figure out what this query exactly performs. In particular the part in which are used the variables @ and the assignment :=. The first part is quite simple because we have a nested query from derived table t1 but what is not really clear to me is the result of the column rn. Here's the query:

SELECT 
t1.user_id, 
t1.percentage, 
t1.id, 
t1.name,
(@rn := if(@uid = t1.user_id, @rn + 1,
   if(@uid := t1.user_id, 1, 1))
) as rn

FROM

(SELECT 
 pbt.user_id, 
 pbt.percentage, 
 t.id, t.name
 FROM 
 user_purchased_brand_tags AS pbt 
 JOIN tags t on t.id = pbt.tag_id 
 ORDER BY pbt.user_id, pbt.percentage desc) t1

You should use the google or manual for doubts, and there are also a lot on questions in stackoverflow about it, for example: https://stackoverflow.com/questions/37869719/difference-between-and or https://stackoverflow.com/questions/39379659/what-does-the-operator-mean-in-mysql — Ryosaku, Jul 12 '17 at 08:42

score 1 · Accepted Answer · answered Jul 12 '17 at 08:58

It creates a row number that resets to 1 when the user_id changes.

The if function's parameter are (condition, true part, false part).

(@rn := /* assign the new value to the variable @rn */

if(@uid = t1.user_id, @rn + 1, /* when user_id is the same as the value in @uid, return @rn + 1. 
                                  The comparison is done before assigning the value of the current row below. Therefore the @uid variable still holds the value of the previous row */

   if(@uid := t1.user_id, 1, 1)) /* this applies when above condition is not true.
                                    It's a clever combination of assigning the value of the current row to @uid and returning 1 at the same time. */

) as rn

Thank you! This is the answer that I was searching for – UgoL Jul 12 '17 at 09:02 — UgoL, Jul 12 '17 at 09:02

score 1 · Answer 2 · answered Jul 12 '17 at 08:59

:= is an assignement operator

IF() function works like this

IF(expression ,expression_if_true, expression_if_false);

This:

@rn := if(@uid = t1.user_id, @rn + 1,
   if(@uid := t1.user_id, 1, 1))

can be decomposed like that in PHP/C style :

if (@uid == t1.user_id) {  // <------ t1.user_id is COMPARED with @uid

    @rn = @rn + 1 ; // or @rn++, ie we increment it by 1

}else{

        if (@uid = t1.user_id) { // <------ t1.user_id is ASSIGNED to @uid

            @rn = 1;

        }else{

            @rn = 1;

        }

}

The second if assign always the same value 1 to @rn but it also assign the value of t1.user_id to @uid

What this mysql query mean?

2 Answers2