Database and table of arbitrary percentages?

Question

This question is kind of complicated and I do not even know how to Google for it in the first place. I am even having loads of difficulty phrasing my question coherently but here goes!

Hi guys, I need some help with some SQL. I am making a database (DB2 Express-C) backend for a company's corporate training software.

The intention is to compare a candidate's score to a 'raw score-percentage value' match such as

eg: Game I
Words identified(raw score) - Percentage value
10 - 0%
40 - 20%
60 - 50%
90 - 80%
200 - 100%

So for example if an employee scores 30 words, according to the values set above, he will be awarded 0% While if another scores 91 words, she will be awarded 80% And yet another scores 300 words, score will be 100%

As the sample above indicates, the series of values is definitely not linear. So therefore I require a table capable of storing raw scores (quantitative or qualitative) paired with a corresponding percentage value for different games.

I thought of a method as follows (feel free to critique) A database table with 20 columns (named 0-100% in 5% intervals i.e. col name 0, 5, 10, 15 .. 100) with a column for the Game ID. Then I figured I can access the column ordinals and iterate while comparing the value to each column. On encountering a value larger than the raw score, the previous column name is taken as the percentage to be awarded. But thinking about it, ordinals seem terribly inefficient/unwieldy.

Then another method I thought of was to use 2 columns, one for the Game ID and another as an array datatype. A fixed array of 20 elements will be defined for each row. Then if the element is NOT NULL, it will be used as the first basis of comparison. The array will be advanced through until a value greater than the candidate's score is encountered. So the previous NON NULL value will be used as the percentage to be allocated.

However, I am pretty dissatisified with such an inelegant solution and I am hoping if you have any better solutions in mind.

Thanks so much all and i really appreciate you reading this and helping me.

I am very keen to learn and so please do critique my methods and thinking.

EDIT!: There seems to be a misunderstanding between me and well intentioned people.

I need to emphasize that the example of words is just an example. There are many other games like number of jumps. number of clicks and etc. So having one two-column table will not be useful cos I will require a value-percentage pair from 0-100% for n games.

Answer 1

If you create a table with the two columns that you specify, one for No Of Words and one for score you can query it like this:

 select max(NoOfWords), Score from table
 where NoOfWords <= UserNumberOfWords

Where usernumberof words is the number of words the user identified. This way you restriuct the result to only those scores that are less than the number of words the user actually got, then finds the max.

Answer 2

Try this:

• Create a table called scorelookup with two columns, called percentage and words. This table has to be filled with your raw score to percentage lookup values
• I'm assuming that you have a table called scores containing all pertinent data (represented by field1,field2 and field3 in the example below), and a column called rawscore, containing the test subject's raw score in words.

Then we just need this query:

select field1, field2, field3,rawscore, (
    select max(percentage) from scorelookup where words < r.rawscore  ) rank
from scores r;

If I understood you question correctly that should do the trick.

Edit (after your edit):

Just as your example is an example, this example is an example.

• Extend table scorelookup with a colum called quiz
• Alter column name words in table scorelookup to scoreable
• Adapt query to, assuming for the sake of this example that we will look at some score called Test2

It should look like this now:

select field1, field2, field3,raw, (
    select max(percentage) from scorelookup where scoreable < r.rawscore and quiz='Test2'  ) rank
from scores r;

OK, there's still some work to to (this is just an example after all) but this should get you started.

Answer 3

I think you just need 2 tables here. The first table has 2 columns called game_id and an optional column called Game_Name or something like that. The second table has 3 columns; game_id, score and percentage. You then just select percentage from table2 where game_id=yourgame and score < yourscore. The first table isn't even really necessary as you could just arbitrarily assign a game ID but a lookup table will be much easier to manage than arbitrary number assigments.

Answer 4

This approach is different in the way the query is written. The table definition is largely similar to other recommendations here.

CREATE TABLE gameScoreThresholds (
game_id INTEGER NOT NULL,
raw_score SMALLINT NOT NULL,
score_percentage SMALLINT NOT NULL
)
;

CREATE UNIQUE INDEX gamescoreidx ON gameScoreThresholds(game_id, raw_score) CLUSTER
;

ALTER TABLE gameScoreThresholds ADD PRIMARY KEY (game_id, raw_score)
;

SELECT score_percentage FROM gameScoreThresholds 
WHERE game_id = ? AND raw_score <= ?
ORDER BY raw_score DESC 
FETCH FIRST ROW ONLY
;