Re: [sqlite] Window functions

[Dan Kennedy]

On 5/9/62 00:13, David Raymond wrote:

Kind of annoying that when the author shows a screenshot of the sample data he's using 
for his queries that he doesn't include 2 of the fields that are in the queries. Makes it 
harder to "play along at home"

For their ntile example (on page2) I don't think I've seen a window function used with a 
"group by". Does the ntile un-group the groups? Something just looks wrong 
there between the query and the results shown below it. But like you I don't know enough 
to say if that's right or if it's on crack.


You can run window functions on aggregate queries. The windowing step 
occurs logically after the aggregation.


It still looks right to me. Each output row contains a unique 
combination of territoryid/customerid, so no need for any "un-grouping". 
Of course, the input data doesn't feature any rows with duplicate 
territoryid/customerid values, so running the query without the GROUP BY 
and replacing "sum(subtotal)" with "subtotal" would produce the same 
results.


Dan






-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Wednesday, September 04, 2019 12:15 PM
To: SQLite mailing list 
Subject: [sqlite] Window functions

I ran into this two-part article, probably on Hacker News:

<http://www.helenanderson.co.nz/sql-window-functions-part-1/>

I tried comparing it with

<https://www.sqlite.org/windowfunctions.html>

but I don't know enough to be able to tell whether the language used in the 
article is compatible with the way window functions are implemented in SQLite.  
Could someone who knows more than I do take a look and post a summary ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions

[David Raymond]

Kind of annoying that when the author shows a screenshot of the sample data 
he's using for his queries that he doesn't include 2 of the fields that are in 
the queries. Makes it harder to "play along at home"

For their ntile example (on page2) I don't think I've seen a window function 
used with a "group by". Does the ntile un-group the groups? Something just 
looks wrong there between the query and the results shown below it. But like 
you I don't know enough to say if that's right or if it's on crack.


-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Wednesday, September 04, 2019 12:15 PM
To: SQLite mailing list 
Subject: [sqlite] Window functions

I ran into this two-part article, probably on Hacker News:

<http://www.helenanderson.co.nz/sql-window-functions-part-1/>

I tried comparing it with

<https://www.sqlite.org/windowfunctions.html>

but I don't know enough to be able to tell whether the language used in the 
article is compatible with the way window functions are implemented in SQLite.  
Could someone who knows more than I do take a look and post a summary ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions

[Dan Kennedy]

On 4/9/62 23:14, Simon Slavin wrote:

I ran into this two-part article, probably on Hacker News:



I tried comparing it with



but I don't know enough to be able to tell whether the language used in the 
article is compatible with the way window functions are implemented in SQLite.  
Could someone who knows more than I do take a look and post a summary ?


I only skimmed it, but I think everything there is applicable to SQLite.

Although I think she's using "window frame" differently to the way we 
do. Not that it matters too much, as the term only occurs once in each 
of the two blog entries anyway.


Dan.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Window functions

[Simon Slavin]

I ran into this two-part article, probably on Hacker News:



I tried comparing it with



but I don't know enough to be able to tell whether the language used in the 
article is compatible with the way window functions are implemented in SQLite.  
Could someone who knows more than I do take a look and post a summary ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Window functions in System.Data.Sqlite

[Alexandre Billon]

Hello,

I am sorry in advance for this post but I am really looking forward having the 
window functions in System.Data.Sqlite.

The expected release date went from December 2018 to February 2019 in the news 
page.

Do you have any more info when will the new version of System.Data.Sqlite be 
released ?

Thank you.

Alex

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions in sqlite 3.26.0

[Keith Medcalf]

You can always turn your 100/count(ID) into a scalar expression (so that it is 
only calculated once):

select ID,
(count(Quantity) over Win1) * (select 100.0/count(ID) from mytable) as 
Percentile
from myTable
Window Win1 as (order by Quantity range between unbounded preceding and current 
row);


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Balaji Ramanathan
>Sent: Tuesday, 25 December, 2018 12:24
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Window functions in sqlite 3.26.0
>
>Thank you, Dan.  My eyes saw the underscore, but my brain did not
>process
>it!
>
>And many thanks to the SQLite development team for introducing this
>feature
>and making it so efficient.  I had the need to compute running sums
>and
>averages in a query, and I have views which use regular queries and
>recursive queries.  The recursive queries perform better, but still
>take
>about 10 minutes to process the data.  I replaced them with queries
>using
>window functions, and the results came back in under a second.
>Amazing!
>
>For a related question:  I am trying to calculate a percentile score
>using
>a query like below:
>
>select ID,
>(count(Quantity) over Win1) *100.0/count(ID) as Percentile,
>from myTable
>Window Win1 as (order by Quantityrange between unbounded preceding
>and
>current row)
>
>This gives me the error:  misuse of aggregate: count().
>
>So, I replaced it with the following:
>
>select ID,
>(count(Quantity) over Win1) *100.0/(count(ID) over Win2) as
>Percentile,
>from myTable
>Window Win1 as (order by Quantity range between unbounded preceding
>and
>current row),
>Win2 as (order by ID range between unbounded preceding and unbounded
>following)
>
>This works, but use of a window just to get the total count of ID's
>using a
>range "between unbounded preceding and unbounded following" just
>seems
>wrong!  Is there a simpler construct I am missing?  Thank you.
>
>Balaji Ramanathan
>
>
>> From: Dan Kennedy 
>> To: sqlite-users@mailinglists.sqlite.org
>> Cc:
>> Bcc:
>> Date: Mon, 24 Dec 2018 14:40:58 +0700
>> Subject: Re: [sqlite] Window functions in sqlite 3.26.0
>> On 12/24/2018 01:43 AM, Balaji Ramanathan wrote:
>> > Hi,
>> >
>> > Are window functions enabled by default in the sqlite command
>line
>> > shell program that is available for download on the sqlite
>website?  I
>> get
>> > the error message "no such function: rownumber()" when I try to
>use that
>> > window function.
>>
>>
>> They are in 3.26.0. Try "row_number", with an underscore. Or, if
>that's
>> not the problem, please post the failing SQL statement.
>>
>> Thanks,
>> Dan.
>>
>>
>>
>>
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions in sqlite 3.26.0

[Balaji Ramanathan]

Thank you, Dan.  My eyes saw the underscore, but my brain did not process
it!

And many thanks to the SQLite development team for introducing this feature
and making it so efficient.  I had the need to compute running sums and
averages in a query, and I have views which use regular queries and
recursive queries.  The recursive queries perform better, but still take
about 10 minutes to process the data.  I replaced them with queries using
window functions, and the results came back in under a second.  Amazing!

For a related question:  I am trying to calculate a percentile score using
a query like below:

select ID,
(count(Quantity) over Win1) *100.0/count(ID) as Percentile,
from myTable
Window Win1 as (order by Quantityrange between unbounded preceding and
current row)

This gives me the error:  misuse of aggregate: count().

So, I replaced it with the following:

