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