topic Translating SQL Query into Splunk Search Query: "LAG(...) OVER (...)" in Splunk Search

Translating SQL Query into Splunk Search Query: "LAG(...) OVER (...)"

syusjk6 — Wed, 05 Dec 2012 05:34:43 GMT

Hi, I got stuck in translating the following SQL query into Splunk Search Query:

"LAG ( BCOLLDT, 1) OVER ( PARTITION BY PID ORDER BY PID, BCOLLDT, LASTREPTDT, PRCPDD, EXECPRCPUNIQNO )"

Here, BCOLLDT, PID, LASTREPTDT, PRCPDD, EXECPRCPUNIQNO are fields, respectively.

Any help ??

lguinn2 — Wed, 05 Dec 2012 08:28:18 GMT

It would help those of us who don't use Oracle SQL if we could understand the problem in English. My interpretation is

For each PID, sort the events by the list of fields, then compare the BCOLLDT value in each event with the BCOLLDT value in the preceding event.

But I could be very wrong. And that still doesn't tell me - "what are you trying to accomplish?"

I often find that a completely different approach with Splunk can give a better answer more quickly. I hesitate to simply translate from SQL to SPL.

Ayn — Wed, 05 Dec 2012 08:46:02 GMT

I'm not very proficient in Oracle SQL syntax either, but maybe this could help somehow? http://splunk-base.splunk.com/answers/41986/lead-lag-in-splunk