Window Functions in SQL (2013) • Hacker News Board

Re: Window Functions in SQL (2013)

#11

Post by nickpeterson » Wed, Oct 05, 2016, 4:47 PM UTC

mmsmatt wrote: post #3

> Once you know window functions, you risk putting them all over the place Agreed. A few years of writing expressive, powerful, performant SQL in big blocks will really change your mental model of data across _all_ languages. I highly recommend a deep dive into the relational world, and I feel super grateful for the SQL-focused job I held previously. Brought those lessons back with me to both FP and OO.

They are extremely useful, although they seem to vary in performance quite a bit (SQL Server experience here, so YMMV). I'm always a bit shocked how little SQL most software developers know, especially because it's often the bottleneck if used poorly.

Re: Window Functions in SQL (2013)

#12

Post by thom » Wed, Oct 05, 2016, 5:31 PM UTC

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+?

Re: Window Functions in SQL (2013)

#13

Post by cygned » Wed, Oct 05, 2016, 5:37 PM UTC

The most useful feature imho is temporal data. Too bad Postgres does not support it.

Re: Window Functions in SQL (2013)

#14

Post by lukaseder » Wed, Oct 05, 2016, 5:51 PM UTC

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…

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

Re: Window Functions in SQL (2013)

#15

Post by lukaseder » Wed, Oct 05, 2016, 5:52 PM UTC

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?

Re: Window Functions in SQL (2013)

#16

Post by jnordwick » Wed, Oct 05, 2016, 5:54 PM UTC

saurik wrote: post #9

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…

Exciting! Can you point at something where one could learn about this specific feature of these kinds of databases? (Maybe like, "this is an example of that aspect of their syntax in particular, go read how it works", or "here is an article taking about how awesome this property is".)

Not exciting. Most of the array processing / streaming facilities are steaming piles.

You have Hadoop/MR and Monet in the OSS group. SysbaseIQ, KDB, Teradata, Vertica, VectorWise, etc. all provide either extended SQL or additional query support to use ordering information in some (often weak) way.

You can even count Oracle In Memory Analytics with TSQL with its cursor model if you really want.

For example for KDB to get the last price and sum of volumer for each second of AAPL you would do in KSQL:

  select last px, sum vol by time.second from trade where sym=`AAPL

on it can be done in its lower level language K/Q as (approx, I'm a little rusty)

  g:group trade.time.second @ where trade.sym=`AAPL
  flip`px`vol!(last each trade.px g; sum each trade.vol g)

Re: Window Functions in SQL (2013)

#17

Post by [deleted] » Wed, Oct 05, 2016, 5:58 PM UTC

lukaseder wrote: post #14

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…

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

[deleted]

Re: Window Functions in SQL (2013)

#18

Post by jnordwick » Wed, Oct 05, 2016, 6:10 PM UTC

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+?

This is about the closest I know. Kx does a lot with utilities for metering and IoT stuff, but I haven't really kept up with that side of things as much as I do with the finance side. However I do know there are some using it for geospatial, and they are always eager to push into new areas so love the challenge. If a new datatype/index type needs to be done to support geospatial and be the fastest there, they are always up to the challenge.

There were a couple really, really cool tech talks I've been to. The title alone gives it props :)

"where Rumsfeldism & Kx collide"

https://kx.com/2014/12/07/visualizing-really-big-data/

in case you're curious about one of the other: a polymer hypergrid with an absurd amount of real time updating data:

http://openfin.github.io/fin-hypergrid-polymer-demo/componen...

Re: Window Functions in SQL (2013)

#19

Post by teilo » Wed, Oct 05, 2016, 6:19 PM UTC

Windows functions are a feature I cannot live without. They achieve results that otherwise could only be achieved with complex and ugly CASE and embedded SELECTs. Another reason Postgres is superior to MySQL.

Re: Window Functions in SQL (2013)

#20

Post by jnordwick » Wed, Oct 05, 2016, 6:25 PM UTC

lukaseder wrote: post #14

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…

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 SQL lacks so much of the machinery for order-dependent processing (this was a feature when SQL was conceived remember).