I want to calculate number of changes at E-Mail address per year:
WITH t AS (SELECT 1 as customer_id,'a@gmail.com' as email,2010 as year FROM DUAL
UNION
SELECT 1 as customer_id,'a@yahoo.com' as email,2011 as year FROM DUAL
UNION
SELECT 2 as customer_id,'b@yahoo.com' as email,2010 as year FROM DUAL
UNION
SELECT 3 as customer_id,'c@yahoo.com' as email,2012 as year FROM DUAL
UNION
SELECT 3 as customer_id,'c@google.com' as email,2012 as year FROM DUAL
)
SELECT year, customer_id,email,
LAG(email,1,0) OVER(ORDER BY email) as email_prev,
sum(case when email <> LAG(sal, 1, 0) OVER (ORDER BY sal) then 1 else 0 end ) changes
FROM t
;
ORA-30483: window functions are not allowed here.
Results should be:
Year changes
==== =======
2010 1
2012 1
2018 20
Your query is not what you really trying, is it? there's no SAL column in the t CTE, so the query won't run, even if you take care of the error. Try this query:
WITH t AS (SELECT 1 as customer_id,'a@gmail.com' as email,2010 as year FROM DUAL
UNION
SELECT 1 as customer_id,'a@yahoo.com' as email,2011 as year FROM DUAL
UNION
SELECT 2 as customer_id,'b@yahoo.com' as email,2010 as year FROM DUAL
UNION
SELECT 3 as customer_id,'c@yahoo.com' as email,2012 as year FROM DUAL
UNION
SELECT 3 as customer_id,'c@google.com' as email,2012 as year FROM DUAL
),
t1 as ( SELECT year, customer_id,email,
LAG(email,1) OVER(partition by customer_id ORDER BY email) as email_prev
from t)
select year,customer_id,
sum(case when email <> email_prev or email_prev is null then 1 else 0 end ) changes
FROM t1
group by year,customer_id;
This is the output:
YEAR CUSTOMER_ID CHANGES
2010 2 1
2010 1 1
2011 1 1
2012 3 2