产品和运营如何利用MLSQL完成excel处理

概览

MLSQL Console 是一款集数据研发,数据分析,机器学习等于一体Web产品。他的目标是让产品,运营,分析师,研发,算法等都有一个统一的数据工作台。这篇文章重点面向产品和运营,在该文章中,他们会学习到如何在该平台上操作excel,关联多个excel,同时将结果进行图表化。

工作区介绍

image.png

image.png

快捷菜单区可以自动帮我们生成MLSQL语句,一般而言,用户只需要自己能够手动写一些select 语句即可。

待处理数据描述

有两个excel文件:

image.png

内容分别如下:

image.png

image.png

第一个excel有每天每个科室的接待病人的数量。第二个excel有主任和对应的邮箱。

分析任务列表

现在我们的目标是:

  1. 绘制每个科室每天接收到病人的分布图,从而方便查看两个科室的就诊人数的分布情况。
  2. 找到日均就诊病人最多的那个医生的邮箱
  3. 将我们的分析结果保存成新的excel,并且下载到自己电脑。

任务一

我们大致会分成四个步骤:

  1. 将excel文档上传,上传完成后下载到自己的工作区得到操作路径
  2. 加载excel文件,然后给他们取表名
  3. 使用SQL对这些excel进行数据操作
  4. 使用SQL生成图标

下面我们看下具体步骤:

Step1:上传文件

打开操作界面的 Tools/Dashboard,然后拖拽excel-example(目录里包含了两个示例excel)到上传区进行上传操作:

image.png

上传成功后,拖拽Quick Menu/Download uploaded file到编辑区:

image.png

输入上传的文件夹名以及要保存的目录。点击Ok,系统会自动生成语句,点击运行,系统会显示文件下载的实际目录:

image.png

到此为止,我们的文件在远程服务器的路径为:

/tmp/excel-example/triage-patient.xlsx
/tmp/excel-example/master-email.xlsx

我们后面的步骤会用到。

Step2: 加载Excel并且查看

接着我们要加载我们的excel,把它们转化为SQL能操作的表。拖拽 Load data到编辑区:

image.png

填写路径以及表名。表名随意,只要你自己记得就行。点击Ok,那么就能生成对应的语句了。 同理完成另外一个脚本的处理。

这个时候你已经可以通过表名来查看内容了:

image.png

excel里的内容能够被正确的展示。

Step3: 对数据做预处理

现在我们开始用SQL绘图,我们需要的是折线图,横坐标是date, 纵坐标是patientNum两条曲线,分别是眼科和皮肤科。眼科对应的patientNum我们取名叫y1,皮肤科对应的patientNum叫y2。为了方便,我们先把把皮肤科的都过滤出来,然后y1设置为0,y2设置为实际的病人数, 同理眼科,然后把这些数据放到一起,最后的SQL大致如下:

select date  as x, 0 as y1, patientNum as y2 from triagePatient where triage="皮肤科"
union all
select date  as x, patientNum as y1, 0 as y2 from triagePatient where triage="眼科" 
as tempTable;

Step4: 生成图表并分析

select x,sum(y1) as `眼科`,sum(y2) as `皮肤科`, 
-- 告诉系统需要生成图表
"line" as dash
from tempTable where x is not null group by x order by x asc 
as finalTable;

为了展示出图,横坐标名字一定要为x,然后通过dash参数告诉系统使用什么图做展示。这里是折线图,写line就好。最后的SQL大概是如下的:

image.png

我们点击运行,运行的结果如下:

image.png

点击 Tools/Dashboard 查看图标:

image.png

可以看到 两者差异还是非常大的,而且皮肤科还有数据缺失。

image.png

任务二

因为我们已经做完了文件上传和加载excel文件等,所以任务二里,我们只要做数据预处理和生成图标即可。

Step1: 数据预处理

那么现在,第一个任务已经做好了,我们接着做第二任务,第二个任务核心就是要关联两张表, 这可以用Join语法:

select tp.*,me.email from triagePatient as tp  left join masterEmail as me on tp.master==me.master
as triagePatientWithEmail;

Step2: 生成图表并做分析

这样我们得到了一张新表,该表有email字段了。接着我们根据用户进行聚合:

select first(email) as x, 
avg(patientNum) as patientEveryDay
"bar" as dash
from triagePatientWithEmail 
group by master 
order by patientEveryDay desc
as output;

我们用email做横坐标,然后平均病人数作为纵坐标的值,同时使用柱状图:

image.png

可以看到 jack@hotmail的科室日均接诊量遥遥领先。

任务三:保存和下载包含email的新表为excel文件

最后我们希望把triagePatientWithEmail表保存下来,然后下载到自己的电脑上。拖拽 Save data到编辑区,打开对话框,选择excel格式,然后将triagePatientWithEmail 表保存到/tmp/triagePatientWithEmail.xlsx 文件:

image.png

点击ok后自动生成语句,然后点击运行,结果显示保存完毕。我们可以用前面查看excel的方法加载他:

image.png

很完美。然后我们现在要下载他,拖拽

image.png

到编辑区,然后填写路径:

image.png

点击Ok,会打开新标签页进行下载。

完整脚本

最后完整脚本如下:

--------------------------------------------------------------------------------
-- 数据描述:
--
-- 我们有两个excel文件,第一个文件是每个科室每天接收的病人,并且有这个科室的负责人。
-- 第二个文件是科室负责人以及对应的email信息。
--
-- 需求描述:
-- 1. 我们希望看到科室每天接收到的人的一个时间分布图。
-- 2. 日均接收用户最高的科室负责人的email
--------------------------------------------------------------------------------

-- 需求一

-- 下载文件
-- run command as DownloadExt.`` where 
-- from="excel-example" and
-- to="/tmp";
 
load excel.`/tmp/excel-example/triage-patient.xlsx` where useHeader="true"  as triagePatient;
load excel.`/tmp/excel-example/master-email.xlsx` where useHeader="true" as masterEmail;

-- select date_format(cast (UNIX_TIMESTAMP(date, 'dd/MM/yy') as TIMESTAMP),'dd/MM/yy') as x,date from triagePatient as output;

select date  as x, 0 as y1, patientNum as y2 from triagePatient where triage="皮肤科"
union all
select date  as x, patientNum as y1, 0 as y2 from triagePatient where triage="眼科" 
as tempTable;

select x,sum(y1) as `眼科`,sum(y2) as `皮肤科`, 
-- 告诉系统需要生成图表
"line" as dash
from tempTable where x is not null group by x order by x asc 
as finalTable;

select tp.*,me.email from triagePatient as tp  left join masterEmail as me on tp.master==me.master
as triagePatientWithEmail;

select first(email) as x, 
avg(patientNum) as patientEveryDay,master,first(email) as email, 
"bar" as dash
from triagePatientWithEmail 
group by master 
order by patientEveryDay desc
as output;

save overwrite triagePatientWithEmail as excel.`/tmp/triagePatientWithEmail.xlsx`;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券