高级SQL查询（※※※※※）

本是古典何须时尚 2024-04-06 15:51 111阅读 0赞

#### 高级SQL查询 ####

*   *  1.SQL语句顺序
     *  2.复杂查询
     *   *  2.1 子查询
     *  3 销售业绩分析
     *   *  【项目背景】
         *  【数据集介绍】
         *  【分析思路】
     *  4. 销售客户分析
     *   *  【项目背景】
         *  【数据集介绍】
         *  【分析思路】
         *   *  \*\*1.数据质量分析，数据清洗、数据整理\*\*
             *  \*\*2.计算R、F、M指标的值\*\*
             *  \*\*3.对R、F、M指标的值进行打分\*\*
             *  \*\*4.计算R、F、M指标的得分是否大于各自得分平均值\*\*
             *  \*\*5.将客户按价值分类\*\*

### 1.SQL语句顺序 ###

我们按如图3.9所示的SQL语句的书写顺序来理解各个子句的含义。

![请添加图片描述][356aa7ffbb4f45acbc9d8660c4ac5526.png]

• select表示查询结果。  
• from表示从哪个表中查找数据。  
• where表示查询条件，用于筛选出符合条件的数据。  
• group by表示对数据按某列分组。  
• having表示对分组后的结果指定条件。  
• order by表示对查询结果进行升序或者降序的排列。  
• limit表示从查询结果中取出限定行。  
• 子查询、临时表with…as、视图、case表达式（用于多条件判断）。

### 2.复杂查询 ###

*  子查询、临时表with…as、视图、case表达式（用于多条件判断）

#### 2.1 子查询 ####

In子查询 常用于Where 条件中，举例说明

2.1.1 现在找出购买了A和B但没有购买C的产品的顾客人数

