oracle 查询

[qq_35511531]

表结构 table
id name type value year
1 test 电 120 2017
2 test 水 100 2017
3 test 电 80 2017
4 test 水 1 2016

1.查询当年度总能耗
select sum(value) vv from table where year='2017'

2.查询当年度各分类总能耗
select type,sum(value) aa from table
where year='2017'
group by type

3.查询当年度各分类能耗占比，计算方法：round((aa/vv)*100,2)
请问大神sql怎么写？？？

[花开了叫我]

sum()over(partition by )/sum()over() 这个语法手机懒得打字了

[卜塔]

试试这个

SELECT type,year,占比 FROM (
SELECT type,year,SUM(value) OVER (PARTITION BY type,year)/SUM(value) OVER () AS 占比 ,row_number() OVER (PARTITION BY type,year ORDER BY id) rn
FROM TABLE
GROUP BY type,value,id,year)a
WHERE a.rn=1;

[mayanzs]

有个专门的占比函数： select type,round(ratio_to_report(s1)*100,2) over() zb from (select type,sum(value) s1 from table where year='2017' group by type) group by type;

[qq_35511531]

引用 2 楼 NextAction的回复:

试试这个

SELECT type,year,占比 FROM (
SELECT type,year,SUM(value) OVER (PARTITION BY type,year)/SUM(value) OVER () AS 占比 ,row_number() OVER (PARTITION BY type,year ORDER BY id) rn
FROM TABLE
GROUP BY type,value,id,year)a
WHERE a.rn=1;

谢谢，问题已解决。