From: Ogden <onefix@waste.org>
To: pgsql-sql@postgresql.org
Subject: Data Calculation
Date: Mon, 29 Sep 2003 02:02:37 -0500 (CDT)
Message-ID: <Pine.LNX.4.58.0309290155440.2636@waste.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: onefix@waste.org
Subject: Re: Data Calculation
In-Reply-To: <Pine.LNX.4.58.0309290155440.2636@waste.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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