文档章节

Tableau多表关联之后统计遇到的问题

caohong
 caohong
发布于 2015/01/18 20:18
字数 793
阅读 4241
收藏 2

 


问题描述

员工列表

员工ID 姓名 部门 月薪
1 李彦宏 销售部1 3000
2 周鸿祎 销售部1 3500
3 雷军 销售部2 2000
4 贾跃亭 销售部2 3100
5 董明珠 销售部3 4200
6 冯仑 销售部3 4100
7 马云 销售部3 3900

员工业绩单:

订单号 员工ID 订单业绩
1 2 1000
2 2 1100
3 4 1500
4 4 1800
5 4 2000
6 7 1500
7 7 1400
8 7 1600

其中两个表是通过员工ID来进行关联的,现在需要统计:

  1. 各个部门的总月薪
  2. 各个部门的总订单数
  3. 各个部门总月薪数的产出:订单总额/月薪

通过Tableau关联

 

发现统计的总月薪数不对:

 

 

问题分析

我们看到表关联之后两个表之间是对应1对多的关系,关联之后,出现了笛卡尔积:

mysql>  SELECT employees.employee_id, employees.name, employees.salary, employees.employee_id, orders.order_id FROM employees LEFT JOIN orders ON employees.employee_id=orders.employee_id;
+-------------+-----------+--------+-------------+----------+
| employee_id | name      | salary | employee_id | order_id |
+-------------+-----------+--------+-------------+----------+
| 2           | 周鸿祎    |   3500 | 2           | 1        |
| 2           | 周鸿祎    |   3500 | 2           | 2        |
| 4           | 贾跃亭    |   3100 | 4           | 3        |
| 4           | 贾跃亭    |   3100 | 4           | 4        |
| 4           | 贾跃亭    |   3100 | 4           | 5        |
| 7           | 马云      |   3900 | 7           | 6        |
| 7           | 马云      |   3900 | 7           | 7        |
| 7           | 马云      |   3900 | 7           | 8        |
| 1           | 李彦宏    |   3000 | 1           | NULL     |
| 3           | 雷军      |   2000 | 3           | NULL     |
| 5           | 董明珠    |   4200 | 5           | NULL     |
| 6           | 冯仑      |   4100 | 6           | NULL     |
+-------------+-----------+--------+-------------+----------+
12 rows in set (0.00 sec)

因此在Tableau做聚合统计的时候,sum(salary)直接用了"LEFT JOIN"之后的表,其查询语句如下:

mysql> SELECT employees.employee_id, employees.name, sum(employees.salary), count(employees.employee_id) FROM employees LEFT JOIN orders ON employees.employee_id=orders.employee_id GROUP BY employees.employee_id;
+-------------+-----------+-----------------------+------------------------------+
| employee_id | name      | sum(employees.salary) | count(employees.employee_id) |
+-------------+-----------+-----------------------+------------------------------+
| 1           | 李彦宏    |                  3000 |                            1 |
| 2           | 周鸿祎    |                  7000 |                            2 |
| 3           | 雷军      |                  2000 |                            1 |
| 4           | 贾跃亭    |                  9300 |                            3 |
| 5           | 董明珠    |                  4200 |                            1 |
| 6           | 冯仑      |                  4100 |                            1 |
| 7           | 马云      |                 11700 |                            3 |
+-------------+-----------+-----------------------+------------------------------+

解决方案

为避免出现JOIN之后出现salary重复,不妨先在orders表里先算出,我们来回顾一下需求:

  1. 各个部门的总月薪
  2. 各个部门的总订单数
  3. 各个部门总月薪数的产出:订单总额/月薪

订单数和订单总额都可以在orders单表里查询出:

mysql> SELECT orders.employee_id, count(orders.order_id) AS order_count, sum(orders.amount) as order_amount FROM orders GROUP BY orders.employee_id;
	+-------------+-------------+--------------+
	| employee_id | order_count | order_amount |
	+-------------+-------------+--------------+
	| 2           |           2 |         2100 |
	| 4           |           3 |         5300 |
	| 7           |           3 |         4500 |
	+-------------+-------------+--------------+

这个表可以在Tableau创建数据连接的时候,自定义SQL:

然后在做LEFT JOIN:

统计出来的月薪正确了,如图:

 

Tableau实际上就将查询结果建立一个临时表之后在LEFT JOIN:

