vendredi 14 août 2020

percent quotas for a SQL statement [closed]

I currently have this sql statement:

$sql = "SELECT question, answerA, answerB, answerC, answerD, correctanswer 
        FROM goodquestions 
        WHERE type = '3' 
        ORDER BY RAND() 
        LIMIT 0,20"; 

In my database each question has 4 answer choices and a characteristic called "skill". There are 12 values for skill (1,2,3,4,5...). However, I want to implement a quota system. Out of those 20 questions, 5% should be 1, 4% should be 2, 1% should be 3, etc, etc. The values are irrelevant. How would I implement a quota system like this?

EDIT

Sample Data:

create table goodquestions (
  question varchar(100), 
  type int,
  skill int,
  answerA varchar(100), 
  answerB varchar(100), 
  answerC varchar(100), 
  answerD varchar(100), 
  correctanswer int
);

insert into goodquestions (question, type, skill) values
('Question1', '3', 5), ('Question2', '3', 6), ('Question3', '3', 7), 
('Question4', '3', 8), ('Question5', '3', 9), ('Question6', '3', 10), 
('Question7', '3', 11), ('Question8', '3', 12), ('Question9', '3', 13), 
('Question10', '3', 5), ('Question11', '3', 6), ('Question12', '3', 7), 
('Question13', '3', 10), ('Question14', '3', 9), ('Question15', '3', 8), 
('Question16', '3', 11), ('Question17', '3', 12), ('Question18', '3', 13), 
('Question19', '3', 7), ('Question20', '3', 6), ('Question21', '3', 5), 
('Question22', '3', 8), ('Question23', '3', 9), ('Question24', '3', 10), 
('Question25', '3', 13), ('Question26', '3', 12), ('Question27', '3', 11),
('Question28', '3', 5), ('Question29', '3', 6), ('Question30', '3', 7);

CREATE VIEW skill_rates AS
select 5 skill, 0.21 rate union all
select 6, 0.13 union all
select 7, 0.10 union all
select 8, 0.10 union all
select 9, 0.15 union all
select 10, 0.02 union all
select 11, 0.02 union all
select 12, 0.20 union all
select 13, 0.10;

Desired outcome:

Select statement should be made up of 50% of 4-skill questions 30% of 2-skill questions and 20% of 1-skill questions.

Just random numbers to demonstrate.




Aucun commentaire:

Enregistrer un commentaire