落后发电单元 sql

最新推荐文章于 2024-04-28 16:21:47 发布

原创最新推荐文章于 2024-04-28 16:21:47 发布 · 157 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

MySQL 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询旨在通过多表联接和子查询的方式对比不同时间段内的设备性能比，找出效率损失较大的记录，并进行进一步的分析。

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

SELECT
	a.s_id,
	a.busi_code,
	a.POORLOSS,
	a.display_time,
	a.d_name
FROM
	(
		SELECT
			*
		FROM
			(
				SELECT
					c.s_id,
					c.busi_code,
					(
						a.perpower_ratio_avg - c.perpower_ratio
					) * b.TOTALCAPACITY AS POORLOSS,
					c.perpower_ratio,
					a.perpower_ratio_avg,
					c.display_time,
					c.d_name
				FROM
					(
						SELECT
							a.loc_id,
							avg(a.perpower_ratio) AS perpower_ratio_avg
						FROM
							(
								SELECT
									b.loc_id,
									a.perpower_ratio
								FROM
									pms_kpi_inverter_day_t a,
									pms_device_t b
								WHERE
									a.busi_code = b.busi_code
								AND a.s_Id = b.s_Id
								AND b.d_state = 1
								AND b.dev_type_id = 1
								AND a.display_time = 20180725
								AND a.s_id = '2c28878c60ca56f50161509bc0606b26'
							) a
						GROUP BY
							a.loc_id
					) a,
					(
						SELECT
							dev.loc_id,
							dev.busi_code,
							dev.pv_total_capacity AS TOTALCAPACITY
						FROM
							pms_device_t dev
						WHERE
							dev.s_Id = '2c28878c60ca56f50161509bc0606b26'
						AND dev.d_state = 1
						AND dev.dev_type_id = 1
					) b,
					(
						SELECT
							s_id,
							busi_code,
							lose_effectiveness,
							display_time,
							d_name,
							perpower_ratio
						FROM
							pms_kpi_inverter_day_t
						WHERE
							perpower_ratio <> 0
						AND s_id = '2c28878c60ca56f50161509bc0606b26'
						AND display_time = 20180725
					) c
				WHERE
					a.loc_id = b.loc_id
				AND b.busi_code = c.busi_code
				AND a.perpower_ratio_avg > c.perpower_ratio
				AND (
					1 - (
						a.perpower_ratio_avg - c.perpower_ratio
					) / perpower_ratio_avg
				) * 100 < 0.99
			) a
		UNION ALL
			(
				SELECT
					kit.s_Id AS s_id,
					kit.busi_code,
					kit.lose_power AS POORLOSS,
					kit.perpower_ratio,
					pprAvg.perpower_ratio_avg,
					kit.display_time,
					kit.d_name
				FROM
					pms_kpi_inverter_day_t kit,
					pms_device_t dt,
					(
						SELECT
							AVG(avgQuery.perpower_ratio) AS perpower_ratio_avg
						FROM
							(
								SELECT
									perpower_ratio
								FROM
									pms_kpi_inverter_day_t t,
									pms_device_t d
								WHERE
									t.busi_code = d.busi_code
								AND t.s_id = '2c28878c60ca56f50161509bc0606b26'
								AND d.s_id = '2c28878c60ca56f50161509bc0606b26'
								AND t.display_time = 20180725
								AND d.dev_type_id = 14
							) avgQuery
					) pprAvg
				WHERE
					kit.busi_code = dt.busi_code
				AND kit.s_id = '2c28878c60ca56f50161509bc0606b26'
				AND dt.s_id = '2c28878c60ca56f50161509bc0606b26'
				AND dt.dev_type_id = 14
				AND kit.display_time = 20180725
				AND kit.lose_power > 0
				AND pprAvg.perpower_ratio_avg > kit.perpower_ratio
				AND (
					1 - (
						pprAvg.perpower_ratio_avg - kit.perpower_ratio
					) / pprAvg.perpower_ratio_avg
				) * 100 < 0.99
			)
		ORDER BY
			POORLOSS DESC
	) a