Reputation: 49
I have one follow up question on an already solved Q (Oracle SQL: Counting in 4h?).
If there is another column in data sample presented, amount column, and I want to do all the same as it is proposed in solution
select t.*,
from (select t.*, lag(dt, 6) over (partition by employeeId order by dt) as ord6dt
from t
) t
where dt - ord6dt < 4.0/24
, with one extra condition sum of amount column > 10?
So query should list only those who had 6 orders in 4 hours AND sum(order_value)>10
.
Upvotes: 1
Views: 269
Reputation: 21973
the solution you have just looks back 6 rows (which may not be the first order that satisfied 4 hours old). So adding another analytic keeping that solution:
select *
from (select t1.*, lag(dt, 6) over (partition by employee order by dt) as ord6dt,
sum(order_value) over (partition by employee order by dt rows 6 preceding) val
from emptest t1
)
where dt - ord6dt < 4.0/24
and val >= 10;
would again only count 6 rows back (which may miss some real matches if we had over 6 orders in the last four hours).
we could model this instead:
select *
from (select *
from emptest
model
dimension by (employee, dt)
measures ( order_value, order_id, 0 order_sum, 0 number_of_orders)
rules (
order_sum[any,any] = sum(order_value)[cv(employee), dt between cv(dt)-(4/24) and cv(dt)],
number_of_orders[any,any] = count(*)[cv(employee), dt between cv(dt)-(4/24) and cv(dt)]
))
where order_sum > 10
and number_of_orders >= 6
order by employee, dt;
which looks for the number_of_orders at 4 or more and also checks the count over 4 hours too.
fiddle: http://sqlfiddle.com/#!4/3045d/1
Upvotes: 2