mysql> SELECT employees.employee_id, employees.department, employees.name, employees.salary, order_total_counts.order_count, order_total_counts.order_amount from employees  LEFT JOIN  (SELECT orders.employee_id, count(orders.order_id) AS order_count, sum(orders.amount) AS order_amount FROM orders GROUP BY orders.employee_id) order_total_counts ON employees.employee_id=order_total_counts.employee_id;
+-------------+------------+-----------+--------+-------------+--------------+
| employee_id | department | name      | salary | order_count | order_amount |
+-------------+------------+-----------+--------+-------------+--------------+
| 1           | 销售部1    | 李彦宏    |   3000 |        NULL |         NULL |
| 2           | 销售部1    | 周鸿祎    |   3500 |           2 |         2100 |
| 3           | 销售部2    | 雷军      |   2000 |        NULL |         NULL |
| 4           | 销售部2    | 贾跃亭    |   3100 |           3 |         5300 |
| 5           | 销售部3    | 董明珠    |   4200 |        NULL |         NULL |
| 6           | 销售部3    | 冯仑      |   4100 |        NULL |         NULL |
| 7           | 销售部3    | 马云      |   3900 |           3 |         4500 |
+-------------+------------+-----------+--------+-------------+--------------+

这样就可以完成按照部门来统计了:

 


 

© 著作权归作者所有

caohong
粉丝 0
博文 21
码字总数 5649
作品 0
昌平
私信 提问
数据库分库分表之后如何做多表关联查询以及统计

对于分布式架构,数据库的分库分表是缓解数据库服务器压力和增加并发量的途径之一。 但是随着数据库的分库分表之后,也不可避免的带来了一些问题,我现在所想到的问题,也是很显而易见的问题...

王大叔爱编程
2016/01/14
6.8K
8
【原创】python词云分析陈粒和赵雷

未经同意禁止转载,否则通过维权骑士追究 【完整源代码请点击 此处 留言以获取,可以顺便给颗Star😄】 记录一个练习小项目,训练一下python分析技能。用到的知识有“爬虫”、“jieba分词”...

_SoLo_
2018/06/30
0
0
如何使用tableau连接mongodb数据库?

今天遇到了一个坑,我想把mongodb数据库中的数据导入到tableau中,好利用tableau来学习数据分析,结果我一直没连上…. 摸索了一番,过程如下: 首先在tableau官网上下载tableau desktop: 下...

爱吃西瓜的番茄酱
2018/06/23
0
0
玩转processlist,高效追溯MySQL活跃连接数飙升根因

作者介绍 在运维MySQL时,经常遇到的一个问题就是活跃连接数飙升。一旦遇到这样的问题,都根据后台保存的processlist信息,或者连上MySQL环境,分析MySQL的连接情况。处理类似的故障多了,就...

刘世勇
2017/07/26
0
0
MySQL · 性能优化· CloudDBA SQL优化建议之统计信息获取

阿里云CloudDBA具有SQL优化建议功能,包括SQL重写建议和索引建议。SQL索引建议是帮助数据库优化器创造最佳执行路径,需要遵循数据库优化器的一系列规则来实现。CloudDBA需要首先计算表统计信...

阿里云RDS-数据库内核组
2017/10/03
0
0

没有更多内容

加载失败,请刷新页面

加载更多

day13static-final

静态变量的特点:只加载一次,不会随着对象的创建而增加

architect刘源源
45分钟前
1
0
分而治之-快速排序

快速排序的思想: 快速排序首先在数组中确定1个枢纽项(比如数组中的第一个元素),将大于该枢纽项的元素放到右侧,小于该枢纽项的元素放到左侧,这样枢纽项将数组划分成两部分。接着继续对划...

万山红遍
今天
4
0
Qt编写自定义控件9-导航按钮控件

前言 导航按钮控件,主要用于各种漂亮精美的导航条,我们经常在web中看到导航条都非常精美,都是html+css+js实现的,还自带动画过度效果,Qt提供的qss其实也是无敌的,支持基本上所有的CSS2属...

飞扬青云
今天
3
0
Python开发工具:pyJasper

原文:https://www.oschina.net/p/pyjasper 前言 pyJasper是 JasperReports 网络服务器的 Python 客户端。 pyJasper 是一组 Python 基础工具,可以用来处理 JasperReports 报表 。因为 Jasper...

A_裙232550246
今天
2
0
如何提高使用Java反射的效率?

前言 在我们平时的工作或者面试中,都会经常遇到“反射”这个知识点,通过“反射”我们可以动态的获取到对象的信息以及灵活的调用对象方法等,但是在使用的同时又伴随着另一种声音的出现,那...

花漾年华
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部