CODEWITHSUNDEEP
mysql
arunondeck
arunondeck

Reputation: 368

Create a mysql function that accepts a result set as parameter?

Is it possible to create a mySQL function that accepts as a parameter the result set from a query?

Basically I have a lot of queries that will return a result result set as follows:

  id   |  score
  70   |   25
  71   |    7
  72   |  215
  74   |   32
  75   |  710
  76   |   34
  78   |  998
  79   |  103
  80   |    3

I want to normalize the values such that they come to a range between 0 and 1. The way I thought I'd do this was by applying calculation:

nscore = (score-min(score))/(max(score) - min(score))

to get following result

  id   |  score
  70   |  0.022
  71   |  0.004
  72   |  0.213
  74   |  0.029
  75   |  0.710
  76   |  0.031
  78   |  1.000
  79   |  0.100
  80   |  0.000

But I'm not able to come up with a query to get the min and max in this query along with results, hence thought of using a function (cannot use stored procedure) but couldn't documentation on how to pass a result set.

Any help appreciated!
Thanks!

EDIT: The score field in result is a computed field. Cannot select it directly.

For eg: Sample query that returns the above result - select t.id as id, count(*) as score from tbl t inner join tbl2 t2 on t.idx = t2.idx where t2.role in (.....) just for demo purpose, not actual schema or query

Upvotes: 2

Views: 258

Answers (1)

spencer7593
spencer7593

Reputation: 108370

No. MySQL doesn't support defining a function with a resultset as an argument.

Unfortunately, MySQL does not support Common Table Expression (CTE), and does not support Analytic functions.

To get this result from a MySQL query... one way to do that in MySQL would require the original query to be returned as an inline view, two times ...

As an example:

SELECT t.id
     , (t.score-s.min_score)/(s.max_score-s.min_score) AS normalized_score
  FROM ( 
         -- original query here
         SELECT id, score FROM ...
       ) t
 CROSS
  JOIN ( SELECT MIN(r.score) AS min_score
              , MAX(r.score) AS max_score
           FROM (
                  -- original query here
                  SELECT id, score FROM ...   
                ) r
       ) s
 ORDER BY t.id

EDIT

Based on the query added to the question ... 

SELECT q.id
     , (q.score-s.min_score)/(s.max_score-s.min_score) AS normalized_score
  FROM ( -- original query goes here
         -- ------------------------ 

                  select t.id as id, count(*) as score 
                  from tbl t 
                  inner join tbl2 t2 on t.idx = t2.idx
                  where t2.role in (.....)

         -- ------------------------ 
       ) q
 CROSS
  JOIN ( SELECT MIN(r.score) AS min_score
              , MAX(r.score) AS max_score
           FROM ( -- original query goes here
                  -- ------------------------ 

                  select t.id as id, count(*) as score 
                  from tbl t 
                  inner join tbl2 t2 on t.idx = t2.idx
                  where t2.role in (.....)

                  -- ------------------------ 
                ) r
       ) s
 ORDER BY q.id

Upvotes: 1

Related Questions