计算同地区下,同比百分比
select '2020',
area_no,
area_desc,
city_no,
city_desc,
area_level,
key_id,
CASE
WHEN NVL(SUM(VAL_19), 0) = 0 THEN
0
ELSE
nvl(round((SUM(VAL_20) - SUM(VAL_19)) * 100 / SUM(VAL_19), 2), '0')
END KEY_VALUE_yy
from (select area_no,
area_desc,
city_no,
city_desc,
area_level,
key_id,
sum(case acct_year
when '2019' then
key_value
else
0
end) as val_19,
sum(case acct_year
when '2020' then
key_value
else
0
end) as val_20
from (select *
from bi_annuals_fzjb
where acct_year in ('2019', '2020')
and key_id = 'ZYYFW2000CYRS'
and area_level = 0
order by city_desc, acct_year) a
group by area_no, area_desc, city_no, city_desc, area_level, key_id)
group by area_no, area_desc, city_no, city_desc, area_level, key_id
数据格式
2019 130100 ZYYFW2000CYRS 287703
2019 130600 ZYYFW2000CYRS 143803
2019 130682 ZYYFW2000CYRS 15969
2019 130700 ZYYFW2000CYRS 72800
2019 130181 ZYYFW2000CYRS 8256
2019 130500 ZYYFW2000CYRS 98904
2019 130900 ZYYFW2000CYRS 219638
2019 131100 ZYYFW2000CYRS 33895
2019 130800 ZYYFW2000CYRS 118235
2019 130200 ZYYFW2000CYRS 195637
2019 130300 ZYYFW2000CYRS 44926
2019 130400 ZYYFW2000CYRS 214724
2019 131200 ZYYFW2000CYRS 14497
2019 131000 ZYYFW2000CYRS 83348
2020 130400 ZYYFW2000CYRS 200229
2020 130300 ZYYFW2000CYRS 39980
2020 130181 ZYYFW2000CYRS 7453
2020 130200 ZYYFW2000CYRS 174810
2020 130500 ZYYFW2000CYRS 92798
2020 130700 ZYYFW2000CYRS 58155
2020 130800 ZYYFW2000CYRS 102575
2020 130900 ZYYFW2000CYRS 196013
2020 131100 ZYYFW2000CYRS 36712
2020 130100 ZYYFW2000CYRS 291806
2020 130600 ZYYFW2000CYRS 135383
2020 131200 ZYYFW2000CYRS 12423
2020 130682 ZYYFW2000CYRS 33970
2020 131000 ZYYFW2000CYRS 71628