Splunk Search

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

syusjk6
Engager

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

Tags (1)
0 Karma

Ayn
Legend

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
Legend

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.

Get Updates on the Splunk Community!

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...

4 Ways the Splunk Community Helps You Prepare for .conf25

.conf25 is right around the corner, and whether you’re a first-time attendee or a seasoned Splunker, the ...