C005 运营日报数据采集

运营日报数据采集

 

目的：定时任务每日查询日报数据，并通过邮件发送相关人员，实现运营日报自动化，提高运营人员工作效率。

 

方法：

一、每月1号9点删临时中间表重新采集数据（每月从现网rac库取数据到190数据库）

 

190数据库主机上：

定时任务：

0 9 1 * * /home/oracle/gyl/mk_tables_daily_life_payment.sh > /dev/null 2>&1 &

 

定时任务脚本mk_tables_daily_life_payment.sh内容：

su - oracle -c "export NLS_LANG='AMERICAN_AMERICA.UTF8';sqlplus -s helife_portal_interaction/改成用户密码"<<EOF

set feedback off;

set heading off;

set pagesize 0; 

set linesize 400;

set verify off; 

set echo off;   

set termout off;

set trimout on; 

set trimspool on;

spool /home/oracle/gyl/log/mk_tables_daily_life_payment.log

@/home/oracle/gyl/mk_tables_daily_life_payment.sql

spool off;

exit;

EOF

 

定时任务执行的mk_tables_daily_life_payment.sql内容：

--前2个月生活缴费记录(按月建），召回与留存都需用到这张表--

drop table jiaofei_back001;

create table jiaofei_back001 as

select * from PAY_RECORD@Link_rac t

where t.state in (1,6) and to_char(t.create_time, 'yyyy-mm-dd') >= to_char(last_day(add_months(sysdate,-3)) + 1,'yyyy-mm-dd')

and to_char(t.CREATE_TIME, 'yyyy-mm-dd') <= to_char(last_day(add_months(sysdate,-1)),'yyyy-mm-dd')

and t.type in('电费','燃气费','水费','有线电视费');

--自2018年1月1日至上个月末的缴费记录(按月建）--

drop table jiaofei_new001;

create table jiaofei_new001 as

select * from PAY_RECORD@link_rac t

where t.state in (1,6) and to_char(t.create_time, 'yyyy-mm-dd')>='2018-01-01'

and to_char(t.CREATE_TIME, 'yyyy-mm-dd') <= to_char(last_day(add_months(sysdate,-1)),'yyyy-mm-dd')

and t.type in('电费','燃气费','水费','有线电视费');

 

二、每天06:00采集运营日报数据，并把日报数据拷贝到邮件服务器主机上

 

190数据库主机上：

定时任务：

0 6 * * * /home/oracle/gyl/daily_life_payment.sh > /dev/null 2>&1 &

 

定时任务脚本daily_life_payment.sh内容：

su - oracle -c "export NLS_LANG='AMERICAN_AMERICA.UTF8';sqlplus -s helife_portal_interaction/改成用户密码"<<EOF

set feedback off;

set heading off;

set pagesize 0; 

set linesize 400;

set verify off; 

set echo off;   

set termout off;

set trimout on; 

set trimspool on;

spool /home/oracle/gyl/log/daily_life_payment_TMP.csv

@/home/oracle/gyl/daily_life_payment.sql

spool off;

exit;

EOF

source /etc/profile;

echo "" >> /home/oracle/gyl/log/daily_life_payment_TMP.csv;

sed -i '1i\日期,笔数,金额（万）,交易用户数,每日拉新,拉新率,每日召回人数,召回率,当日留存,留存率,PV,UV' /home/oracle/gyl/log/daily_life_payment_TMP.csv;

iconv -c -f utf-8 -t GB2312 /home/oracle/gyl/log/daily_life_payment_TMP.csv > /home/oracle/gyl/log/生活缴费日报_`date -d "1 days ago" '+%F'`.csv;

scp -P 10022 -i /root/.ssh/monitor_id_rsa /home/oracle/gyl/log/生活缴费日报_`date -d "1 days ago" '+%F'`.csv root@192.168.164.162:/root/gyl/daily_life_payment/

 

定时任务执行的daily_life_payment.sql内容：

-- 每日生活缴费

select T1.日期||','||T1.笔数||','||T1.金额万||','||T1.交易用户数||','||

T2.每日拉新||','||to_char(T2.每日拉新/T1.交易用户数*100,'fm9999990.00')||'%,'||

T3.每日召回人数||','||to_char(T3.每日召回人数/T1.交易用户数*100,'fm9999990.0')||'%,'||

T4.当日留存||','||to_char((sum(当日留存) over(order by T4.日期))/T5.前两月交易用户数*100,'fm9999990')||'%,'||

T6.pv||','||T6.uv  from

(select count(distinct t.pay_account) as 前两月交易用户数 from PAY_RECORD@link_rac t

where t.state in (1,6) and to_char(t.create_time, 'YYYY-MM') >= to_char(add_months(sysdate-1,-2), 'YYYY-MM')

and to_char(t.CREATE_TIME, 'YYYY-MM') <= to_char(add_months(sysdate-1,-1), 'YYYY-MM')

and t.type in('电费','燃气费','水费','有线电视费')) T5,

(select to_char(t.create_time, 'MM"月"DD"日"') as 日期, count(1) as 笔数,

to_char(sum(t.money)/1000000,'fm9999990.0') as 金额万, count(distinct t.pay_account) as 交易用户数

from PAY_RECORD@link_rac t

where t.state in (1,6) and to_char(t.create_time, 'YYYY-MM') = to_char(sysdate-1,'YYYY-MM')

and to_char(t.CREATE_TIME, 'YYYY-MM-DD') <= to_char(sysdate-1,'YYYY-MM-DD')

and t.type in('电费','燃气费','水费','有线电视费')

group by to_char(t.create_time, 'MM"月"DD"日"')

order by to_char(t.create_time, 'MM"月"DD"日"') asc) T1

left join

(select to_char(t.create_time, 'MM"月"DD"日"') as 日期, count(distinct t.pay_account) as 每日拉新 from PAY_RECORD@link_rac t

where t.state in (1,6) and to_char(t.create_time, 'YYYY-MM') = to_char(sysdate-1,'YYYY-MM')

and to_char(t.CREATE_TIME, 'YYYY-MM-DD') <= to_char(sysdate-1,'YYYY-MM-DD')

and t.type in('电费','燃气费','水费','有线电视费')

and not exists (select * from jiaofei_new001 b where b.pay_account=t.pay_account )

group by to_char(t.create_time, 'MM"月"DD"日"')) T2

on T1.日期=T2.日期

left join

(select to_char(t.create_time, 'MM"月"DD"日"') as 日期, count(distinct t.pay_account) as 每日召回人数

from PAY_RECORD@link_rac t

where t.state in (1,6) and to_char(t.create_time, 'YYYY-MM') = to_char(sysdate-1,'YYYY-MM')

and to_char(t.CREATE_TIME, 'YYYY-MM-DD') <= to_char(sysdate-1,'YYYY-MM-DD')

and t.type in('电费','燃气费','水费','有线电视费')

and exists (select * from jiaofei_new001 b where b.pay_account=t.pay_account)

and not exists (select * from jiaofei_back001 c where c.pay_account=t.pay_account)

group by to_char(t.create_time, 'MM"月"DD"日"')) T3

on T1.日期=T3.日期

left join

(select to_char(t.create_time, 'MM"月"DD"日"') as 日期, count(distinct t.pay_account) as 当日留存

from PAY_RECORD@link_rac t

where t.state in (1,6) and to_char(t.create_time, 'YYYY-MM') = to_char(sysdate-1,'YYYY-MM')

and to_char(t.CREATE_TIME, 'YYYY-MM-DD') <= to_char(sysdate-1,'YYYY-MM-DD')

and t.type in('电费','燃气费','水费','有线电视费')

and exists (select * from jiaofei_new001 b where b.pay_account=t.pay_account)

and exists (select * from jiaofei_back001 c where c.pay_account=t.pay_account)

group by to_char(t.create_time, 'MM"月"DD"日"')) T4

on T1.日期=T4.日期

left join

(select to_char(t.time, 'MM"月"DD"日"') as 日期, count(1) as pv, count(distinct t.user_name) as uv

from client_start_record_new t

where t.module like '%生活缴费H5新版-端内%'

and to_char(t.time, 'YYYY-MM') = to_char(sysdate-1,'YYYY-MM')

and to_char(t.time, 'YYYY-MM-DD') <= to_char(sysdate-1,'YYYY-MM-DD')

group by to_char(t.time, 'MM"月"DD"日"')) T6

on T1.日期=T6.日期;

 

-- 当月生活缴费

select to_char(sysdate-1-1, 'MM"月汇总"')||','||T1.笔数||','||T1.金额万||','||T1.交易用户数||','||

T2.每日拉新||','||to_char(T2.每日拉新/T1.交易用户数*100,'fm9999990.00')||'%,'||

T3.每日召回人数||','||to_char(T3.每日召回人数/T1.交易用户数*100,'fm9999990.0')||'%,'||

T4.当日留存||','||to_char(T4.当日留存/T5.前两月交易用户数*100,'fm9999990')||'%,'||

T6.pv||','||T6.uv  from

(select count(1) as 笔数, to_char(sum(t.money)/1000000,'fm9999990.0') as 金额万, count(distinct t.pay_account) as 交易用户数

from PAY_RECORD@link_rac t

where t.state in (1,6) and to_char(t.create_time, 'YYYY-MM') = to_char(sysdate-1,'YYYY-MM')

and to_char(t.CREATE_TIME, 'YYYY-MM-DD') <= to_char(sysdate-1,'YYYY-MM-DD')

and t.type in('电费','燃气费','水费','有线电视费')) T1,

(select count(distinct t.pay_account) as 每日拉新 from PAY_RECORD@link_rac t

where t.state in (1,6) and to_char(t.create_time, 'YYYY-MM') = to_char(sysdate-1,'YYYY-MM')

and to_char(t.CREATE_TIME, 'YYYY-MM-DD') <= to_char(sysdate-1,'YYYY-MM-DD')

and t.type in('电费','燃气费','水费','有线电视费')

and not exists (select * from jiaofei_new001 b where b.pay_account=t.pay_account )) T2,

(select count(distinct t.pay_account) as 每日召回人数

from PAY_RECORD@link_rac t

where t.state in (1,