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

21–30 of 36 posts

Re: Window Functions in SQL (2013)

#21

Post by alfalfasprout »

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…

KDB+ is incredibly well designed, fast, and very mature. But it has one big problem. At $$$ / year (can't divulge pricing due to NDAs but it's > $10^4 for a very modest installation) you're talking about a massive expense if you want any kind of large deployment.

Re: Window Functions in SQL (2013)

#22

Post by thom »

lukaseder wrote: post #15
thom wrote: post #4

I think my only real complaint about window functions is that you can't access the 'current' row in aggregates and 'filter' conditions. It means very complex look aheads or look behinds aren't really possible. It would also be great if windows could be function parameters in Postgres. I like to abstract complicated conditionals and calculations into functions, but it falls down if you're operating over a window.

Would you mind providing an example of what you'd like to do, which is too complex for window functions?

Apologies for contrived examples. First, it'd be nice to be able to use both rows inside some aggregates, e.g.:

    min(ST_Distance(this.point, foo.point)) over (blah)
That is, for each 'foo', get me the minimum distance to another foo in the current window.

The second peeve is to do with filters, e.g.:

    count(*) filter (where this.time - foo.time
That is, how many events of type 42 are there up to 5 seconds before the current event.

In both cases, it's sad not being able to address the current row as well as the row in the window. I'm not saying either form is completely impossible to get working somehow, I just think it'd be a nice feature.

Re: Window Functions in SQL (2013)

#23

Post by jnordwick »

alfalfasprout wrote: post #21
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…

KDB+ is incredibly well designed, fast, and very mature. But it has one big problem. At $$$ / year (can't divulge pricing due to NDAs but it's > $10^4 for a very modest installation) you're talking about a massive expense if you want any kind of large deployment.

I love KDB. When I first started, I hated it... with a fierce passion. I didn't see the point, but I was young and dumb and better looking.

When the "Why's that company so big? I can do that in a weekend." article came out last week:

https://news.ycombinator.com/item?id=12626314

I immediately thought of Kx and KDB and how it is literally the exact opposite. If people only knew how small the firm started with Arthur hacking away seemingly out developing entire corporations they would laugh.

"Yeah give me about two years and a dev team of a about 20 then about 10 for QA and we've have something shippable... abother year or two for performance... 5 years we'll be in the hunt." Yeah, we'll this guy over here basically did it in six months with only two others. And it's 100x faster than anything else. Why can't you do that?

Given his history too with A+ at Morgan Stanley, it wouldn't be the first time a team of a small handful he was at the core of beat the socks off everybody else either. Some people just have this amazing ability to see simplicity. I've had the pleasure of being around a few people boss like that.

Re: Window Functions in SQL (2013)

#24

Post by [deleted] »

jnordwick wrote: post #20
lukaseder wrote: post #14

Earlier quoted context omitted.

I would be very surprised if LEAD and LAG couldn't profit from indexing, if you design your query / window function in a way that it actually can profit. Perhaps you're hinting at the idea that it's too easy to screw this up as a user, for it to be inefficient

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…

[deleted]

Re: Window Functions in SQL (2013)

#25

Post by Jweb_Guru »

jnordwick wrote: post #20
lukaseder wrote: post #14

Earlier quoted context omitted.

I would be very surprised if LEAD and LAG couldn't profit from indexing, if you design your query / window function in a way that it actually can profit. Perhaps you're hinting at the idea that it's too easy to screw this up as a user, for it to be inefficient

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.

Re: Window Functions in SQL (2013)

#26

Post by lukaseder »

jnordwick wrote: post #20
lukaseder wrote: post #14

Earlier quoted context omitted.

I would be very surprised if LEAD and LAG couldn't profit from indexing, if you design your query / window function in a way that it actually can profit. Perhaps you're hinting at the idea that it's too easy to screw this up as a user, for it to be inefficient

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…

What if I'm designing a covering index to cover for both my filtering and ordering needs? The SQL engine should pick that up in my opinion and avoid needing to re-order anything.

Of course, SQL is by far more general purpose than producing ordered stuff. In fact, ordering is a completely non-relational thing that was even criticised in SQL in early days (such as offsets, limits, duplication, etc.). But SQL has learned to shoehorn foreign concepts into the language / platform for a long time, so I'm just curious about a concrete example where SQL window functions fail (and couldn't be fixed) in SQL for you.

Re: Window Functions in SQL (2013)

#27

Post by lukaseder »

thom wrote: post #22
lukaseder wrote: post #15

Earlier quoted context omitted.

Would you mind providing an example of what you'd like to do, which is too complex for window functions?

Apologies for contrived examples. First, it'd be nice to be able to use both rows inside some aggregates, e.g.: min(ST_Distance(this.point, foo.point)) over (blah) That is, for each 'foo', get me the minimum distance to another foo in the current window. The second peeve is to do with filters, e.g.: count(*) filter (where this.time - foo.time That is, how many events of type 42 are there up to 5 seconds before the cu…

Thanks for clarifying. Oh, I see yes indeed. We can only nest aggregate functions inside of window functions, not the other way round (unless resorting to derived tables, views, ctes, etc.). Indeed, it's logical to know why it's not possible (the way the language was designed), but the language could have been designed differently.

Do note that Oracle has the KEEP clause that might probably solve your issue with aggregate functions only... But it's a rather esoteric, vendor-specific extension.

Anyway, I do agree, what you're describing would be a very nice feature.

Re: Window Functions in SQL (2013)

#28

Post by jnordwick »

lukaseder wrote: post #26
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…

What if I'm designing a covering index to cover for both my filtering and ordering needs? The SQL engine should pick that up in my opinion and avoid needing to re-order anything. Of course, SQL is by far more general purpose than producing ordered stuff. In fact, ordering is a completely non-relational thing that was even criticised in SQL in early days (such as offsets, limits, duplication, etc.). But SQL has learne…

Try doing a VWAP in a group by 5000 stock symbol over a LAG of last 30000 - basically a sample every 10 millis for 5 minutes. This is the simple case where you can actually use LAG of a fixed amount.

(When the query returns tomorrow morning you realize that LAG was actually useless because your observation arrivals weren't periodic and you need to deal with / bucket for that to actually get usable results).

We used to run a lot of LEAD/LAG queries on a very, very large Oracle install could never get the performance we wanted out of it. Basically turned it into a very expensive file server that spat out HDF that we then processed by a mix of shell and python. Should not have been faster than the overpriced heater in the corner we called a database but was.

Re: Window Functions in SQL (2013)

#29

Post by thom »

jnordwick wrote: post #28
lukaseder wrote: post #26

Earlier quoted context omitted.

What if I'm designing a covering index to cover for both my filtering and ordering needs? The SQL engine should pick that up in my opinion and avoid needing to re-order anything. Of course, SQL is by far more general purpose than producing ordered stuff. In fact, ordering is a completely non-relational thing that was even criticised in SQL in early days (such as offsets, limits, duplication, etc.). But SQL has learne…

Try doing a VWAP in a group by 5000 stock symbol over a LAG of last 30000 - basically a sample every 10 millis for 5 minutes. This is the simple case where you can actually use LAG of a fixed amount. (When the query returns tomorrow morning you realize that LAG was actually useless because your observation arrivals weren't periodic and you need to deal with / bucket for that to actually get usable results). We used t…

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).

Re: Window Functions in SQL (2013)

#30

Post by jnordwick »

thom wrote: post #29
jnordwick wrote: post #28

Earlier quoted context omitted.

Try doing a VWAP in a group by 5000 stock symbol over a LAG of last 30000 - basically a sample every 10 millis for 5 minutes. This is the simple case where you can actually use LAG of a fixed amount. (When the query returns tomorrow morning you realize that LAG was actually useless because your observation arrivals weren't periodic and you need to deal with / bucket for that to actually get usable results). We used t…

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). We had a 20 millisecond budget, and had to break the queries up to at least get partial results to push out bid even if we didn't have all the results back in from the database that we wanted.

We used OVER+RANGE for average price, but needed LAG for first derivative (is the price trending up or down). We had to start doing roll ups of the data hourly and be constantly trimming to keep the real-time performance high enough, but that meant we needed to have have a separate off-line analytical system. Common to do, but still I think it can be done without it if you use the correct tech stack.

Post reply on HN