On 2014-08-26 18:00, sqlite-users-requ...@sqlite.org wrote:
SELECT
employee_name,
employee_id,
salary,
rank() OVER(PARTITION BY dept ORDER BY salary DESC),
100.0*salary/sum(salary) OVER (PARTITION BY dept)
FROM employee;
I don't know if the above is valid SQL or not. But is seems like something
somebody might like to do. And it also seems hard to implement.
Yes it's valid. But this one actually is not that difficult to
implement. Basically read rows must be ordered by dept, salary DESC. Then:
- rank() - it only needs to know if dept or salary is different than in
previous row. that's all.
- sum() - probably the easiest way to implement it is:
-- first pass: calculate aggregate and write result to a temporary table
-- second pass: simply read calculated aggregate and append it to the result
Below are explains of both functions implemented in sqlite 3.3.8 based
database.
However it doesn't mean that all window functions are easy to implement.
For example I have no idea how to implement efficiently moving
aggregates (BETWEEN x PRECEDING AND y FOLLOWING) - I think those have to
recalculated for every row separately making whole query really slow
(sum() can be optimized, but most likely most aggregates can't). There
are many more problems. Most databases still haven't implemented
everything from standard.
Explains:
- table definition: create table employee(salary, dept)
- table is already sorted by dept, salary DESC (so no sorting/index is
visible in explains)
- no window specification defaults to ROWS UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING (behavior not specified in a standard)
- especially for rank() there is very little code, but it should be even
shorter (for example opcodes 24, 25, 26 are useless)
SELECT rank() OVER(PARTITION BY dept ORDER BY salary DESC) FROM employee:
addr | opcode | p1 | p2 | p3 |
+----+---------------+-----+----+-----------------+--
0 | Noop | 0 | 0 | |
1 | MemNull | 2 | 0 | |
2 | Goto | 0 | 32 | |
3 | Integer | 2 | 0 | |
4 | OpenRead | 0 | 2 | |
5 | SetNumColumns | 0 | 2 | |
6 | MemInt | 0 | 1 | |
7 | Rewind | 0 | 30 | |
8 | MemLoad | 5 | 0 | |
9 | Column | 0 | 1 | |
10 | Ne | 512 | 12 | collseq(BINARY) |
11 | Goto | 0 | 15 | |
12 | MemNull | 2 | 0 | |
13 | Column | 0 | 1 | |
14 | MemStore | 5 | 1 | |
15 | MemLoad | 6 | 0 | |
16 | Column | 0 | 0 | |
17 | Ne | 512 | 20 | collseq(BINARY) |
18 | MemInt | 0 | 4 | |
19 | Goto | 0 | 23 | |
20 | MemInt | 1 | 4 | |
21 | Column | 0 | 0 | |
22 | MemStore | 6 | 1 | |
23 | WindowStep | 2 | 0 | rank(0) |
24 | MemStore | 3 | 0 | |
25 | Pop | 1 | 0 | |
26 | MemLoad | 3 | 0 | |
27 | Callback | 1 | 0 | |
28 | MemIncr | 1 | 1 | |
29 | Next | 0 | 8 | |
30 | Close | 0 | 0 | |
31 | Halt | 0 | 0 | |
32 | Transaction | 2 | 0 | |
33 | VerifyCookie | 2 | 1 | |
34 | Goto | 0 | 3 | |
SELECT sum(salary) OVER (PARTITION BY dept) FROM employee:
addr | opcode | p1 | p2 | p3 |
+----+-------------------+-----+----+-----------------+--
0 | Noop | 0 | 0 | |
1 | OpenEphemeralList | 2 | 2 | |
2 | MemInt | 1 | 2 | |
3 | MemNull | 3 | 0 | |
4 | MemInt | -1 | 0 | |
5 | Goto | 0 | 61 | |
6 | Integer | 2 | 0 | |
7 | OpenRead | 0 | 2 | |
8 | SetNumColumns | 0 | 2 | |
9 | Rewind | 0 | 35 | |
10 | IfMemPos | 2 | 28 | |
11 | MemLoad | 4 | 0 | |
12 | Column | 0 | 1 | |
13 | Ne | 512 | 17 | collseq(BINARY) |
14 | Column | 0 | 0 | |
15 | AggStep | 3 | 1 | sum(1) |
16 | Goto | 0 | 33 | |
17 | AggFinal | 3 | 0 | sum(1) |
18 | MemLoad | 0 | 0 | |
19 | MemLoad | 3 | 0 | |
20 | MakeRecord | 1 | 0 | |
21 | Insert | 2 | 0 | |
22 | MemNull | 3 | 0 | |
23 | Column | 0 | 0 | |
24 | AggStep | 3 | 1 | sum(1) |
25 | Column | 0 | 1 | |
26 | MemStore | 4 | 1 | |
27 | Goto | 0 | 33 | |
28 | MemInt | 0 | 2 | |
29 | Column | 0 | 0 | |
30 | AggStep | 3 | 1 | sum(1) |
31 | Column | 0 | 1 | |
32 | MemStore | 4 | 1 | |
33 | MemIncr | 1 | 0 | |
34 | Next | 0 | 10 | |
35 | Close | 0 | 0 | |
36 | AggFinal | 3 | 0 | sum(1) |
37 | MemLoad | 0 | 0 | |
38 | MemLoad | 3 | 0 | |
39 | MakeRecord | 1 | 0 | |
40 | Insert | 2 | 0 | |
41 | Integer | 2 | 0 | |
42 | OpenRead | 0 | 2 | |
43 | SetNumColumns | 0 | 2 | |
44 | MemInt | 0 | 1 | |
45 | Rewind | 2 | 0 | |
46 | Rewind | 0 | 59 | |
47 | Column | 2 | 0 | |
48 | RealAffinity | 0 | 0 | |
49 | Rowid | 2 | 0 | |
50 | MemLoad | 1 | 0 | |
51 | Ne | 100 | 53 | |
52 | Next | 2 | 53 | |
53 | MemStore | 5 | 0 | |
54 | Pop | 1 | 0 | |
55 | MemLoad | 5 | 0 | |
56 | Callback | 1 | 0 | |
57 | MemIncr | 1 | 1 | |
58 | Next | 0 | 47 | |
59 | Close | 0 | 0 | |
60 | Halt | 0 | 0 | |
61 | Transaction | 2 | 0 | |
62 | VerifyCookie | 2 | 1 | |
63 | Goto | 0 | 6 | |
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