select ID,
(count(Quantity) over Win1) *100.0/(count(ID) over Win2) as Percentile,
from myTable
Window Win1 as (order by Quantity range between unbounded preceding and
current row),
Win2 as (order by ID range between unbounded preceding and unbounded
following)

This works, but use of a window just to get the total count of ID's using a
range "between unbounded preceding and unbounded following" just seems
wrong!  Is there a simpler construct I am missing?  Thank you.

Balaji Ramanathan


> From: Dan Kennedy 
> To: sqlite-users@mailinglists.sqlite.org
> Cc:
> Bcc:
> Date: Mon, 24 Dec 2018 14:40:58 +0700
> Subject: Re: [sqlite] Window functions in sqlite 3.26.0
> On 12/24/2018 01:43 AM, Balaji Ramanathan wrote:
> > Hi,
> >
> > Are window functions enabled by default in the sqlite command line
> > shell program that is available for download on the sqlite website?  I
> get
> > the error message "no such function: rownumber()" when I try to use that
> > window function.
>
>
> They are in 3.26.0. Try "row_number", with an underscore. Or, if that's
> not the problem, please post the failing SQL statement.
>
> Thanks,
> Dan.
>
>
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions in sqlite 3.26.0

[Dan Kennedy]

On 12/24/2018 01:43 AM, Balaji Ramanathan wrote:

Hi,

Are window functions enabled by default in the sqlite command line
shell program that is available for download on the sqlite website?  I get
the error message "no such function: rownumber()" when I try to use that
window function.



They are in 3.26.0. Try "row_number", with an underscore. Or, if that's 
not the problem, please post the failing SQL statement.


Thanks,
Dan.



The documentation only mentions that window functions

were added in version 3.25.0, and does not give any indication as to
whether they are enabled or disabled in the command line tool.  Is there
perhaps a pragma setting I have to turn on for window functions to work?
Thank you.

Balaji Ramanathan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Window functions in sqlite 3.26.0

[Balaji Ramanathan]

Hi,

Are window functions enabled by default in the sqlite command line
shell program that is available for download on the sqlite website?  I get
the error message "no such function: rownumber()" when I try to use that
window function.  The documentation only mentions that window functions
were added in version 3.25.0, and does not give any indication as to
whether they are enabled or disabled in the command line tool.  Is there
perhaps a pragma setting I have to turn on for window functions to work?
Thank you.

Balaji Ramanathan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[John McKown]

I see the point being made by many in this thread. I am not against
expanding SQLite's functionality. But, if I might, I will throw out some
contrarian ideas. First is that SQLite really is an embedded SQL data base.
It is meant to be combined into your application's main executable file. So
the larger you make the basic SQLite engine, the more "bloat" in the
applications which use it. Of course, a good developer is going to set up
all the proper SQLite #define variables to reduce SQLite's footprint to be
only as functional a possible. This is the proper thing to do. The problem,
if there is one, is that the more "options" that can be enabled/disabled
via #define variables, the more variants exist of SQLite. And the more
difficult it is to test every possible variant. Remember, adding just one
more #define for an "optional" function doubles the number of variants.
This is a quite a bit of work to ask of Dr. Hipp for a completely free
software product.

Another thing that I wonder is why people want all of this in an _embedded_
SQL engine? If somebody really needs a full blown relational database
management system, I really think that a client/server model is superior.
Yes, I agree, this is just my opinion. You can have a different one and
neither of us is really "wrong". I love SQLite for a number of things that
I do. But when I want a multi-gigabyte database used by multiple
applications, I go with PostgreSQL. And, yes, I'm aware that there are such
DBs based on SQLite. But I find the "extras" which come with the
client/server model RDBMS fill a great need. One which I must write myself
when I use SQLite, or find one already written by someone else that I can
adopt/adapt. I mentioned PostgreSQL instead of Oracle or MS SQL Server
mainly due to cost and licensing fees. PostgreSQL is FOSS and basically
allows you to "close source" not only your apps, but any mods to PostgreSQL
that you want to. Very similar to the 2-clause BSD and MIT licenses. In
addition, there is a commercial version, EDB, for people who want or need
professional maintenance, such as many larger companies like to have.
SQLite also has such paid support, if needed.

Well, I've just put up a couple of my thoughts. That's all they are. Maybe
some points for polite discussion. Or maybe I'm just more comfortable with
the way that I already do things. {shrug} What do you expect from a person
near retirement?

On Fri, May 4, 2018 at 2:33 PM, cherie  wrote:

>
> In 2008 I was part of a project which was mostly DB driven using Sybase
> 12.5. Sybase neither had support for user functions nor window functions &
> many other features, which other contemporary RDBMS had and same arguments
> was thrown why you need user functions or window functions if both can be
> accomplished by stored procedures. Version 1 release (early 2009) ended up
> with around 200 tables, 250 views and roughly 600 stored procs.
>
> Now after 10 years Sybase 15.7 has introduced user functions (and many
> other
> new features) saying it reduces TCO/time-to-market. Now my same application
> has grown to 500 tables, 410 views and 2600 stored procs. Most of these
> procs are repetitive codes with hard to read complex SQL written by
> developers mostly accomplishing reporting needs that grew in years (as
> application stabilized). And looking at those procs I can very clearly say
> that these are nothing but workarounds cooked to achieve what window
> functions provide in very easy to implement manner to developers. Every now
> and then we keep getting alerts that one of the proc is non performing or
> slow etc.
>
> I would love to see window function (before I die) being introduced in
> SQLite to reduce TCO because these output are anyhow being written in java,
> python or wherever if not in SQLite SQL, how its reducing the overall space
> footprint in android, I am not sure. 10 lines of window function can be
> achieved by 100 lines of CTE based SQL and if you do not have developers
> with SQL inclination they end up writing same in one language or the other.
> Think of testing effort that goes in verifying these codes.
>
> Everything in computer can done by machine/assembly codes, then why to use
> Java or C (its simplicity).
>
> -- Abstraction_(computer_science)
> 
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[cherie]

In 2008 I was part of a project which was mostly DB driven using Sybase
12.5. Sybase neither had support for user functions nor window functions &
many other features, which other contemporary RDBMS had and same arguments
was thrown why you need user functions or window functions if both can be
accomplished by stored procedures. Version 1 release (early 2009) ended up
with around 200 tables, 250 views and roughly 600 stored procs. 

Now after 10 years Sybase 15.7 has introduced user functions (and many other
new features) saying it reduces TCO/time-to-market. Now my same application
has grown to 500 tables, 410 views and 2600 stored procs. Most of these
procs are repetitive codes with hard to read complex SQL written by
developers mostly accomplishing reporting needs that grew in years (as
application stabilized). And looking at those procs I can very clearly say
that these are nothing but workarounds cooked to achieve what window
functions provide in very easy to implement manner to developers. Every now
and then we keep getting alerts that one of the proc is non performing or
slow etc.

