【SQL边干边学系列】08高级问题-4

文章目录

前言
回顾
高级问题
- 48.客户分组
- 49.客户分组-修复null
- 50.使用百分比的客户分组
- 51.灵活的客户分组
答案
- 48.客户分组
- 49.客户分组-修复null
- 50.使用百分比的客户分组
- 51.灵活的客户分组
未完待续

前言

在这里插入图片描述
该系列教程，将会从实际问题出发，边干边学，逐步深入讲解SQL的各方面知识。

你需要完成所有的问题吗？绝对不是。介绍性的问题相当简单，所以你可以直接跳过到“中级问题”部分。如果你不是初学者，但不确定应该从哪里开始，请在“入门问题”部分看看问题和预期结果，并确保你理解这些概念。如果已经理解了这些概念，请开始阅读“中级问题”部分。

你想从这本书中复制代码并在你的服务器上运行？我建议你手动输入，而不是复制粘贴。为什么要去麻烦地重新打字呢？科学表明，打字的行为会在你的脑中留下更深刻的印象。当你只是复制和粘贴时，代码只是直接从你电脑里的一个窗口转到另一个窗口，而不会给你留下多少印象。但是当你把它打出来时，你必须集中精力，这非常有助于保留信息。

一旦你完成了所有的问题，将拥有一些在数据分析和高级Select语句使用方面非常有用的技能。当然，这并不是SQL的全部内容。还有修改数据（更新、插入、删除）、DDL（数据定义语言，即如何创建和修改数据库对象）、编程（如存储过程）和许多其他主题。

该系列教程中，只涉及到了使用Select语句检索数据的问题，这几乎是所有其他数据库主题的基础开端。

回顾

上篇文章👉《【SQL边干边学系列】07高级问题-3》讲了部分高级问题，这篇接着讨论更多的高级问题。

高级问题

48.客户分组

假如想为现有客户做一个销售活动。希望想根据客户在2016年的订购量，将他们分组。然后，根据客户所在的群组，采用不同的销售材料。

客户分组类别为0到1000、1000到5000、5000到10000个，以及超过10000个。

这个查询的一个很好的起点是来自“高价值客户-总订单”这个问题的答案。我们不想展示2016年没有任何订单的客户。

按CustomerID对结果排序。

-- 预期结果
CustomerID CompanyName                        TotalOrderAmount CustomerGroup
---------- ---------------------------------- ---------------- -------------
ALFKI      Alfreds Futterkiste                2302.20          Medium
ANATR      Ana Trujillo Emparedados y helados 514.40           Low
ANTON      Antonio Moreno Taquería            660.00           Low
...
WHITC White Clover Markets 15278.90 Very High
WILMK Wilman Kala 1987.00 Medium
WOLZA Wolski Zajazd 1865.10 Medium
(81 row(s) affected)

提示如下

这是来自“高价值客户-总订单”问题的SQL，但没有针对订单总数超过10,000个的过滤器。

Select
 Customers.CustomerID
 ,Customers.CompanyName
 ,TotalOrderAmount = SUM(Quantity * UnitPrice)
From Customers
 Join Orders
 on Orders.CustomerID = Customers.CustomerID
 Join OrderDetails
 on Orders.OrderID = OrderDetails.OrderID
Where
  OrderDate >= '20160101'
 and OrderDate < '20170101'
Group By
 Customers.CustomerID
 ,Customers.CompanyName
Order By TotalOrderAmount Desc;

你可以在CTE（common table expression）中使用上面的SQL，然后在“TotalOrderAmount”上使用Case语句。

49.客户分组-修复null

上一个问题的答案有一个错误。CustomerGroup中有一行的值为null。

修复SQL，使CustomerGroup字段中没有null。

-- 预期结果
CustomerID CompanyName                     TotalOrderAmount      CustomerGroup
---------- ------------------------------- --------------------- -------------
LILAS      LILA-Supermercado               5994.06               High
LINOD      LINO-Delicateses                10085.60              Very High
LONEP      Lonesome Pine Restaurant        1709.40               Medium
...

提示如下

CustomerID为MAISD的总订单量是多少？这与我们的CustomerGroup边界有何关系？

使用“between”很适合整数值。然而，我们正在分析的字段是Money，它有小数位。因此不能使用下面的SQL：

when TotalOrderAmount between 0 and 1000 then 'Low'

而应该使用下面的SQL：

when TotalOrderAmount >= 0 and TotalOrderAmount < 1000 then 'Low'

50.使用百分比的客户分组

