数据分析画图,使用原生sql查询数据
1、使用工具
https://www.hcharts.cn/
http://echarts.baidu.com/
2、子表查询
id 创建时间 内容 处理者 1 2017-02-01 11:11 1 2 2017-03-01 11:11 11 3 2017-04-01 11:11 2 4 2017-05-01 11:11 1 5 2017-02-02 11:11 1 1. 思路 处理者列表 = select 处理着 from tb for 处理者 in 处理者列表: v = select * from tb where 处理者=处理者 group by 创建时间(2017-02) 2. 思路 # 每个月份全部门处理的订单个数 select * from tb group by 创建时间(%Y-%m) select 创建时间(%Y-%m), (select count(id) from tb as T2 where 处理者=1 and T2.ctime = T1.ctime ), (select count(id) from tb as T2 where 处理者=2 and T2.ctime = T1.ctime ), from tb as T1 group by 创建时间(%Y-%m) 年月 id=1 id=2 2017-02 2 2017-03 0 2017-04 0 2017-05 1
def trouble_json_report(request): reponse = [] from django.db import connection,connections userlist = UserInfo.objects.all() for row in userlist: cursor = connection.cursor() cursor.execute(""" select unix_timestamp(date_format(ctime,"%%Y-%%m-01"))*1000 ,COUNT(id) from repository_trouble WHERE processer_id=%s GROUP BY date_format(ctime,"%%Y-%%m") """,[row.nid]) result = cursor.fetchall() temp = { 'name':row.username, 'data':result } reponse.append(temp) import json return HttpResponse(json.dumps(reponse))
{% extends 'backdemo.html' %} {% block title %} article {% endblock %} {% block css %} <style> </style> {% endblock %} {% block modal %} {% endblock %} {% block content %} <ol class="breadcrumb"> <li><a href="#">保障管理</a></li> <li class="active">数据分析</li> </ol> </head> <body> <div id="container"></div> {% endblock %} {% block js %} <script src="/static/plugins/Highcharts/code/highcharts.js"></script> <script> Highcharts.setOptions({ global: { useUTC: false } }); $(function(){ initChart(); }); function initChart(){ var config = { chart: { type: 'spline' }, title: { text: '动态模拟实时数据' }, xAxis: { type: 'datetime' }, yAxis: { title: { text: '值' }, plotLines: [{ value: 0, width: 1, color: '#808080' }] }, tooltip: { formatter: function () { return '<b>' + this.series.name + '</b><br/>' + Highcharts.dateFormat('%Y-%m-%d %H:%M:%S', this.x) + '<br/>' + Highcharts.numberFormat(this.y, 2); } }, legend: { enabled: true }, exporting: { enabled: false }, series: [ { name: 'A', data: [ [1491535949788.035, 7.0], [1491535949888.035, 6.0], [1491535949988.035, 10.0], [1491535950088.035, 1.0], ] }, { name: 'B', data: [ [1491535949788.035, 8.0], [1491535949888.035, 2.0], [1491535949988.035, 40.0], [1491535950088.035, 1.0], ] } , { name: 'C', data: [ [1491535949788.035, 10.0], [1491535949888.035, 2.0], [1491535949988.035, 10.0], [1491535950088.035, 8.0], ] } ] }; // 数据库中获取 series {# $('#container').highcharts(config);#} $.ajax({ url: '/backend/trouble-json-report.html', dataType: 'json', success:function(arg){ console.log(123123); config['series'] = arg; $('#container').highcharts(config); } }) } </script> {% endblock %}