Apache Superset 是一个现代的数据探索和可视化平台。它功能强大且十分易用，可对接
各种数据源，包括很多现代的大数据分析引擎，拥有丰富的图表展示形式，并且支持自定义
仪表盘。
image.png

一、安装

1. 环境

操作系统为 CentOS 7，Superset 对接的数据源为 MySQL 数据库。
Superset 官网地址：http://superset.apache.org/

1.1 Python环境

Superset 是由 Python 语言编写的 Web 应用，要求 Python3.7 的环境。

1.2 Miniconda

conda 是一个开源的包、环境管理器，可以用于在同一个机器上安装不同 Python 版本的
软件包及其依赖，并能够在不同的 Python 环境之间切换，Anaconda 包括 Conda、Python 以
及一大堆安装好的工具包，比如：numpy、pandas 等，Miniconda 包括 Conda、Python。
此处，我们不需要如此多的工具包，故选择 MiniConda。

  1. 下载 Miniconda（Python3 版本）

下载地址：https://repo.anaconda.com/miniconda/Miniconda3-latest-Linux-x86_64.sh

  1. 安装 Miniconda

1）执行以下命令进行安装，并按照提示操作，直到安装完成。

  1. bash Miniconda3-latest-Linux-x86_64.sh

安装过程图解：
a. 确认继续
image.png
b. 同意协议
image.png
image.png
c. 确认安装的路径
image.png
d. 确认初始化
image.png
e. 安装成功
image.png

  1. 加载环境变量配置文件，使之生效

方式一：直接重新开一个连接窗口
方式二：使用命令刷新：source ~/.bashrc

下图代表miniconda处于生效状态：
image.png

  1. 取消激活 base 环境

Miniconda 安装完成后，每次打开终端都会激活其默认的 base 环境，我们可通过以下命
令，禁止激活默认 base 环境。

  1. # false改为true则开启
  2. conda config --set auto_activate_base false

手动切换至base环境：

  1. conda activate base

退出当前环境：

  1. conda deactivate

  1. conda常用命令

    1. 创建环境：conda create -n env_name python=3.7
    2. 查看所有环境：conda info --envs
    3. 删除一个环境：conda remove -n env_name --all


  2. 使用Miniconda配置Python环境

需要在conda生效状态下进行。
1）配置 conda 国内镜像

  1. conda config --add channels https://mirrors.tuna.tsinghua.edu.cn/anaconda/pkgs/free
  3. conda config --add channels https://mirrors.tuna.tsinghua.edu.cn/anaconda/pkgs/main
  1. 2）**创建 Python3.7 环境**
  1. # 末尾为指定python版本
  2. conda create --name superset python=3.7
  1. 过程：<br />输入“y”继续：<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/22365594/1636948162135-57d05ac0-a338-4478-add4-fe38e413a533.png#clientId=u88fa0479-7b35-4&from=paste&height=361&id=u21592bda&margin=%5Bobject%20Object%5D&name=image.png&originHeight=361&originWidth=603&originalType=binary&ratio=1&size=42014&status=done&style=none&taskId=ua4f0ea2d-1e27-4e2a-b6b4-98365a518b8&width=603)<br />完成：<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/22365594/1636948310301-39279c57-cc7b-4a2d-b48d-4e79095574cb.png#clientId=u88fa0479-7b35-4&from=paste&height=484&id=u5e2394fe&margin=%5Bobject%20Object%5D&name=image.png&originHeight=484&originWidth=763&originalType=binary&ratio=1&size=43760&status=done&style=none&taskId=u9ed8e0aa-bb5b-44be-9a8d-a0b6fa6d083&width=763)<br />3）**激活 superset 环境**
  1. conda activate superset
  1. 激活后效果如下图所示：<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/22365594/1636948465093-4fe4ceea-504d-4ede-9ccf-8493ef309728.png#clientId=u88fa0479-7b35-4&from=paste&height=34&id=u681a7032&margin=%5Bobject%20Object%5D&name=image.png&originHeight=34&originWidth=335&originalType=binary&ratio=1&size=2821&status=done&style=none&taskId=u91d23282-2d64-4d22-a58d-70ccfa334d6&width=335)
  3. 4）**执行 python 命令查看 python 版本**
  1. python -V
  1. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/22365594/1636948499557-6bcc7b24-0fbe-4615-a9e4-ec7853151f99.png#clientId=u88fa0479-7b35-4&from=paste&height=68&id=u97f2782c&margin=%5Bobject%20Object%5D&name=image.png&originHeight=68&originWidth=360&originalType=binary&ratio=1&size=4633&status=done&style=none&taskId=u89836807-0735-47e6-9456-25dad3c9d90&width=360)

2. Superset部署

注意：下列步骤需要在superset环境下进行。

2.1 安装依赖

  1. sudo yum install -y gcc gcc-c++ libffi-devel python-devel python-pip python-wheel python-setuptools openssl-devel cyrus-sasl-devel openldap-devel

image.png

2.2 安装（更新）setuptools 和 pip

pip 是 python 的包管理工具，可以和 centos 中的 yum 类比。

  1. pip install --upgrade setuptools pip -i https://pypi.douban.com/simple/

image.png

2.3 安装 Supetset

  1. pip install apache-superset -i https://pypi.douban.com/simple/

image.png
注：-i 的作用是指定镜像，这里选择国内镜像；如果遇到网络错误导致不能下载，可尝试更换镜像：

  1. pip install apache-superset --trusted-host https://repo.huaweicloud.com -i https://repo.huaweicloud.com/repository/pypi/simple

2.4 初始化 Supetset 数据库

  1. superset db upgrade

