OLAP is so attractive from afar, it solves so many Big Important Problems, how can anyone not love it?
Well, it's easy not to love OLAP: just try something simple, something that should be easy given all the expressive power built in to the WINDOW clause and all the related window functions like, say, FIRST_VALUE.
Question: How do I select the first row in each partition of a table?
Here's an example:
CREATE TABLE t ( partition_id INTEGER NOT NULL, entry_id INTEGER NOT NULL, data VARCHAR ( 10 ) NOT NULL, PRIMARY KEY ( partition_id, entry_id ) ); INSERT t VALUES ( 10, 1, 'aaa' ); INSERT t VALUES ( 10, 2, 'bbb' ); INSERT t VALUES ( 10, 3, 'ccc' ); INSERT t VALUES ( 20, 1, 'ppp' ); INSERT t VALUES ( 20, 2, 'qqq' ); INSERT t VALUES ( 20, 3, 'rrr' ); INSERT t VALUES ( 30, 1, 'xxx' ); INSERT t VALUES ( 30, 2, 'yyy' ); INSERT t VALUES ( 30, 3, 'zzz' ); SELECT [the first row in each partition of t]; partition_id,entry_id,data 10,1,'aaa' 20,1,'ppp' 30,1,'xxx'
Answer: OLAP to the rescue! It's got the PARTITION keyword, and ORDER BY, and RANGE, and FIRST_VALUE, everything you need!
Hubba hubba, here we go! ...
SELECT partition_id, FIRST_VALUE ( entry_id ) OVER partition_id_window AS first_entry_id, FIRST_VALUE ( data ) OVER partition_id_window AS first_data FROM t WINDOW partition_id_window AS ( PARTITION BY partition_id ORDER BY entry_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) ORDER BY partition_id; partition_id,first_entry_id,first_data 10,1,'aaa' 10,1,'aaa' 10,1,'aaa' 20,1,'ppp' 20,1,'ppp' 20,1,'ppp' 30,1,'xxx' 30,1,'xxx' 30,1,'xxx'
Well, that sucks!That's not even close!
You can't code RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING, that throws a syntax error. In fact, the RANGE clause seems to be singularly useless, it has so few options.
It's almost like the WINDOW clause was designed by a standards committee.
Old SchoolRemember GROUP BY? Yeah, we used to think THAT was complicated before the WINDOW clause came along.
SELECT t.* FROM t INNER JOIN ( SELECT partition_id AS partition_id, MIN ( entry_id ) AS min_entry_id FROM t GROUP BY partition_id ) AS first_row ON t.partition_id = first_row.partition_id AND t.entry_id = first_row.min_entry_id ORDER BY t.partition_id; partition_id,entry_id,data 10,1,'aaa' 20,1,'ppp' 30,1,'xxx'
Tell me it ain't so!Please, someone tell me the WINDOW clause isn't so hard to get.
That may be wishful thinking, however...
"Window operations don’t add expressive power to the SQL language because other constructions, which may require multiple statements, can provide the same functionality as window functions."
Glenn Paulley in The Window operator and window aggregate functions, March 31st, 2009