Excel或者ECharts的可视化实践（sakila数据库）

水深无声 2022-02-23 04:40 425阅读 0赞

一、获取数据

利用SQL语句，将customer\_list表和payment表连接，从而得到各个城市每个消费者的消费额

use sakila;
    
    create table test as select * from customer_list,payment
    
    where customer_list.ID=payment.customer_id

以csv格式导出结果

单击右键，选择data export

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70]

选择路径

导出成功

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70 1]

二、利用Excel进行数据分析

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70 2]

选择数据，进行分列

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70 3]

在Excel中建立数据透视表，得到各个国家的消费额

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70 4]

利用数据透视表可以清楚地看到各个国家每个月的消费额情况

三、利用echarts进行可视化

首先要下载echarts.js和world.js

下载地址：[https://echarts.baidu.com/download.html][https_echarts.baidu.com_download.html]

index.html

<!DOCTYPE html>
    <html>
    <head>
    <meta charset="utf-8" />
    <title></title>
    </head>
    <body>
    <div id="main" style="width: 1000px;height:500px;border:1px
    solid #ccc"></div>
    <script
    src="incubator-echarts-4.2.1/dist/echarts.min.js"></script>
    <script
    src="incubator-echarts-4.2.1/map/js/world.js"></script> 
    <script src="js/index.js"></script>
    </body>
    </html>

index.js

最开始在引入js文件时，老是遇到问题，其中一个困扰我很久，最后终于找到解决方法，参考：[https://blog.csdn.net/teavamc/article/details/77579069?utm\_source=blogxgwz1][https_blog.csdn.net_teavamc_article_details_77579069_utm_source_blogxgwz1]  
![在这里插入图片描述][20190406154153744.png]  
将把引入js的那段代码直接放在整段代码的最后  
最后结果如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70 5]  
添加roam:true，实现开启滚轮缩放和拖拽漫游

visualMap: {  
                    min: 50,  
                    max: 7000,  
                    text:['High','Low'],  
                    realtime: false,  
                    calculable: true, 
                    roam:true,
                    color: ['orangered','yellow','lightskyblue']  
                },

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70]: /images/20220223/21d0a3cfd4c24260a144f8ddcd7cdfdc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70 1]: /images/20220223/9379bf1b541341df8ad06795af797979.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70 2]: /images/20220223/d710be8a55b84b9982947a0c56ff9509.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70 3]: /images/20220223/61357d1fae714d97b3044c2f2ad93bee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70 4]: /images/20220223/8ba2a182b0a74c01bd7b417ffd65818e.png
[https_echarts.baidu.com_download.html]: https://echarts.baidu.com/download.html
[https_blog.csdn.net_teavamc_article_details_77579069_utm_source_blogxgwz1]: https://blog.csdn.net/teavamc/article/details/77579069?utm_source=blogxgwz1
[20190406154153744.png]: /images/20220223/d4517a9aeda14efa9054d6dbd952789e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTY3MTM2_size_16_color_FFFFFF_t_70 5]: /images/20220223/3a9e898455a94bb5a6af88eb8738a104.png