Window functions?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
36 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Window functions?

Webb S.
Has anyone thought in some detail about what it would it take to add window
functions to SQLite?

http://www.postgresql.org/docs/9.4/static/tutorial-window.html

For data analysis shops like us (think SAS + baroquely complex Excel + lots
of graphs), SQLite with window functions would be immense.  One the reasons
we don't migrate to more SQL is the need for a server for systems like
PostgreSQL in order to use features like window functions.

I am not promising anything, but I would be interested in a sketch of it
might take a hacker to add these to SQLite -- what files need to be touched,
what sections of the lemon parser, etc.  

Thanks, especially for your patience if this just noise.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Simon Slavin-3

On 25 Aug 2014, at 4:43pm, forkandwait <[hidden email]> wrote:

> Has anyone thought in some detail about what it would it take to add window
> functions to SQLite?

Would you care to explain what advantages Window functions would give us that VIEWs and sub-SELECTs don't give us ?  I'm not being contrary, I'd like to know.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Richard Hipp-3
In reply to this post by Webb S.
On Mon, Aug 25, 2014 at 11:43 AM, forkandwait <[hidden email]>
wrote:

>
> I am not promising anything, but I would be interested in a sketch of it
> might take a hacker to add these to SQLite -- what files need to be
> touched,
> what sections of the lemon parser, etc.
>

You used the word "immense" which I like - it is an apt description of the
knowledge and effort needed to add windowing functions to SQLite (and
probably any other database engine for that matter).


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Webb S.
In reply to this post by Simon Slavin-3
Simon Slavin <slavins@...> writes:


> Would you care to explain what advantages Window functions would give us
that VIEWs and sub-SELECTs don't
> give us ?  I'm not being contrary, I'd like to know.

I have never compared lines of code between the various approaches, but
window functions make it pretty simple to do rankings, moving averages, etc,
while sub-SELECTS etc are much more convoluted.

I think of window functions as syntactic sugar only, but still very nice and
very useful for analytical work, less so for pure data store work.  

Compare the two SQL examples between Approach 2 and Approach 3 in the linked
page:

http://hashrocket.com/blog/posts/sql-window-functions

Also, VIEWS are permanent and thus lead to clutter, though temporary views
mitigate somewhat.

I am not sure it would be the best use of developer time and energy, but
like I say it would be great for us analysts who would like to do more SQL
work without a server setup, and less SAS/ Excel/ R/ whatever.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Webb S.
In reply to this post by Richard Hipp-3
> You used the word "immense" which I like - it is an apt description of the
> knowledge and effort needed to add windowing functions to SQLite (and
> probably any other database engine for that matter).

Hehe.  I would be interested in any of your specific thoughts on the
immensity of it.  I can imagine that most of the work would be in the
parser, but things always simpler to non-experts ;)

Postgres pulled it off, though, as well as the big three commercial DB's
(DB2, SQL Server, Oracle).




_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Richard Hipp-3
On Mon, Aug 25, 2014 at 1:21 PM, forkandwait <[hidden email]> wrote:

> > You used the word "immense" which I like - it is an apt description of
> the
> > knowledge and effort needed to add windowing functions to SQLite (and
> > probably any other database engine for that matter).
>
> Hehe.  I would be interested in any of your specific thoughts on the
> immensity of it.  I can imagine that most of the work would be in the
> parser, but things always simpler to non-experts ;)
>

Parsing is the easy part.  The tricky part is the code generator - the
piece that takes the abstract syntax tree that the parser generates and
turns it into bytecode that renders the desired output, taking care to
correctly handle the myriad corner cases.   Then comes the tedious part of
writing 100% MC/DC test cases.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Petite Abeille-2
In reply to this post by Richard Hipp-3

On Aug 25, 2014, at 7:12 PM, Richard Hipp <[hidden email]> wrote:

> You used the word "immense" which I like - it is an apt description of the
> knowledge and effort needed to add windowing functions to SQLite (and
> probably any other database engine for that matter).

True. But what a quantum leap that would be. Like moving from the wheelbarrow to the jet engine.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Petite Abeille-2
In reply to this post by Simon Slavin-3

On Aug 25, 2014, at 7:04 PM, Simon Slavin <[hidden email]> wrote:

