【Oracle SQL】计算同比与环比（列转行进行偏移）

最新推荐文章于 2024-08-15 16:49:50 发布

江中洲

最新推荐文章于 2024-08-15 16:49:50 发布

阅读量1.7k

点赞数 3

分类专栏：大数据实习文章标签： oracle sql 数据库

本文链接：https://blog.youkuaiyun.com/qq_60823340/article/details/125887225

版权

大数据实习专栏收录该内容

5 篇文章

订阅专栏

这篇博客详细介绍了如何使用Oracle SQL查询月度和年度变化数据，并进行同比环比计算，同时处理了数据为0或空的情况。示例中涉及到了窗口函数、DECODE和NVL函数的使用，以及条件判断来确保除法运算的准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

关于oracle计算同比环比参考博客
在这里插入图片描述

--仅仅查询出来了月度和年度变化数据还需要进行计算
SELECT
	t2.D_DATE,
	t2.D_TASKS,
	LAG ( D_TASKS, 1, 0 ) OVER ( ORDER BY t2.D_DATE ) AS monthOnMonth,
	LAG ( t2.D_TASKS, 12, 0 ) OVER ( ORDER BY t2.D_DATE ) AS yearOnYear 
FROM
	(
	SELECT
		t0.D_DATE,
		t1.D_TASKS 
	FROM
		( SELECT TO_CHAR( add_months( SYSDATE,- ROWNUM ), 'yyyy-MM' ) D_DATE FROM dual CONNECT BY 13 >= ROWNUM ) t0
		LEFT JOIN ( SELECT SUM( D_TASKS ) D_TASKS, D_DATE FROM ZZ_SYSTEM_DATA WHERE STYPE_NAME = '千人负伤率‰（当期年累计）' GROUP BY D_DATE ORDER BY D_DATE ) t1 ON t0.D_DATE = t1.D_DATE 
	ORDER BY
	t0.D_DATE 
	) t2

还要考虑是不是为0或者为空，为0为null 做除法更麻烦了

SELECT
	t3.YEARMONTH,
	t3.DATA,
CASE
		
		WHEN t3.DATA IS NULL THEN
		'0.00%' ELSE TO_CHAR(
			NVL(
				ROUND(
					DECODE( t3.LASTMONTH, 0, 100, ( t3.DATA - t3.LASTMONTH ) / t3.LASTMONTH * 100 ),
					2 
				),
				0 
			),
			'fm9999999990.00' 
		) || '%' 
	END AS month_on_year,
CASE
		
		WHEN t3.DATA IS NULL THEN
		'0.00%' ELSE TO_CHAR(
			NVL(
				ROUND(
					DECODE( t3.SameMonth, 0, 100, ( t3.DATA - t3.SameMonth ) / t3.SameMonth * 100 ),
					2 
				),
				0 
			),
			'fm9999999990.00' 
		) || '%' 
	END AS year_on_year 
FROM
	(
	SELECT
		t2.D_DATE AS YearMonth,
		t2.D_TASKS AS DATA,
		LAG ( D_TASKS, 1, 0 ) OVER ( ORDER BY t2.D_DATE ) AS LastMonth,
		LAG ( t2.D_TASKS, 12, 0 ) OVER ( ORDER BY t2.D_DATE ) AS SameMonth 
	FROM
		(
		SELECT
			t0.D_DATE,
			t1.D_TASKS 
		FROM
			(
			SELECT
				TO_CHAR( add_months( add_months( SYSDATE,- ( 0- ( 12-to_char ( SYSDATE, 'mm' ) ) ) ),- ROWNUM + 1 ), 'yyyy-MM' ) D_DATE 
			FROM
				dual CONNECT BY 24 >= ROWNUM 
			) t0
			LEFT JOIN ( SELECT SUM( D_TASKS ) D_TASKS, D_DATE FROM ZZ_SYSTEM_DATA WHERE STYPE_NAME = '制造体系计件人数（含劳务派遣）' GROUP BY D_DATE ORDER BY D_DATE ) t1 ON t0.D_DATE = t1.D_DATE 
		ORDER BY
			t0.D_DATE 
		) t2 
	) t3

在这里插