2.5 创建管理员用户

  1. # 声明环境变量
  2. export FLASK_APP=superset
  3. # 创建管理员用户命令
  4. superset fab create-admin

注：flask 是一个 python web 框架，Superset 使用的就是 flask

配置如下，红色部分需要填写，黄色部分可以回车跳过：image.png

2.6 Superset 初始化

  1. superset init

image.png

2.7 启动 Supterset

1）安装 gunicorn
gunicorn 是一个 Python Web Server，可以和 java 中的 TomCat 类比

  1. pip install gunicorn -i https://pypi.douban.com/simple/

image.png
2）启动 Superset

  1.   - **确保当前 conda 环境为 superset，及下图所示**
  2.      - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/22365594/1636956822629-22e933b5-8fa2-4389-acfc-ed3eb8826eb8.png#clientId=u88fa0479-7b35-4&from=paste&height=22&id=u2b5c1d72&margin=%5Bobject%20Object%5D&name=image.png&originHeight=22&originWidth=315&originalType=binary&ratio=1&size=1704&status=done&style=none&taskId=u6f5391f7-c4e2-4161-967e-17d2e64f855&width=315)
  3.   - **启动**
  1. # 注意命令中绑定的ip地址需要对照自己Superset部署的服务器进行更改
  2. gunicorn --workers 5 --timeout 120 --bind 192.168.234.130:8787 "superset.app:create_app()" --daemon

说明：
—workers：指定进程个数
—timeout：worker 进程超时时间，超时会自动重启
—bind：绑定本机地址，即为 Superset 访问地址
—daemon：后台运行
3）登录 Superset
访问 http://对应ip:8787，使用前阶段创建的管理员账号进行登录。
注意：防火墙若没有关闭，则需开放8787端口
image.png
4）停止 superset

  1. # 停掉 gunicorn 进程
  2. ps -ef | awk '/superset/ && !/awk/{print $2}' | xargs kill -9
  4. # 退出 superset 环境
  5. conda deactivate

2.8 Superset 启停脚本

  1. 创建 superset.sh 文件，编辑脚本内容

1）创建文件：
在bin目录创建此文件

  1. vim superset.sh

