2021-11-07大数据学习日志——MySQL进阶——报表项目

01_数据表介绍

学习目标

• 了解项目使用的数据表结构及表关系

课程使用微软的 Northwind 数据集, 零售业务，包含了客户，供应商和订单数据。原始数据集可以在 微软GitHub 仓库下载。为了满足课程需求，数据库数据在原始数据基础上做了微调。

基于此份数据，我们将通过 SQL 来创建数据报表，满足业务需求。

1.1 数据表整体概览

1.2 员工表(employees)

保存员工基本信息，包含如下字段：

• employee_id：员工唯一ID
• first_name：名
• last_name：姓
• title：职务
• ...
• reports_to：员工直属领导的员工ID (也在这张表中保存)

1.3 客户表(customers)

保存客户的信息，包含如下字段：

• customer_id：客户唯一ID， 客户的ID是公司全名的缩写，用5个字母表示
• company_name：公司名称
• contact_name：客户公司的联系人
• contact_title：客户公司联系人的职务
• 除此之外还保存了顾客的地址信息和联系方式city、region、postal_code、country、fax

1.4 商品表(products)和商品类别表(categories)

1）商品表中保存了在 Northwind 商店中出售的商品信息，包含如下字段：

• product_id：商品唯一ID
• product_name：商品名称
• supplier_id：供应商ID
• category_id：商品类别ID
• uite_price：商品单价
• discontinued：商品是否缺货：false (有货)、true (缺货)

2）商品类别表 保存了所有商品的类别信息，包含如下字段：

• id：商品类别ID
• category_name：商品类别名称
• description：商品类别简单描述信息

1.5 供应商表(suppliers)

供应商表保存了商品供应商的信息，包含字段如下：

• supplier_id：供应商唯一ID

• company_name：供应商公司名称

• 表中还记录了供应商的地址信息 address、city、region、postal_code、country

1.6 订单表(orders)和订单明细表(order_items)

1）订单表中保存一个订单的基本信息，包含以下字段：

• order_id：订单ID
• customer_id：客户ID
• employee_id：销售员工ID
• order_date：下单日期
• shipped_date：配送日期
• ship_via：运输方式
• freight ：运费
• ship_address：收货地址
• ship_city ：收货城市
• ship_region：收货地区
• ship_postal_code：收货地址邮编
• ship_country：收货国家

2）订单明细表保存了订单中的具体商品信息，包含如下字段：

• order_id：订单ID
• product_id：商品ID
• unit_price：商品单价
• quantity：购买数量
• discount：折扣

02_SQL 数据汇总操作

学习目标

• 掌握 GROUP BY 分组和 COUNT、SUM 等聚合函数的使用

上一小节中，我们介绍了数据库中的表关系，了解了基本数据情况，在这一小节中，我们将通过SQL来创建简单数据报表

将一个或者多个业务对象的详细信息汇总到一张表中是一种比较常见的报表形式，我们需要的信息可能分散在多张表中，在写 SQL 时可以通过一个或者多个 JOIN 子句将信息进行汇总

2.1 详细报告

练习1

需求：查询运输到法国的订单信息，返回如下结果

查询结果字段：

• customer_company_name(客户公司名称)、employee_first_name和employee_last_name(销售员工姓名)、order_date(下单日期)、shipped_date(发货日期)、ship_country(收货国家)

SELECT
  c.company_name AS `customer_company_name`, 
  e.first_name AS `employee_first_name`, 
  e.last_name AS `employee_last_name`,
  o.order_date,
  o.shipped_date,
  o.ship_country
FROM orders o
JOIN employees e
ON o.employee_id = e.employee_id
JOIN customers c
ON o.customer_id = c.customer_id
WHERE o.ship_country = 'France';

注意在写 SQL 时，我们可以为每一张表都起了一个别名，可以减少输入的字符数

查询结果

练习2

需求：查询订单编号为10250的订单详情，按商品名称排序，返回如下结果

查询结果字段：

• product_name(商品名称)、quantity(购买数量)、unit_price(购买单价)、discount(折扣)、order_date(下单日期)

SELECT
  product_name,
  quantity,
  oi.unit_price,
  discount,
  order_date
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
JOIN products p
ON oi.product_id = p.product_id
WHERE o.order_id = 10250
ORDER BY product_name;

查询结果

