sql 存储过程如何实现生成csv 并通过邮件发送_如何制作数据报表并实现自动化？...

定期的数据报表，包括日报、周报、月报、半年报、年报，是每一位数据运营或数据分析师可能遇到的工作，需求方主要包括产品运营部门和各业务部门。基于不同公司的不同发展阶段，遇到的报表工作“层次”也可能不同。面对“报表”，我们“跑不掉”～

本文通过使用Excel+Python，梳理制作一份基础的日报流程：

1、制作报表前“思考人生“
2、取数，制作原始数据表
3、调用，制作中间数据表
4、自动化生成Excel日报
5、发送报表前“思考人生”

一、制作报表前“思考人生“

据说，让一名数据运营或数据分析师避免沦为“取数工具”、“表哥”、“表姐”的几种方式是：
（1）让那些繁杂的数据工作自动化；
（2）制定高质量的数据需求流程，不随便接取数的活；
（3）将高频的数据需求报表化；
（4）成为最懂业务的数据分析师，一切以业务为导向。

对于制作数据报表而言，需要从实际业务应用场景出发，再制作一套数据管理模版，可以大大提高需求方和数据分析师的效率。

在制作数据报表之前，需要进行的准备工作包括：

• 梳理报表所含的数据指标：需求方要看哪些数据？为什么要看这些数据？
• 理清指标之间的逻辑：这些数据对于业务或KPI有什么帮助？
• 确定报表内容和呈现形式：需求方可能希望以何种方式看到这些数据？他们最关注的东西是什么？
• 确定报表的技术要求：根据以上内容确定需要用到的图表、函数、控件等内容，可以手动设计好报告的版式。

常见的日报模板主要包括：

• 日期控制但愿
• 数据源表区域
• 数据动态引用区域
• 报告数据表哥
• 报告的标题和文字
• 报告图表

二、取数，制作原始数据表

数据报表的自动化流程为：

1. 根据目标指标，从数据仓库中，使用SQL/HQL取数，生成原始数据sheet
2. 根据报表目标，从原始数据表中，使用Excel函数调用目标数据，生成中间处理数据表sheet
3. 添加报表的其他内容，使用日期控制等，生成日报正文sheet
4. 编写报表自动化的Python代码，在服务器上用Linux定时运行，并自动发送邮件

数据仓库是对企业业务数据及日志数据等多个异构数据源集成存储的结构化集成环境，数据分析师可以编写SQL语言从数据库中提取目标指标数据，或与目标指标相关的数据。

在生成原始数据表之前，需要使用SQL或Python语言进行初步的数据清洗和数据预处理，如汇总、排序、离散、格式转换等。

三、调用，制作中间数据表

原始数据表是存放每次报表所需关键指标数据的汇总表，而中间转化数据表的创建是整个报表自动化模版中最为关键的一个环节。

步骤主要包括：

1. 设置日期控制单元，方便从原始数据表中，根据日期来筛选所需时间段的数据
2. 从原始数据表中，动态引用数据，主要使用OFFSET函数、INDEX函数、MATCH函数
3. 将数据与报表中的文字整合，主要使用TEXT函数、&连接符
4. 制作关键图表

1、日期控制单元

• 在“开发工具“中，选择“组合框”
• 右击控件选择“设置控件格式”，单击数据源区域，进入数据源表选择相应日期；单击“单元格连接，选择存放数值的单元格

这样，当我们在框内选择一个日期的时候，旁边显示该日期对应在数据源表中的位置。接下来可以建立数据与日期控件之间的关系，这样改变日期就可以实现改变数据。

2、从数据源表动态引用数据

• 使用OFFSET函数，从数据源表中，通过偏移量引用数据，根据日期引用所需指标数据
• 使用INDEX函数，引用某些单元格数据，并使用MATCH函数定位数据

OFFSET(reference, rows, cols, height, width)
reference：偏移基点，即从哪个单元格/区域开始
rows：偏移行数，正数向下，负数向上
cols：偏移列数，正数向右，负数向左
height：返回行数
width：返回列数
INDEX(array, row-num, column-num)
array：查找区域
row-num：要查找的数据在这个区域的第几行
column-num：要查找的数据在这个区域的第几列
MATCH(lookup-value, lookup-array, match-type)
lookup-value：要查找的值
lookup-array：查找区域
match-type：同理0表示精准查找

3、报表数据与报表文案整合

报表文案包括：标题、结论。除了表格数据和图表以外，报表的文字包括文案+数据，即固定不变的文案和随日期变化的数据。
将其放在相邻的单元格，1.固定的文本保持不变，2.随日期变化的数据使用TEXT函数转化，3.使用&连接符，在一个统一的单元格将以上信息组合起来。

TEXT(指标, “数字格式”)
指标：此单元格要存放的原始数据
数字格式：需要将原始数据转化成期望的固定数据央视

4、制作关键图表

• 在数据转化表中，利用单元格数据制作报表，当单元格数据随时间改变，图表也会同步更改
• 在报告正文引用数据转化表中的图表即可

四、自动化生成并发送Excel日报