> Would you care to explain what advantages Window functions would give us that VIEWs and sub-SELECTs don't give us ?  I'm not being contrary, I'd like to know.

Analytics are to sub-selects like cruise missile are to muskets: an entirely different ballgame.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Stephan Beal-3
In reply to this post by Petite Abeille-2
On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille <[hidden email]>
wrote:

> True. But what a quantum leap that would be. Like moving from the
> wheelbarrow to the jet engine.
>

For the small percentage of users who need it (or would even know how to
apply it). i've been following this list since 2006 or 2007 and i recall
this topic having come up only a small handful of times, which implies that
only a small minority of users feels the need for it.

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Petite Abeille-2

On Aug 25, 2014, at 9:25 PM, Stephan Beal <[hidden email]> wrote:

> For the small percentage of users who need it (or would even know how to
> apply it). i've been following this list since 2006 or 2007 and i recall
> this topic having come up only a small handful of times, which implies that
> only a small minority of users feels the need for it.

Meh, most developers cannot put a join together, much less comprehend what they never used. But ignorance is not an excuse :)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Nelson, Erik - 2
In reply to this post by Stephan Beal-3
Stephan Beal wrote on Monday, August 25, 2014 3:26 PM
>
> For the small percentage of users who need it (or would even know how
> to apply it). i've been following this list since 2006 or 2007 and i
> recall this topic having come up only a small handful of times, which
> implies that only a small minority of users feels the need for it.
>

Not necessarily... we use SQLite extensively in our application, and when we wanted to use window functions we looked in the docs and found it wasn't supported, we just accepted that as the way things are.  I suspect many users might do the same.


----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Webb S.
In reply to this post by Stephan Beal-3
Stephan Beal <sgbeal@...> writes:

> For the small percentage of users who need it (or would even know how to
> apply it). i've been following this list since 2006 or 2007 and i recall
> this topic having come up only a small handful of times, which implies that
> only a small minority of users feels the need for it.

Respectfully, I wouldn't consider feature requests via the listserv a
necessarily good indicator of the demand for a given feature.  For one, many
SQL users don't know that window functions are even possible, so they aren't
going to ask for them.  (I learned about them after Postgres added them,
then discovered their extraordinary utility.)  Second, window functions are
a bit out of the (current) niche for SQLite, which (I think) is doing
datastore work for single user applications.

I am still not convinced window functions are a good use of developer time
or money, just that lack of mention on the listserv doesn't necessarily
imply such a fact.

It seems to me the way to experiment is to branch, extend the parser to
accept the syntax but yield no-ops, then to work on the bytecode generation.
I unfortunately don't have the skill to do it at the moment or the budget to
contract, so it is speculative for me only currently.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Petite Abeille-2
In reply to this post by Webb S.

On Aug 25, 2014, at 7:18 PM, forkandwait <[hidden email]> wrote:

> Compare the two SQL examples between Approach 2 and Approach 3 in the linked
> page:
>
> http://hashrocket.com/blog/posts/sql-window-functions

Couple more:

There was SQL before window functions and SQL after window functions
http://tapoueh.org/blog/2013/08/20-Window-Functions


NoSQL? No, SQL! – How to Calculate Running Totals
http://blog.jooq.org/2014/04/29/nosql-no-sql-how-to-calculate-running-totals/


Probably the Coolest SQL Feature: Window Functions
http://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/

etc, etc, etc… the future is bright, the future is analytic :D


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

big stone
In reply to this post by Webb S.
Hi,

For one of the few wishing it :
- I can understand when Richard writes it's very complex to implement "in
full", as I can imagine tricky requests with it,

- but would a basic subset, like the one described here in March  '14 (
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-March/051635.html
)  :
    . be a sufficient step forward for people looking for it in SQLite,
    . be also simple enough to be  pre-translated in  'current' supported
syntax , so hopefully avoiding complexity of implementation ?

Sqlite supports already only a subset of "ALTER TABLE", so it would not be
the first time only a subset is implemented.

Regards,
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Webb S.
big stone <stonebig34@...> writes:

> - but would a basic subset, like the one described here in March  '14 (
>
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-March/051635.html

For non-subscribers to read:

