MySQL: reusing user defined variables in a single SELECT statement
I did a search on stackoverflow but couldn't find the exact answer to my question, so please help me if you can.
"I want" to build a SQL statement like this:
SELECT @tax1 := (complicated calculation formula), @owe := (another complicated calculation formula), IF(@owe=0, 0,@tax1/@owe) FROM ...
However, in the MySQL documentation regarding user-defined variables , it advises against it:
Generally, you should never assign a value to a user variable and read the value within the same statement. You may get the expected results, but this is not guaranteed. The order of evaluation of expressions with user-defined variables is undefined and can be changed based on the elements contained in this operator; In addition, this order is not guaranteed to be the same between MySQL Server versions. In SELECT @a, @a: = @a + 1, ..., you might think that MySQL will evaluate @a first and then do the second lesson. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to choose an execution plan with a different order of evaluation.
My goal is to avoid copying and pasting very complex formulas because it makes it difficult to read, and if every formula changes, I need to make sure it's changed in all places - BUT I really know, Work well.
Also, I know ALIAS doesn't work because aliases only work in GROUP BY, HAVING and ORDER clauses.
I read in some other posts to execute a subquery first, to calculate @ tax1 and @owe first, and then use another query to combine the results. BUT I think the performance might be less efficient than just copying and pasting these formulas in place.
Does anyone have a suggestion on what they will do? Or am I sticking with the choice between readability and performance?
Thanks in advance.
source to share
Yes, the only way to do this in SQL without user-defined variables is to write a derived subquery of the table. Then you can use column aliases to refer to the results of these complex expressions:
SELECT tax1, owe, IF(owe=0, 0,tax1/owe) AS ratio FROM ( SELECT (complicated calculation formula) AS tax1, (another complicated calculation formula) AS owe FROM ... ) AS _sub
MySQL has some issues with subquery optimization, but the derived table case is not one of the bad cases.
A warning about MySQL and subqueries is the use of subqueries in range conditions in the WHERE clause. MySQL cannot figure out that the subquery is constant, and it will re-evaluate the subquery as a dependent subquery even if it is not needed.
source to share
I regularly use variables in select statements for sequential scoring, aggregation, group statistics, and data classification.
This is an example:
MySQL Create a list of top X records for each category by concatenating each record with its previous difference
create table if not exists closemovers engine=memory select code , date , close , rank , prevclose , sign , cumm from ( select `code` , `date` , `close` , @rank := if( @code = code , @rank + 1 , 1) as rank , @prevclose := if( @code = code , cast( @prclose as decimal(10,3) ), null) as prevclose , if(@code = code, sign( @prclose - close), NULL) as sign , @cumm := if(@code = code and @psign = sign(@prclose - close), @cumm + 1 , 1) as cumm , @psign := sign(@prclose - close) , @code := code , @prclose := close from companyhistory order by code, date ) as ranked join pricefeed using (code) where rank < 10 and sign is not null ;
I hope this can give you a hint
source to share