Python+大数据-SQL进阶-报表项目

于 2022-10-09 16:07:14 发布
阅读量5.9k 收藏
点赞数
分类专栏： python+大数据 文章标签： sql 大数据 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45588318/article/details/127228609
版权

Python+大数据-SQL进阶-报表项目

多表查询的步骤：
1.根据需求了解到底需要关联那些数据表
2.看具体的查询条件，到底where 或者having或者group by 那个字段
3.最后写select查询字段列表

1. 使用SQL进行数据汇总

-- 需求：提供订单编号为10248的相关信息，包括product_name, unit_price (在 `order_items` 表中),  quantity（数量）, company_name（供应商公司名字 ，起别名 `supplier_name`)

SELECT
  product_name,
  oi.unit_price,
  oi.quantity,
  company_name AS supplier_name	
FROM order_items oi
JOIN products p 
  ON oi.product_id = p.product_id
JOIN suppliers s
  ON s.supplier_id = p.supplier_id
WHERE oi.order_id = 10248;

image-20221005104837377

-- 统计每个员工处理的订单总数
-- 结果包含员工ID`employee_id`，姓名`first_name` 和 `last_name`，处理的订单总数(别名 `orders_count`)
SELECT
  e.employee_id,
  e.first_name,
  e.last_name,
  COUNT(*) AS orders_count
FROM orders o
JOIN employees e
  ON e.employee_id = o.employee_id
GROUP BY e.employee_id,
  e.first_name,
  e.last_name;

image-20221005105349569

  • group by
-- 需求：计算每个客户的下订单数
-- 结果包含：用户id、用户公司名称、订单数量（`customer_id`, `company_name`,  `orders_count` ）
SELECT
  c.customer_id,
  c.company_name,
  COUNT(*) AS orders_count
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id,
  c.company_name;

image-20221005105512046

  • count
需求：查找每个供应商提供的产品总数
显示`company_name` 和 `products_count`（提供的产品数量）列
包括没有提供任何产品的供应商

SELECT 
  s.company_name,
  COUNT(p.product_id) AS products_count
FROM suppliers s
LEFT JOIN products p
  ON p.supplier_id = s.supplier_id
GROUP BY
  s.supplier_id,
  s.company_name;

image-20221005110408445

    • 统计不同的对象数量
    假设我们要查找每个订单中的商品数量，但是同一商品可能在一个订单中出现多次。 在这种情况下，我们需要每个订单中唯一商品的数量。 以下查询将为我们提供总计数和唯一项目数：
SELECT 
  order_id, 
  COUNT(product_id) AS products_count, 
  COUNT(DISTINCT product_id) AS unique_products_count
FROM order_items
GROUP BY order_id;

image-20221005110616064

2. 使用CASE WHEN 和 GROUP BY将数据分组

  • CASE WHEN中ELSE的引入
我们的商店要针对北美地区的用户做促销活动：任何运送到北美地区（美国，加拿大) 的包裹免运费。 
创建报表，查询订单编号为10720~10730 活动后的运费价格

SELECT 
  order_id,
  customer_id,
  ship_country,
  CASE
    WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN 0.0
    ELSE 10.0
  END AS shipping_cost
FROM orders
WHERE order_id BETWEEN 10720 AND 10730;

image-20221005110827949

  • 在GROUP BY中使用CASE WHEN
需求：创建一个简单的报表来统计员工的年龄情况
报表中包含如下字段：
年龄（ `age` ）：生日大于1980年1月1日 `young` ，其余`old`
员工数量 （ `employee_count`）
SELECT
  CASE
    WHEN birth_date > '1980-01-01' THEN 'young'
    ELSE 'old'
  END AS age,
  COUNT(*) AS employee_count
FROM employees
GROUP BY
  CASE
    WHEN birth_date > '1980-01-01' THEN 'young'
    ELSE 'old'
  END;