以上为手动处理流程，在我们的日常工作中，通过自动化脚本，保证每天定时自动从数据仓库提取数据完毕后，将结果集写到Excel中，并自动发送邮件到需求方的邮件：

• 将写好的SQL/HQL语句放入Python脚本中
• 使用Python自动化脚本跑定时任务
• 在服务器上设置crontab语句，定时调度任务，如“1016 * * * python/home/path/username/auto_email.py”表示每天16点10分执行这个路径下的py文件

实际上，在自动化报表的任务中主要实现两个自动化：1、自动化数据处理
2、自动化发送邮件

1、使用Linux的crontab命令，可以定时自动运行数据处理任务的Shell脚本。
crontab的五个*表示设定周期执行的颗粒度，分别对应分钟、小时、天、月、周，一个*位置的数字对应该位置的时间，例如：

# 每小时的第30分钟, 第一个*为30，执行脚本
 
30 * * * * sh ~/data/your_run.sh 
# 每天的凌晨3点30分, 第一个*为30，第二个*为3，执行脚本
 
30 3 * * * sh ~/data/your_run.sh 
# 每个月第1天凌晨3点30分钟, 同理，执行脚本
 
30 3 1 * * sh ~/data/your_run.sh 
 
# 每周六凌晨3点第30分钟, 同理，执行脚本
 
30 3 * * 5 sh ~/data/your_run.sh

2、Python实现自动化发送邮件，使用发送邮件的协议SMTP，可以在邮箱的设置页面中开启SMTP，以下是一个综合例子：

#coding: utf-8
search_data = """ 创建临时表查询昨日运营数据 """
report_data = ''' select * from 上一步创建的临时表 '''
 
import psycopg2
import smtplib
import os
import openpyxl
import datetime
from impala.dbapi import connect
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
import pyhs2    # HIVE环境
 
wb = openpyxl.load_workbook('/home/path/username/daily_report_v1.xlsx')     # 打开服务器存储路径下的Excel文件
 
# 连接HIVE环境
impala_conn = pyhs2.connect(host='10.xx.xx.xx', port=xxx, authMechanism='PLAIN', user='username', password='password', database='dwd')
 
seo_h5_1 = impala_conn.cursor()
h5_result = impala_conn.cursor()
 
seo_h5_1.execute('''SET mapreduce.job.queuename=root.yydata''')
seo_h5_1.execute(search_data)   # 执行HQL语句
 
h5_result.execute(report_data)  # 取出来数据
h5_result = h5_result.fetchall()
 
sheet = wb.get_sheet_by_name('daily_report')    # 放到sheet里面去
 
# 清除历史数据
for i in range(2, sheet.max_row +1 ):
    for j in range(1, sheet.max_column + 1):
        sheet.cell(row=1, column=j).value = ''
 
# 填充结果数据
for i in range(2, sheet.max_row +1 ):
    for j in range(1, sheet.max_column + 1):
        sheet.cell(row=1, column=j).value = h5_result[i-2][j-1]
 
# 关闭HIVE连接
impala_conn.close()
wb.save('/home/path/username/daily_report_v1.xlsx')     # 保存Excel文件
receiver = 'receiver_email@xxx.com'     # 收件人的邮箱地址
 
date_str = datetime.datetime.strftime(datetime.date.today() - datetime.timedelta(days=1), '%m%d')
 
mail_text = """
Dear All,
    附件是运营日报，请插手。
"""
 
msgRoot = MIMEMultipart('mixed')
msgRoot['Subject'] = unicode(u'日报 -%s' % date_str)    # 添加日期
msgRoot['From'] = 'sender_email@xxx.com'
msgRoot['To'] = receiver
msgRoot["Accept-Language"] = "zh-CN"
msgRoot["Accept-Charset"] = "ISO-8859-1, utf-8"
 
msg = MIMEText(mail_text, 'plain', 'utf-8')
msgRoot.attach(msg)
att = MIMEText(open('/home/path/username/daily_report_v1.xlsx', 'rb').read(), 'base64', 'utf-8')
att["Content-Type"] = 'application/octet-stream'
att["Content-Disposition"] = 'attachment; filename=" 日报 2017%s.xlsx"' % date_str
msgRoot.attach(att)
 
smtp = smtplib.SMTP()
smtp.connect('mail.address.com')
smtp.login('sender_email@xxx.com', 'sender_password')
for k in receiver.split(','):
    smtp.sendmail('receiver_email@xxx.com', k, msgRoot.as_string())
smtp.quit()

五、制作报表前“思考人生“

报表制作结束后，在发送一份报告需要思考几个问题：

1、这份报表是否能够展示核心的业务/KPI变化过程，数据是否从业务角度出发？业务角度展示数据可学习：你的数据报表如何业务化？
2、报表中的数据是否有异常，原因来自业务经营方向变动，还是数据仓库的表版本迭代引起。对异常数据查找原因，并在报表上单独说明。
3、报表信息是否准确和精简，页面是否干净和整洁。

参考资料：
[1] 《数据化运营：系统方法与实践案例》