我有这样一张类似的表:
Category Product City Yrar Sales
1 a BJ 2006 100
1 a BJ 2007 120
1 a BJ 2008 200
1 a SH 2006 80
1 a SH 2007 81
1 a SH 2008 82
如何计算每个产品在不同的城市的年增长率呢?
谢谢大家!
Category Product City Yrar Sales
1 a BJ 2006 100
1 a BJ 2007 120
1 a BJ 2008 200
1 a SH 2006 80
1 a SH 2007 81
1 a SH 2008 82
如何计算每个产品在不同的城市的年增长率呢?
谢谢大家!
BY suncrafted
*/WITH TEST AS
(
SELECT 1 AS CATEGORY, 'a' AS PRODUCT, 'BJ' AS CITY, '2006' AS YEAR, 100 AS SALES FROM DUAL UNION
SELECT 1 AS CATEGORY, 'a' AS PRODUCT, 'BJ' AS CITY, '2007' AS YEAR, 120 AS SALES FROM DUAL UNION
SELECT 1 AS CATEGORY, 'a' AS PRODUCT, 'BJ' AS CITY, '2008' AS YEAR, 200 AS SALES FROM DUAL UNION
SELECT 1 AS CATEGORY, 'a' AS PRODUCT, 'SH' AS CITY, '2006' AS YEAR, 80 AS SALES FROM DUAL UNION
SELECT 1 AS CATEGORY, 'a' AS PRODUCT, 'SH' AS CITY, '2007' AS YEAR, 81 AS SALES FROM DUAL UNION
SELECT 1 AS CATEGORY, 'a' AS PRODUCT, 'SH' AS CITY, '2008' AS YEAR, 82 AS SALES FROM DUAL
)SELECT R.CATEGORY,R.PRODUCT,R.CITY,R.YEAR,R.SALES,NVL2(R.SALES_LASTYEAR,(R.SALES-R.SALES_LASTYEAR)/R.SALES_LASTYEAR,NULL) AS 年增长率
FROM
(
SELECT T.CATEGORY,T.PRODUCT,T.CITY,T.YEAR,T.SALES,
LAG(T.SALES,1,NULL) OVER(PARTITION BY T.CATEGORY,T.PRODUCT,T.CITY ORDER BY T.CATEGORY,T.PRODUCT,T.CITY,T.YEAR ) AS SALES_LASTYEAR
FROM TEST T
ORDER BY T.CATEGORY,T.PRODUCT,T.CITY,T.YEAR
) R
1 a BJ 2006 100
1 a BJ 2007 120 0.2
1 a BJ 2008 200 0.666666666666667
1 a SH 2006 80
1 a SH 2007 81 0.0125
1 a SH 2008 82 0.0123456790123457
SQL> col product format a4;
SQL> col city format a4;
SQL> col year format a8;
SQL> select category,product,city,year,sales, (sales-(lag(sales) over(partition
by category, product, city order by category, product, city, year)))/sales col3
from t_salary order by category, product, city, year;CATEGORY PROD CITY YEAR SALES COL3
-------- ---- ---- -------- ---------- ----------
1 a BJ 2006 100
1 a BJ 2007 120 .166666667
1 a BJ 2008 200 .4
1 a SH 2006 80
1 a SH 2007 81 .012345679
1 a SH 2007 82 .012195122已选择6行。
是的,搞错了。应该改成
SQL> select category,product,city,year,sales, ((sales-(lag(sales) over(partition
by category, product, city order by category, product, city, year)))/(lag(sales
) over(partition by category, product, city order by category, product, city, ye
ar))) col3 from t_salary order by category, product, city, year;CATEGORY PROD CITY YEAR SALES COL3
-------- ---- ---- -------- ---------- ----------
1 a BJ 2006 100
1 a BJ 2007 120 .2
1 a BJ 2008 200 .666666667
1 a SH 2006 80
1 a SH 2007 81 .0125
1 a SH 2007 82 .012345679已选择6行。