Say I have a Student table, it's got an int ID. I have a fixed set of 10 multiple choice questions with 5 possible answers. I have a normalized answer table that has the question id, the Student.answer (1-5) and the Student.ID
假设我有一个Student表,它有一个int ID。我有一组固定的10个选择题,有5个可能的答案。我有一个标准化的答案表,其中包含问题ID,Student.answer(1-5)和Student.ID
I'm trying to write a single query that will return all scores over a certain pecentage. To this end I wrote a simple UDF that accepts the Student.answers and the correct answer, so it has 20 parameters.
我正在尝试编写一个查询,它将返回超过某个百分比的所有分数。为此我写了一个简单的UDF,接受Student.answers和正确的答案,所以它有20个参数。
I'm starting to wonder if it's better to denormalize the answer table, bring it into my applcation and let my application do the scoring.
我开始怀疑是否更好地对答案表进行反规范化,将其纳入我的应用程序并让我的应用程序进行评分。
Anyone ever tackle something like this and have insight?
有人曾经解决过这样的问题吗?
6 个解决方案
#1
2
If I understand your schema and question correctly, how about something like this:
如果我理解你的架构和问题,那么这样的事情怎么样:
select student_name, score
from students
join (select student_answers.student_id, count(*) as score
from student_answers, answer_key
group by student_id
where student_answers.question_id = answer_key.question_id
and student_answers.answer = answer_key.answer)
as student_scores on students.student_id = student_scores.student_id
where score >= 7
order by score, student_name
That should select the students with a score of 7 or more, for example. Just adjust the where clause for your purposes.
例如,那应该选择得分为7或更高的学生。只需根据您的目的调整where子句。
#2
1
I would probably leave it up to your application to perform the scoring. Check out Maybe Normalizing Isn't Normal by Jeff Atwood.
我可能会将它留给您的应用程序来执行评分。查看Jeff Atwood的“正常化不正常”。
#3
1
Denormalization is generally considered a last resort. The problem seems very similar to survey applications, which are very common. Without seeing your data model, it's difficult to propose a solution, but I will say that it is definitely possible. I'm wondering why you need 20 parameters to that function?
非规范化通常被认为是最后的手段。问题似乎与调查应用程序非常相似,这些应用程序很常见。如果没有看到您的数据模型,很难提出解决方案,但我会说这绝对是可能的。我想知道为什么你需要20个参数来实现这个功能?
A relational set-based solution will be simpler and faster in most cases.
在大多数情况下,基于关系集的解决方案将更简单,更快捷。
#4
0
The architecture you are talking about could become very cumbersome in the long run, and if you need to change the questions it means more changes to the UDF you are using.
从长远来看,您所谈论的体系结构可能变得非常麻烦,如果您需要更改问题,则意味着您正在使用的UDF会有更多变化。
I would think you could probably do your analysis in code without necessarily de-normalizing your database. De-normalization could also lend to inflexibility, or at least added expense to update, down the road.
我认为您可以在代码中进行分析,而不必对数据库进行反规范化。去标准化也可能导致不灵活,或至少增加更新费用。
#5
0
No way, you definitely want to keep it normalized. It's not even that hard of a query.
没办法,你肯定想让它保持正常化。它甚至不是那么难以查询。
Basically, you want to left join the students correct answers with the total answers for that question, and do a count. This will give you the percent correct. Do that for each student, and put the minimum percent correct in a where clause.
基本上,您希望将学生的正确答案与该问题的总答案联系起来,并进行计数。这将为您提供正确的百分比。为每个学生执行此操作,并将最小百分比放在where子句中。
#6
0
This query should be quite easy... assuming you have the correct answer stored in the question table. You do have the correct answer stored in the question table, right?
此查询应该非常简单...假设您在问题表中存储了正确的答案。你确实在问题表中存储了正确答案,对吧?
#1
2
If I understand your schema and question correctly, how about something like this:
如果我理解你的架构和问题,那么这样的事情怎么样:
select student_name, score
from students
join (select student_answers.student_id, count(*) as score
from student_answers, answer_key
group by student_id
where student_answers.question_id = answer_key.question_id
and student_answers.answer = answer_key.answer)
as student_scores on students.student_id = student_scores.student_id
where score >= 7
order by score, student_name
That should select the students with a score of 7 or more, for example. Just adjust the where clause for your purposes.
例如,那应该选择得分为7或更高的学生。只需根据您的目的调整where子句。
#2
1
I would probably leave it up to your application to perform the scoring. Check out Maybe Normalizing Isn't Normal by Jeff Atwood.
我可能会将它留给您的应用程序来执行评分。查看Jeff Atwood的“正常化不正常”。
#3
1
Denormalization is generally considered a last resort. The problem seems very similar to survey applications, which are very common. Without seeing your data model, it's difficult to propose a solution, but I will say that it is definitely possible. I'm wondering why you need 20 parameters to that function?
非规范化通常被认为是最后的手段。问题似乎与调查应用程序非常相似,这些应用程序很常见。如果没有看到您的数据模型,很难提出解决方案,但我会说这绝对是可能的。我想知道为什么你需要20个参数来实现这个功能?
A relational set-based solution will be simpler and faster in most cases.
在大多数情况下,基于关系集的解决方案将更简单,更快捷。
#4
0
The architecture you are talking about could become very cumbersome in the long run, and if you need to change the questions it means more changes to the UDF you are using.
从长远来看,您所谈论的体系结构可能变得非常麻烦,如果您需要更改问题,则意味着您正在使用的UDF会有更多变化。
I would think you could probably do your analysis in code without necessarily de-normalizing your database. De-normalization could also lend to inflexibility, or at least added expense to update, down the road.
我认为您可以在代码中进行分析,而不必对数据库进行反规范化。去标准化也可能导致不灵活,或至少增加更新费用。
#5
0
No way, you definitely want to keep it normalized. It's not even that hard of a query.
没办法,你肯定想让它保持正常化。它甚至不是那么难以查询。
Basically, you want to left join the students correct answers with the total answers for that question, and do a count. This will give you the percent correct. Do that for each student, and put the minimum percent correct in a where clause.
基本上,您希望将学生的正确答案与该问题的总答案联系起来,并进行计数。这将为您提供正确的百分比。为每个学生执行此操作,并将最小百分比放在where子句中。
#6
0
This query should be quite easy... assuming you have the correct answer stored in the question table. You do have the correct answer stored in the question table, right?
此查询应该非常简单...假设您在问题表中存储了正确的答案。你确实在问题表中存储了正确答案,对吧?