lundi 29 juin 2015

How to partition by a cusomized sum value?

I have a table with the following columns: customer_id, event_date_time

I'd like to figure out how many times a customer triggers an event every 12 hours from the start of an event. In other words, aggregate the time between events for up to 12 hours by customer.

For example, if a customer triggers an event (in order) at noon, 1:30pm, 5pm, 2am, and 3pm, I would want to return the noon, 2am, and 3pm record.

I've written this query:

select
    cust_id,
    event_datetime,
    nvl(24*(event_datetime - lag(event_datetime) over (partition BY cust_id ORDER BY event_datetime)),0) as difference
from
  tbl

I feel like I'm close with this. Is there a way to add something like

 over (partition BY cust_id, sum(difference)<12 ORDER BY event_datetime)

Aucun commentaire:

Enregistrer un commentaire