I would love to see window function (before I die) being introduced in
SQLite to reduce TCO because these output are anyhow being written in java,
python or wherever if not in SQLite SQL, how its reducing the overall space
footprint in android, I am not sure. 10 lines of window function can be
achieved by 100 lines of CTE based SQL and if you do not have developers
with SQL inclination they end up writing same in one language or the other.
Think of testing effort that goes in verifying these codes.

Everything in computer can done by machine/assembly codes, then why to use
Java or C (its simplicity).

-- Abstraction_(computer_science)
  



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions

[Olivier Mascia]

> On Wed, Apr 25, 2018 at 12:04 PM, Charles Leifer  wrote:
> 
>> Hi,
>> 
>> I'm sure this has been asked before, but are window functions on the
>> roadmap? Is it the authors' experience that the implementation would
>> significantly complicate sqlite? Just curious. Thanks so much for a
>> fantastic library.
>> 
>> Charlie

> Le 26 avr. 2018 à 05:41, J Decker  a écrit :
> 
> What are 'window functions'?

SQL window functions are constructs like:

https://en.wikipedia.org/wiki/SQL_window_function
https://www.postgresql.org/docs/9.1/static/tutorial-window.html
https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-windowfuncs.html
https://drill.apache.org/docs/sql-window-functions-introduction/
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions

[J Decker]

What are 'window functions'?

If you mean GUI; that's really outside the scope of Sqlite; and whatever
environment you're in can provide your GUI;
https://www.npmjs.com/package/sack-gui for instance for Javascript(Node.js).

On Wed, Apr 25, 2018 at 12:04 PM, Charles Leifer  wrote:

> Hi,
>
> I'm sure this has been asked before, but are window functions on the
> roadmap? Is it the authors' experience that the implementation would
> significantly complicate sqlite? Just curious. Thanks so much for a
> fantastic library.
>
> Charlie
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Window functions

[Charles Leifer]

Hi,

I'm sure this has been asked before, but are window functions on the
roadmap? Is it the authors' experience that the implementation would
significantly complicate sqlite? Just curious. Thanks so much for a
fantastic library.

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite window functions extension?

[Don V Nielsen]

I'm disappointed.  I killed it after 1.5hrs, and suspected everything you
explained in the other thread.  It is always true.

Que Sera Sera

dvn

On Fri, Oct 16, 2015 at 2:33 PM, Igor Tandetnik  wrote:

> On 10/16/2015 3:23 PM, Don V Nielsen wrote:
>
>>  limit (
>>SELECT net_non_pieces
>>FROM crrt_net_non [b]
>>WHERE [b].zip = zip AND [b].crrt = crrt
>>
>
> Again, I don't think this does what you think it does. Test with at least
> two rows in crrt_net_non, with different values for net_non_pieces, and
> confirm that as many rows are being updated as you've hoped. I suspect you
> may be disappointed.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Igor Tandetnik]