http://thread.gmane.org/gmane.comp.db.sqlite.general/86702

> )  :
>     . be a sufficient step forward for people looking for it in SQLite,
>     . be also simple enough to be  pre-translated in  'current' supported
> syntax , so hopefully avoiding complexity of implementation ?

It would work for me, for now!

I would be interested to hear what parts of the full window function spec
are not covered by the example, if someone can describe it easily.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Petite Abeille-2

On Aug 25, 2014, at 10:20 PM, forkandwait <[hidden email]> wrote:

> I would be interested to hear what parts of the full window function spec
> are not covered by the example, if someone can describe it easily.

Well, the exact implementation varies from implementation to implementation, e.g. Oracle sports more than 32 of them [1][2].

But a very good start would be to turn the existing 6 aggregate functions [3] into analytics.


[1] http://www.oracle-base.com/articles/misc/analytic-functions.php
[2] http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#SQLRF06174
[3] http://www.sqlite.org/lang_aggfunc.html

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Richard Hipp-3
In reply to this post by Webb S.
On Mon, Aug 25, 2014 at 4:20 PM, forkandwait <[hidden email]> wrote:

>
> I would be interested to hear what parts of the full window function spec
> are not covered by the example, if someone can describe it easily.
>


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.  Note that
the rank() function works very different from the sum() function, even
though they have similar syntax.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Stephan Beal-3
In reply to this post by Webb S.
On Mon, Aug 25, 2014 at 9:43 PM, forkandwait <[hidden email]> wrote:

> Stephan Beal <sgbeal@...> writes:
>
> > For the small percentage of users who need it (or would even know how to
> > apply it). i've been following this list since 2006 or 2007 and i recall
> > this topic having come up only a small handful of times, which implies
> that
> > only a small minority of users feels the need for it.
>
> or money, just that lack of mention on the listserv doesn't necessarily
> imply such a fact.
>

As a many-year veteran of open source projects, i can attest that the two
primary indicators for "do we need this?" are:

a) does it scratch a personal itch?

b) are users asking for it?

If (b) isn't happening where the devs can see it (the public lists/forums)
then it is, in effect, not happening.

i.e. i disagree with that point - not with the others brought up - i won't
argue their utility, but i will argue that they don't fit in "lite," given
the multiple estimates given with regards to their development effort over
the years. Many other products have them - use those. We don't need
windowing functions taking up space on all the Android phones and embedded
devices in the world (and those installations outnumber yours and mine by
many times over).

Now back to the in-progress horse beating, but whether the horse is dead or
not apparently remains to be seen...

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

big stone
In reply to this post by Webb S.
Hi Stephan,

"lite," is not a mathematic definition, and is increasing over time.
(at least 5% per year in Sqlite code size, by 30% in smartphone capabilities )

==> What was "heavy" in 2003 will become "lite" one day.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions?

Keith Medcalf
In reply to this post by Webb S.

  select id, category_id, name, min(price) as minprice
    from cat_pictures
group by category_id;

Done.  And no need for any windowing functions ...

>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of forkandwait
>Sent: Monday, 25 August, 2014 11:19
>To: [hidden email]
>Subject: Re: [sqlite] Window functions?
>
>Simon Slavin <slavins@...> writes:
>
>
>> Would you care to explain what advantages Window functions would give
>us
>that VIEWs and sub-SELECTs don't
>> give us ?  I'm not being contrary, I'd like to know.
>
>I have never compared lines of code between the various approaches, but
>window functions make it pretty simple to do rankings, moving averages,
>etc,
>while sub-SELECTS etc are much more convoluted.
>
>I think of window functions as syntactic sugar only, but still very nice
>and
>very useful for analytical work, less so for pure data store work.
>
>Compare the two SQL examples between Approach 2 and Approach 3 in the
>linked
>page:
>
>http://hashrocket.com/blog/posts/sql-window-functions
>
>Also, VIEWS are permanent and thus lead to clutter, though temporary
>views
>mitigate somewhat.
>
>I am not sure it would be the best use of developer time and energy, but
>like I say it would be great for us analysts who would like to do more
>SQL
>work without a server setup, and less SAS/ Excel/ R/ whatever.
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
12