Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Compare Current Row with Previous row + Window Function

3_stripes
Contributor

Hello you awesome people!

Pardon in advance for not providing a query example, I still haven't learnt how to do it(Next thing on my list tho!)

So it is the following, I have created something along the lines of this:

 

 

SELECT
COUNT(ORDERS) AS TOTAL_ORDERS -- counting total orders
COUNT(DISTINCT ORDERS) TOTAL_DIST_ORDERS, -- counting distinct orders
LOCAL.TOTAL_ORDERS/LOCAL.TOTAL_DIST_ORDERS AS RATIO_ORDERS, -- ratio of orders
AVG(LOCAL.RATIO_ORDERS) OVER BY(ORDER BY DATE ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MOVING_AVERAGE, -- moving average of ratio of orders for the past 4 months
LOCAL.MOVING_AVERAGE - LAG(MOVING_AVERAGE,1) OVER (ORDER BY LOCAL.DATE) AS day_difference -- calculate the difference of today with the previous day
FROM ORDER_TABLE

 

 

 

Problem:

Line number 6, variable name "day_difference" does not execute on my side, because and I quote "SQL Error [42000]: Analytic function not allowed here" 

Tried:

I tried the code with the variable TOTAL ORDERS and it worked just fine.

Question:

Is there a way to make it work? Is there a simpler solution?

 

Best regards!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

3_stripes
Contributor

Hello everyone, I found a solution:

SELECT 
A.*,
MOVING_AVERAGE - LAG(MOVING_AVERAGE,1) OVER (ORDER BY DATE) AS day_difference -- calculate the difference of today with the previous day
from(
SELECT
COUNT(ORDERS) AS TOTAL_ORDERS -- counting total orders
COUNT(DISTINCT ORDERS) TOTAL_DIST_ORDERS, -- counting distinct orders
LOCAL.TOTAL_ORDERS/LOCAL.TOTAL_DIST_ORDERS AS RATIO_ORDERS, -- ratio of orders
AVG(LOCAL.RATIO_ORDERS) OVER BY(ORDER BY DATE ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MOVING_AVERAGE, -- moving average of ratio of orders for the past 4 months
FROM ORDER_TABLE) AS A

 

View solution in original post

2 REPLIES 2

mwellbro
Xpert

Hi @3_stripes ,

you need to be aware of how much nesting of agg- and analytical-functions you are creating with you construct.

I can´t quite make out how your order_table works, I´m assuming since you work with row offsets in your window every row
is for a month ? But then, how would we end up with differenct count and count_dist ?! well, not too important here.....

Maybe this could help you a bit further:

with order_table as (
          select 4711   as orders,10 as val,to_date('20100101','YYYYMMDD') as dat from dual
union all select 4711            ,12       ,to_date('20100101','YYYYMMDD')        from dual
union all select 4712            ,15       ,to_date('20100101','YYYYMMDD') 
)
,lv2 as (
select 
 count(orders)          as total_orders
,count(distinct orders) as total_dist_orders
,local.total_orders/local.total_dist_orders as ratio_orders
,dat
--,avg(local.ratio_orders) over(order by dat rows between 4 preceding and current row) as moving_average
--,local.moving_average - lag(local.moving_average,1) over(order by dat) as day_difference
 from order_table
group by dat
)
select 
a.*
,avg(ratio_orders) over(order by dat rows between 4 preceding and current row) as moving_average
 from lv2 a
;


Try to further "deconstruct" your initial query so that the basis for your window functions isn´t already an aggregate function - that should get you on the right track, I think.

Cheers,
Malte

3_stripes
Contributor

Hello everyone, I found a solution:

SELECT 
A.*,
MOVING_AVERAGE - LAG(MOVING_AVERAGE,1) OVER (ORDER BY DATE) AS day_difference -- calculate the difference of today with the previous day
from(
SELECT
COUNT(ORDERS) AS TOTAL_ORDERS -- counting total orders
COUNT(DISTINCT ORDERS) TOTAL_DIST_ORDERS, -- counting distinct orders
LOCAL.TOTAL_ORDERS/LOCAL.TOTAL_DIST_ORDERS AS RATIO_ORDERS, -- ratio of orders
AVG(LOCAL.RATIO_ORDERS) OVER BY(ORDER BY DATE ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MOVING_AVERAGE, -- moving average of ratio of orders for the past 4 months
FROM ORDER_TABLE) AS A