On 10/16/2015 3:23 PM, Don V Nielsen wrote:
>  limit (
>SELECT net_non_pieces
>FROM crrt_net_non [b]
>WHERE [b].zip = zip AND [b].crrt = crrt

Again, I don't think this does what you think it does. Test with at 
least two rows in crrt_net_non, with different values for 
net_non_pieces, and confirm that as many rows are being updated as 
you've hoped. I suspect you may be disappointed.
-- 
Igor Tandetnik

[sqlite] sqlite window functions extension?

[Don V Nielsen]

Wrapping up this thread.

What I am going to go with is the following.  I'm using the initial WITH to
reduce as much as possible the number of records to be repeatedly searched:
maybe 25/30%.  The addresses table currently has 4.4mm rows; it will go up
to 11.5mm when this goes to prod.  I'm testing performance right now.
Might not be too good.

begin transaction;
with ver_addresses AS (
  SELECT rowid as id,zip,crrt
  from addresses
  where version_id = '0060'
)
update addresses
set segment = '60'
where
  rowid in (
select id
from ver_addresses [a]
where [a].zip = [addresses].zip and [a].crrt = [addresses].crrt
limit (
  SELECT net_non_pieces
  FROM crrt_net_non [b]
  WHERE [b].zip = zip AND [b].crrt = crrt
)
  );
commit;


On Fri, Oct 16, 2015 at 9:24 AM, Don V Nielsen 
wrote:

> The final solution at this point is Ruby.  I really want to push
> everything I can into Sqlite because *it is so freakin fast!*
> Unfortunately. I I am just not getting it.  Also unfortunately, iterating,
> getting data, and updating data in scripting languages is not efficient.
> I've implemented a transaction for each crrt_net_non iteration, but that is
> still painfully slow.  I will have to be more creative in my data handling
> (move to dbi versus activerecord) and partitioning of transactions
> (committing after every 100,000 updates, for example.)
>
> NetNon.all.each {|nn|
>   ActiveRecord::Base.transaction {
> addrs = Address.
> where(zip:nn.zip,crrt:nn.crrt,version_id:non_sel_ver.ver_id).
> select(:id,:segment).
> take(nn.net_non_pieces)
> addrs.each {|addr| addr.update(segment:'xx') }
>   }
>   pb.inc
> }
>
> In English..get all rows from crrt_net_non: it has the control quantity
> net_non_pieces.  Select from addresses all rows with the same zip and crrt
> values, and matching the version to be updated.  I only need columns id and
> segment, and take only the number of records as calculated in
> net_non_pieces.  Update the segment code.  Loop.
>
> On Thu, Oct 15, 2015 at 9:47 AM, Don V Nielsen 
> wrote:
>
>> I'm surprised that and extension for this type of functionality has not
>> been been developed by someone with the c/c++.  It's seems like a natural
>> fit.  I wish I had the kind of ability & smarts to do it.
>>
>> On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik 
>> wrote:
>>
>>> On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>>>
  limit ifnull( (
select net_non_pieces from crrt_net_non net
where net.zip=zip and net.crrt=crrt

>>>
>>> I suspect this WHERE clause is equivalent to "where net.zip=net.zip and
>>> net.crrt=net.crrt" - that is, always true. The actual limit value comes
>>> from whichever row accidentally happens to be first.
>>>
>>> --
>>> Igor Tandetnik
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

The final solution at this point is Ruby.  I really want to push everything
I can into Sqlite because *it is so freakin fast!*  Unfortunately. I I am
just not getting it.  Also unfortunately, iterating, getting data, and
updating data in scripting languages is not efficient.  I've implemented a
transaction for each crrt_net_non iteration, but that is still painfully
slow.  I will have to be more creative in my data handling (move to dbi
versus activerecord) and partitioning of transactions (committing after
every 100,000 updates, for example.)

NetNon.all.each {|nn|
  ActiveRecord::Base.transaction {
addrs = Address.
where(zip:nn.zip,crrt:nn.crrt,version_id:non_sel_ver.ver_id).
select(:id,:segment).
take(nn.net_non_pieces)
addrs.each {|addr| addr.update(segment:'xx') }
  }
  pb.inc
}

In English..get all rows from crrt_net_non: it has the control quantity
net_non_pieces.  Select from addresses all rows with the same zip and crrt
values, and matching the version to be updated.  I only need columns id and
segment, and take only the number of records as calculated in
net_non_pieces.  Update the segment code.  Loop.

On Thu, Oct 15, 2015 at 9:47 AM, Don V Nielsen 
wrote:

> I'm surprised that and extension for this type of functionality has not
> been been developed by someone with the c/c++.  It's seems like a natural
> fit.  I wish I had the kind of ability & smarts to do it.
>
> On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik 
> wrote:
>
>> On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>>
>>>  limit ifnull( (
>>>select net_non_pieces from crrt_net_non net
>>>where net.zip=zip and net.crrt=crrt
>>>
>>
>> I suspect this WHERE clause is equivalent to "where net.zip=net.zip and
>> net.crrt=net.crrt" - that is, always true. The actual limit value comes
>> from whichever row accidentally happens to be first.
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[sqlite] sqlite window functions extension?

[Igor Tandetnik]

On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>  limit ifnull( (
>select net_non_pieces from crrt_net_non net
>where net.zip=zip and net.crrt=crrt

I suspect this WHERE clause is equivalent to "where net.zip=net.zip and 
net.crrt=net.crrt" - that is, always true. The actual limit value comes 
from whichever row accidentally happens to be first.
-- 
Igor Tandetnik

[sqlite] sqlite window functions extension?

[Don V Nielsen]

I'm surprised that and extension for this type of functionality has not
been been developed by someone with the c/c++.  It's seems like a natural
fit.  I wish I had the kind of ability & smarts to do it.

On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik  wrote:

> On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>
>>  limit ifnull( (
>>select net_non_pieces from crrt_net_non net
>>where net.zip=zip and net.crrt=crrt
>>
>
> I suspect this WHERE clause is equivalent to "where net.zip=net.zip and
> net.crrt=net.crrt" - that is, always true. The actual limit value comes
> from whichever row accidentally happens to be first.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

The correlated query was not accepted in the select statement.  However, I
modified your original UPDATE query, as follows, and it did execute, and
passed the proper quantity of rows for the first zip/crrt combination,
only.  The rows were not updated.  It counted correctly, but did not change
the values.

sql> UPDATE tmp_addresses
  set segment='xx'
  where rowid in (
select x2.rowid from tmp_addresses x2 where x2.zip=zip and x2.crrt=crrt
order by
  CASE
  WHEN x2.version_id = '0060' THEN 0
  WHEN x2.version_id = '0064' THEN 2
  WHEN x2.version_id = '0061' THEN 3
  ELSE 99
  END ASC
limit ifnull( (
  select net_non_pieces from crrt_net_non net
  where net.zip=zip and net.crrt=crrt
), 0)
  )
[2015-10-15 06:54:18] *42 row(s)* affected in 189ms

I have not yet attempted your most recent suggestion.  I will get to that
some time today.



On Wed, Oct 14, 2015 at 9:21 PM, Igor Tandetnik  wrote:

> On 10/14/2015 9:29 PM, Don V Nielsen wrote:
>
>> But I am having problems with the LIMIT statement.  It throws an exception
>> no matter what table alias is used: X or x2.  It says "no such column".
>>
>
> Ah, interesting. LIMIT clause doesn't appear to allow correlated
> subqueries; only self-contained expressions. Here goes that idea.
>
> Something like this should work:
>
> select X.*
> from addresses X join crrt_net_non_pieces using (zip, crrt)
> where net_non_pieces > (
>   select count(*) from addresses x2
>   where X.zip=x2.zip and X.crrt=x2.crrt and
>   (x2.version_id < X.version_id or (x2.version_id = X.version_id and
> x2.rowid < X.rowid))
> );
>
> I took the liberty to simplify the ordering expression, for purposes of
> exposition (it's very long and would need to be repeated four times).
> Replace all occurrences of T.version_id with your CASE clause, calculated
> against table T. Also, I'm breaking ties by rowid; your original problem
> statement is underspecified unless there's a total order on Addresses.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[R.Smith]

On 2015-10-14 11:20 PM, Don V Nielsen wrote:
> X has columns zip & crrt, just like crrt_net_non.  These form a composite
> key identifying groups within x.  A value "53001.R501" would be an
> example...53001 being the zip code and R501 being the carrier route.  There
> are 52 rows in X that have the key 53001.R501.  A calculation determined
> that I need 42 rows from that key and saved the result in crrt_net_non, the
> row looking like "53001.R501.52.6.46.42".  What I need is a sql function
> that can iterate over crrt_net_non, then grab the rows from X, "53001.R501"
> being first key, sort them into an internal group sequence, then update a
> code of the first 42 rows of that sorted group, and then doing this until
> crrt_net_non is exhausted.

Hi Don, usually a CTE can be pressed into service to make things work. 
Maybe others know exactly what you mean here but it's a bit Greek to me.

As Igor suggested - perhaps some data to accompany this schema and an 
example result-set (or resulting updated table) from it will make it 
clear what you want to achieve with the query. Feel free to simplify so 
the essence of what is needed remain, but be sure to give an example 
that cannot be achieved by other arbitrary means.

[sqlite] sqlite window functions extension?

[Igor Tandetnik]

On 10/14/2015 9:29 PM, Don V Nielsen wrote:
> But I am having problems with the LIMIT statement.  It throws an exception
> no matter what table alias is used: X or x2.  It says "no such column".

Ah, interesting. LIMIT clause doesn't appear to allow correlated 
subqueries; only self-contained expressions. Here goes that idea.

Something like this should work:

select X.*
from addresses X join crrt_net_non_pieces using (zip, crrt)
where net_non_pieces > (
   select count(*) from addresses x2
   where X.zip=x2.zip and X.crrt=x2.crrt and
   (x2.version_id < X.version_id or (x2.version_id = X.version_id and 
x2.rowid < X.rowid))
);

I took the liberty to simplify the ordering expression, for purposes of 
exposition (it's very long and would need to be repeated four times). 
Replace all occurrences of T.version_id with your CASE clause, 
calculated against table T. Also, I'm breaking ties by rowid; your 
original problem statement is underspecified unless there's a total 
order on Addresses.
-- 
Igor Tandetnik

[sqlite] sqlite window functions extension?

[Don V Nielsen]

The only reason NUM exists in the result set is so I could use it to limit
the outer most select.

But I am having problems with the LIMIT statement.  It throws an exception
no matter what table alias is used: X or x2.  It says "no such column".

Thanks for your time, by the way.

On Wed, Oct 14, 2015 at 6:08 PM, Igor Tandetnik  wrote:

> On 10/14/2015 6:24 PM, Don V Nielsen wrote:
>
>> zip crrt   version_id   NUM   segment
>> [truncated num 1..38]
>> 53001   R501   0060 39xx
>> 53001   R501   0060 40xx
>> 53001   R501   0060 41xx
>> 53001   R501   0060 42xx
>> [truncated num 1..24]
>> 53001   R502   0060 21xx
>> 53001   R502   0060 22xx
>> 53001   R502   0060 23xx
>> 53001   R502   0060 24xx
>>
>
> select * from addresses X where rowid in (
>   select x2.rowid from addresses x2 where X.zip=x2.zip and X.crrt=x2.crrt
>   order by (CASE ...)
>   limit ifnull( (
>  select net_non_pieces from crrt_net_non net
>  where X.zip=net.zip and X.crrt=net.crrt
>   ), 0)
> );
>
> Do you actually need NUM column in the resultset? That one would be tricky
> to pull off.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Igor Tandetnik]

On 10/14/2015 6:24 PM, Don V Nielsen wrote:
> zip crrt   version_id   NUM   segment
> [truncated num 1..38]
> 53001   R501   0060 39xx
> 53001   R501   0060 40xx
> 53001   R501   0060 41xx
> 53001   R501   0060 42xx
> [truncated num 1..24]
> 53001   R502   0060 21xx
> 53001   R502   0060 22xx
> 53001   R502   0060 23xx
> 53001   R502   0060 24xx

select * from addresses X where rowid in (
   select x2.rowid from addresses x2 where X.zip=x2.zip and X.crrt=x2.crrt
   order by (CASE ...)
   limit ifnull( (
  select net_non_pieces from crrt_net_non net
  where X.zip=net.zip and X.crrt=net.crrt
   ), 0)
);

Do you actually need NUM column in the resultset? That one would be 
tricky to pull off.
-- 
Igor Tandetnik

[sqlite] sqlite window functions extension?

[Igor Tandetnik]

On 10/14/2015 5:20 PM, Don V Nielsen wrote:
> X has columns zip & crrt, just like crrt_net_non.  These form a composite
> key identifying groups within x.  A value "53001.R501" would be an
> example...53001 being the zip code and R501 being the carrier route.  There
> are 52 rows in X that have the key 53001.R501.  A calculation determined
> that I need 42 rows from that key and saved the result in crrt_net_non, the
> row looking like "53001.R501.52.6.46.42".  What I need is a sql function
> that can iterate over crrt_net_non, then grab the rows from X, "53001.R501"
> being first key, sort them into an internal group sequence, then update a
> code of the first 42 rows of that sorted group, and then doing this until
> crrt_net_non is exhausted.

Are you looking for something like this?

UPDATE X set code=whatever where rowid in (
   select x2.rowid from X x2 where X.zip=x2.zip and X.crrt=x2.crrt
   order by someOrder
   limit ifnull( (
  select net_non_pieces from crrt_net_non net
  where X.zip=net.zip and X.crrt=net.crrt
   ), 0)
);

-- 
Igor Tandetnik

[sqlite] sqlite window functions extension?

[Don V Nielsen]

Table crrt_net_non_pieces:

zip crrt   pkg_pieces   sel_pieces   non_pieces   net_non_pieces
53001   R501   52   646   42
53001   R502   34   727   24


Addresses (as I have been referring to as X) is going to have too many rows
(86 for this example, 52 rows for 53001.R501 and 34 rows for 53001.R502.
The calculation result says I need 42 of the 52 X rows from 53001.R501 to
be recoded and 24 of the 34 X rows from 53001.R502.

Addresses table:

zip crrt   segment   version_id
53001   R501   060060
53001   R501   060060
53001   R501   060060
53001   R501   060060
53001   R501   060060

In sql server, I would be doing something like this:

  select x.* ,'xx' as segment from ( select -- count(1) a.zip ,a.crrt
,a.version_id ,ROW_NUMBER() OVER ( PARTITION BY a.zip,a.crrt ORDER BY CASE WHEN
a.version_id = '0060' THEN 0 WHEN a.version_id = '0064' THEN 2 WHEN
a.version_id = '0061' THEN 3 ELSE 99 END ASC ) AS NUM from
dbwork..addresses a )x join dbwork..crrt_net_non y on y.zip = x.zip and
y.crrt = x.crrt where x.num <= y.net_non_pieces ;
And the results would come out something like the following:  I truncate
the rows with num values 1..whatever.  Notice NUM is the sequential value
applied to the input (1..52 in the case of R501 and 1..34 in the case of
R502).  I then only kept the number of rows as identified by the
corresponding y.net_non_pieces value.

zip crrt   version_id   NUM   segment
[truncated num 1..38]
53001   R501   0060 39xx
53001   R501   0060 40xx
53001   R501   0060 41xx
53001   R501   0060 42xx
[truncated num 1..24]
53001   R502   0060 21xx
53001   R502   0060 22xx
53001   R502   0060 23xx
53001   R502   0060 24xx


Is this better?  Sorry that I imagined/assumed that you guys just picture
this stuff in your head.  I get that impression reading this list.

dvn


These window functions seem to be some sort of Holy Grail that I just can't
imagine duplicating without it.


On Wed, Oct 14, 2015 at 4:27 PM, R.Smith  wrote:

>
>
> On 2015-10-14 11:20 PM, Don V Nielsen wrote:
>
>> X has columns zip & crrt, just like crrt_net_non.  These form a composite
>> key identifying groups within x.  A value "53001.R501" would be an
>> example...53001 being the zip code and R501 being the carrier route.
>> There
>> are 52 rows in X that have the key 53001.R501.  A calculation determined
>> that I need 42 rows from that key and saved the result in crrt_net_non,
>> the
>> row looking like "53001.R501.52.6.46.42".  What I need is a sql function
>> that can iterate over crrt_net_non, then grab the rows from X,
>> "53001.R501"
>> being first key, sort them into an internal group sequence, then update a
>> code of the first 42 rows of that sorted group, and then doing this until
>> crrt_net_non is exhausted.
>>
>
> Hi Don, usually a CTE can be pressed into service to make things work.
> Maybe others know exactly what you mean here but it's a bit Greek to me.
>
> As Igor suggested - perhaps some data to accompany this schema and an
> example result-set (or resulting updated table) from it will make it clear
> what you want to achieve with the query. Feel free to simplify so the
> essence of what is needed remain, but be sure to give an example that
> cannot be achieved by other arbitrary means.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Igor Tandetnik]

On 10/14/2015 4:49 PM, Don V Nielsen wrote:
> What am I looking to do?  Using a table X, I've built a summary table,
> calculating a value called net_non_pieces.  net_not_pieces is a qty of rows
> from a group rows that needs to be recoded to a different value.  So what I
> want to do is for each group in X, look up the calculated value in
> crrt_net_non, then grab the first net_non_pieces (sequenced, of course) and
> apply a code to those rows, leaving the remaining rows alone.

It might help if you show the definition of X, a sample of data in it, 
and the desired result of the query over that data. I, for one, have 
difficulty following your description.
-- 
Igor Tandetnik

[sqlite] sqlite window functions extension?

[Don V Nielsen]

X has columns zip & crrt, just like crrt_net_non.  These form a composite
key identifying groups within x.  A value "53001.R501" would be an
example...53001 being the zip code and R501 being the carrier route.  There
are 52 rows in X that have the key 53001.R501.  A calculation determined
that I need 42 rows from that key and saved the result in crrt_net_non, the
row looking like "53001.R501.52.6.46.42".  What I need is a sql function
that can iterate over crrt_net_non, then grab the rows from X, "53001.R501"
being first key, sort them into an internal group sequence, then update a
code of the first 42 rows of that sorted group, and then doing this until
crrt_net_non is exhausted.

On Wed, Oct 14, 2015 at 4:03 PM, Igor Tandetnik  wrote:

> On 10/14/2015 4:49 PM, Don V Nielsen wrote:
>
>> What am I looking to do?  Using a table X, I've built a summary table,
>> calculating a value called net_non_pieces.  net_not_pieces is a qty of
>> rows
>> from a group rows that needs to be recoded to a different value.  So what
>> I
>> want to do is for each group in X, look up the calculated value in
>> crrt_net_non, then grab the first net_non_pieces (sequenced, of course)
>> and
>> apply a code to those rows, leaving the remaining rows alone.
>>
>
> It might help if you show the definition of X, a sample of data in it, and
> the desired result of the query over that data. I, for one, have difficulty
> following your description.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite window functions extension?

[Don V Nielsen]

Is there an extension to sqlite the provide window functions such as
row_number over and partition?  There are plenty of googles seeking said
functionality.

What am I looking to do?  Using a table X, I've built a summary table,
calculating a value called net_non_pieces.  net_not_pieces is a qty of rows
from a group rows that needs to be recoded to a different value.  So what I
want to do is for each group in X, look up the calculated value in
crrt_net_non, then grab the first net_non_pieces (sequenced, of course) and
apply a code to those rows, leaving the remaining rows alone.

I'm not sure how to accomplish such a thing

CREATE TABLE crrt_net_non -- developed from table X
(
zip TEXT,   --composite key
crrt TEXT,  --composite key
pkg_pieces integer,  --total row in group
sel_pieces integer,
non_pieces integer,
net_non_pieces integer  --qty of this group to recode
);

Its a pretty innocent thing to do with window functions.  It is not trivial
to do it without.  I can't even figure out how.

Why am I not dumping Sqlite and going with prostgres or the like?  Because
sqlite is awesome.  It is a portable file, easily created as I need for
each project, that can be moved around or deleted, making it super easy to
maintenance in my production environment.  Love the thing.  But this
problem has me flummuxed.

Thanks for your time and consideration,
dvn

Re: [sqlite] Window functions?

[James K. Lowden]

On Wed, 27 Aug 2014 18:25:28 -0600
"Keith Medcalf"  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

Re: [sqlite] Window functions?

[Eduardo Morras]

On Wed, 27 Aug 2014 23:04:40 +0200
Petite Abeille  wrote:

> 
> On Aug 27, 2014, at 10:57 PM, Eduardo Morras 
> wrote:
> 
> > Sorry, don't understand why others will throw an exception in the
> > group by, perhaps I'm misunderstanding the group by, but that
> > should work on others engines.
> 
> Because not all expressions are accounted for, i.e.:
> 
> "not a GROUP BY expression
> 
> Cause: The GROUP BY clause does not contain all the expressions in
> the SELECT clause. SELECT expressions that are not included in a
> group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or
> VARIANCE, must be listed in the GROUP BY clause.
> 
> Action: Include in the GROUP BY clause all SELECT expressions that
> are not group function arguments.”

Yep, it's true, I didn't notice it and asked too early.

> 
> Try it. See what happen.

It bangs, as expected, in PostgreSQL 9.2, adding the other selects columns 
solves it.

Thanks.

---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Simon Slavin]

On 28 Aug 2014, at 3:45pm, Richard Hipp  wrote:

> On Thu, Aug 28, 2014 at 10:38 AM, Gabor Grothendieck <
> ggrothendi...@gmail.com> wrote:
> 
>> The wording in the cited link is that
>> 
>> "Queries of the form: "SELECT max(x), y FROM table" returns the value
>> of y on the same row that contains the maximum x value."
>> 
>> There is some question of whether min(x) is "of the form" max(x).
> 
> It is.  That behavior is defined and tested for min() and max().  But it
> doesn't work for any other aggregate function.  Note also that if you have
> multiple min() and/or max() aggregate functions in the same query, then the
> result will be from the row in which one of them is the min() or the max(),
> but which one is arbitrary.  So it is only well-defined if you have a
> single min() or a single max().

Similarly, it's possible that two or more rows in the table will have the 
maximum value for column x.  There's no rule about which of them will be chosen 
to have its y value returned or even that SQLite will be consistent about it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Richard Hipp]

On Thu, Aug 28, 2014 at 10:38 AM, Gabor Grothendieck <
ggrothendi...@gmail.com> wrote:

> The wording in the cited link is that
>
> "Queries of the form: "SELECT max(x), y FROM table" returns the value
> of y on the same row that contains the maximum x value."
>
> There is some question of whether min(x) is "of the form" max(x).
>

It is.  That behavior is defined and tested for min() and max().  But it
doesn't work for any other aggregate function.  Note also that if you have
multiple min() and/or max() aggregate functions in the same query, then the
result will be from the row in which one of them is the min() or the max(),
but which one is arbitrary.  So it is only well-defined if you have a
single min() or a single max().

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Gabor Grothendieck]

The wording in the cited link is that

"Queries of the form: "SELECT max(x), y FROM table" returns the value
of y on the same row that contains the maximum x value."

There is some question of whether min(x) is "of the form" max(x).

On Thu, Aug 28, 2014 at 10:28 AM, Clemens Ladisch  wrote:
> Adam Devita wrote:
 select id, category_id, name, min(price) as minprice
from cat_pictures
 group by category_id;

>>
>> I'd be reluctant to write that query because it is non standard SQL and I
>> can't easily (5 minutes of searching) point at a document that tells me the
>> expected behavior.
>
> The SQL standard does not allow it.
>
> SQLite allows it for bug compatibility with MySQL.
> (The returned values are from some random row.)
>
> In SQLite 3.7.11 or later, the behaviour is defined:
> 
> but IIRC this was the wish of a paying customer, and is
> not documented anywhere else.
>
>
> Regards,
> Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Clemens Ladisch]

Adam Devita wrote:
>>> select id, category_id, name, min(price) as minprice
>>>from cat_pictures
>>> group by category_id;
>>>
>
> I'd be reluctant to write that query because it is non standard SQL and I
> can't easily (5 minutes of searching) point at a document that tells me the
> expected behavior.

The SQL standard does not allow it.

SQLite allows it for bug compatibility with MySQL.
(The returned values are from some random row.)

In SQLite 3.7.11 or later, the behaviour is defined:

but IIRC this was the wish of a paying customer, and is
not documented anywhere else.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Adam Devita]

dbase3 would give an error if you did not include all the non-aggregate
fields in the Group By. (One could also step forward/backward in a row-set,
so some crude windowing was available if one coded to do that.)

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

I'd be reluctant to write that query because it is non standard SQL and I
can't easily (5 minutes of searching) point at a document that tells me the
expected behavior. One usually codes to documented behavior because it it
is less likely to change without notice.

Thanks for the references about windowing functions.  Very interesting. The
point of what is heavy now vs in 2020 is well made.

Is Windowing a Major endeavor, better for sqlite 4?

Adam DeVita


On Wed, Aug 27, 2014 at 8:25 PM, Keith Medcalf  wrote:

>
> On Wednesday, 27 August, 2014 13:17, Petite Abeille said:
>
> >On Aug 26, 2014, at 2:09 AM, Keith Medcalf  wrote:
>
> >>  select id, category_id, name, min(price) as minprice
> >>from cat_pictures
> >> group by category_id;
> >>
> >> Done.  And no need for any windowing functions ...
>
> >This peculiar behavior is very unique to SQLite.
>
> Not really.  Sybase, SQL Server and DB2 do (or did do) the same thing.
>
> >Most reasonable SQL engines will throw an exception when confronted
> >with the above. SQLite calls it a feature. I personally see it as a
> >misfeature. ( Ditto with tagging an implicit limit 1  to scalar
> >queries. Anyway. )
>
> Well, I kind of like the former (group by) behaviour.  Tacking of an
> automatic "limit 1" on a scalar subquery may lead one to make bad
> assumptions about the shape of one's data, however, if one actually knows
> what one is doing, I don't think this is a problem either.
>
> >On the other hand, one could look at the current 'group by' behavior as
> >exhibited by SQLite as a precursor to a proper, more formalize, handling
> >of analytic functions :)
>
> Perhaps.  On the other hand, I really do not understand why people want
> "analytic functions" -- we did perfectly well analyzing data long before
> they were invented.  But then again I cannot understand why people think
> that Relational Databases using SQL are "better" for everything than good
> old-fashioned Network-Extended Navigational Databases.  But then again,
> maybe I'm just an old fart ...
>
> >___
> >sqlite-users mailing list
> >sqlite-users@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Edward Lau]

SQL Server 2008 does not have this behaviour.



SELECT   id, category_id, name, min(price) as minprice
FROM(
  SELECT   1  id ,1 category_id ,'name1' name ,1 price  -- Generate some 
data.
) AS cat_pictures
GROUP BY category_id;



produced:




Msg 8120, Level 16, State 1, Line 3


Column 'cat_pictures.id' is invalid in the select list because it is not 
contained in either an aggregate function or the GROUP BY clause.





-Original Message-
From: Keith Medcalf <kmedc...@dessus.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Wed, Aug 27, 2014 5:25 pm
Subject: Re: [sqlite] Window functions?



On Wednesday, 27 August, 2014 13:17, Petite Abeille said:

>On Aug 26, 2014, at 2:09 AM, Keith Medcalf <kmedc...@dessus.com> wrote:

>>  select id, category_id, name, min(price) as minprice
>>from cat_pictures
>> group by category_id;
>>
>> Done.  And no need for any windowing functions ...

>This peculiar behavior is very unique to SQLite. 

Not really.  Sybase, SQL Server and DB2 do (or did do) the same thing.

>Most reasonable SQL engines will throw an exception when confronted 
>with the above. SQLite calls it a feature. I personally see it as a 
>misfeature. ( Ditto with tagging an implicit limit 1  to scalar 
>queries. Anyway. )

Well, I kind of like the former (group by) behaviour.  Tacking of an automatic 
"limit 1" on a scalar subquery may lead one to make bad assumptions about the 
shape of one's data, however, if one actually knows what one is doing, I don't 
think this is a problem either.

>On the other hand, one could look at the current 'group by' behavior as
>exhibited by SQLite as a precursor to a proper, more formalize, handling
>of analytic functions :)

Perhaps.  On the other hand, I really do not understand why people want 
"analytic functions" -- we did perfectly well analyzing data long before they 
were invented.  But then again I cannot understand why people think that 
Relational Databases using SQL are "better" for everything than good 
old-fashioned Network-Extended Navigational Databases.  But then again, maybe 
I'm just an old fart ...

>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Keith Medcalf]

On Wednesday, 27 August, 2014 13:17, Petite Abeille said:

>On Aug 26, 2014, at 2:09 AM, Keith Medcalf  wrote:

>>  select id, category_id, name, min(price) as minprice
>>from cat_pictures
>> group by category_id;
>>
>> Done.  And no need for any windowing functions ...

>This peculiar behavior is very unique to SQLite. 

Not really.  Sybase, SQL Server and DB2 do (or did do) the same thing.

>Most reasonable SQL engines will throw an exception when confronted 
>with the above. SQLite calls it a feature. I personally see it as a 
>misfeature. ( Ditto with tagging an implicit limit 1  to scalar 
>queries. Anyway. )

Well, I kind of like the former (group by) behaviour.  Tacking of an automatic 
"limit 1" on a scalar subquery may lead one to make bad assumptions about the 
shape of one's data, however, if one actually knows what one is doing, I don't 
think this is a problem either.

>On the other hand, one could look at the current 'group by' behavior as
>exhibited by SQLite as a precursor to a proper, more formalize, handling
>of analytic functions :)

Perhaps.  On the other hand, I really do not understand why people want 
"analytic functions" -- we did perfectly well analyzing data long before they 
were invented.  But then again I cannot understand why people think that 
Relational Databases using SQL are "better" for everything than good 
old-fashioned Network-Extended Navigational Databases.  But then again, maybe 
I'm just an old fart ...

>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Edward Lau]

Hi Eduardo:

As Petite have mention, the following query 
selectid, category_id, name, min(price) as minprice
from   cat_pictures
group by category_id;



is missing the "id" and "name" columns in the GROUP BY clause ... per standard 
SQL standard requirement.  If the above works, it is a SQLite "feature" or 
mis-feature.


Regards.


-Original Message-
From: Petite Abeille <petite.abei...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Wed, Aug 27, 2014 2:04 pm
Subject: Re: [sqlite] Window functions?



On Aug 27, 2014, at 10:57 PM, Eduardo Morras <emorr...@yahoo.es> wrote:

> Sorry, don't understand why others will throw an exception in the group by, 
perhaps I'm misunderstanding the group by, but that should work on others 
engines.

Because not all expressions are accounted for, i.e.:

"not a GROUP BY expression

Cause: The GROUP BY clause does not contain all the expressions in the SELECT 
clause. SELECT expressions that are not included in a group function, such as 
AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY 
clause.

Action: Include in the GROUP BY clause all SELECT expressions that are not 
group 
function arguments.”

Try it. See what happen.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Petite Abeille]

On Aug 27, 2014, at 10:57 PM, Eduardo Morras  wrote:

> Sorry, don't understand why others will throw an exception in the group by, 
> perhaps I'm misunderstanding the group by, but that should work on others 
> engines.

Because not all expressions are accounted for, i.e.:

"not a GROUP BY expression

Cause: The GROUP BY clause does not contain all the expressions in the SELECT 
clause. SELECT expressions that are not included in a group function, such as 
AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY 
clause.

Action: Include in the GROUP BY clause all SELECT expressions that are not 
group function arguments.”

Try it. See what happen.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Eduardo Morras]

On Wed, 27 Aug 2014 21:17:05 +0200
Petite Abeille  wrote:

> 
> On Aug 26, 2014, at 2:09 AM, Keith Medcalf 
> wrote:
> 
> >  select id, category_id, name, min(price) as minprice
> >from cat_pictures
> > group by category_id;
> > 
> > Done.  And no need for any windowing functions …
> 
> This peculiar behavior is very unique to SQLite. Most reasonable SQL
> engines will throw an exception when confronted with the above.
> SQLite calls it a feature. I personally see it as a misfeature.
> ( Ditto with tagging an implicit limit 1  to scalar queries. Anyway. )

Sorry, don't understand why others will throw an exception in the group by, 
perhaps I'm misunderstanding the group by, but that should work on others 
engines.

> On the other hand, one could look at the current ‘group by’ behavior
> as exhibited by SQLite as a precursor to a proper, more formalize,
> handling of analytic functions…. :)



---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Petite Abeille]

On Aug 26, 2014, at 2:09 AM, Keith Medcalf  wrote:

>  select id, category_id, name, min(price) as minprice
>from cat_pictures
> group by category_id;
> 
> Done.  And no need for any windowing functions …

This peculiar behavior is very unique to SQLite. Most reasonable SQL engines 
will throw an exception when confronted with the above. SQLite calls it a 
feature. I personally see it as a misfeature. ( Ditto with tagging an implicit 
limit 1  to scalar queries. Anyway. )

On the other hand, one could look at the current ‘group by’ behavior as 
exhibited by SQLite as a precursor to a proper, more formalize, handling of 
analytic functions…. :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Wiktor Adamski]

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 |

Re: [sqlite] Window functions?

[Alek Paunov]

On 25.08.2014 20:47, Richard Hipp wrote:

On Mon, Aug 25, 2014 at 1:21 PM, forkandwait  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.



This is not a fresh idea, I am dropping it again, because I continue to 
think that something in that direction could be useful - mostly for 
studying SQLite in a university environment, but also for on-demand 
research like the OPs feature request, where nor compilation time, nor 
the full soundness of the generated code are critical:


- Single new supported SQLite feature bundle:
  - "Standard" database schema for representing "disassembled"
VDBE programs
  - SQLite extension consisting of:
- function "disassemble" for dumping prepared statement to the
  above schema
- function "assemble" for loading and linking VDBE program from
  given rowid of the schema for execution as prepared statement.

- Community project sqlite-asm-tools (possibly coordinated trough 
dedicated list @sqlite.org), aimed to help further development with more 
high level tools over that VDBE schema like: code templates application, 
code pattern marchers, manipulation methods, visualizations, etc.


It seems to me that the above basis will be enough for student projects 
like MERGE implementation or Stored procedures or even new languages 
experiments, just like the myriad of academic experiments on top of JVM, 
LLVM and other backends, some of them far away of the popularity of SQLite.


Kind regards,
Alek

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[FarSight Data Systems]

On Monday, August 25, 2014 09:25:47 PM Stephan Beal wrote:
> On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille 
> 
> 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.

I'm coming at this from a different perspective, as I'm using pysqlite and 
wxpython to display, in a windowed fashion, data in a SQLite database.

Would that solve the problem?

Mark


-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Keith Medcalf]

  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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of forkandwait
>Sent: Monday, 25 August, 2014 11:19
>To: sqlite-users@sqlite.org
>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
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[big stone]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Stephan Beal]

On Mon, Aug 25, 2014 at 9:43 PM, forkandwait  wrote:

> Stephan Beal  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Richard Hipp]

On Mon, Aug 25, 2014 at 4:20 PM, forkandwait  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Petite Abeille]

On Aug 25, 2014, at 10:20 PM, forkandwait  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[forkandwait]

big stone  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[big stone]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Petite Abeille]

On Aug 25, 2014, at 7:18 PM, forkandwait  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[forkandwait]

Stephan Beal  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Nelson, Erik - 2]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Petite Abeille]

On Aug 25, 2014, at 9:25 PM, Stephan Beal  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Stephan Beal]

On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille 
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Petite Abeille]

On Aug 25, 2014, at 7:04 PM, Simon Slavin  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Petite Abeille]

On Aug 25, 2014, at 7:12 PM, Richard Hipp  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Richard Hipp]

On Mon, Aug 25, 2014 at 1:21 PM, forkandwait  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[forkandwait]

> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[forkandwait]

Simon Slavin  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Richard Hipp]

On Mon, Aug 25, 2014 at 11:43 AM, forkandwait 
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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Simon Slavin]

On 25 Aug 2014, at 4:43pm, forkandwait  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Window functions?

[forkandwait]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions

[Elefterios Stamatogiannakis]

I'm using sqlite's count function to emulate OLAP functionality. 
Unfortunately count function is not exposed in sqlite, nevertheless it 
eases the pain of not having analytics functions in sqlite (lead, lag, 
median etc).

lefteris


Alexey Pechnikov wrote:
> Hello!
> 
> В сообщении от Monday 24 November 2008 19:16:46 Constantine Vassil написал(а):
>> OLAP functionality includes the concept of a sliding *window* that moves
>> down
>> through the input rows as they are processed. Additional calculations can
>> be
>>
>> performed on the data in the window as it moves, allowing further analysis
>> in a
>> manner that is more efficient than using semantically equivalent self-join
>> queries, or correlated subqueries.
>>
>> I am thinking this functionality is possible to implement working directly
>> with
>> the B-Tree.
>>
>> Does anyone has better ideas?
> 
> I think application-level solutions (R language for example) is better. I'm 
> using safe tcl 
> interpreter with custom functions and do send input data row by row to it.
> 
> Best regards, Alexey.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions

[Alexey Pechnikov]

Hello!

В сообщении от Monday 24 November 2008 19:16:46 Constantine Vassil написал(а):
> OLAP functionality includes the concept of a sliding *window* that moves
> down
> through the input rows as they are processed. Additional calculations can
> be
>
> performed on the data in the window as it moves, allowing further analysis
> in a
> manner that is more efficient than using semantically equivalent self-join
> queries, or correlated subqueries.
>
> I am thinking this functionality is possible to implement working directly
> with
> the B-Tree.
>
> Does anyone has better ideas?

I think application-level solutions (R language for example) is better. I'm 
using safe tcl 
interpreter with custom functions and do send input data row by row to it.

Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Window functions

[Constantine Vassil]

OLAP functionality includes the concept of a sliding *window* that moves
down
through the input rows as they are processed. Additional calculations can be

performed on the data in the window as it moves, allowing further analysis
in a
manner that is more efficient than using semantically equivalent self-join
queries, or correlated subqueries.

I am thinking this functionality is possible to implement working directly
with
the B-Tree.

Does anyone has better ideas?

Thanks,
Constantine
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users