oracle分项电器排名查询-优快云博客

该查询针对2021年4月份特定区域（3029区）的用户用电情况进行分析，展示了用电量排名前几的用户及其详细信息。同时给出了该区域内用户的总用电量和平均用电量。数据表明，3029区的用户在4月份的电力消耗情况，并揭示了不同用户群体的用电特征。

SELECT *
FROM (
	SELECT rank() OVER (PARTITION BY b.C_EEGRPID ORDER BY b.EEKWH DESC) AS rank, b.METERNO, b.DISTRICTBCDID, b.ADDRESSID, b.CHANNELID
		, b.EEKWH, b.C_EEGRPID, b.C_EEGRPNAME
	FROM (
		SELECT SUM(a.EEKWH) AS EEKWH, b.METERNO, a.DISTRICTBCDID, a.ADDRESSID
			, a.CHANNELID, c.C_EEGRPID, g.C_EEGRPNAME
		FROM QX_SGMTUSR_DTL a
			LEFT JOIN V_USING_TOPO b
			ON a.DISTRICTBCDID = b.DISTRICTBCDID
				AND a.ADDRESSID = b.ADDRESSID
				AND a.CHANNELID = b.CHANNELID
			LEFT JOIN T_EECLASSIFICATION c ON a.EEID = c.EEID
			LEFT JOIN T_EECLASSIFICATIONGRP g ON c.C_EEGRPID = g.C_EEGRPID
		WHERE a.RECORDDATE >= 210401
			AND a.RECORDDATE <= 210431
			AND b.EPU_COURT = '9ce315bd-3e31-4da4-b279-a1ac46aa32cf'
		GROUP BY b.METERNO, a.DISTRICTBCDID, a.ADDRESSID, a.CHANNELID, c.C_EEGRPID, g.C_EEGRPNAME
		ORDER BY a.DISTRICTBCDID, a.ADDRESSID, a.CHANNELID
	) b
)
WHERE DISTRICTBCDID = 3029
	AND ADDRESSID = 66
	AND CHANNELID = 6
ORDER BY EEKWH desc

所有用户
3029-13-6、3029-13-3、3029-18-6、3029-21-1、3029-34-3、3029-41-6、3029-64-1、3029-64-4、3029-66-6


SELECT SUM(a.EEKWH) / 143 AS EEKWH, c.C_EEGRPID, g.C_EEGRPNAME
FROM QX_SGMTUSR_DTL a
	LEFT JOIN V_USING_TOPO b
	ON a.DISTRICTBCDID = b.DISTRICTBCDID
		AND a.ADDRESSID = b.ADDRESSID
		AND a.CHANNELID = b.CHANNELID
	LEFT JOIN T_EECLASSIFICATION c ON a.EEID = c.EEID
	LEFT JOIN T_EECLASSIFICATIONGRP g ON c.C_EEGRPID = g.C_EEGRPID
WHERE a.RECORDDATE >= 210401
	AND a.RECORDDATE <= 210431
	AND b.EPU_COURT = '9ce315bd-3e31-4da4-b279-a1ac46aa32cf'
GROUP BY c.C_EEGRPID, g.C_EEGRPNAME
ORDER BY c.C_EEGRPID

总用户数
143