CODEWITHSUNDEEP
sqlstored-proceduresnetezza
RAJESH
RAJESH

Reputation: 404

Netezza SQL for collecting values from Successive records

I have some data in web_event table of Netezza in this below format. 

**vstr_id  |  sessn_id  |  event_ts | wbpg_nm** 
*V1       |  V1S1      |  02-02-2015 09:30:00 | /home/contacts*
*V1       |  V1S1      |  02-02-2015 09:20:00 | /home/login*
*V1       |  V1S1      |  02-02-2015 09:50:00 | /home/search*
*V2       |  V2S1      |  02-02-2015 09:10:00 | /home*
*V2       |  V2S2      |  02-02-2015 09:20:00 | /home/news*

This is my source table.

I am trying to use that web_event table and create another table like below.

I am trying to to do 

Insert into page_path (select VSTR_ID,
           SESSN_ID,
           EVENT_ts,
           *?* as PREV_WBPG_NM,
           WBPG_NM,
           *?* as NXT_WBPG_NM,
           from web_event)

I want the page_path table to be loaded like below.

In this below table i want to load the data for previous page and next page columns based on the sorted order of event_ts column.

How can we do this in Netezza or any SQL query?

**vstr_id  |  sessn_id  |  event_ts | previous_wbpg_nm | wbpg_nm | next_wbpg_nm**  
*V1       |  V1S1      |  02-02-2015 09:30:00 | /home/login | /home/contacts | /home/search*
*V1       |  V1S1      |  02-02-2015 09:20:00 | null | /home/login | /home/contacts*
*V1       |  V1S1      |  02-02-2015 09:50:00 | /home/contacts | /home/search | null *
*V2       |  V2S1      |  02-02-2015 09:10:00 | null | /home/ | null*
*V2       |  V2S2      |  02-02-2015 09:20:00 | null | /home/news | null*

Upvotes: 0

Views: 113

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can do this in a SQL Query just by using lag() and lead():

select vstr_id, sessn_id, event_ts,
       lag(wbpg_nm) over (partition by vstr_id, sessn_id order by event_ts) as prev_wbpg_nm,
       wbpg_nm,
       lead(wbpg_nm) over (partition by vstr_id, sessn_id order by event_ts) as next_wbpg_nm
from page_path;

Upvotes: 2

Related Questions