image-20221005111002205

  • CASE WHEN 和 COUNT
需求：Washington (WA) 是 Northwind的主要运营地区，统计有多少订单是由华盛顿地区的员工处理的，多少订单是有其它地区的员工处理的
结果字段： `orders_wa_employees` 和 `orders_not_wa_employees`

SELECT 
  COUNT(CASE
    WHEN region = 'WA' THEN order_id
  END) AS orders_wa_employees,
  COUNT(CASE
    WHEN region != 'WA' THEN order_id
  END) AS orders_not_wa_employees
FROM employees e
JOIN orders o
  ON e.employee_id = o.employee_id;

image-20221005111056883

  • GROUP BY 和 CASE WHEN组合使用
需求：创建报表，统计不同类别产品的库存量，将库存量分成两类 >30 和 <=30 两档分别统计数量
报表包含三个字段：
类别名称  `category_name`
库存充足  `high_availability` 
库存紧张 `low_availability`

SELECT 
  c.category_name,
  COUNT(CASE
    WHEN units_in_stock > 30 THEN product_id
  END) AS high_availability,
  COUNT(CASE
    WHEN units_in_stock <= 30 THEN product_id
  END) AS low_availability
FROM products p
JOIN categories c
  ON p.category_id = c.category_id
GROUP BY c.category_id,
  c.category_name;

image-20221005111208283

  • SUM中使用CASE WHEN
需求：创建报表统计运输到法国的的订单中，打折和未打折订单的总数量
结果包含两个字段：`full_price` （原价）和 `discounted_price`（打折）
SELECT
  SUM(CASE
    WHEN discount = 0 THEN 1
  END) AS full_price,
  SUM(CASE
    WHEN discount != 0 THEN 1
  END) AS discounted_price
FROM orders o
JOIN order_items oi
  ON o.order_id = oi.order_id
WHERE ship_country = 'France';

image-20221005111315191

3. 使用 WITH (Common Table Expressions) 公用表达式

  • WITH (Common Table Expressions) 公用表达式介绍
WITH some_name AS (
  -- your CTE
)
SELECT
  ... 
FROM some_name


① 需要给CTE起一个名字（上面的例子中使用了`some_name`)，具体的查询语句写在括号中
② 在括号后面，就可以通过`SELECT` 将CTE的结果当作一张表来使用
③ 将CTE称为“内部查询”，其后的部分称为“外部查询”
④ 需要先定义CTE，即在外部查询的`SELECT`之前定义CTE
  • CTE 二步走
接下来我们分步来实现一个CTE查询，需求是查询每个订单的平均商品数量：
第一步：
① 先创建一个简单查询，用来统计每个订单的商品总数量
② 结果有两列：`order_id` 和 `item_count

SELECT
  order_id,
  SUM(quantity) AS item_count
FROM order_items
GROUP BY order_id;

image-20221005111515206

第二步：接下来我们可以将上面的查询转换为CTE，并进一步计算所有订单的平均商品数量
WITH order_items_counts AS (
  SELECT
    order_id,
    SUM(quantity) AS item_count
  FROM order_items
  GROUP BY order_id
)
SELECT
  AVG(item_count) AS avg_item_count 
FROM order_items_counts;

image-20221005111558597

需求：创建报表，统计华盛顿地区（WA）每位员工所处理订单的订单平均价格
报表中包含如下列：
`employee_id`, `first_name`, `last_name`, 和 `avg_total_price`
员工ID，名字，姓氏，平均订单总价
提示：
通过CTE计算所有订单的总价格，并将处理该订单的员工ID一起返回
在外部查询中，将CTE与`employees`表JOIN起来，计算平均值，显示所有所需信息，并按地区过滤结果

WITH order_total_prices AS (
  SELECT
    o.order_id,
    o.employee_id,
    SUM(unit_price * quantity) AS total_price
  FROM orders o
  JOIN order_items oi
    ON o.order_id = oi.order_id
  GROUP BY o.order_id,
    o.employee_id
)
SELECT
  e.employee_id,
  e.first_name,
  e.last_name,
  AVG(total_price) AS avg_total_price
FROM order_total_prices otp
JOIN employees e
  ON otp.employee_id = e.employee_id
WHERE e.region = 'WA'
GROUP BY e.employee_id,
  e.first_name,
  e.last_name;

image-20221005111706392

  • 多级聚合 与 CASE WHEN 组合使用
需求：创建报表统计高价值和低价值客户的数量
客户在折扣前支付的所有订单的总价大于 $20,000 ，则将该客户视为“高价值”
否则，将它们视为“低值”
报表中包含两个字段
category ( 'high-value' 或 'low-value')， `customer_count`
类别（“高价值”或“低价值”）和 “用户数量

WITH customer_order_values AS (
  SELECT
    customer_id, 
    CASE
      WHEN SUM(quantity * unit_price) > 20000
        THEN 'high-value' 
      ELSE 'low-value'
    END AS category
  FROM orders o
  JOIN order_items oi
    ON o.order_id = oi.order_id
  GROUP BY customer_id
) 
SELECT 
  category,
  COUNT(customer_id) AS customer_count
FROM customer_order_values
GROUP BY category;

image-20221005111816143

  • 三层聚合
需求：计算每位员工的平均订单价值（折后），找到最小平均值（`minimal_average`列）和最大平均值（`maximal_average`列）
提示：使用两个CTE
第一个CTE中，计算每位员工的折扣后的订单总价
第二个CTE中，计算每个员工折扣后的平均订单价值
最后，在外部查询中，使用`MIN（）`和`MAX（）`找到最小平均值（`minimal_average`列）和最大平均值（`maximal_average`列）

WITH order_values AS (
  SELECT
    employee_id,
    SUM(unit_price * quantity * (1 - discount)) AS total_discount_price
  FROM orders o
  JOIN order_items oi
    ON o.order_id = oi.order_id
  GROUP BY o.order_id, employee_id
),
customer_averages AS (
  SELECT
    employee_id,
    AVG(total_discount_price) AS avg_discount_total_price
  FROM order_values
  GROUP BY employee_id
)
SELECT
  MIN(avg_discount_total_price) AS minimal_average,
  MAX(avg_discount_total_price) AS maximal_average
FROM customer_averages;

image-20221005111919149

  • 小结

    with子句必须在引用的select语句 之前定义 ,同级with关键字 只能使用一次，多个只能 用逗号分割；最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割, with 子句的查询必须用括号括起来。

如果定义了with子句，但其后没有跟select查询，则会报错！

前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句！

4. 多个指标

  • 计算一个业务对象的多个指标
需求：创建报表统计每个客户的订单数量以及他们的消费金额（折扣后）
报表显示三列：
客户的ID（`customer_id`）
订单数（如`order_count`）
折扣后所有订单支付的总价（` total_revenue_after_discount`）
SELECT
  o.customer_id,
  COUNT(DISTINCT o.order_id) AS order_count,
  SUM(unit_price * quantity * (1 - discount)) AS total_revenue_after_discount
FROM orders o
JOIN order_items oi
  ON o.order_id = oi.order_id
GROUP BY o.customer_id;

image-20221005112158025

  • 自定义指标
需求：统计每个订单的全价商品和打折商品数量（同一个ID的商品购买多件只计一次）
结果显示三列：`order_id`，订单ID，`full_price_product_count` 未打折的订单项数，`discount_product_count` 已打折的订单项数
SELECT
  o.order_id,
  COUNT(CASE WHEN discount = 0 THEN product_id END) AS full_price_product_count,
  COUNT(CASE WHEN discount > 0 THEN product_id END) AS discount_product_count
FROM orders o
JOIN order_items oi
  ON o.order_id = oi.order_id
GROUP BY o.order_id;

image-20221005112339481

  • 统计总量并计算占比
需求：创建报表，统计2016年7月下订单的客户以及每个客户的消费金额占2016年7月总销售金额的占比
WITH total_sales AS (
  SELECT 
    SUM(quantity * unit_price) AS july_sales 
  FROM order_items oi
  JOIN orders o
    ON o.order_id = oi.order_id
  WHERE order_date >= '2016-07-01' AND order_date < '2016-08-01'
)
SELECT 
  c.customer_id, 
  SUM(quantity * unit_price) AS revenue, 
  ROUND(SUM(quantity * unit_price) / total_sales.july_sales * 100, 2) AS revenue_percentage
FROM total_sales, customers c
JOIN orders o
  ON c.customer_id = o.customer_id 
JOIN order_items oi
  ON oi.order_id = o.order_id
WHERE order_date >= '2016-07-01' AND order_date < '2016-08-01'
GROUP BY c.customer_id, total_sales.july_sales;


解析：
在CTE中，我们仅计算2016年7月的总销售收入
在外部查询中，我们查询了2016年7月有消费的客户的`customer_id`与该客户在2016年7月的消费金额
最后一列中，我们将客户的消费金额（来自上一列）/ CTE中计算的2016年7月的总销售收入计算出每个客户贡献的销售收入占比
注意：
1、我们必须在`GROUP BY`子句中添加`july_sales`列，因为它没有与任何聚合函数一起使用。
2、我们通过以下方式连接“ total_sales”和“ customers”表：
FROM total_sales, customers c
将`total_sales`（CTE的计算结果）与`customers`表中的所有行组合在一起

image-20221005112510847

5. 分组对比

  • 按行比较
需求：统计不同库存水平的产品数量（`product_id`）计数
结果显示两列：`availability` 和 `product_count`
`Low`  库存（`units_in_stock`）<= 10，`Average`  库存（`units_in_stock`）10~30，`High`  库存（`units_in_stock`）>30

WITH products_by_group AS (
  SELECT
    product_id,
    CASE
      WHEN units_in_stock > 30 THEN 'High' 
      WHEN units_in_stock > 10 THEN 'Average'
      ELSE 'Low'
    END AS availability
  FROM products)
SELECT
  availability,
  COUNT(product_id) AS product_count
FROM products_by_group
GROUP BY availability;

image-20221005112721599

需求：统计法国客户和其他国家/地区的客户消费的总金额（折扣后）
结果显示两列：`customer_country`（“ France”或“其他”）和 `discount_revenue`（四舍五入到小数点后两位）
WITH orders_by_group AS (
  SELECT
    o.order_id,
  CASE
    WHEN country = 'France' THEN 'France'
    ELSE 'Other'
  END AS customer_country
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
)
SELECT
  customer_country,
  ROUND(SUM(quantity * unit_price * (1 - discount)), 2) AS discount_revenue
FROM orders_by_group obg
JOIN order_items oi
  ON obg.order_id = oi.order_id
GROUP BY customer_country;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wImkjyzY-1665302799426)(C:\Users\liuyikang\AppData\Roaming\Typora\typora-user-images\image-20221005112815778.png)]

  • 按列比较
需求：统计素食和非素食的商品数量
结果包含两列：`non_vegetarian_count` 和 `vegetarian_count`
提示：非素食产品类别 `category_id` 的值是6或8
SELECT
  COUNT(CASE
    WHEN category_id IN (6, 8)
      THEN product_id
  END) AS non_vegetarian_count,
  COUNT(CASE
    WHEN category_id NOT IN (6, 8)
      THEN product_id
  END) AS vegetarian_count
FROM products;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7x9Bvsmd-1665302799427)(C:\Users\liuyikang\AppData\Roaming\Typora\typora-user-images\image-20221005112927830.png)]

呆猛的呆呆小哥
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值