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

最新推荐文章于 2024-12-30 23:25:26 发布

原创最新推荐文章于 2024-12-30 23:25:26 发布 · 6.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #大数据 #数据库

python+大数据专栏收录该内容

50 篇文章

订阅专栏

本文介绍如何运用SQL进行报表制作，涵盖多表查询、CASE WHEN、GROUP BY等高级技巧，以及使用WITH子句提升复杂报表的可读性和维护性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;

-- 统计每个员工处理的订单总数
-- 结果包含员工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;

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;

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;

统计不同的对象数量

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

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;

在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;

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;

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;

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';

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;

第二步：接下来我们可以将上面的查询转换为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;

需求：创建报表，统计华盛顿地区（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;

多级聚合与 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;

三层聚合

需求：计算每位员工的平均订单价值（折后），找到最小平均值（`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;

小结

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;

自定义指标

需求：统计每个订单的全价商品和打折商品数量（同一个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;

统计总量并计算占比

需求：创建报表，统计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`表中的所有行组合在一起

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;

需求：统计法国客户和其他国家/地区的客户消费的总金额（折扣后）
结果显示两列：`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)]