需求把实时表里面的数据按要求统计到统计表中
select * from test_ssfh_data --实时表，表结构如下图：
select * from test_wdfh_data --统计表，表结构如下图：
思路：
月最大用电量查找：
1.先用max() over()对实时表分区取月最大用电量，分区条件是部门code和年月时间，
select distinct a.org_code,to_char(a.stat_date,'yyyy-mm') as month_date,
max(a.active_power_sum) over(partition by a.org_code,to_char(a.stat_date,'yyyy-mm')) as month_data
from test_ssfh_data a;结果：
2.然后在用月最大用电量去查月最大用电量时间，使用连接查询去查询（连接条件是部门code、用电量、用电时间必须等于年月时间）。
select A.Org_Code,A.month_date,B.Stat_Date,A.month_data from(
select distinct a.org_code,to_char(a.stat_date,'yyyy-mm') as month_date,
max(a.active_power_sum) over(partition by a.org_code,to_char(a.stat_date,'yyyy-mm')) as month_data
from test_ssfh_data a ) A left join test_ssfh_data B on
A.org_code=B.org_code and A.month_data=B.Active_Power_Sum and A.month_date=to_char(B.Stat_Date,'yyyy-mm')结果：
3.第二步骤取出来的数据还需要在筛一次最大值，比如2022年3月有2天用电量都是最大值，那我们必须取最大时间的用电量，就必须在用一次max() over()筛选。
select distinct AA.org_code,to_char(max(AA.stat_date) over(partition by AA.org_code,AA.month_date),'yyyy-mm-dd'),AA.month_data from
(select A.Org_Code,A.month_date,B.Stat_Date,A.month_data from(
select distinct a.org_code,to_char(a.stat_date,'yyyy-mm') as month_date,
max(a.active_power_sum) over(partition by a.org_code,to_char(a.stat_date,'yyyy-mm')) as month_data
from test_ssfh_data a ) A left join test_ssfh_data B on
A.org_code=B.org_code and A.month_data=B.Active_Power_Sum and A.month_date=to_char(B.Stat_Date,'yyyy-mm'))AA结果：
年最大用电量和历史最大用电量也是这么查找，这里就不一一描述了，直接上sql。
--年最大用电量
select distinct AA.org_code,to_char(max(AA.stat_date) over(partition by AA.org_code,AA.year_date),'yyyy-mm-dd'),AA.year_data from
(select A.Org_Code,A.year_date,B.Stat_Date,A.year_data from(
select distinct a.org_code,to_char(a.stat_date,'yyyy') as year_date,
max(a.active_power_sum) over(partition by a.org_code,to_char(a.stat_date,'yyyy')) as year_data
from test_ssfh_data a ) A left join test_ssfh_data B on
A.org_code=B.org_code and A.year_data=B.Active_Power_Sum and A.year_date=to_char(B.Stat_Date,'yyyy')) AA--历史最大用电量
select distinct AA.org_code,to_char(max(AA.stat_date) over(partition by AA.org_code,AA.history_date),'yyyy-mm-dd'),AA.history_data
from(select A.org_code,A.history_date,B.stat_date,A.history_data from (select distinct a.org_code,a.stat_date as history_date,
max(a.active_power_sum) over(partition by a.org_code) as history_data
from test_ssfh_data a) A left join test_ssfh_data B on A.org_code=B.org_code and
A.history_data=B.Active_Power_Sum )AA存储过程如下：
create or replace procedure test_p_t_zwyx_dwfh_data(out_message out varchar2) is
v_org_name varchar2(64);
data_is_Exist number(12, 2);
begin
--月份
for i in (select distinct AA.org_code,
to_char(max(AA.stat_date)
over(partition by AA.org_code,
AA.month_date),
'yyyy-mm') as month_date,
to_char(max(AA.stat_date)
over(partition by AA.org_code,
AA.month_date),
'yyyy-mm-dd') as month_stat_date,
AA.month_data as month_max_load
from (select A.org_code,
A.month_date,
B.Stat_Date,
A.month_data
from (select distinct a.org_code,
to_char(a.stat_date, 'yyyy-mm') as month_date,
max(a.active_power_sum) over(partition by a.org_code, to_char(a.stat_date, 'yyyy-mm')) as month_data
from test_ssfh_data a) A
left join test_ssfh_data B
on A.org_code = B.org_code
and A.month_data = B.Active_Power_Sum
and A.month_date = to_char(B.Stat_Date, 'yyyy-mm')) AA) loop
--查看新数据所在的月份存不存在
select count(1)
into data_is_Exist
from test_wdfh_data
where org_code = i.org_code
and to_char(month_stat_date, 'yyyy-mm') = i.month_date
and rownum = 1;
--如果新数据所在月份存在，就在已有的数据上更新数据
if (data_is_Exist != 0) then
--更新月份字段
update test_wdfh_data t
set t.month_stat_date = to_date(i.month_stat_date, 'yyyy-mm-dd'),
t.month_max_load = i.month_max_load
where t.org_code = i.org_code
and to_char(t.month_stat_date, 'yyyy-mm') = i.month_date;
commit;
---月份字段更新完成后，接着更新年份和历史
--更新年份
for i1 in (select distinct AA.org_code,
to_char(max(AA.stat_date)
over(partition by AA.org_code,
AA.year_date),
'yyyy') as year_date,
to_char(max(AA.stat_date)
over(partition by AA.org_code,
AA.year_date),
'yyyy-mm-dd') as year_stat_date,
AA.year_data as year_max_load
from (select A.org_code,
A.year_date,
B.Stat_Date,
A.year_data
from (select distinct a.org_code,
to_char(a.stat_date, 'yyyy') as year_date,
max(a.active_power_sum) over(partition by a.org_code, to_char(a.stat_date, 'yyyy')) as year_data
from test_ssfh_data a) A
left join test_ssfh_data B
on A.org_code = B.org_code
and A.year_data = B.Active_Power_Sum
and A.year_date = to_char(B.Stat_Date, 'yyyy')) AA) loop
--更新年份
update test_wdfh_data t
set t.year_stat_date = to_date(i1.year_stat_date, 'yyyy-mm-dd'),
t.year_max_load = i1.year_max_load
where t.org_code = i1.org_code
and to_char(t.year_stat_date, 'yyyy') = i1.year_date;
commit;
end loop;
--更新历史
for i2 in (select distinct AA.org_code,
to_char(max(AA.stat_date)
over(partition by AA.org_code,
AA.history_date),
'yyyy-mm-dd') as history_stat_date,
AA.history_data as history_max_load
from (select A.org_code,
A.history_date,
B.stat_date,
A.history_data
from (select distinct a.org_code,
Stat_Date as history_date,
max(a.active_power_sum) over(partition by a.org_code) as history_data
from test_ssfh_data a) A
left join test_ssfh_data B
on A.org_code = B.org_code
and A.history_data = B.Active_Power_Sum) AA) loop
--更新历史
update test_wdfh_data t
set t.history_stat_date = to_date(i2.history_stat_date,
'yyyy-mm-dd'),
t.history_max_load = i2.history_max_load
where t.org_code = i2.org_code;
commit;
end loop;
else
--如果新数据所在月份不存在，就插入新数据。
--年份和历史的数据先默认填写月份的数据，之后循环更新年份和历史数据
select org_name
into v_org_name
from t_org_mapping
where black2 = i.org_code;
insert into test_wdfh_data
values
(rawtohex(sys_guid()),
i.org_code,
v_org_name,
to_date(i.month_stat_date, 'yyyy-mm-dd'),
i.month_max_load,
to_date(i.month_stat_date, 'yyyy-mm-dd'),
i.month_max_load,
to_date(i.month_stat_date, 'yyyy-mm-dd'),
i.month_max_load,
trunc(sysdate));
commit;
---月份字段插入完成后，接着更新年份和历史字段
--更新年份
for i1 in (select distinct AA.org_code,
to_char(max(AA.stat_date)
over(partition by AA.org_code,
AA.year_date),
'yyyy') as year_date,
to_char(max(AA.stat_date)
over(partition by AA.org_code,
AA.year_date),
'yyyy-mm-dd') as year_stat_date,
AA.year_data as year_max_load
from (select A.org_code,
A.year_date,
B.Stat_Date,
A.year_data
from (select distinct a.org_code,
to_char(a.stat_date, 'yyyy') as year_date,
max(a.active_power_sum) over(partition by a.org_code, to_char(a.stat_date, 'yyyy')) as year_data
from test_ssfh_data a) A
left join test_ssfh_data B
on A.org_code = B.org_code
and A.year_data = B.Active_Power_Sum
and A.year_date = to_char(B.Stat_Date, 'yyyy')) AA) loop
--更新年份
update test_wdfh_data t
set t.year_stat_date = to_date(i1.year_stat_date, 'yyyy-mm-dd'),
t.year_max_load = i1.year_max_load
where t.org_code = i.org_code
and to_char(t.year_stat_date, 'yyyy') = i1.year_date;
commit;
end loop;
--更新历史
for i2 in (select distinct AA.org_code,
to_char(max(AA.stat_date)
over(partition by AA.org_code,
AA.history_date),
'yyyy-mm-dd') as history_stat_date,
AA.history_data as history_max_load
from (select A.org_code,
A.history_date,
B.stat_date,
A.history_data
from (select distinct a.org_code,
Stat_Date as history_date,
max(a.active_power_sum) over(partition by a.org_code) as history_data
from test_ssfh_data a) A
left join test_ssfh_data B
on A.org_code = B.org_code
and A.history_data = B.Active_Power_Sum) AA) loop
--更新历史
update test_wdfh_data t
set t.history_stat_date = to_date(i2.history_stat_date,
'yyyy-mm-dd'),
t.history_max_load = i2.history_max_load
where t.org_code = i.org_code;
commit;
end loop;
end if;
end loop;
--数据处理成功标识
out_message := 'success';
dbms_output.put_line('out_message:' || out_message);
exception
when others then
out_message := 'failed';
dbms_output.put_line('out_message:' || out_message || ',报错信息:' ||
sqlerrm);
rollback;
end test_p_t_zwyx_dwfh_data;
调用存储过程：
declare
out_message varchar2(100);
begin
test_p_t_zwyx_dwfh_data(out_message);
end;注意这个需求不能使用merge into函数，因为merge into函数on后面的条件列是不能更新的。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
