Johnny CRO
Johnny CRO

Reputation: 49

Oracle SQL: Count and sum in e.g. 4 hours?

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

Answers (1)

DazzaL
DazzaL

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

Related Questions