|
Page 6 of 12
Modifying the Source: Creating a Permanent Sign Function
Sqlite functions are defined in "./src/func.c". In this file, the name of this function will be "signFunc". The user will call this function in sqlite as sign(n). It will hold only a single variable.
It is helpful to model the sign function after the abs function "absFunc", since they are very similar. If fact, I would highly recommend looking at the abs function any time a new version of sqlite is released.
You will want to follow these steps: First, copy the abs function "absFunc" and make the following changes:
Change the function name from absFunc to signFunc.
Change the variable iVal. It should equal -1 if sqlite3_value_type(argv[0]) is less than zero. Note that this value is an integer. Otherwise, if this integer is zero, return zero. Or if this integer is greater than zero, return 1. All of this can be expressed simply as follows:
iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
Perform the same steps above for rVal, which is the real value, as opposed to the integer value above.
rVal = ( rVal > 0) ? 1: ( rVal < 0 ) ? -1: 0;
Add the following entry in aFuncs[]:
{ "sign", 1, 0, SQLITE_UTF8, 0, signFunc },
Recompile sqlite from the main directory and install.
$ ./configure
$ make && make install
For a closer look, below is the section that changed. Look here for the complete file: func.c.
From ./src/func.c:
... cut ...
/*
** Implementation of the sign() function
*/
static void signFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
assert( argc==1 );
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
i64 iVal = sqlite3_value_int64(argv[0]);
/* 1st change below. Line below was: if( iVal<0 ) iVal = iVal * -1; */
iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
sqlite3_result_int64(context, iVal);
break;
}
case SQLITE_NULL: {
sqlite3_result_null(context);
break;
}
default: {
/* 2nd change below. Line for abs was: if( rVal<0 ) rVal = rVal * -1.0; */
double rVal = sqlite3_value_double(argv[0]);
rVal = ( rVal > 0) ? 1: ( rVal < 0 ) ? -1: 0;
sqlite3_result_double(context, rVal);
break;
}
}
}
... cut ...
} aFuncs[] = {
{ "min", -1, 0, SQLITE_UTF8, 1, minmaxFunc },
{ "min", 0, 0, SQLITE_UTF8, 1, 0 },
{ "max", -1, 2, SQLITE_UTF8, 1, minmaxFunc },
{ "max", 0, 2, SQLITE_UTF8, 1, 0 },
{ "typeof", 1, 0, SQLITE_UTF8, 0, typeofFunc },
{ "length", 1, 0, SQLITE_UTF8, 0, lengthFunc },
{ "substr", 3, 0, SQLITE_UTF8, 0, substrFunc },
{ "substr", 3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr },
{ "abs", 1, 0, SQLITE_UTF8, 0, absFunc },
/* Added here */
{ "sign", 1, 0, SQLITE_UTF8, 0, signFunc },
{ "round", 1, 0, SQLITE_UTF8, 0, roundFunc },
{ "round", 2, 0, SQLITE_UTF8, 0, roundFunc },
... cut ...
[mospagebreak title=Using the New Sign Function}
Using the New Sign Function
Now, back to the problem of creating a pivot table for displaying exam scores in a spreadsheet-like format. First, more data is is needed. By the way, if have not added any data, the following script, enterExamdata, will create the necessary tables and insert the data.
$ sqlite3 examdatabase "insert into exam (ln,fn,exam,score) values ('Anderson','Bob',3,92)"
$ sqlite3 examdatabase "insert into exam (ln,fn,exam,score) values ('Anderson','Bob',4,95)"
$ sqlite3 examdatabase "insert into exam (ln,fn,exam,score) values ('Stoppard','Tom',1,88)"
$ sqlite3 examdatabase "insert into exam (ln,fn,exam,score) values ('Stoppard','Tom',2,90)"
$ sqlite3 examdatabase "insert into exam (ln,fn,exam,score) values ('Stoppard','Tom',3,92)"
$ sqlite3 examdatabase "insert into exam (ln,fn,exam,score) values ('Stoppard','Tom',4,95)"
$ sqlite3 examdatabase2 "insert into exam (ln,fn,exam,score) values ('Carter','Sue',3,99)"
$ sqlite3 examdatabase2 "insert into exam (ln,fn,exam,score) values ('Carter','Sue',4,95)"
Below is the select statement for generating a pivot table for four exams on the table exams.
select ln,fn,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exam group by ln,fn;
Below is the select statement, like the statement above. However, it works on the virtual table, or the combined exam tables from the databases examdatabase and examdatabase2.
$ sqlite3
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> attach database examdatabase as e1;
sqlite> attach database examdatabase2 as e2;
sqlite> .database
seq name file
--- --------------- ----------------------------------------------------------
0 main
2 e1 /work/cpearls/src/posted_on_sf/sqlite_examples/sqlite_exam
3 e2 /work/cpearls/src/posted_on_sf/sqlite_examples/sqlite_exam
sqlite> .header on
sqlite> .mode column
sqlite> select ln,fn,sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
group by ln,fn;
ln fn exam1 exam2 exam3 exam4
---------- ---------- ---------- ---------- ---------- ----------
Anderson Bob 75 82 92 95
Carter Sue 89 100 99 95
Stoppard Tom 88 90 92 95
sqlite>
Taking a closer look at the results, it's very easy to see that Anderson, Bob got 75 on the first exam, 82 on the second, 92 on the third, and 95 on the forth. Likewise, Stoppard received 88, 90, 92, and 95, respectively.
ln fn exam1 exam2 exam3 exam4
---------- ---------- ---------- ---------- ---------- ----------
Anderson Bob 75 82 92 95
Carter Sue 89 100 99 95
Stoppard Tom 88 90 92 95
Now back to the question of finding the top scores for each exam in one select statement. That is, finding the top scores for the combined tables. First, a look at all the data:
$ sqlite3
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> attach database examdatabase as e1;
sqlite> attach database examdatabase2 as e2;
sqlite> .header on
sqlite> .mode column
sqlite> select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam;
db ekey fn ln exam score timeEnter
---------- ---------- ---------- ---------- ---------- ---------- -------------------
e1 1 Bob Anderson 1 75 2004-10-17 22:01:42
e1 2 Bob Anderson 2 82 2004-10-17 22:02:19
e1 3 Bob Anderson 3 92 2004-10-17 22:05:04
e1 4 Bob Anderson 4 95 2004-10-17 22:05:16
e1 5 Tom Stoppard 1 88 2004-10-17 22:05:24
e1 6 Tom Stoppard 2 90 2004-10-17 22:05:31
e1 7 Tom Stoppard 3 92 2004-10-17 22:05:40
e1 8 Tom Stoppard 4 95 2004-10-17 22:05:50
e2 1 Sue Carter 1 89 2004-10-17 22:03:10
e2 2 Sue Carter 2 100 2004-10-17 22:03:10
e2 3 Sue Carter 3 99 2004-10-17 22:05:57
e2 4 Sue Carter 4 95 2004-10-17 22:06:05
sqlite>
Below, continuing with the same attached setup, is an example of horizontal averages and horizontal maximum values.
sqlite> .headers on
sqlite> .mode column
sqlite> select db,ln as lastname,fn as first,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
avg(score) as avg, max(score) as max
from (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
group by ln,fn,db ;
db lastname first exam1 exam2 exam3 exam4 avg max
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---- ----
e1 Anderson Bob 75 82 92 95 86 95
e2 Carter Sue 89 100 99 95 95.75 100
e1 Stoppard Tom 88 90 92 95 91.25 95
Try finding the deltas, or the differences between each exam score. For hints on this, see the end of this article in the LONGWINDED TIPS section.
|