根据上面的查询，显示所有已定义的客户组，以及每个组中的百分比。按每一组的总数排序，按降序排序。

-- 预期结果
CustomerGroup TotalInGroup PercentageInGroup
------------- ------------ ---------------------------------------
Medium        35           0.432098765432
Low           20           0.246913580246
High          13           0.160493827160
Very High     13           0.160493827160
(4 row(s) affected)

提示如下

作为起点，你可以使用问题“客户分组-修复null”中的答案。

我们不再需要在最终的输出中显示CustomerID和CompanyName。但是，我们需要计算每个CustomerGrouping组中有多少客户。你可以创建另一个CTE级别，以便获得最终输出的每个CustomerGrouping中的计数。

51.灵活的客户分组

我们希望根据客户订购的金额来完全灵活地分组。我们不想为了更改客户组的边界而不得不编辑SQL。

你需要一个叫做客户组阈值的表（CustomerGroupThreshold），仅使用从2016年开始的订单。

-- 预期结果
CustomerID CompanyName                        TotalOrderAmount CustomerGroupName
---------- ---------------------------------- ---------------- --------------------
ALFKI     Alfreds Futterkiste                 2302.20          Medium
ANATR     Ana Trujillo Emparedados y helados  514.40           Low
ANTON     Antonio Moreno Taquería             660.00           Low
...
WHITC     White Clover Markets                15278.90         Very High
WILMK     Wilman Kala                         1987.00          Medium
WOLZA     Wolski Zajazd                       1865.10          Medium
(81 row(s) affected)

作为起点，使用问题“使用百分比的客户分组”中的第一个CTE语句

Select
 Customers.CustomerID
 ,Customers.CompanyName
 ,TotalOrderAmount = SUM(Quantity * UnitPrice)
From Customers
 join Orders
 on Orders.CustomerID = Customers.CustomerID
 join OrderDetails
 on Orders.OrderID = OrderDetails.OrderID
Where
 OrderDate >= '20160101'
 and OrderDate < '20170101'
Group By
 Customers.CustomerID
 ,Customers.CompanyName

提示：当考虑如何使用CustomerGroupThreshold表时，请注意，当连接到一个表时，你不仅可以使用一个等值连接（=），还可以使用其他操作符，如between、>或< 。

答案

48.客户分组

答案

;with Orders2016 as (
 Select
 Customers.CustomerID
 ,Customers.CompanyName
 ,TotalOrderAmount = SUM(Quantity * UnitPrice)
 From Customers
 Join Orders
 on Orders.CustomerID = Customers.CustomerID
 Join OrderDetails
 on Orders.OrderID = OrderDetails.OrderID
 Where
 OrderDate >= '20160101'
 and OrderDate < '20170101'
 Group by
 Customers.CustomerID
 ,Customers.CompanyName
)
Select
 CustomerID
 ,CompanyName
 ,TotalOrderAmount
 ,CustomerGroup =
 Case
 when TotalOrderAmount between 0 and 1000 then 'Low'
 when TotalOrderAmount between 1001 and 5000 then 'Medium'
 when TotalOrderAmount between 5001 and 10000 then 'High'
 when TotalOrderAmount > 10000 then 'Very High'
 End
from Orders2016
Order by CustomerID

讨论

CTE很适合解决这个问题，但这并不是严格必要的。你也可以使用这样的SQL：

Select
 Customers.CustomerID
 ,Customers.CompanyName
 ,TotalOrderAmount = SUM(Quantity * UnitPrice)
 ,CustomerGroup =
 Case
 when SUM(Quantity * UnitPrice) between 0 and 1000 then 'Low'
 when SUM(Quantity * UnitPrice) between 1001 and 5000 then 'Medium'
 when SUM(Quantity * UnitPrice) between 5001 and 10000 then 'High'
 when SUM(Quantity * UnitPrice) > 10000 then 'Very High'
 End
From Customers
 Join Orders
   on Orders.CustomerID = Customers.CustomerID
 Join OrderDetails
   on Orders.OrderID = OrderDetails.OrderID
Where
 OrderDate >= '20160101'
 and OrderDate < '20170101'
Group By
 Customers.CustomerID
 ,Customers.CompanyName

这给出了相同的结果，但请注意，TotalOrderAmount被重复了5次，包括Case语句中的4次。

最好避免重复这样的计算。这些计算结果通常会非常复杂和难以阅读，而且你只想把它们放在一个地方。在一些简单的情况下，比如 Quantity * UnitPrice，这并不一定是一个问题。但大多数时候，你应该避免重复任何计算和代码。记住——“不要重复你自己”。