2）脚本内容：
注意：启动脚本里的ip地址同样要根据个人情况做修改

  1. #!/bin/bash
  3. superset_status(){
  4.     result=`ps -ef | awk '/gunicorn/ && !/awk/{print $2}' | wc -l`
  5.     if [[ $result -eq 0 ]]; then
  6.         return 0
  7.     else
  8.         return 1
  9.     fi
  10. }
  11. superset_start(){
  12.     source ~/.bashrc
  13.     superset_status >/dev/null 2>&1
  14.     if [[ $? -eq 0 ]]; then
  15.         conda activate superset ; gunicorn --workers 5 --timeout 120 --bind 192.168.234.130:8787 --daemon 'superset.app:create_app()'
  16.     else
  17.         echo "superset 正在运行"
  18.     fi
  19. }
  20. superset_stop(){
  21.     superset_status >/dev/null 2>&1
  22.     if [[ $? -eq 0 ]]; then
  23.         echo "superset 未在运行"
  24.     else
  25.         ps -ef | awk '/gunicorn/ && !/awk/{print $2}' | xargs kill -9
  26.     fi
  27. }
  28. case $1 in
  29.     start )
  30.         echo "启动 Superset"
  31.         superset_start
  32.     ;;
  33.     stop )
  34.         echo "停止 Superset"
  35.         superset_stop
  36.     ;;
  37.     restart )
  38.         echo "重启 Superset"
  39.         superset_stop
  40.         superset_start
  41.     ;;
  42.     status )
  43.         superset_status >/dev/null 2>&1
  44.         if [[ $? -eq 0 ]]; then
  45.             echo "superset 未在运行"
  46.         else
  47.             echo "superset 正在运行"
  48.         fi
  49. esac

  1. 加执行权限

    1. chmod +x superset.sh


  2. 测试 ```shell

    启动

    superset.sh start

停止

superset.sh stop

重启

superset.sh restart

状态

superset.sh status

  2. <a name="QeFCO"></a>
  3. # 二、使用
  4. <a name="ULwXG"></a>
  5. ## 1. 配置数据源
  6. <a name="sSWY6"></a>
  7. ### 1.1 MySQL
  8. **1）创建 MySQL 数据库**<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/22365594/1637119617980-9d2b36e4-231e-49b7-97cc-00eb3e9e5d88.png#clientId=u67a164ee-3cc1-4&from=paste&height=401&id=uc70ec34a&margin=%5Bobject%20Object%5D&name=image.png&originHeight=401&originWidth=446&originalType=binary&ratio=1&size=12475&status=done&style=none&taskId=u70a3f695-29df-41c9-b7e4-a3f71b99a66&width=446)<br />**2）导入表结构和模拟数据**<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/22365594/1637119728748-28473cd3-559e-45a2-9f76-519fa850873f.png#clientId=u67a164ee-3cc1-4&from=paste&height=364&id=u58490064&margin=%5Bobject%20Object%5D&name=image.png&originHeight=364&originWidth=412&originalType=binary&ratio=1&size=17498&status=done&style=none&taskId=u4c35b785-a124-4bfe-8e91-c22f33b4016&width=412)<br />表结构：<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/22365594/1637119907787-d50e31f2-db7f-4d87-b631-2dc7856b92c5.png#clientId=u67a164ee-3cc1-4&from=paste&height=596&id=u2752a504&margin=%5Bobject%20Object%5D&name=image.png&originHeight=596&originWidth=1090&originalType=binary&ratio=1&size=83165&status=done&style=none&taskId=ud3c1c5b9-296b-401f-a1f8-648f41d0d43&width=1090)
  10. sql脚本内容：
  11. ```sql
  12. /*
  13.  Navicat MySQL Data Transfer
  15.  Source Server         : hadoop102
  16.  Source Server Type    : MySQL
  17.  Source Server Version : 50716
  18.  Source Host           : hadoop102:3306
  19.  Source Schema         : gmall_report
  21.  Target Server Type    : MySQL
  22.  Target Server Version : 50716
  23.  File Encoding         : 65001
  25.  Date: 16/08/2021 10:59:43
  26. */
  28. SET NAMES utf8mb4;
  29. SET FOREIGN_KEY_CHECKS = 0;
  31. -- ----------------------------
  32. -- Table structure for ads_order_by_province
  33. -- ----------------------------
  34. DROP TABLE IF EXISTS `ads_order_by_province`;
  35. CREATE TABLE `ads_order_by_province`  (
  36.   `dt` date NOT NULL,
  37.   `province_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '统计日期',
  38.   `province_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '省份名称',
  39.   `area_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '地区编码',
  40.   `iso_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '国际标准地区编码',
  41.   `order_count` bigint(20) NULL DEFAULT NULL COMMENT '订单数',
  42.   `order_amount` decimal(16, 2) NULL DEFAULT NULL COMMENT '订单金额',
  43.   PRIMARY KEY (`dt`, `province_id`) USING BTREE
  44. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
  46. -- ----------------------------
  47. -- Records of ads_order_by_province
  48. -- ----------------------------
  49. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '1', '北京', '110000', 'CN-11', 13, 402232.30);
  50. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '10', '福建', '350000', 'CN-35', 7, 315645.65);
  51. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '11', '江西', '360000', 'CN-36', 4, 114391.00);
  52. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '12', '山东', '370000', 'CN-37', 5, 291400.00);
  53. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '13', '重庆', '500000', 'CN-50', 6, 153022.00);
  54. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '14', '台湾', '710000', 'CN-71', 6, 184981.70);
  55. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '15', '黑龙江', '230000', 'CN-23', 2, 80049.00);
  56. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '16', '吉林', '220000', 'CN-22', 8, 144899.00);
  57. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '17', '辽宁', '210000', 'CN-21', 2, 20976.00);
  58. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '18', '陕西', '610000', 'CN-61', 3, 100557.00);
  59. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '19', '甘肃', '620000', 'CN-62', 9, 422565.70);
  60. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '2', '天津', '120000', 'CN-12', 11, 283788.70);
  61. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '20', '青海', '630000', 'CN-63', 12, 289611.65);
  62. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '21', '宁夏', '640000', 'CN-64', 5, 184526.00);
  63. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '22', '新疆', '650000', 'CN-65', 9, 262549.35);
  64. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '23', '河南', '410000', 'CN-41', 5, 197189.30);
  65. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '24', '湖北', '420000', 'CN-42', 4, 25823.35);
  66. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '25', '湖南', '430000', 'CN-43', 7, 187572.35);
  67. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '26', '广东', '440000', 'CN-44', 9, 357141.00);
  68. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '27', '广西', '450000', 'CN-45', 4, 130352.00);
  69. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '28', '海南', '460000', 'CN-46', 6, 104162.35);
  70. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '29', '香港', '810000', 'CN-91', 6, 203598.00);
  71. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '3', '山西', '140000', 'CN-14', 2, 65986.00);
  72. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '30', '澳门', '820000', 'CN-92', 7, 268936.30);
  73. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '31', '四川', '510000', 'CN-51', 1, 18893.00);
  74. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '32', '贵州', '520000', 'CN-52', 6, 236319.35);
  75. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '33', '云南', '530000', 'CN-53', 8, 381042.00);
  76. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '34', '西藏', '540000', 'CN-54', 5, 187908.00);
  77. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '4', '内蒙古', '150000', 'CN-15', 6, 178156.35);
  78. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '5', '河北', '130000', 'CN-13', 5, 267768.05);
  79. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '6', '上海', '310000', 'CN-31', 9, 333872.70);
  80. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '7', '江苏', '320000', 'CN-32', 5, 165192.70);
  81. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '8', '浙江', '330000', 'CN-33', 8, 232229.00);
  82. INSERT INTO `ads_order_by_province` VALUES ('2020-06-14', '9', '安徽', '340000', 'CN-34', 6, 158620.05);
  83. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '1', '北京', '110000', 'CN-11', 13, 402232.30);
  84. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '10', '福建', '350000', 'CN-35', 7, 315645.65);
  85. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '11', '江西', '360000', 'CN-36', 4, 114391.00);
  86. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '12', '山东', '370000', 'CN-37', 5, 291400.00);
  87. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '13', '重庆', '500000', 'CN-50', 6, 153022.00);
  88. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '14', '台湾', '710000', 'CN-71', 6, 184981.70);
  89. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '15', '黑龙江', '230000', 'CN-23', 2, 80049.00);
  90. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '16', '吉林', '220000', 'CN-22', 8, 144899.00);
  91. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '17', '辽宁', '210000', 'CN-21', 2, 20976.00);
  92. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '18', '陕西', '610000', 'CN-61', 3, 100557.00);
  93. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '19', '甘肃', '620000', 'CN-62', 9, 422565.70);
  94. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '2', '天津', '120000', 'CN-12', 11, 283788.70);
  95. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '20', '青海', '630000', 'CN-63', 12, 289611.65);
  96. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '21', '宁夏', '640000', 'CN-64', 5, 184526.00);
  97. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '22', '新疆', '650000', 'CN-65', 9, 262549.35);
  98. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '23', '河南', '410000', 'CN-41', 5, 197189.30);
  99. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '24', '湖北', '420000', 'CN-42', 4, 25823.35);
  100. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '25', '湖南', '430000', 'CN-43', 7, 187572.35);
  101. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '26', '广东', '440000', 'CN-44', 9, 357141.00);
  102. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '27', '广西', '450000', 'CN-45', 4, 130352.00);
  103. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '28', '海南', '460000', 'CN-46', 6, 104162.35);
  104. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '29', '香港', '810000', 'CN-91', 6, 203598.00);
  105. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '3', '山西', '140000', 'CN-14', 2, 65986.00);
  106. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '30', '澳门', '820000', 'CN-92', 7, 268936.30);
  107. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '31', '四川', '510000', 'CN-51', 1, 18893.00);
  108. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '32', '贵州', '520000', 'CN-52', 6, 236319.35);
  109. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '33', '云南', '530000', 'CN-53', 8, 381042.00);
  110. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '34', '西藏', '540000', 'CN-54', 5, 187908.00);
  111. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '4', '内蒙古', '150000', 'CN-15', 6, 178156.35);
  112. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '5', '河北', '130000', 'CN-13', 5, 267768.05);
  113. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '6', '上海', '310000', 'CN-31', 9, 333872.70);
  114. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '7', '江苏', '320000', 'CN-32', 5, 165192.70);
  115. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '8', '浙江', '330000', 'CN-33', 8, 232229.00);
  116. INSERT INTO `ads_order_by_province` VALUES ('2020-06-15', '9', '安徽', '340000', 'CN-34', 6, 158620.05);
  117. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '1', '北京', '110000', 'CN-11', 13, 402232.30);
  118. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '10', '福建', '350000', 'CN-35', 7, 315645.65);
  119. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '11', '江西', '360000', 'CN-36', 4, 114391.00);
  120. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '12', '山东', '370000', 'CN-37', 5, 291400.00);
  121. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '13', '重庆', '500000', 'CN-50', 6, 153022.00);
  122. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '14', '台湾', '710000', 'CN-71', 6, 184981.70);
  123. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '15', '黑龙江', '230000', 'CN-23', 2, 80049.00);
  124. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '16', '吉林', '220000', 'CN-22', 8, 144899.00);
  125. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '17', '辽宁', '210000', 'CN-21', 2, 20976.00);
  126. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '18', '陕西', '610000', 'CN-61', 3, 100557.00);
  127. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '19', '甘肃', '620000', 'CN-62', 9, 422565.70);
  128. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '2', '天津', '120000', 'CN-12', 11, 283788.70);
  129. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '20', '青海', '630000', 'CN-63', 12, 289611.65);
  130. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '21', '宁夏', '640000', 'CN-64', 5, 184526.00);
  131. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '22', '新疆', '650000', 'CN-65', 9, 262549.35);
  132. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '23', '河南', '410000', 'CN-41', 5, 197189.30);
  133. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '24', '湖北', '420000', 'CN-42', 4, 25823.35);
  134. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '25', '湖南', '430000', 'CN-43', 7, 187572.35);
  135. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '26', '广东', '440000', 'CN-44', 9, 357141.00);
  136. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '27', '广西', '450000', 'CN-45', 4, 130352.00);
  137. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '28', '海南', '460000', 'CN-46', 6, 104162.35);
  138. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '29', '香港', '810000', 'CN-91', 6, 203598.00);
  139. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '3', '山西', '140000', 'CN-14', 2, 65986.00);
  140. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '30', '澳门', '820000', 'CN-92', 7, 268936.30);
  141. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '31', '四川', '510000', 'CN-51', 1, 18893.00);
  142. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '32', '贵州', '520000', 'CN-52', 6, 236319.35);
  143. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '33', '云南', '530000', 'CN-53', 8, 381042.00);
  144. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '34', '西藏', '540000', 'CN-54', 5, 187908.00);
  145. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '4', '内蒙古', '150000', 'CN-15', 6, 178156.35);
  146. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '5', '河北', '130000', 'CN-13', 5, 267768.05);
  147. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '6', '上海', '310000', 'CN-31', 9, 333872.70);
  148. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '7', '江苏', '320000', 'CN-32', 5, 165192.70);
  149. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '8', '浙江', '330000', 'CN-33', 8, 232229.00);
  150. INSERT INTO `ads_order_by_province` VALUES ('2020-06-16', '9', '安徽', '340000', 'CN-34', 6, 158620.05);
  151. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '1', '北京', '110000', 'CN-11', 13, 402232.30);
  152. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '10', '福建', '350000', 'CN-35', 7, 315645.65);
  153. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '11', '江西', '360000', 'CN-36', 4, 114391.00);
  154. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '12', '山东', '370000', 'CN-37', 5, 291400.00);
  155. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '13', '重庆', '500000', 'CN-50', 6, 153022.00);
  156. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '14', '台湾', '710000', 'CN-71', 6, 184981.70);
  157. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '15', '黑龙江', '230000', 'CN-23', 2, 80049.00);
  158. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '16', '吉林', '220000', 'CN-22', 8, 144899.00);
  159. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '17', '辽宁', '210000', 'CN-21', 2, 20976.00);
  160. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '18', '陕西', '610000', 'CN-61', 3, 100557.00);
  161. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '19', '甘肃', '620000', 'CN-62', 9, 422565.70);
  162. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '2', '天津', '120000', 'CN-12', 11, 283788.70);
  163. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '20', '青海', '630000', 'CN-63', 12, 289611.65);
  164. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '21', '宁夏', '640000', 'CN-64', 5, 184526.00);
  165. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '22', '新疆', '650000', 'CN-65', 9, 262549.35);
  166. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '23', '河南', '410000', 'CN-41', 5, 197189.30);
  167. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '24', '湖北', '420000', 'CN-42', 4, 25823.35);
  168. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '25', '湖南', '430000', 'CN-43', 7, 187572.35);
  169. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '26', '广东', '440000', 'CN-44', 9, 357141.00);
  170. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '27', '广西', '450000', 'CN-45', 4, 130352.00);
  171. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '28', '海南', '460000', 'CN-46', 6, 104162.35);
  172. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '29', '香港', '810000', 'CN-91', 6, 203598.00);
  173. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '3', '山西', '140000', 'CN-14', 2, 65986.00);
  174. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '30', '澳门', '820000', 'CN-92', 7, 268936.30);
  175. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '31', '四川', '510000', 'CN-51', 1, 18893.00);
  176. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '32', '贵州', '520000', 'CN-52', 6, 236319.35);
  177. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '33', '云南', '530000', 'CN-53', 8, 381042.00);
  178. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '34', '西藏', '540000', 'CN-54', 5, 187908.00);
  179. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '4', '内蒙古', '150000', 'CN-15', 6, 178156.35);
  180. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '5', '河北', '130000', 'CN-13', 5, 267768.05);
  181. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '6', '上海', '310000', 'CN-31', 9, 333872.70);
  182. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '7', '江苏', '320000', 'CN-32', 5, 165192.70);
  183. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '8', '浙江', '330000', 'CN-33', 8, 232229.00);
  184. INSERT INTO `ads_order_by_province` VALUES ('2020-06-17', '9', '安徽', '340000', 'CN-34', 6, 158620.05);
  185. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '1', '北京', '110000', 'CN-11', 13, 402232.30);
  186. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '10', '福建', '350000', 'CN-35', 7, 315645.65);
  187. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '11', '江西', '360000', 'CN-36', 4, 114391.00);
  188. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '12', '山东', '370000', 'CN-37', 5, 291400.00);
  189. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '13', '重庆', '500000', 'CN-50', 6, 153022.00);
  190. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '14', '台湾', '710000', 'CN-71', 6, 184981.70);
  191. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '15', '黑龙江', '230000', 'CN-23', 2, 80049.00);
  192. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '16', '吉林', '220000', 'CN-22', 8, 144899.00);
  193. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '17', '辽宁', '210000', 'CN-21', 2, 20976.00);
  194. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '18', '陕西', '610000', 'CN-61', 3, 100557.00);
  195. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '19', '甘肃', '620000', 'CN-62', 9, 422565.70);
  196. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '2', '天津', '120000', 'CN-12', 11, 283788.70);
  197. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '20', '青海', '630000', 'CN-63', 12, 289611.65);
  198. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '21', '宁夏', '640000', 'CN-64', 5, 184526.00);
  199. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '22', '新疆', '650000', 'CN-65', 9, 262549.35);
  200. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '23', '河南', '410000', 'CN-41', 5, 197189.30);
  201. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '24', '湖北', '420000', 'CN-42', 4, 25823.35);
  202. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '25', '湖南', '430000', 'CN-43', 7, 187572.35);
  203. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '26', '广东', '440000', 'CN-44', 9, 357141.00);
  204. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '27', '广西', '450000', 'CN-45', 4, 130352.00);
  205. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '28', '海南', '460000', 'CN-46', 6, 104162.35);
  206. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '29', '香港', '810000', 'CN-91', 6, 203598.00);
  207. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '3', '山西', '140000', 'CN-14', 2, 65986.00);
  208. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '30', '澳门', '820000', 'CN-92', 7, 268936.30);
  209. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '31', '四川', '510000', 'CN-51', 1, 18893.00);
  210. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '32', '贵州', '520000', 'CN-52', 6, 236319.35);
  211. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '33', '云南', '530000', 'CN-53', 8, 381042.00);
  212. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '34', '西藏', '540000', 'CN-54', 5, 187908.00);
  213. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '4', '内蒙古', '150000', 'CN-15', 6, 178156.35);
  214. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '5', '河北', '130000', 'CN-13', 5, 267768.05);
  215. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '6', '上海', '310000', 'CN-31', 9, 333872.70);
  216. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '7', '江苏', '320000', 'CN-32', 5, 165192.70);
  217. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '8', '浙江', '330000', 'CN-33', 8, 232229.00);
  218. INSERT INTO `ads_order_by_province` VALUES ('2020-06-18', '9', '安徽', '340000', 'CN-34', 6, 158620.05);
  220. -- ----------------------------
  221. -- Table structure for ads_order_spu_stats
  222. -- ----------------------------
  223. DROP TABLE IF EXISTS `ads_order_spu_stats`;
  224. CREATE TABLE `ads_order_spu_stats`  (
  225.   `dt` date NOT NULL COMMENT '统计日期',
  226.   `spu_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '商品ID',
  227.   `spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '商品名称',
  228.   `tm_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '品牌ID',
  229.   `tm_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '品牌名称',
  230.   `category3_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '三级品类ID',
  231.   `category3_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '三级品类名称',
  232.   `category2_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '二级品类ID',
  233.   `category2_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '二级品类名称',
  234.   `category1_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '一级品类ID',
  235.   `category1_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '一级品类名称',
  236.   `order_count` bigint(20) NULL DEFAULT NULL COMMENT '订单数',
  237.   `order_amount` decimal(16, 2) NULL DEFAULT NULL COMMENT '订单金额',
  238.   PRIMARY KEY (`dt`, `spu_id`) USING BTREE
  239. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
  241. -- ----------------------------
  242. -- Records of ads_order_spu_stats
  243. -- ----------------------------
  244. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '1', '小米10', '1', '三星', '61', '手机', '13', '手机通讯', '2', '手机', 75, 999840.00);
  245. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '10', 'CAREMiLLE珂曼奶油小方口红 雾面滋润保湿持久丝缎唇膏', '9', 'CAREMiLLE', '477', '唇部', '54', '香水彩妆', '8', '个护化妆', 194, 27410.64);
  246. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '11', '香奈儿（Chanel）女士香水5号香水 粉邂逅柔情淡香水EDT ', '11', '香奈儿', '473', '香水', '54', '香水彩妆', '8', '个护化妆', 51, 37520.00);
  247. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '12', '华为智慧屏 4K全面屏智能电视机', '3', '华为', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 54, 505065.00);
  248. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '2', 'Redmi 10X', '1', '三星', '61', '手机', '13', '手机通讯', '2', '手机', 113, 299439.00);
  249. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '3', 'Apple iPhone 12', '2', '苹果', '61', '手机', '13', '手机通讯', '2', '手机', 137, 2428867.29);
  250. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '4', 'HUAWEI P40', '3', '华为', '61', '手机', '13', '手机通讯', '2', '手机', 97, 858353.71);
  251. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '5', 'TCL巨幕私人影院电视 4K超高清 AI智慧屏  液晶平板电视机', '4', 'TCL', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 72, 1478239.00);
  252. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '6', '小米电视 内置小爱 智能网络液晶平板教育电视', '5', '小米', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 50, 326894.00);
  253. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '7', '十月稻田 长粒香大米 东北大米 东北香米 5kg', '6', '长粒香', '803', '米面杂粮', '82', '粮油调味', '14', '食品饮料、保健食品', 52, 4816.00);
  254. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '8', '金沙河面条 原味银丝挂面 龙须面 方便速食拉面 清汤面 900g', '7', '金沙河', '803', '米面杂粮', '82', '粮油调味', '14', '食品饮料、保健食品', 40, 1118.00);
  255. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-14', '9', '索芙特i-Softto 口红不掉色唇膏保湿滋润 璀璨金钻哑光唇膏 ', '8', '索芙特', '477', '唇部', '54', '香水彩妆', '8', '个护化妆', 180, 50819.76);
  256. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '1', '小米10', '1', '三星', '61', '手机', '13', '手机通讯', '2', '手机', 75, 999840.00);
  257. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '10', 'CAREMiLLE珂曼奶油小方口红 雾面滋润保湿持久丝缎唇膏', '9', 'CAREMiLLE', '477', '唇部', '54', '香水彩妆', '8', '个护化妆', 194, 27410.64);
  258. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '11', '香奈儿（Chanel）女士香水5号香水 粉邂逅柔情淡香水EDT ', '11', '香奈儿', '473', '香水', '54', '香水彩妆', '8', '个护化妆', 51, 37520.00);
  259. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '12', '华为智慧屏 4K全面屏智能电视机', '3', '华为', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 54, 505065.00);
  260. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '2', 'Redmi 10X', '1', '三星', '61', '手机', '13', '手机通讯', '2', '手机', 113, 299439.00);
  261. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '3', 'Apple iPhone 12', '2', '苹果', '61', '手机', '13', '手机通讯', '2', '手机', 137, 2428867.29);
  262. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '4', 'HUAWEI P40', '3', '华为', '61', '手机', '13', '手机通讯', '2', '手机', 97, 858353.71);
  263. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '5', 'TCL巨幕私人影院电视 4K超高清 AI智慧屏  液晶平板电视机', '4', 'TCL', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 72, 1478239.00);
  264. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '6', '小米电视 内置小爱 智能网络液晶平板教育电视', '5', '小米', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 50, 326894.00);
  265. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '7', '十月稻田 长粒香大米 东北大米 东北香米 5kg', '6', '长粒香', '803', '米面杂粮', '82', '粮油调味', '14', '食品饮料、保健食品', 52, 4816.00);
  266. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '8', '金沙河面条 原味银丝挂面 龙须面 方便速食拉面 清汤面 900g', '7', '金沙河', '803', '米面杂粮', '82', '粮油调味', '14', '食品饮料、保健食品', 40, 1118.00);
  267. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-15', '9', '索芙特i-Softto 口红不掉色唇膏保湿滋润 璀璨金钻哑光唇膏 ', '8', '索芙特', '477', '唇部', '54', '香水彩妆', '8', '个护化妆', 180, 50819.76);
  268. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '1', '小米10', '1', '三星', '61', '手机', '13', '手机通讯', '2', '手机', 75, 999840.00);
  269. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '10', 'CAREMiLLE珂曼奶油小方口红 雾面滋润保湿持久丝缎唇膏', '9', 'CAREMiLLE', '477', '唇部', '54', '香水彩妆', '8', '个护化妆', 194, 27410.64);
  270. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '11', '香奈儿（Chanel）女士香水5号香水 粉邂逅柔情淡香水EDT ', '11', '香奈儿', '473', '香水', '54', '香水彩妆', '8', '个护化妆', 51, 37520.00);
  271. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '12', '华为智慧屏 4K全面屏智能电视机', '3', '华为', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 54, 505065.00);
  272. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '2', 'Redmi 10X', '1', '三星', '61', '手机', '13', '手机通讯', '2', '手机', 113, 299439.00);
  273. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '3', 'Apple iPhone 12', '2', '苹果', '61', '手机', '13', '手机通讯', '2', '手机', 137, 2428867.29);
  274. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '4', 'HUAWEI P40', '3', '华为', '61', '手机', '13', '手机通讯', '2', '手机', 97, 858353.71);
  275. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '5', 'TCL巨幕私人影院电视 4K超高清 AI智慧屏  液晶平板电视机', '4', 'TCL', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 72, 1478239.00);
  276. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '6', '小米电视 内置小爱 智能网络液晶平板教育电视', '5', '小米', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 50, 326894.00);
  277. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '7', '十月稻田 长粒香大米 东北大米 东北香米 5kg', '6', '长粒香', '803', '米面杂粮', '82', '粮油调味', '14', '食品饮料、保健食品', 52, 4816.00);
  278. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '8', '金沙河面条 原味银丝挂面 龙须面 方便速食拉面 清汤面 900g', '7', '金沙河', '803', '米面杂粮', '82', '粮油调味', '14', '食品饮料、保健食品', 40, 1118.00);
  279. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-16', '9', '索芙特i-Softto 口红不掉色唇膏保湿滋润 璀璨金钻哑光唇膏 ', '8', '索芙特', '477', '唇部', '54', '香水彩妆', '8', '个护化妆', 180, 50819.76);
  280. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '1', '小米10', '1', '三星', '61', '手机', '13', '手机通讯', '2', '手机', 75, 999840.00);
  281. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '10', 'CAREMiLLE珂曼奶油小方口红 雾面滋润保湿持久丝缎唇膏', '9', 'CAREMiLLE', '477', '唇部', '54', '香水彩妆', '8', '个护化妆', 194, 27410.64);
  282. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '11', '香奈儿（Chanel）女士香水5号香水 粉邂逅柔情淡香水EDT ', '11', '香奈儿', '473', '香水', '54', '香水彩妆', '8', '个护化妆', 51, 37520.00);
  283. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '12', '华为智慧屏 4K全面屏智能电视机', '3', '华为', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 54, 505065.00);
  284. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '2', 'Redmi 10X', '1', '三星', '61', '手机', '13', '手机通讯', '2', '手机', 113, 299439.00);
  285. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '3', 'Apple iPhone 12', '2', '苹果', '61', '手机', '13', '手机通讯', '2', '手机', 137, 2428867.29);
  286. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '4', 'HUAWEI P40', '3', '华为', '61', '手机', '13', '手机通讯', '2', '手机', 97, 858353.71);
  287. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '5', 'TCL巨幕私人影院电视 4K超高清 AI智慧屏  液晶平板电视机', '4', 'TCL', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 72, 1478239.00);
  288. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '6', '小米电视 内置小爱 智能网络液晶平板教育电视', '5', '小米', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 50, 326894.00);
  289. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '7', '十月稻田 长粒香大米 东北大米 东北香米 5kg', '6', '长粒香', '803', '米面杂粮', '82', '粮油调味', '14', '食品饮料、保健食品', 52, 4816.00);
  290. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '8', '金沙河面条 原味银丝挂面 龙须面 方便速食拉面 清汤面 900g', '7', '金沙河', '803', '米面杂粮', '82', '粮油调味', '14', '食品饮料、保健食品', 40, 1118.00);
  291. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-17', '9', '索芙特i-Softto 口红不掉色唇膏保湿滋润 璀璨金钻哑光唇膏 ', '8', '索芙特', '477', '唇部', '54', '香水彩妆', '8', '个护化妆', 180, 50819.76);
  292. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '1', '小米10', '1', '三星', '61', '手机', '13', '手机通讯', '2', '手机', 75, 999840.00);
  293. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '10', 'CAREMiLLE珂曼奶油小方口红 雾面滋润保湿持久丝缎唇膏', '9', 'CAREMiLLE', '477', '唇部', '54', '香水彩妆', '8', '个护化妆', 194, 27410.64);
  294. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '11', '香奈儿（Chanel）女士香水5号香水 粉邂逅柔情淡香水EDT ', '11', '香奈儿', '473', '香水', '54', '香水彩妆', '8', '个护化妆', 51, 37520.00);
  295. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '12', '华为智慧屏 4K全面屏智能电视机', '3', '华为', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 54, 505065.00);
  296. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '2', 'Redmi 10X', '1', '三星', '61', '手机', '13', '手机通讯', '2', '手机', 113, 299439.00);
  297. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '3', 'Apple iPhone 12', '2', '苹果', '61', '手机', '13', '手机通讯', '2', '手机', 137, 2428867.29);
  298. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '4', 'HUAWEI P40', '3', '华为', '61', '手机', '13', '手机通讯', '2', '手机', 97, 858353.71);
  299. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '5', 'TCL巨幕私人影院电视 4K超高清 AI智慧屏  液晶平板电视机', '4', 'TCL', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 72, 1478239.00);
  300. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '6', '小米电视 内置小爱 智能网络液晶平板教育电视', '5', '小米', '86', '平板电视', '16', '大 家 电', '3', '家用电器', 50, 326894.00);
  301. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '7', '十月稻田 长粒香大米 东北大米 东北香米 5kg', '6', '长粒香', '803', '米面杂粮', '82', '粮油调味', '14', '食品饮料、保健食品', 52, 4816.00);
  302. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '8', '金沙河面条 原味银丝挂面 龙须面 方便速食拉面 清汤面 900g', '7', '金沙河', '803', '米面杂粮', '82', '粮油调味', '14', '食品饮料、保健食品', 40, 1118.00);
  303. INSERT INTO `ads_order_spu_stats` VALUES ('2020-06-18', '9', '索芙特i-Softto 口红不掉色唇膏保湿滋润 璀璨金钻哑光唇膏 ', '8', '索芙特', '477', '唇部', '54', '香水彩妆', '8', '个护化妆', 180, 50819.76);
  305. -- ----------------------------
  306. -- Table structure for ads_order_total
  307. -- ----------------------------
  308. DROP TABLE IF EXISTS `ads_order_total`;
  309. CREATE TABLE `ads_order_total`  (
  310.   `dt` date NOT NULL COMMENT '统计日期',
  311.   `order_count` bigint(255) NULL DEFAULT NULL COMMENT '订单数',
  312.   `order_amount` decimal(16, 2) NULL DEFAULT NULL COMMENT '订单金额',
  313.   `order_user_count` bigint(255) NULL DEFAULT NULL COMMENT '下单人数',
  314.   PRIMARY KEY (`dt`) USING BTREE
  315. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
  317. -- ----------------------------
  318. -- Records of ads_order_total
  319. -- ----------------------------
  320. INSERT INTO `ads_order_total` VALUES ('2020-06-14', 211, 6951956.90, 211);
  321. INSERT INTO `ads_order_total` VALUES ('2020-06-15', 263, 5265165.23, 259);
  322. INSERT INTO `ads_order_total` VALUES ('2020-06-16', 226, 7521463.46, 220);
  323. INSERT INTO `ads_order_total` VALUES ('2020-06-17', 237, 5625751.35, 223);
  324. INSERT INTO `ads_order_total` VALUES ('2020-06-18', 280, 8651362.82, 275);
  326. -- ----------------------------
  327. -- Table structure for ads_user_total
  328. -- ----------------------------
  329. DROP TABLE IF EXISTS `ads_user_total`;
  330. CREATE TABLE `ads_user_total`  (
  331.   `dt` date NOT NULL COMMENT '统计日期',
  332.   `new_user_count` bigint(20) NULL DEFAULT NULL COMMENT '新注册用户数',
  333.   `new_order_user_count` bigint(20) NULL DEFAULT NULL COMMENT '新增下单用户数',
  334.   `order_user_count` bigint(20) NULL DEFAULT NULL COMMENT '下单用户数',
  335.   `no_order_user_count` bigint(20) NULL DEFAULT NULL COMMENT '未下单用户数(具体指活跃用户中未下单用户)',
  336.   PRIMARY KEY (`dt`) USING BTREE
  337. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
  339. -- ----------------------------
  340. -- Records of ads_user_total
  341. -- ----------------------------
  342. INSERT INTO `ads_user_total` VALUES ('2020-06-14', 100, 73, 211, 343);
  343. INSERT INTO `ads_user_total` VALUES ('2020-06-15', 123, 65, 190, 231);
  344. INSERT INTO `ads_user_total` VALUES ('2020-06-16', 125, 55, 234, 421);
  345. INSERT INTO `ads_user_total` VALUES ('2020-06-17', 92, 84, 124, 425);
  346. INSERT INTO `ads_user_total` VALUES ('2020-06-18', 153, 120, 343, 357);
  348. SET FOREIGN_KEY_CHECKS = 1;

2. 对接数据源

2.1 MySQL

  1. 安装依赖

注意需要在superset环境下进行。

  1. conda install mysqlclient

说明：对接不同的数据源，需安装不同的依赖，以下地址为官网说明，
https://superset.apache.org/docs/databases/installing-database-drivers

过程：
image.png
image.png

  1. 重启 Superset ```sql

    使用之前配置的启停脚本

superset.sh restart ```

  1. 数据源配置

1）Database 配置
Step1：如下图，按顺序选择
image.png

Step2：如下图，点击添加数据库
image.png

Step3：点击填写 Database 及 SQL Alchemy URI
注：SQL Alchemy URI 编写规范：mysql://用户名:密码@主机名:端口号/数据库名称
此处填写(主机名根据自己情况填写)：
mysql://root:root@192.168.234.129:3306/gmall_report?charset=utf8

选择数据源类型：
image.png
填写数据源信息，点击”Connect”：
image.png
安装成功，点击”Finish”：
image.png
可以看到所配置数据源的信息：
image.png

2）Datasets配置

Step1：选择”Datasets”，点击添加
image.png
Step2：选择相应的数据源、数据库、数据表
image.png
操作保存成功：
image.png
列表中出现选中的数据表：
image.png

Step3：依照上步骤2，完成其余表的添加
image.png

3. 制作仪表盘

3.1 创建空白仪表盘

1）点击 Dashboards/+DASHBOARDS
image.png
2）命名并保存
image.png
保存成功：
image.png

3.2 创建图表

1）选择”Charts”，点击添加图表
image.png

2）选择数据源及图表类型，创建新图表
image.png

3）根据需要配置图表
image.png

4）配置完成，点击右侧的”Run Query”即可
image.png
生成图表：
image.png

5）命名该图表，并保存至仪表盘
image.png
点击保存后：
image.png
点击”Save”后，保存成功：
image.png

3.3 查看仪表盘

1）点击“Dashboards”→“数据可视化”
image.png
2）查看仪表盘
image.png

4. 制作地图

4.1 创建图表

image.png
选择合适的数据表、图表类型：
image.png

4.2 配置图表

image.png

4.3 执行查询

image.png
生成了图表：
image.png

4.4 保存图表

image.png
image.png
保存成功：
image.png

5. 制作柱状图

5.1 创建图表

image.png
image.png

5.2 配置图表

image.png

5.3 保存图表

image.png
image.png

6. 创建饼状图

6.1 创建图表

image.png

6.2 配置图表

image.png

6.3 保存图表

image.png
image.png

7. 调整布局

点击编辑：
image.png
使用行列组件预先布局：
image.png
拖动图表进入坑位：
image.png
点击保存：
image.png
最终效果：
image.png

8. 自动刷新

点击配置，选择”设置自动刷新间隔”：
image.png

选择合适的时间间隔，保存配置：
image.png