pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Data Calculation
2+ messages / 2 participants
[nested] [flat]
* Re: Data Calculation
@ 1970-01-01 00:00 Christoph Haller <ch@rodos.fzk.de>
parent: Ogden <onefix@waste.org>
0 siblings, 0 replies; 2+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: onefix@waste.org; +Cc: pgsql-sql
According to your scenario I did the following
create table answerkey (
question_number int,
answer varchar(2));
create table studentanswer (
student_id varchar(6),
answer01 varchar(2),
answer02 varchar(2));
insert into answerkey values(1,'A');
insert into answerkey values(2,'B');
insert into studentanswer values ('010019','B','C');
insert into studentanswer values ('029393','B','B');
create view studentanswer_boolean as
select student_id,
answer01 = (select answer from answerkey where question_number=1) as a01,
answer02 = (select answer from answerkey where question_number=2) as a02
from studentanswer ;
select * from studentanswer_boolean ;
create view studentanswer_numeric as
select student_id,
case when answer01 = (select answer from answerkey where question_number=1) then 1 else 0 end as a01,
case when answer02 = (select answer from answerkey where question_number=2) then 1 else 0 end as a02
from studentanswer ;
select * from studentanswer_numeric ;
create view studentanswer_sumcorrect as
select student_id,
(case when answer01 = (select answer from answerkey where question_number=1) then 1 else 0 end)+
(case when answer02 = (select answer from answerkey where question_number=2) then 1 else 0 end)
as "sum_of_correct_answers"
from studentanswer ;
HTH
About your second scenario:
Have you seen PL/Perl - Perl Procedural Language within the documentation?
Regards, Christoph
> I need some help for which I could not find much help for within the
> Postgres book I am looking at, or the online tutorial. I don't know if
> this is even possible, but here goes...
>
> I am writing for advice, as the method I followed is not the most
> effecient, I think.
>
> Scenario: Grading results. I have two tables set up - one with the
> answerkey and one with the students results from a test:
>
> answerkey:
>
> question_number int,
> answer varchar(2)
>
> So something like this (select * from answerkey):
>
> 1 | 2 | 3 ...... | 30
> ------------------
> A | B | C..... | D
>
> Student results are similar as the answerkey table, but contain a unique
> student id:
>
> student_id | 1 | 2 | 3 .....
> -------------------------
> 010019 | B | C | C ....
> 029393 | B | B | C.....
>
> Currently, to calculate how each student did, within Perl, I obtain the
> table results in an array and do an array calculation:
>
> if ($student_answer->[$i] eq $correct_answer[$i-1]){$answer_correct++;}
>
> This works fine and is quite fast, though could postgres be used to do
> this faster?
>
> The reason being is that once I have the number of correct answers for a
> student, I then calculate the % score for each student, based on the
> weight of the question (also in another table).
>
> Now, all this data that Perl calculates is displayed for the end user in a
> table. The user can also sort by a field, such as %.
>
> Because Perl did the % calculations, I have to re-calculate everything and
> dump it into a temporary table, to make sorting easier:
>
> student_id, answer_correct, weights_score, percentage_score
>
> Then, if the user wants to sort by the percentage field, I do a select *
> from temp_answers order by $field.
>
> This works fine, and of a class with 500 students, all this happens in
> about 10 seconds.
>
> I am new to the Postgres world, and am just wondering: is there any way I
> can make Postgres do all the calculations, therefore, bypassing the need
> to create a temporary table upon every lookup, just for sorting purposes?
>
> A second scenario is this. I have a list of 12,000 students. The end user
> selects what criteria to search for (ie. to look up students belonging in
> a certain class and/or teacher). The select is fine and works, however,
> then the user needs to be taken to a reports page, where this a different
> Perl program running for each different report.
>
> How do I pass this SQL statement to the perl programs? Currently, I select
> the students that match the criteria and put their IDs into a temporary
> table and pass the name of this table name to the other perl programs. Is
> there a way to bypass this creation of a table?
>
> Thank you very much for your time.
>
> Ogden Nefix
>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Data Calculation
@ 2003-09-29 07:02 Ogden <onefix@waste.org>
0 siblings, 1 reply; 2+ messages in thread
From: Ogden @ 2003-09-29 07:02 UTC (permalink / raw)
To: pgsql-sql
I need some help for which I could not find much help for within the
Postgres book I am looking at, or the online tutorial. I don't know if
this is even possible, but here goes...
I am writing for advice, as the method I followed is not the most
effecient, I think.
Scenario: Grading results. I have two tables set up - one with the
answerkey and one with the students results from a test:
answerkey:
question_number int,
answer varchar(2)
So something like this (select * from answerkey):
1 | 2 | 3 ...... | 30
------------------
A | B | C..... | D
Student results are similar as the answerkey table, but contain a unique
student id:
student_id | 1 | 2 | 3 .....
-------------------------
010019 | B | C | C ....
029393 | B | B | C.....
Currently, to calculate how each student did, within Perl, I obtain the
table results in an array and do an array calculation:
if ($student_answer->[$i] eq $correct_answer[$i-1]){$answer_correct++;}
This works fine and is quite fast, though could postgres be used to do
this faster?
The reason being is that once I have the number of correct answers for a
student, I then calculate the % score for each student, based on the
weight of the question (also in another table).
Now, all this data that Perl calculates is displayed for the end user in a
table. The user can also sort by a field, such as %.
Because Perl did the % calculations, I have to re-calculate everything and
dump it into a temporary table, to make sorting easier:
student_id, answer_correct, weights_score, percentage_score
Then, if the user wants to sort by the percentage field, I do a select *
from temp_answers order by $field.
This works fine, and of a class with 500 students, all this happens in
about 10 seconds.
I am new to the Postgres world, and am just wondering: is there any way I
can make Postgres do all the calculations, therefore, bypassing the need
to create a temporary table upon every lookup, just for sorting purposes?
A second scenario is this. I have a list of 12,000 students. The end user
selects what criteria to search for (ie. to look up students belonging in
a certain class and/or teacher). The select is fine and works, however,
then the user needs to be taken to a reports page, where this a different
Perl program running for each different report.
How do I pass this SQL statement to the perl programs? Currently, I select
the students that match the criteria and put their IDs into a temporary
table and pass the name of this table name to the other perl programs. Is
there a way to bypass this creation of a table?
Thank you very much for your time.
Ogden Nefix
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-09-29 07:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-29 07:02 Data Calculation Ogden <onefix@waste.org>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox