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,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)) T3,

(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 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)) T4,

(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 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')) T6;

 

-- 上月同期生活缴费

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

(select to_char(add_months(sysdate-1,-1), '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(add_months(sysdate-1,-1), 'yyyy-mm')

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

and to_char(add_months(last_day(sysdate-1), -1), 'yyyy-mm-dd') >= to_char(add_months(sysdate-1,-1), 'yyyy-mm-dd')

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

 

-- 上月同期环比

select '环比,'||to_char((T1.笔数-T2.笔数)/T2.笔数*100,'fm9999990.00')||'%,'||

to_char((T1.金额万-T2.金额万)/T2.金额万*100,'fm9999990.00')||'%,'||

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

from

(select to_char(sysdate-1,'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('电费','燃气费','水费','有线电视费')) T1,

(select to_char(add_months(sysdate-1,-1), '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(add_months(sysdate-1,-1), 'yyyy-mm')

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

and to_char(add_months(last_day(sysdate-1), -1), 'yyyy-mm-dd') >= to_char(add_months(sysdate-1,-1), 'yyyy-mm-dd')

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

 

日报附件结果如下：

日期	笔数	金额（万）	交易用户数	每日拉新	拉新率	每日召回人数	召回率	当日留存	留存率	PV	UV
3月1日	1152	14.3	1009	49	4.86%	150	14.90%	810	1%	63466	29751
3月2日	2352	28.6	1839	89	4.84%	186	10.10%	1564	4%	68709	31328
3月3日	6077	69.8	4567	137	3.00%	484	10.60%	3946	9%	85498	25601
3月4日	8092	91.2	6122	201	3.28%	694	11.30%	5227	17%	98353	26530
3月5日	6961	81	5415	204	3.77%	694	12.80%	4517	24%	86870	24138
3月6日	4679	56.1	3728	175	4.69%	478	12.80%	3075	29%	70205	21804
3月7日	3559	41.5	2828	144	5.09%	405	14.30%	2279	32%	59897	19976
3月8日	3588	41.8	2814	110	3.91%	399	14.20%	2305	35%	56655	18615
3月9日	3209	37.9	2516	139	5.52%	362	14.40%	2015	38%	55944	19736
3月10日	3680	43	2936	146	4.97%	449	15.30%	2341	42%	88356	37786
3月11日	3001	35	2371	102	4.30%	373	15.70%	1896	45%	71617	31671
3月12日	2952	31.6	2259	114	5.05%	296	13.10%	1849	48%	80954	33428
3月13日	2827	31.5	2185	79	3.62%	309	14.10%	1797	50%	73547	29200
3月14日	2417	24.9	1888	98	5.19%	242	12.80%	1548	53%	61099	24199
3月15日	2223	23.8	1724	97	5.63%	222	12.90%	1405	55%	58665	23396
3月16日	2822	29.4	2202	100	4.54%	302	13.70%	1800	57%	83328	35298
3月17日	2672	28.6	2095	94	4.49%	247	11.80%	1754	60%	73403	30506
3月18日	2554	29.8	1995	102	5.11%	259	13.00%	1634	62%	63626	25849
3月19日	2362	28.3	1883	107	5.68%	259	13.80%	1517	65%	63525	25635
3月20日	2170	25.4	1749	106	6.06%	238	13.60%	1405	67%	59906	24017
3月21日	1727	20.5	1410	83	5.89%	221	15.70%	1106	68%	61375	27525
3月22日	1336	16.8	1084	68	6.27%	153	14.10%	863	70%	43330	18031
3月23日	1272	14.5	1076	61	5.67%	165	15.30%	850	71%	6901	4377
3月24日	1940	14.3	1773	728	41.06%	423	23.90%	622	72%	55975	21940
3月25日	1218	11.9	1096	306	27.92%	257	23.40%	533	73%	49286	20428
3月26日	988	11.5	848	86	10.14%	142	16.70%	620	74%	46714	20408
3月27日	1003	11	841	53	6.30%	137	16.30%	651	75%	41340	17858
3月28日	944	9.3	792	50	6.31%	106	13.40%	636	76%	41875	18450
3月29日	882	10	739	53	7.17%	131	17.70%	555	76%	39896	17259
3月30日	916	10	789	69	8.75%	120	15.20%	600	77%	39659	17077
03月汇总	81575	923.2	52551	3805	7.24%	8304	15.80%	40442	60%	1849974	488779
02月29日汇总	77684	1094.5	51303
环比	5.01%	-15.65%	2.43%

注：环比是与上月同期数据比较，但无2月30日，所以就与2月29日环比

 

三、每天08:00发送运营日报邮件

 

邮件发送附件日报数据给相关人员，且正文还需要显示部分日报数据

 

邮件服务器主机上（跳转机）：

定时任务：

0 8 * * * /root/gyl/daily_life_payment.sh > /dev/null 2>&1 &

 

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

source /etc/profile;

cd /root/gyl/daily_life_payment/

MailText()

{

iconv -f GB2312 -t utf-8 生活缴费日报_`date -d "1 days ago" '+%F'`.csv > utf-8.csv

cat utf-8.csv | grep `date -d last-day +%m月%d日` | awk -F , '{print "每日生活缴费 交易笔数："$2,"，金额(万)："$3,"，人数："$4}';

cat utf-8.csv | grep `date -d last-day +%m月汇总` | awk -F , '{print "当月生活缴费 交易笔数："$2,"，金额(万)："$3,"，人数："$4}';

s1=`cat utf-8.csv | grep "日汇总" | awk -F , '{print $2}'`;

s2=`cat utf-8.csv | grep "日汇总" | awk -F , '{print $3}'`;

s3=`cat utf-8.csv | grep "日汇总" | awk -F , '{print $4}'`;

s4=`cat utf-8.csv | grep "环比" | awk -F , '{print $2}'`;

s5=`cat utf-8.csv | grep "环比" | awk -F , '{print $3}'`;

s6=`cat utf-8.csv | grep "环比" | awk -F , '{print $4}'`;

echo "上月同期生活缴费 交易笔数：$s1，环比：$s4，金额：$s2，环比：$s5，人数：$s3，环比：$s6";

echo ""

cat utf-8.csv | grep `date -d last-day +%m月%d日` | awk -F , '{print "每日生活缴费 新用户数："$5,"，拉新率："$6}';

cat utf-8.csv | grep `date -d last-day +%m月汇总` | awk -F , '{print "每日生活缴费 新用户数："$5}';

echo ""

cat utf-8.csv | grep `date -d last-day +%m月%d日` | awk -F , '{print "每日生活缴费 召回用户数："$7,"，召回率："$8}';

cat utf-8.csv | grep `date -d last-day +%m月汇总` | awk -F , '{print "每月生活缴费 召回用户数："$7}';

echo ""

cat utf-8.csv | grep `date -d last-day +%m月%d日` | awk -F , '{print "每日生活缴费 留存用户数："$9,"，留存率："$10}';

cat utf-8.csv | grep `date -d last-day +%m月汇总` | awk -F , '{print "每月生活缴费 留存用户数："$9}';

}

MailText > milText.txt

cat milText.txt | mailx -s "生活缴费日报"`date -d "1 days ago" '+%F'` -a 生活缴费日报_`date -d "1 days ago" '+%F'`.csv -r LifeDaily@helife.com chengjiansgs@ah.chinamobile.com hejun@ah.chinamobile.com liyonghui@ah.chinamobile.com chenlusgs@ah.chinamobile.com zhangyiwei@wondertek.com.cn jinlili@richinfo.cn

 

邮件正文内容如下：