数据分析画图,使用原生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 %}

巴特西

数据分析画图,使用原生sql查询数据

1、使用工具

2、子表查询

最新文章

热门文章