Procedure SQL (run every second)

Question

Can anyone explain me how this mysql command will work?

 SELECT @row := @row + 1 AS n_linha , m.* FROM
    (SELECT b.user_id as prox_user_id, t.auctionID, t.productID, t.auc_due_price+t.auc_plus_price AS prox_valor, t.auc_due_time, b.id as id_lance, t.auc_plus_time, t.auc_plus_price
    FROM bidbutler b
    inner join c_cron_tempo t on b.auc_id = t.auctionID
    where b.butler_status = 0 and (b.butler_bid-b.used_bids)>0
      and t.auc_due_time < GREATEST(LEAST(TRUNCATE(15 * RAND(),0),15),3)
    Order by b.auc_id, RAND()) m,(SELECT @row :=0) r;

Especially the RAND part, I couldn’t understand.

I know that it performs at various values of the RAND, as I could do also for it to run at a value defined by me without the RAND?

Answer 1

• To function RAND() returns a float from 0 to 1. (example: 0.47806898642151524. Multiplied by 15: 7.171034796322728)

• To function TRUNCATE() cuts a float by a number of houses decimals passed in the second parameter. (example for TRUNCATE(7.171034796322728, 0): 7, leaves zero decimal places)

• To function LEAST() returns the minor of two numbers. (example for LEAST(7,15): 7)

• To function GREATEST() returns the greater of two numbers. (example for GREATEST(7,3): 7)

Therefore, the command:

select GREATEST(LEAST(TRUNCATE(15 * RAND(),0),15),3)

Will return a random number between 3 and 15.

Using the same reasoning to change the range to, for example, between 1 and 5, multiply the result of RAND() by 5, set the ceiling in LEAST() as 5 and the minimum in GREATEST() like 1:

select GREATEST(LEAST(TRUNCATE(5 * RAND(),0),5),1);

(Follows SQL Fiddle by exemplifying the use of each function, as well as followed executions of the above command to demonstrate the degree of randomness obtained: http://sqlfiddle.com/#! 9/340e01/69)

Your full query uses this method to check whether the field auc_due_time is less than any number between 3 and 15.

If you want to fix the number, remove all this block and replace it directly by the number you want to set, for example 10:

...
and t.auc_due_time < 10
...