2.2 带时间限制的报表

另一种常见的报表需求是查询某段时间内的业务指标

练习3

需求：统计2016年7月的订单数量

查询结果字段：

• order_count(2016年7月的订单数量)

SELECT
  COUNT(*) As `order_count`
FROM orders
WHERE order_date >= '2016-07-01' AND  order_date <= '2016-07-31';

查询结果

2.3 计算多个对象

在业务报表中，我们通常希望同时计算多个业务对象的某些指标

练习4

需求：统计订单号在10200-10260之间的订单中的总商品件数

查询结果字段：

• order_id(订单ID)、order_items_count(订单中的总商品件数)

SELECT
  order_id,
  COUNT(*) AS `order_items_count`
FROM order_items
WHERE order_id BETWEEN 10200 AND 10260
GROUP BY order_id;

查询结果

2.4 订单金额计算

在销售报表中，我们经常需要计算订单的总付款额

练习5

需求：统计ID为10250的订单的总价（折扣前）

查询结果字段：

• order_id(订单ID)、total_price(订单总价-折扣前)

SELECT
  order_id,
  SUM(unit_price * quantity) AS `total_price`
FROM order_items
WHERE order_id = 10250;

查询结果

练习6

需求：统计运输到法国的每个订单的总金额

查询结果字段：

• order_id(订单ID)、company_name(客户公司名称)、total_price(每个订单的总金额)

SELECT
  o.order_id,
  c.company_name AS `customer_company_name`, 
  SUM(unit_price * quantity) AS `total_price`
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
JOIN customers c
ON o.customer_id = c.customer_id
WHERE o.ship_country = 'France'
GROUP BY o.order_id, c.company_name;

• 注意：通过GROUP BY我们只需要对 order_id 进行分组就可以了，但MySQL 5.7之后要求，在使用GROUP BY分组时，SELECT 后的字段，如果没有在聚合函数中使用，就必须在GROUP BY 后出现

查询结果

2.5 GROUP BY 分组操作

在业务报表中，我们经常也需要分组进行数据的汇总

注意1：使用GROUP BY分组聚合统计时，需要考虑分组字段中的相同值的业务含义是否相同

练习7

需求：统计每个员工销售的订单数量

查询结果字段：

• first_name和last_name(员工姓和名)、orders_count(员工销售订单数)

1）首先思考下面的 SQL 语句是否正确

SELECT
  e.first_name,
  e.last_name,
  COUNT(*) AS `orders_count`
FROM orders o
JOIN employees e
ON o.employee_id = e.employee_id
GROUP BY e.first_name, e.last_name;

上面的SQL貌似正确，但是没有考虑到员工重名的问题

2）正确的写法

SELECT
  e.employee_id,
  e.first_name,
  e.last_name,
  COUNT(*) AS `orders_count`
FROM orders o
JOIN employees e
ON o.employee_id = e.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name;

• 在SELECT 和 GROUP BY 中添加了 员工ID employee_id字段后，重名的问题就可以解决了

查询结果：

注意2：GROUP BY之后的分组字段不是必须在 SELECT 中出现

练习8

需求：统计2016年6月到2016年7月每个客户的总下单金额，并按金额从高到低排序

提示：

• 计算实际总付款金额： SUM(unit_price * quantity * (1 - discount))

查询结果字段：

• company_name(客户公司名称)、total_paid(客户总下单金额-折扣后)

SELECT
  c.company_name, 
  SUM(unit_price * quantity * (1 - discount)) AS `total_paid`
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
JOIN customers c
ON o.customer_id = c.customer_id
WHERE order_date >= '2016-06-01' AND order_date <= '2016-07-31'
GROUP BY c.customer_id, c.company_name
ORDER BY total_paid DESC;

查询结果

2.6 COUNT()计数统计注意点

注意点1：COUNT(*) 和 COUNT(列名)之间的区别

• COUNT(*)：进行计数，包括NULL
• COUNT(列名)：对指定列的非NULL数据进行计数

练习9

需求：统计要发货到不同国家的订单数量以及已经发货的订单数量

提示：

• shipped_date为NULL，表示还未发货

查询结果字段：

• ship_country(国家)、all_orders(总订单数)、shipped_orders(已发货订单数)

SELECT
  ship_country,
  COUNT(*) AS `all_orders