49.客户分组-修复null

答案

;with Orders2016 as (
 Select
 Customers.CustomerID
 ,Customers.CompanyName
 ,TotalOrderAmount = SUM(Quantity * UnitPrice)
 From Customers
 Join Orders
 on Orders.CustomerID = Customers.CustomerID
 Join OrderDetails
 on Orders.OrderID = OrderDetails.OrderID
 Where
 OrderDate >= '20160101'
 and OrderDate < '20170101'
 Group by
 Customers.CustomerID
 ,Customers.CompanyName
)
Select
 CustomerID
 ,CompanyName
 ,TotalOrderAmount
 ,CustomerGroup =
 case
   when TotalOrderAmount >= 0 and TotalOrderAmount < 1000 then 'Low'
   when TotalOrderAmount >= 1000 and TotalOrderAmount < 5000 then 'Medium'
   when TotalOrderAmount >= 5000 and TotalOrderAmount <10000 then 'High'
   when TotalOrderAmount >= 10000 then 'Very High'
 end
from Orders2016
Order by CustomerID

讨论

正如你在上述问题中所看到的那样，了解你正在处理的数据类型以及理解它们之间的差异对于获得正确的结果非常重要。使用“between”对于整数值就可以，但对于Money则不行。

50.使用百分比的客户分组

答案

;with Orders2016 as (
 Select
   Customers.CustomerID
   ,Customers.CompanyName
   ,TotalOrderAmount = SUM(Quantity * UnitPrice)
 From Customers
   join Orders
     on Orders.CustomerID = Customers.CustomerID
   join OrderDetails
   on Orders.OrderID = OrderDetails.OrderID
 Where
   OrderDate >= '20160101'
   and OrderDate < '20170101'
 Group By
   Customers.CustomerID
   ,Customers.CompanyName
)
,CustomerGrouping as (
 Select
   CustomerID
   ,CompanyName
   ,TotalOrderAmount
   ,CustomerGroup =
     case
       when TotalOrderAmount >= 0 and TotalOrderAmount < 1000 then 'Low'
       when TotalOrderAmount >= 1000 and TotalOrderAmount < 5000 then 'Medium'
       when TotalOrderAmount >= 5000 and TotalOrderAmount <10000 then 'High'
       when TotalOrderAmount >= 10000 then 'Very High'
    end
 from Orders2016
 -- Order by CustomerID
)
Select
 CustomerGroup
 , TotalInGroup = Count(*)
 , PercentageInGroup = Count(*) * 1.0/ (select count(*) from CustomerGrouping)
from CustomerGrouping
group by CustomerGroup
order by TotalInGroup desc

讨论

在答案中，我们添加了一个名为CustomerGrouping的中间CTE。CustomerGrouping被引用两次—— 一次是获取组中的客户总数，另一次是获得客户总数，作为百分比的分母。

请注意，在第二个CTE中的Order by CustomerID已经被注释掉了。如果你把它放在里面，你会得到错误。

51.灵活的客户分组

答案

;with Orders2016 as (
 Select
   Customers.CustomerID
   ,Customers.CompanyName
   ,TotalOrderAmount = SUM(Quantity * UnitPrice)
 From Customers
   Join Orders
     on Orders.CustomerID = Customers.CustomerID
   Join OrderDetails
     on Orders.OrderID = OrderDetails.OrderID
 Where
   OrderDate >= '20160101'
   and OrderDate < '20170101'
 Group by
   Customers.CustomerID
   ,Customers.CompanyName
)
Select
 CustomerID
 ,CompanyName
 ,TotalOrderAmount
 ,CustomerGroupName
from Orders2016
 Join CustomerGroupThresholds
   on Orders2016.TotalOrderAmount between
      CustomerGroupThresholds.RangeBottom and CustomerGroupThresholds.RangeTop
Order by CustomerID

讨论

请注意，这给出的结果与原来的问题相同。但是，不要使用Case语句中的硬编码值来定义CustomerGroups的边界，而是将它们放在表中。

这样做的好处是，你不需要在对客户进行分组的每个查询中重复以下代码，因为它是在表中定义的。

,CustomerGroup =
   case
     when TotalOrderAmount >= 0 and TotalOrderAmount < 1000 then 'Low'
     when TotalOrderAmount >= 1000 and TotalOrderAmount < 5000 then 'Medium'
     when TotalOrderAmount >= 5000 and TotalOrderAmount <10000 then 'High'
     when TotalOrderAmount >= 10000 then 'Very High'
   end