Reputation: 919
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
Upvotes: 0
Views: 139
Reputation: 10226
:=
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
Upvotes: 1
Reputation: 51948
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
Upvotes: 1