JuliaDatabases ---- MySql 连接与数据分析

1、功能概述

JuliaDatabases库提供一些连接数据库的组件,能正常使用的包括Redis,MySql,ODBC,SQLite,其他一些在1.2版本后基本无法使用.https:github.com/JuliaDatabases
JDBC.jl 支持linux 和mcos 目前还不支持windows, 所以这里使用MySql.jl 连接MySql.

 

2、代码样例

using DataFrames,DataFramesMeta,Plots
using MySQL
using Gadfly

#连接Mysql查询要分析的数据
conn = MySQL.connect("localhost", "root", "", db = "device_plan",port=3306)
pl_device_task = MySQL.Query(conn, """SELECT * FROM pl_device_task;""") |> DataFrame
pl_task_result = MySQL.Query(conn, """SELECT * FROM pl_task_result;""") |> DataFrame
pl_inspection_plan = MySQL.Query(conn, """SELECT * FROM pl_inspection_plan  ;""") |> DataFrame

MySQL.disconnect(conn)

names(pl_task_result)
names(pl_device_task)  #任务数据不对

#关联查询巡检任务的执行计划信息
plan_task_result = join(pl_task_result, pl_device_task, on=[:task_id],kind=:left, makeunique=true) #
plan_task_result = plan_task_result[!,[:task_id,:plan_id,:batch_id]]

#取所有行和指定範圍列
names(plan_task_result) #统计执行结果任务
plan_task_result = plan_task_result[!,  All(:task_id, :plan_id,:batch_id)]

#统计每个计划每个批次的执行步骤数
plan_batch_info = by(plan_task_result,[:plan_id,:batch_id],:task_id => length)

#统计每个计划执行了多少个批次
plan_batch_info = by(plan_batch_info,[:plan_id],:batch_id => length)


names(pl_inspection_plan)
typeof(pl_inspection_plan.del_flg)

real_plan_info = @where(pl_inspection_plan,:del_flg.=="0")
plan_batch_info = join(real_plan_info, plan_batch_info, on=[:plan_id],kind=:left, makeunique=true) #

names(plan_batch_info)
dtmp = copy(plan_batch_info)
sort!(dtmp, :batch_id_length)


#绘图 Gadfly
#一定要过滤掉missing的值，或者替换为0
replace!(dtmp.batch_id_length, missing=>0)

@select(dtmp,:plan_name,:batch_id_length)

plot(dtmp, x=:plan_name, y=:batch_id_length)

Color = dtmp[:plan_name]

plot(x=dtmp.plan_name, y=dtmp.batch_id_length, color=Color, Geom.bar,
     Guide.xlabel("plan_name"), Guide.ylabel("batch_id_length"),
     Guide.colorkey(title="plan_name"))

 统计绘制的一张柱状图