17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE PROCEDURE P_USER_STATISTICS IS
time DATE;
cursor userStatistics is
select * from (
select t1.register_num register_num,
t2.recharge_num recharge_num,
t3.apply_loan_sum apply_loan_sum,
t4.invest_num invest_num,
t5.sys_register_sum sys_register_sum,
t6.pc_register_sum pc_register_sum,
t7.wap_register_sum wap_register_sum,
t8.app_register_sum app_register_sum,
t9.login_sum login_sum
from
(select count(1) as register_num from users us where us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate)) t1,
(select count(distinct urd.user_id) as recharge_num from user_recharge_details urd where urd.is_completed = '1'and trunc(urd.time) = trunc(sysdate)) t2,
(select count(distinct b.user_id) as apply_loan_sum from bids b where trunc(b.time) = trunc(sysdate)) t3,
(select count(distinct inv.user_id) as invest_num from invests inv where trunc(inv.time) = trunc(sysdate)) t4,
(select count(1) as sys_register_sum from users us where us.channel_id = '1' and trunc(us.time) = trunc(sysdate)) t5,
(select count(1) as pc_register_sum from users us where us.channel_id = '0' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate)) t6,
(select count(1) as wap_register_sum from users us where us.channel_id = '2' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate)) t7,
(select count(1) as app_register_sum from users us where us.channel_id = '3' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate)) t8,
(select count(1) as login_sum from users us where trunc(us.last_login_time) = trunc(sysdate)) t9
);
us userStatistics%rowtype;
BEGIN
select to_char(sysdate,'yyyy/MM/dd') time
into time
from dual t;
for us in userStatistics loop
INSERT INTO Statistic_Behavior
(id,
time,
register_num,
recharge_num,
apply_loan_sum,
invest_num,
sys_register_sum,
pc_register_sum,
wap_register_sum,
app_register_sum,
login_sum)
values
(SEQ_STATISTIC_BEHAVIOR.NEXTVAL,
time,
us.register_num,
us.recharge_num,
us.apply_loan_sum,
us.invest_num,
us.sys_register_sum,
us.pc_register_sum,
us.wap_register_sum,
us.app_register_sum,
us.login_sum);
END loop;
commit;
end;
create or replace procedure p_user_statistics
is
register_num number(10);
recharge_num number(10);
apply_loan_sum number(10);
invest_num number(10);
sys_register_sum number(10);
pc_register_sum number(10);
wap_register_sum number(10);
app_register_sum number(10);
login_sum number(10);
begin
select count(1) into register_num from users us where us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
select count(distinct urd.user_id) into recharge_num from user_recharge_details urd where urd.is_completed = '1' and trunc(urd.time) = trunc(sysdate);
select count(distinct b.user_id) into apply_loan_sum from bids b where trunc(b.time) = trunc(sysdate);
select count(distinct inv.user_id) into invest_num from invests inv where trunc(inv.time) = trunc(sysdate);
select count(1) into sys_register_sum from users us where us.channel_id = '1' and trunc(us.time) = trunc(sysdate);
select count(1) into pc_register_sum from users us where us.channel_id = '0' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
select count(1) into wap_register_sum from users us where us.channel_id = '2' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
select count(1) into app_register_sum from users us where us.channel_id = '3' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
select count(1) into login_sum from users us where trunc(us.last_login_time) = trunc(sysdate);
insert into statistic_behavior
(
id,
time,
register_num,
recharge_num,
apply_loan_sum,
invest_num,
sys_register_sum,
pc_register_sum,
wap_register_sum,
app_register_sum,
login_sum
)
values
(
seq_statistic_behavior.nextval,
to_char(sysdate,'yyyy/mm/dd'),
register_num,
recharge_num,
apply_loan_sum,
invest_num,
sys_register_sum,
pc_register_sum,
wap_register_sum,
app_register_sum,
login_sum
);
commit;
end p_user_statistics;
create or replace procedure p_user_statistics
is
register_num number(10);
recharge_num number(10);
apply_loan_sum number(10);
invest_num number(10);
sys_register_sum number(10);
pc_register_sum number(10);
wap_register_sum number(10);
app_register_sum number(10);
login_sum number(10);
begin
select count(1) into register_num from users us where us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
select count(distinct urd.user_id) into recharge_num from user_recharge_details urd where urd.is_completed = '1' and trunc(urd.time) = trunc(sysdate);
select count(distinct b.user_id) into apply_loan_sum from bids b where trunc(b.time) = trunc(sysdate);
select count(distinct inv.user_id) into invest_num from invests inv where trunc(inv.time) = trunc(sysdate);
select count(1) into sys_register_sum from users us where us.channel_id = '1' and trunc(us.time) = trunc(sysdate);
select count(1) into pc_register_sum from users us where us.channel_id = '0' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
select count(1) into wap_register_sum from users us where us.channel_id = '2' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
select count(1) into app_register_sum from users us where us.channel_id = '3' and us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
select count(1) into login_sum from users us where trunc(us.last_login_time) = trunc(sysdate);
insert into statistic_behavior
(
id,
time,
register_num,
recharge_num,
apply_loan_sum,
invest_num,
sys_register_sum,
pc_register_sum,
wap_register_sum,
app_register_sum,
login_sum
)
values
(
seq_statistic_behavior.nextval,
to_char(sysdate,'yyyy/mm/dd'),
register_num,
recharge_num,
apply_loan_sum,
invest_num,
sys_register_sum,
pc_register_sum,
wap_register_sum,
app_register_sum,
login_sum
);
commit;
end p_user_statistics;
create or replace procedure p_user_statistics
is
register_num number(10);
recharge_num number(10);
apply_loan_sum number(10);
invest_num number(10);
sys_register_sum number(10);
pc_register_sum number(10);
wap_register_sum number(10);
app_register_sum number(10);
login_sum number(10);
begin
select count(1) into register_num from users us where us.is_allow_login = '1' and trunc(us.time) = trunc(sysdate);
select count(distinct urd.user_id) into recharge_num from user_recharge_details urd where urd.is_completed = '1'and trunc(urd.time) = trunc(sysdate) t2;
select count(distinct b.user_id) into apply_loan_sum from bids b where trunc(b.time) = trunc(sysdate);
select count(distinct inv.user_id) into invest_num from invests inv where trunc(inv.time) = trunc(sysdate);
select count(1) into sys_register_sum from users us where us.channel_id = '1' and trunc(us.time) = trunc(sysdate);
select count(1) into pc_register_sum from users us where us.channel_id = '0' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate);
select count(1) into wap_register_sum from users us where us.channel_id = '2' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate);
select count(1) into app_register_sum from users us where us.channel_id = '3' and us.is_allow_login = '1'and trunc(us.time) = trunc(sysdate);
select count(1) into login_sum from users us where trunc(us.last_login_time) = trunc(sysdate);
insert into statistic_behavior
(
id,
time,
register_num,
recharge_num,
apply_loan_sum,
invest_num,
sys_register_sum,
pc_register_sum,
wap_register_sum,
app_register_sum,
login_sum
)
values
(
seq_statistic_behavior.nextval,
to_char(sysdate,'yyyy/mm/dd'),
register_num,
recharge_num,
apply_loan_sum,
invest_num,
sys_register_sum,
pc_register_sum,
wap_register_sum,
app_register_sum,
login_sum
);
commit;
end p_user_statistics;