On Wed, 27 Aug 2014 18:25:28 -0600
"Keith Medcalf" <kmedc...@dessus.com> wrote:
> >> select id, category_id, name, min(price) as minprice
> >> from cat_pictures
> >> group by category_id;
>
> >This peculiar behavior is very unique to SQLite.
>
> Not really. Sybase, SQL Server and DB2 do (or did do) the same thing.
Did, Keith. In 1995. I think you're thinking of Sybase 4.8 or
thereabouts, when we kept the bit bucket next to the coal bin.
> Well, I kind of like the former (group by) behaviour.
I suppose the above query is equal to
select id, a.category_id, name, minprice
from cat_pictures as a
join (
select category_id, min(price) as minprice
from cat_pictures
group by category_id
) as b
on a.category_id = b.category_id;
I half agree with you. I guess the SQL committee decided to make GROUP
BY 100% redundant because erroneous clauses caused subtle errors. I
wish they'd done away with it instead.
You are right that window functions add no new relational power to
SQL. Everything they can express can be expressed using joins and
subqueries.
There are, nevertheless, advantages, because implementations can often
process them faster, because the short road to the answer is more
obvious.
Consider ideas like rank and lag. You can rank and lag using a self
join and a min(). But the system doesn't know that all you want to do
is get "count the rows in this order" or "get this row and the one
before it", both of which are simple enough to do with a single pass
over the table (and without actually executing a join). But every SQL
engine I know of, when it sees JOIN, executes a join. It executes it
as fast as it can, using indexes and hash joins and whatnot, but it
still uses the same table twice to do something that physically
requires only a single pass and some bookkeeping.
Now consider an item from the first link in Petite's helpful list:
# select x,
row_number() over(),
ntile(4) over w,
lag(x, 1) over w,
lead(x, 1) over w
from generate_series(1, 15, 2) as t(x)
window w as (order by x);
x | row_number | ntile | lag | lead
----+------------+-------+-----+------
1 | 1 | 1 | | 3
3 | 2 | 1 | 1 | 5
5 | 3 | 2 | 3 | 7
7 | 4 | 2 | 5 | 9
9 | 5 | 3 | 7 | 11
11 | 6 | 3 | 9 | 13
13 | 7 | 4 | 11 | 15
15 | 8 | 4 | 13 |
(8 rows)
This kind of thing comes up all the time in timeseries analysis. To do
it in SQLite would require joining the table to itself once for every
window function. The engine sees the JOIN, does the JOIN in its full
generality, only to find its way along the index to the rows adjacent.
Absent novel sematic analysis of the SQL -- which I would welcome --
the short road to efficiency is to introduce windowing functions. They
grab the parser by the lapel and shout, "Hey, Bozo, that row over
there! Stick it with this one!"
I'm not kidding. Listen closely next time you're near a SQL Server.
> But then again, maybe I'm just an old fart ...
Unlikely. I'll let you know when my trademark application is
approved.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
