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

最新推荐文章于 2023-12-31 09:30:00 发布

原创最新推荐文章于 2023-12-31 09:30:00 发布 · 1.1k 阅读

CC 4.0 BY-SA版权

本文通过SQL从sakila数据库中获取并导出消费者消费数据，然后在Excel中进行数据分列和创建数据透视表，分析各国消费额。接着，使用Echarts进行可视化，展示了如何下载并引用Echarts库，解决引入js文件的问题，最终实现图表的滚轮缩放和拖拽漫游功能。

一、获取数据

利用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

在这里插入图片描述

选择路径

导出成功

在这里插入图片描述

二、利用Excel进行数据分析

在这里插入图片描述

选择数据，进行分列

在这里插入图片描述

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

在这里插入图片描述

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

三、利用echarts进行可视化

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

下载地址：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

var myChart =echarts.init(document.getElementById('main')); 
        option = { 
            title:{ 
         	 left: 'center',
          top: 'top' 
            }, 
            visualMap:{            
            min: 50,
            max: 7000,                 
            text:['High','Low'],                
            realtime: false,
            calculable: true, 
            color: ['orangered','yellow','lightskyblue'] 
            }, 
            series:
[                 
{                 
name: 'World Population (2010)',                     
type: 'map',                  
mapType: 'world',                 
roam: true,      
itemStyle:{              
emphasis:{label:{show:true}}                  
},                     
data: [
                   
{name: 'Afghanistan', value:67.82}, {name: 'Algeria', value:383.1}, {name:
'American Samoa', value:71.8}, {name: 'Angola', value:215.48}, {name:
'Anguilla', value:106.65}, {name: 'Argentina', value:1434.48}, {name:
'Armenia', value:118.75}, {name: 'Austria', value:307.22}, {name: 'Azerbaijan',
value:245.43}, {name: 'Bahrain', value:112.75}, {name: 'Bangladesh',
value:402.05}, {name: 'Belarus', value:277.34}, {name: 'Bolivia',
value:183.53}, {name: 'Brazil', value:3200.52}, {name: 'Brunei', value:113.65},
{name: 'Bulgaria', value:204.5}, {name: 'Cambodia', value:191.47}, {name:
'Cameroon', value:200.46}, {name: 'Canada', value:593.63}, {name: 'Chad',
value:135.68}, {name: 'Chile', value:328.29}, {name: 'China', value:5802.73},
{name: 'Colombia', value:709.41}, {name: 'Czech Republic', value:133.71},
{name: 'Dominican Republic', value:318.23}, {name: 'Ecuador', value:390.13},
{name: 'Egypt', value:694.39}, {name: 'Estonia', value:115.7}, {name:
'Ethiopia', value:91.77}, {name: 'Faroe Islands', value:114.72}, {name: 'Finland',
value:101.74}, {name: 'France', value:374.04}, {name: 'French Guiana',
value:103.78}, {name: 'French Polynesia', value:235.46}, {name: 'Gambia',
value:129.7}, {name: 'Germany', value:831.04}, {name: 'Greece', value:232.46},
{name: 'Greenland', value:137.66}, {name: 'Holy See (Vatican City State)',
value:152.66}, {name: 'Hong Kong', value:142.7}, {name: 'Hungary',
value:111.71}, {name: 'India', value:6630.27}, {name: 'Indonesia',
value:1510.33}, {name: 'Iran', value:950.75}, {name: 'Iraq', value:111.73},
{name: 'Israel', value:422.01}, {name: 'Italy', value:831.11}, {name: 'Japan',
value:3471.74}, {name: 'Kazakstan', value:198.48}, {name: 'Kenya',
value:253.46}, {name: 'Kuwait', value:111.74}, {name: 'Latvia', value:262.4},
{name: 'Liechtenstein', value:114.72}, {name: 'Lithuania', value:73.76}, {name:
'Madagascar', value:93.78}, {name: 'Malawi', value:123.72}, {name: 'Malaysia',
value:369.15}, {name: 'Mexico', value:3307.04}, {name: 'Moldova',
value:127.66}, {name: 'Morocco', value:307.29}, {name: 'Mozambique',
value:344.2}, {name: 'Myanmar', value:194.48}, {name: 'Nauru', value:148.69},
{name: 'Nepal', value:116.78}, {name: 'Netherlands', value:586.66}, {name: 'New
Zealand', value:92.76}, {name: 'Nigeria', value:1511.48}, {name: 'North Korea',
value:113.69}, {name: 'Oman', value:187.5}, {name: 'Pakistan', value:525.72},
{name: 'Paraguay', value:275.38}, {name: 'Peru', value:468.88}, {name:
'Philippines', value:2381.32}, {name: 'Poland', value:877.97}, {name: 'Puerto
Rico', value:254.39}, {name: 'Romania', value:235.38}, {name: 'Runion',
value:216.54}, {name: 'Russian Federation', value:3045.87}, {name: 'Saint
Vincent and the Grenadines', value:96.75}, {name: 'Saudi Arabia',
value:523.79}, {name: 'Senegal', value:100.75}, {name: 'Slovakia',
value:89.74}, {name: 'South Africa', value:1204.15}, {name: 'South Korea',
value:574.65}, {name: 'Spain', value:606.58}, {name: 'Sri Lanka', value:116.7},
{name: 'Sudan', value:227.46}, {name: 'Sweden', value:144.66}, {name:
'Switzerland', value:252.39}, {name: 'Taiwan', value:1210.94}, {name:
'Tanzania', value:341.17}, {name: 'Thailand', value:419.04}, {name: 'Tonga',
value:73.82}, {name: 'Tunisia', value:78.77}, {name: 'Turkey', value:1662.12},
{name: 'Turkmenistan', value:136.73}, {name: 'Tuvalu', value:120.74}, {name: 'Ukraine',
value:730.42}, {name: 'United Arab Emirates', value:333.16}, {name: 'United
Kingdom', value:924.8}, {name: 'United States', value:4110.32}, {name:
'Venezuela', value:683.3}, {name: 'Vietnam', value:746.28}, {name: 'Yemen',
value:510.83}, {name: 'Yugoslavia', value:259.43}, {name: 'Zambia',
value:134.67}
             
}  
            ]  
        };  
        myChart.setOption(option);

最开始在引入js文件时，老是遇到问题，其中一个困扰我很久，最后终于找到解决方法，参考：https://blog.youkuaiyun.com/teavamc/article/details/77579069?utm_source=blogxgwz1
在这里插入图片描述
将把引入js的那段代码直接放在整段代码的最后
最后结果如图：

添加roam:true，实现开启滚轮缩放和拖拽漫游

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