📄 readme_sqlite_tutorial.html
字号:
<p>
First take the Cartesian product to show all
possible combinations of x and y.
</p>
<pre>
sqlite> .header on
sqlite> .mode column
sqlite> select x.value,y.value from logic x, logic y;
x.value y.value
---------- ----------
1 1
1 0
1 -1
0 1
0 0
0 -1
-1 1
-1 0
-1 -1
</pre>
<p>
Then after the sign function is created, which we
will do in the next section, using the above table
we could examin Delta[x!=y] as follows;
</p>
<pre>
sqlite> .header on
sqlite> .mode column
sqlite> select x.value,y.value,abs(sign(x.value-y.value)) from logic x, logic y;
x.value y.value abs(sign(x.value-y.value))
---------- ---------- --------------------------
1 1 0
1 0 1
1 -1 1
0 1 1
0 0 0
0 -1 1
-1 1 1
-1 0 1
-1 -1 0
</pre>
<p>
Note above that every time x is not equal to y, abs(sign(x.value-y.value))
returns a 1. After the sign function is created,
these example will run. This is extremely powerful. To show
that we have created a condition statement without using
the where or group by statements, consider the following
example. The z.value will only be displayed in the right hand
column, only when x.value != y.value.
</p>
<pre>
sqlite> select x.value,y.value,z.value,
z.value*abs(sign(x.value-y.value))
from logic x, logic y, logic z;
x.value y.value z.value z.value*abs(sign(x.value-y.value))
---------- ---------- ---------- ----------------------------------
1 1 1 0
1 1 0 0
1 1 -1 0
1 0 1 1
1 0 0 0
1 0 -1 -1
1 -1 1 1
1 -1 0 0
1 -1 -1 -1
0 1 1 1
0 1 0 0
0 1 -1 -1
0 0 1 0
0 0 0 0
0 0 -1 0
0 -1 1 1
0 -1 0 0
0 -1 -1 -1
-1 1 1 1
-1 1 0 0
-1 1 -1 -1
-1 0 1 1
-1 0 0 0
-1 0 -1 -1
-1 -1 1 0
-1 -1 0 0
-1 -1 -1 0
</pre>
<p>
<h2>
Modifying the Source: Creating a Permanent Sign Function
</h2>
<p></p>
<p>
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.
</p>
<p>
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
anytime a new version of sqlite is released.
</p>
<p>
You will want to follow these steps. First copy the abs function
"absFunc" and make the following changes:
<OL>
<LI> Change function name from absFunc to signFunc </LI>
<LI> The variable iVal will need to be changed. iVal
should equal -1 if sqlite3_value_type(argv[0]) is
less than zero. Note this value here 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 simply be expressed as follows:
<pre>
iVal = ( iVal > 0) ? 1 : ( iVal < 0 ) ? -1 : 0;
</pre>
</LI>
<LI> Perform the same steps above for rVal, which is the
real value, as opposed to the integer value above.
<pre>
rVal = ( rVal > 0) ? 1 : ( rVal < 0 ) ? -1 : 0;
</pre>
</LI>
<LI> Add the following entry in aFuncs[]
<pre>
{ "sign", 1, 0, SQLITE_UTF8, 0, signFunc },
</pre>
</LI>
<LI> Recompile sqlite from the main directory and install.
<pre>
$ ./configure
$ make && make install
</pre>
</LI>
</OL>
For a closer look, below is the section that changed.
Look here for the complete file
<a href=http://souptonuts.sourceforge.net/code/func.c.html>func.c
</a>.
<p></p>
<pre>
From <a href=http://souptonuts.sourceforge.net/code/func.c.html>./src/func.c</a>
... 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 ...
</pre>
<p>
<h3>Using the New Sign Function</h3>
<p></p>
<p>
Now, back to the problem of creating a pivot table for displaying
exam scores in spreadsheet like format. First, more data is
is needed. By the way, if have not added any data, the following script
<a href=http://souptonuts.sourceforge.net/code/enterExamdata.html>enterExamdata</a>
will create the necessary tables and insert the data.
</p>
<pre>
$ 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)"
</pre>
<p>
Below is the select statement for generating
a pivot table for 4 exams on the table exams.
</p>
<pre>
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;
</pre>
<p>
Shown below is the select statement, like the statement
above; however, it works on the virtual table, or the
combined exam tables from database examdatabase and
examdatabase2.
</p>
<pre>
$ 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>
</pre>
<p>
Taking a closer look at the results, it's very easy to see
that Anderson, Bob got a 75 on the first exam, 82 on the
second, 92 on the third, and 95 on the forth. Likewise,
Carer received 88,90,92 and 95 respectively.
</p>
<pre>
ln fn exam1 exam2 exam3 exam4
---------- ---------- ---------- ---------- ---------- ----------
Anderson Bob 75 82 92 95
Carter Sue 89 100 99 95
Stoppard Tom 88 90 92 95
</pre>
<p>
Now back to the question of finding the top scores for each
exam in one select statement. That is, find the
top scores for the combined tables. First, a look at all the data.
</p>
<pre>
$ 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
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -