Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Window Functions in SQL (2013)

blog.jooq.org

Post reply on HN
Print view View original

31–36 of 36 posts

Re: Window Functions in SQL (2013)

#31

Post by lukaseder »

jnordwick wrote: post #30
thom wrote: post #29

Earlier quoted context omitted.

Just to clarify, when you say LEAD/LAG here, do you actually mean ROWS/RANGE? So, approximately: sum(price * quantity) over w / sum(quantity) over w as VWAP ⋮ window w as (partition by stock order by time range 30000 preceding) Totally willing to believe first-hand experience, but surprised and disappointed if it's impossible to make that fast. (Apologies if I'm totally misunderstanding the use case).

Is there really that much of a difference between LEAD/LAG and OVER+RANGE when using an aggregation function? I'm sure there is, but it has been a few years since I had to deal with some of this. There are probably also difference between OVER when used in TSQL and PLSQL. Last time I had to do this was actually with real-time advertising bidding, and it was across keywords, not ticker symbols (many more, much sparser…

What you're doing certainly doesn't sound like an easy job for window functions, although it might be possible with RANGE + interval - at least in Oracle 12c.

How about Oracle 12c's MATCH_RECOGNIZE, though?

http://www.oracle.com/ocom/groups/public/@otn/documents/webc...

Re: Window Functions in SQL (2013)

#32

Post by jnordwick »

lukaseder wrote: post #31
jnordwick wrote: post #30

Earlier quoted context omitted.

Is there really that much of a difference between LEAD/LAG and OVER+RANGE when using an aggregation function? I'm sure there is, but it has been a few years since I had to deal with some of this. There are probably also difference between OVER when used in TSQL and PLSQL. Last time I had to do this was actually with real-time advertising bidding, and it was across keywords, not ticker symbols (many more, much sparser…

What you're doing certainly doesn't sound like an easy job for window functions, although it might be possible with RANGE + interval - at least in Oracle 12c. How about Oracle 12c's MATCH_RECOGNIZE, though? http://www.oracle.com/ocom/groups/public/@otn/documents/webc...

Thanks. That was very interesting. Overall, a total band-aid of course, but still better than nothing. I can completely see doing ad hoc queries with that (and I can't wait to try it out) but I fear the performance hit of trying to build a system around it :)

It didn't give any examples of queries going both directions, e.g. the down-up V first example. You probably often also want the up-down ^ shapes too. It would be nice if the PATTERN had alternation too "STRT (DOWN+ UP+)|(UP+ DOWN+)" so you didn't need to double your queries. Or else you're probably better off creating a temp on CUR-LAG(1) to give you the sign and then pattern match the sign (if that makes sense).

Oracle really does go out of their way to keep SQL modern though. I remember the first time I saw a "CONNECT BY". lol. I had an interview a few months later and was asked to write an SQL query to basically traverse a tree (the gist of it). I being a young smart ass gave the correct answer (you can't) and proceeded to write the START / CONNECT just to show off a little.

Re: Window Functions in SQL (2013)

#33

Post by lukaseder »

jnordwick wrote: post #32
lukaseder wrote: post #31

Earlier quoted context omitted.

What you're doing certainly doesn't sound like an easy job for window functions, although it might be possible with RANGE + interval - at least in Oracle 12c. How about Oracle 12c's MATCH_RECOGNIZE, though? http://www.oracle.com/ocom/groups/public/@otn/documents/webc...

Thanks. That was very interesting. Overall, a total band-aid of course, but still better than nothing. I can completely see doing ad hoc queries with that (and I can't wait to try it out) but I fear the performance hit of trying to build a system around it :) It didn't give any examples of queries going both directions, e.g. the down-up V first example. You probably often also want the up-down ^ shapes too. It would…

Yes, it's a band-aid. But perhaps it will feel less so, if you're looking at the original invention by Esper Tech (I have no experience with it, just know that I've seen MATCH_RECOGNIZE there first): http://www.espertech.com/esper/release-5.1.0/esper-reference...

Hah, yeah CONNECT BY is great for simple recursion, but for more complex (probably less performing) cases, I prefer the standard approach with CTEs...

Re: Window Functions in SQL (2013)

#34

Post by jnordwick »

Jweb_Guru wrote: post #25
jnordwick wrote: post #20

Earlier quoted context omitted.

The doesn't help that much since you are probably already using an index for filter criteria, the table is probably unkeyed (fact table). The case it does work is that you are ordered on timestamp (for example), the query planner properly figures to use any filtering indexes and sequential indexes, ... magic occurs Now the job is making an SQL statement that the query planner can actual understand and optimize since…

Eh, there's been work done on this, e.g. order dependencies. Not sure how much of it has made it into commercial SQL engines, though.

link?

Re: Window Functions in SQL (2013)

#35

Post by Jweb_Guru »

jnordwick wrote: post #34
Jweb_Guru wrote: post #25

Earlier quoted context omitted.

Eh, there's been work done on this, e.g. order dependencies. Not sure how much of it has made it into commercial SQL engines, though.

link?

Here's one: https://arxiv.org/pdf/1208.0084v1.pdf

(Note the involvement of IBM. Dunno whether this has made it into DB2, but a lot of really interesting research has; also keep in mind that whatever IBM wants to put in DB2 has a high probability of making it into the SQL standard).

Re: Window Functions in SQL (2013)

#36

Post by aksyn »

thom wrote: post #12
jnordwick wrote: post #8

LAG/LEAD are basically SQL having to give in a little to the timeseries and column db people. It obliterates the set theoretic nature of SQL, and they are dog slow. This is why any decent columnar/timeseries db has its own often arrish query language to take advantages of ordering information inherent in the data. There is an order in that index and SQL table, you just don't have good tools to access and use it. This…

Do you know what the story is for using geospatial data with KDB+?

http://code.kx.com/wiki/Cookbook/Geospatial
Post reply on HN