tag:blogger.com,1999:blog-5672165237896126100.post1422035625145420945..comments2022-03-27T08:59:33.430-07:00Comments on Julian Hyde on Streaming Data, Open Source OLAP. And stuff.: SQL extension to allow FIRST_VALUE and LAST_VALUE in GROUP BY queryJulian Hydehttp://www.blogger.com/profile/17816795169191026372noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-5672165237896126100.post-58536468079079000142008-12-30T10:22:00.000-08:002008-12-30T10:22:00.000-08:00I tried your query on Oracle 10g, and it doesn'...I tried your query on Oracle 10g, and it doesn't work. You get multiple rows with the same hour and ticker.<BR/><BR/>SQL> create table trades0 (ts date, ticker varchar(4), shares int, price number);<BR/>Table created.<BR/><BR/>SQL> insert into trades0 values (to_date('2008-12-30 10:12:00', 'YYYY-MM-DD HH:MI:SS'), 'ORCL', 10, 25);<BR/>1 row created.<BR/><BR/>SQL> insert into trades0 values (to_date('2008-12-30 10:14:00', 'YYYY-MM-DD HH:MI:SS'), 'MSFT', 15, 30);<BR/>1 row created.<BR/><BR/>SQL> insert into trades0 values (to_date('2008-12-30 10:17:00', 'YYYY-MM-DD HH:MI:SS'), 'ORCL', 20, 27);<BR/>1 row created.<BR/><BR/>SQL> create view trades as select trunc(ts) as day, ts - trunc(ts) as timeOfDay, ticker, shares, price from trades0;<BR/>View created.<BR/><BR/>SQL> SELECT DISTINCT<BR/> day,<BR/> ticker,<BR/> SUM(shares) OVER (PARTITION BY day, ticker) AS volume,<BR/> LAST_VALUE(price) OVER (PARTITION BY day, ticker ORDER BY timeOfDay) AS closingPrice<BR/>FROM Trades;<BR/><BR/>DAY TICKER VOLUME CLOSINGPRICE<BR/>------------------ ------------ ---------- ------------<BR/>30-DEC-08 ORCL 30 27<BR/>30-DEC-08 ORCL 30 25<BR/>30-DEC-08 MSFT 15 30Julian Hydehttps://www.blogger.com/profile/17816795169191026372noreply@blogger.comtag:blogger.com,1999:blog-5672165237896126100.post-76776559176340558702008-12-30T08:07:00.000-08:002008-12-30T08:07:00.000-08:00Oracle would allows this asSELECT DISTINCT day,...Oracle would allows this as<BR/><BR/>SELECT DISTINCT<BR/> day,<BR/> ticker,<BR/> SUM(shares) OVER (PARTITION BY day, ticker) AS volume,<BR/> LAST_VALUE(price) OVER (PARTITION BY day, ticker ORDER BY timeOfDay) AS closingPrice<BR/>FROM TradesAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5672165237896126100.post-45887366273165174812008-09-07T08:18:00.000-07:002008-09-07T08:18:00.000-07:00I couldn't agree more that this should be standard...I couldn't agree more that this should be standard part of SQL. I find myself creating work-arounds to achieve this all the time, and my work arounds make me feel like a hack. For example, I might have solved you last query with a chain of views. In these views would include a query that got the max trade time for each day, and then another view to join the day and last trade time back up with price ..... yuck .... I sincerely would appreciate a better way to do this.Anonymousnoreply@blogger.com