select * 
           from order 
           where 顾客ID in （ select ID  from order  where 产品=“A”）
            and 顾客ID in (   select ID  from order  where 产品=“B”）
            and 顾客ID not  in (   select ID  from order  where 产品=“C”）

2.2.2 高级SQL 语句

SELECT COUNT(DISTINCT customer_id) FROM order WHERE product_name IN ('ProductA', 'ProductB') GROUP BY customer_id HAVING COUNT(DISTINCT product_name) = 2 AND SUM(CASE WHEN product_name = 'ProductC' THEN 1 ELSE 0 END) = 0;

2.2.3 写出xml 文件

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
      PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
      "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
    <mapper namespace="com.example.OrderMapper">
      
      <select id="countDistinctCustomersWithConditions" resultType="int">
        SELECT COUNT(DISTINCT customer_id)
        FROM order
        WHERE product_name IN ('ProductA', 'ProductB')
        GROUP BY customer_id
        HAVING COUNT(DISTINCT product_name) = 2
        AND SUM(CASE WHEN product_name = 'ProductC' THEN 1 ELSE 0 END) = 0
      </select>
    
    </mapper>

### 3 销售业绩分析 ###

#### 【项目背景】 ####

对于云服务产品，有些读者可能会觉得陌生，但实际上我们的日常生活已经离不开它们了，我们用微信聊天、在淘宝网购物、用抖音看直播都用到了相关的云产品。主要的云产品有云服务器、云数据库、云存储、域名等，如表8.8所示。

![在这里插入图片描述][f8ff4d8570f9455799e07d5181cd1bdb.png]

云服务器，顾名思义就是云端的服务器，服务器就像电脑主机，能够同时承受多人访问。网站搭建、App和小程序开发部署等都需要使用云服务器。

我们在淘宝网购物时，实际上也是访问淘宝网的云服务器，是它提供了稳定的服务。云服务器比实体的服务器更加便宜，能够快速地进行业务部署，然后用户按需付费。

有了云服务器通常还会需要云数据库，用来存储业务数据。以在淘宝网购物为例，用户信息、商品信息、活动信息都需要数据库的支持。同样，云数据库也可以根据需求选择容量、类型付费使用。

云存储主要提供的是存储服务，可以用来存储文件、系统等，常见的企业网盘、协同办公软件都使用了云存储服务。  
域名服务提供了域名注册、解析、网站备案等服务，我们日常生活中访问的网站都使用了域名服务。  
了解了云产品，我们接着来看一下云服务的业务流程。

云服务公司为用户提供相应的云产品服务，销售人员为用户提供售前的产品介绍和需求梳理、售后的问题跟进，给用户更好的体验，用户通过云产品服务来完成网站、App、小程序的搭建。具体的流程图如图8.2所示。  
![在这里插入图片描述][e539b3f1f7bb445d8217a6e20c91f437.png]

#### 【数据集介绍】 ####

数据集来自某云服务公司，一共有两个表：“订单表”和“任务下发表”。  
“订单表”中是所有用户付费的订单信息，如表8.9所示。  
表8.9 订单表  
![\[插图\]][67de462c4d4e48208357b6791eb52e78.png]

“订单表”中一共有6个字段。  
• 用户id：用户唯一标识码，每一个id对应一个用户。  
• 支付时间：用户支付订单的时间，精确到秒。  
• 实付金额：用户实际支付的金额，剔除优惠券、满减等折扣。  
• 产品名称：用户购买的产品名称，主要有云服务器、数据库、云存储等。  
• 子产品名称：对产品名称的进一步细分。  
• 订单号：订单编号。  
“任务下发表”中是一些商机线索，即可能有续费意愿的用户信息，如表8.10所示。  
表8.10 任务下发表  
![\[插图\]][ca96e93ab1084a2ab4a9b37d244c9a8b.png]

“任务下发表”中一共有4个字段。  
• 用户id：用户唯一标识码，每一个id对应一个用户。  
• 任务下发时间：任务触发的时间，对新用户来说就是注册的时间，对老用户来说就是产品到期前30天的时间。  
• 产品名称：触发任务对应的产品信息。  
• 任务类型：任务分为“产品即将到期提醒”和“新用户注册”两种。  
对于公司和销售人员来说，销售业绩是重中之重，也是销售人员考核的KPI（关键绩效指标）。在2020年5月的月报中，销售主管发现销售业绩出现下降，现在主管急需你从数据中找出业绩下降的原因，并帮助业务人员进行相应的改进。

#### 【分析思路】 ####

**8.2.1 明确问题**  
为了更全面地了解问题， 我们需要看一下过去一段时间业绩同比和环比的情况，根据业绩下降的幅度及过往情况来明确问题，这里就需要用SQL查询每个月的业绩。

表所示的“订单表”中的支付时间精确到秒， 我们需要对比现在（2020年，该项目当时分析的年份）和去年（2019年）每个月份的业绩（实付金额）。

首先用date\_format（支付时间，‘%Y-%m’）获取“支付时间”里的年份和月份。

然后进行分组汇总，按“支付时间”分组，再对实付金额求和，得到业绩。  
SQL语句的书写方法如下：

![\[插图\]][6e10b46069af4f13b3258bbc4558a287.png]

查询结果如表8.11所示。  
表8.11 各月份业绩情况  
![\[插图\]][85ed3254048948b685986e159c355d65.png]

将上述查询结果导入Excel中，对数据进行整理，得到业绩同比情况，如表8.12所示。  
表8.12 业绩同比情况  
![\[插图\]][6d555170e5f747daa208f34af4c36f36.png]

为了更方便地看清楚数据，我们对数据进行可视化。如图8.3所示。  
![\[插图\]][1f080dd8e4c64995b9eb4d8f65ab5ed1.png]

图8.3 组合图绘制步骤

单击“确定”按钮，修改图表标题和坐标轴标题最终可视化结果如图8.4所示。  
![\[插图\]][815197bd547b4c7588ed69f0d726f570.png]

图8.4 业绩同比组合图

从图8.4所示的组合图中可以看出，2020年2月因新型冠状病毒疫情的影响同比下降了52.9%，在3月、4月出现了强势反弹，同比增长超20.8%。然而在5月业绩却出现了下降，同比减少11.6%，说明5月的销售确实出现了问题。  
**8.2.2 分析原因**  
是什么原因造成2020年5月的销售业绩同比减少了11.6%？

我们都知道销售额=用户数×客单价，而销售额减少，可能存在的情况就是客单价降低了或者用户数减少了。而客单价则与产品销售结构有关，用户数又与新用户和老用户的转化率相关。

这里我们可以用多维度拆解分析方法来拆解销售额，进行各个维度的分析。

**1.客单价分析**  
我们对业绩进行公式拆解：销售额=用户数×客单价。  
首先计算用户数情况。  
SQL语句的书写方法如下：  
![\[插图\]][20cb0f3be094478f85c651170d50453e.png]

返回结果如表8.13所示。

### 4. 销售客户分析 ###

#### 【项目背景】 ####

A企业是一家零售企业，一级经销客户有100个。当市场不大时，其对所有的经销客户执行统一的促销策略，一样的终端售价，因为这样有利于树立统一的品牌形象，迅速扩大市场份额。

但随着A企业市场份额的扩大，统一促销策略的弊端开始显现。因经销客户的实力千差万别，所以经销客户的需求也多种多样。

实力强的经销客户，对A企业的业绩贡献大，同时资金占用量大，仓储、渠道开发、人员促销等需要的费用也多，A企业需要给实力强的经销客户更优惠的促销策略，以便这类经销客户有更多的利润支撑运营。

而实力弱一些的经销客户，从A企业的进货量不多，对A企业的业绩贡献不大，无论多么优惠的促销策略，对A企业来说都起不到提高销售业绩的目的。

所以，A企业需要甄别经销客户的价值，判断经销客户的质量。

#### 【数据集介绍】 ####

“销售订单表”中是A企业的100个经销客户在2020年7月1日到12月31日的交易数据（这里只截取部分数据），如表8.22所示。

“销售订单表”中有5个字段：订单号、客户ID、交易日期、交易金额（销售金额：万元）、交易类型（销售类型）。下面利用这6个月的销售记录，研究A企业的100个经销客户的价值情况。

表8.22 销售订单表  
![在这里插入图片描述][2dafd8372a02442ba23dbb646ebdbe3d.png]

交易类型（销售类型）分别代表的含义如表8.23所示。  
表8.23 交易类型含义  
!\[在这里插入图片描述\](https://img-blog.csdnimg.cn/direct/3564bfbb3f6a41a7bf3f6cf97d2e8ec5.png

#### 【分析思路】 ####

**8.3.1 明确问题**

要满足不同的经销客户的需求，首先要了解这些经销客户给A企业带来多少业绩，对A企业的贡献有多大？从A企业进货的频次是多少？  
对A企业重不重要？

对经销客户按价值分类，最常用的分析方法是RFM模型分析方法。

“RFM”是下面3个单词的缩写。  
• Recency：最近一次消费距离要考察的时间，一般是“天数”，考察客户的黏性，理论上R值越小的客户价值越高。

• Frequency：消费频率，在考察时间内的消费总次数，考察客户对企业的忠诚度。

• Monetary：消费金额，在考察时间内的消费总金额，考察客户对企业的贡献。

**8.3.2 分析原因**

##### **1.数据质量分析，数据清洗、数据整理** #####

在实际的销售数据中，数据是杂乱的，有各种各样的原因会造成数据缺失、异常或者与分析目的不一致。  
在拿到销售数据时，首先要检查各项销售指标是不是符合分析的需要，然后对数据进行清洗和整理。  
对于这份销售数据，需要去掉交易金额为0的数据（交易金额！=0），同时去掉销售类型为“赠送”的数据  
（该类型对应的值为0）（交易类型！=0）。  
SQL语句的书写方法如下，把该查询结果记为临时表a  
![在这里插入图片描述][26eb3ee3b81a45d0a493d5a5300f8e86.png]

##### **2.计算R、F、M指标的值** #####

与业务人员沟通后，将参照日期定为2020年12月31日，现在分别计算出R、F、M指标的值。

R指标定义：最后一次交易日期距离参照日期的天数。找出每个客户的最后一次交易日期，也就是取交易日期的最大值距离参照日期（2020年12月31日）的天数。

因为要分析“每个客户”，所以要用到分组汇总。先按客户ID分组（group by子句），然后用汇总函数（max()函数）得到交易日期的最大值，再用datediff()函数计算交易日期的最大值距离参照日期（2020年12月31日）的天数

F指标定义：交易总次数。  
按客户ID分组（group by子句），然后汇总（计数函数count()）得到交易总次数。

M指标定义：交易总金额。  
按客户ID分组（group by子句），然后汇总（求和函数sum()）得到交易总金额。

计算R、F、M指标的值的SQL语句的书写方法如下：  
![在这里插入图片描述][861f14db171f4358b512f2e90afa1380.png]

##### **3.对R、F、M指标的值进行打分** #####

在销售过程中，大部分的销售额往往是由一小部分客户带来的，销售数据不是正态分布的，所以，在划分评分标准区间时不能按照绝对的平均值等分。  
具体怎么划分以实际业务和经验来判断。对于此案例，在与业务人员沟通后，R、F、M指标的打分规则定为如表8.25所示。  
![在这里插入图片描述][59fc150bde5e41ea9943791fbb685c39.png]  
根据以上打分标准，计算每个客户R、F、M指标的得分情况。因为是按表8.25所示的不同情况来打分的，所以是多条件判断，可以使用case when表达式来实现。  
把R指标得分情况放到R\_score列中.

![在这里插入图片描述][3d95596708074753ba774f3648aba4db.png]  
查询结果如表8.26所示，把该查询记为临时表c。  
表8.26 客户R、F、M指标的得分情况  
![1][]

##### **4.计算R、F、M指标的得分是否大于各自得分平均值** #####

分别将R\_score、F\_score、M\_score值和各自的平均值进行比较，即将客户R、F、M指标的值维度化。如果大于平均值则标记为1，如果小于平均值则标记为0，并把标记结果分别记录到R\_dim、F\_dim和M\_dim列中。  
![\[插图\]][29b369e0319149c3b6e142b389bb03f6.png]

查询结果如表8.27所示，把该查询记为临时表d。  
![在这里插入图片描述][ced63eca693041d69bcf25ba5c913ba5.png]

##### **5.将客户按价值分类** #####

根据表8.28所示的分类标准，对经销客户按价值进行分类。表8.28中的“R是否大于得分平均值”就是上一步得到的R\_dim，“F是否大于得分平均值”就是上一步得到的F\_dim，“M是否大于得分平均值”就是上一步得到的M\_dim。

![请添加图片描述][e44886c7a86d417a977b9467aebc0082.png]

[356aa7ffbb4f45acbc9d8660c4ac5526.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/21081b6b1cc343888ab015284b634957.png
[f8ff4d8570f9455799e07d5181cd1bdb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9ff8f4cb946842daa43ff57f699de6c4.png
[e539b3f1f7bb445d8217a6e20c91f437.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/6dec51a17eff4dc692bea23477202b5e.png
[67de462c4d4e48208357b6791eb52e78.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/53c17d81a9994368bcd458fe4d773372.png
[ca96e93ab1084a2ab4a9b37d244c9a8b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/95dfde3b088045f29772c070141a9a61.png
[6e10b46069af4f13b3258bbc4558a287.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/420b55d4a9f3448cb6fec477c5b53689.png
[85ed3254048948b685986e159c355d65.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/d950e94de5b14e238fb424e943431c90.png
[6d555170e5f747daa208f34af4c36f36.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f0d48d6376354df7874b4b749ac4ac7f.png
[1f080dd8e4c64995b9eb4d8f65ab5ed1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/b10ce104fa2b4a1582cb120f1dfa1681.png
[815197bd547b4c7588ed69f0d726f570.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/b3515a7495674f08b4c5f0f55d7e64a5.png
[20cb0f3be094478f85c651170d50453e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/3dca397f334d473282cf5863104cbe70.png
[2dafd8372a02442ba23dbb646ebdbe3d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/1e89d7479dc047aabf73b2995f47f865.png
[26eb3ee3b81a45d0a493d5a5300f8e86.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/816fadd946244378aa696084e88968a4.png
[861f14db171f4358b512f2e90afa1380.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/1af98f7e981c426a90aafe24bc59d98d.png
[59fc150bde5e41ea9943791fbb685c39.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/6bb3197ecb1c45a0bb0b0e201e97e833.png
[3d95596708074753ba774f3648aba4db.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/471b586cc3884858be9db2c97e83b4cf.png
[1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/84a0dcf471e243f79da5a16b66d5dcf7.png
[29b369e0319149c3b6e142b389bb03f6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/cfe94283f6804f7289c7951aafc7f87e.png
[ced63eca693041d69bcf25ba5c913ba5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/940c2cfc82504897acddd55c2358e95d.png
[e44886c7a86d417a977b9467aebc0082.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/d7c4f7ff25d44b4bb1e15d425aad2226.png