3 ms·

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 sin

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.

lukaseder 10y ago
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/webcontent/1965433.pdf http://www.oracle.com/ocom/groups/public/@otn/documents/webc...
- jnordwick 10y ago
  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.
  - lukaseder 10y ago
    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/html/match-recognize.html 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...

lukaseder 10y ago

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/webcontent/1965433.pdf http://www.oracle.com/ocom/groups/public/@otn/documents/webc...

jnordwick 10y ago
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.
- lukaseder 10y ago
  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/html/match-recognize.html 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...