April 8, 2013 at 3:30 am
Due to my lack of expertise with MySQL I have two rather basic questions.
I use this query to give me the results I need, but I have to adjust the bold figures manually:
SELECT computer_ID, 100 - ( nr -1) * (100/157), 100 - (nr_rpi - 1)*(100/157), ... FROM xyz WHERE Version LIKE 6.0 ORDER BY nr
The problem is the number 157. Ideally this should read 'COUNT(*)-1' or 'MAX(nr)-1'. But due to the aggregate nature this only gives me a single row, not the complete list. What I need to do is change absolute rankings on different variables to a scale of 0 to 100. I have - in this example - rankings from 1 to 158 (1 being top) and I need to adjust them to a scale of 100 (100 being top) to 0.
What do I need to change in this query?
I need to generate the rankings used above and currently I use manual labor to create these, but obviously it is far from ideal.
This is what I use to manually generate these ranking figures:
First empty Benchmark_results. Then get rank for total time:
Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)
SELECT @rank:=@rank+1 AS Rank,
@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,
@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,
@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,
@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,
@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,
Ref_ID
FROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS i
WHERE 1 ORDER BY total_time, crpi, cgain ASC LIMIT 0, 5000
Now update rank for total time:
UPDATE Personal_data, Benchmark_results
SET Personal_data.nr=Benchmark_results.RankCS6
WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0
Now empty Benchmark_results again and repeat for crpi:
Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)
SELECT @rank:=@rank+1 AS Rank,
@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,
@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,
@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,
@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,
@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,
Ref_ID
FROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS i
WHERE 1 ORDER BY crpi, total_time, cgain ASC LIMIT 0, 5000
Now update rank for crpi only:
UPDATE Personal_data, Benchmark_results
SET Personal_data.nr_rpi=Benchmark_results.nr_rpi
WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0
Now repeat for Disk_io:
Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)
SELECT @rank:=@rank+1 AS Rank,
@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,
@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,
@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,
@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,
@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,
Ref_ID
FROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS i
WHERE 1 ORDER BY Diskio, crpi, total_time, cgain ASC LIMIT 0, 5000
And update rank for Diskio:
UPDATE Personal_data, Benchmark_results
SET Personal_data.nr_disk=Benchmark_results.nr_disk
WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0
Repeat for dvd:
Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)
SELECT @rank:=@rank+1 AS Rank,
@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,
@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,
@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,
@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,
@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,
Ref_ID
FROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS i
WHERE 1 ORDER BY dvd, crpi, total_time, cgain ASC LIMIT 0, 5000
And update rank for dvd:
UPDATE Personal_data, Benchmark_results
SET Personal_data.nr_mpeg=Benchmark_results.nr_dvd
WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0
Repeat for h264:
Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)
SELECT @rank:=@rank+1 AS Rank,
@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,
@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,
@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,
@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,
@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,
Ref_ID
FROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS i
WHERE 1 ORDER BY h264, crpi, total_time, cgain ASC LIMIT 0, 5000
And update rank for h264:
UPDATE Personal_data, Benchmark_results
SET Personal_data.nr_h264=Benchmark_results.nr_h264
WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0
Now clean up again:
Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID, Total, RPI, Gain)
SELECT @rank:=@rank+1 AS Rank,
@nr:=CASE WHEN Version LIKE 6.0 THEN @nr+1 ELSE @nr END AS RankCS6,
@nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @nr_rpi+1 ELSE @nr_rpi END AS nr_rpi,
@nr_disk:=CASE WHEN Version LIKE 6.0 THEN @nr_disk+1 ELSE @nr_disk END AS nr_disk,
@nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @nr_mpeg+1 ELSE @nr_mpeg END AS nr_dvd,
@nr_h264:=CASE WHEN Version LIKE 6.0 THEN @nr_h264+1 ELSE @nr_h264 END AS nr_h264,
Ref_ID, Diskio+dvd+h264+IFNULL(MPE_On,MPE_Off) As Total, FORMAT((50.893*Diskio+158.333*dvd+77.027*h264+950*IFNULL(MPE_On,MPE_O ff))/12138.0,4) As RPI, FORMAT(MPE_Off/IFNULL(MPE_On,MPE_Off),3) As Gain
FROM Personal_data, (SELECT @rank:=0, @nr:=0, @nr_rpi:=0, @nr_disk:=0, @nr_mpeg:=0, @nr_h264:=0 AS i) AS i
WHERE 1 ORDER BY total_time, crpi, cgain ASC LIMIT 0, 5000
This of course is far from ideal, but the only way I figured out that works, but among you geniuses there must be someone who can help me optimize this workflow and I'm open to all suggestions.
April 8, 2013 at 3:51 am
This is a SQL Server forum so TSQL is the flavour of SQL used not MySQL
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
April 8, 2013 at 3:52 am
This is a Microsoft SQL Server forum, as such most people here won't be all that familiar with MySQL. You will likely get far better, far faster answers somewhere like http://forums.mySQL.com or http://dbforums.com
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply