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

[syusjk6]

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

[Ayn]

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

[lguinn2]

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.