SQL进阶技巧：如何获取状态一致的分组？ | 最大、最小值法

目录

0 需求描述

1 数据准备

2 问题分析

   方法1：最大、最小值法（技巧）

  方法2：常规思路

3 小结

  如果觉得本文对你有帮助，那么不妨也可以选择去看看我的博客专栏 ，部分内容如下：

数字化建设通关指南专栏原价99，现在活动价29.9，按照阶梯式增长，直到恢复原价

---

0 需求描述

星星点灯是一家水果店，它提供了外卖水果拼盘的服务。水果店能够提供四种水果拼盘：水果魔方、海星欧蕾、猫头鹰、草莓雪山，下表反应了某一时刻店内的水果的准备情况。

    id  platter       fruit       ready  
------  ------------  ---------  --------1  水果魔方        猕猴桃            12  水果魔方        香蕉              13  水果魔方        菠萝              14  水果魔方        芒果              15  水果魔方        哈密瓜            16  海星欧蕾        草莓              17  海星欧蕾        橙子              08  猫头鹰          猕猴桃            19  猫头鹰          小橘子            010  猫头鹰          橙子              011  猫头鹰          草莓              112  草莓雪山        草莓              1

上面这些数据存在 platters 表中，platter 是拼盘的名称，fruit 是拼盘要用到的水果，ready 表示水果是否准备好了。当有客户订水果拼盘时，只有拼盘要用到的所有水果都准备好了才能制作。

现在，我们要写 SQL 找出可以立即制作的水果拼盘的名称。

--------------
水果魔方  
草莓雪山  

1 数据准备

create table platters as(select stack(12,1, '水果魔方', '猕猴桃', 1,2, '水果魔方', '香蕉', 1,3, '水果魔方', '菠萝', 1,4, '水果魔方', '芒果', 1,5, '水果魔方', '哈密瓜', 1,6, '海星欧蕾', '草莓', 1,7, '海星欧蕾', '橙子', 0,8, '猫头鹰', '猕猴桃', 1,9, '猫头鹰', '小橘子', 0,10, '猫头鹰', '橙子', 0,11, '猫头鹰', '草莓', 1,12, '草莓雪山', '草莓', 1) as (id, platter, fruit, ready));

2 问题分析

   方法1：最大、最小值法

   思路：本题中ready字段中只有0和1值，因此我们可以利用最小值这种极值思维构建辅助条件。当min(ready) =1说明该字段中所有的值都为1.

第一步：求出ready中的最小值，进行辅助判断

select platter, min(ready) flg
from platters
group by platter

 

为了更好理解此问题我们可以利用窗口函数 min(ready) over (partition by platter) 求出结果并与ready值进行对比分析，这样更直观一些。

select id, platter, fruit, ready, min(ready) over (partition by platter) flg
from platters

 

步骤2：当flg=1时说明ready中全为1，则符合条件，并获取最终结果。

select platter
from (select platter, min(ready) flgfrom plattersgroup by platter) t
where flg = 1

 

上述代码可以简化为：

最终SQL如下

select platter
from platters
group by platter
having min(ready) = 1

方法2：常规思路，需要水果种类数等于准备好的状态数时则满足条件

比如水果魔方，它需要的水果有 5 种，当这些水果处于准备好的状态的数量也为 5 时，它就可以被制作了。 

SELECT platter
FROM platters
GROUP BY platter
HAVING SUM(ready) = COUNT(*);

 

上述方式还可以优化为，当某个水果拼盘下没准备好的水果的数量为 0 时，这个拼盘可以被制作。即：

SELECT platter
FROM platters
GROUP BY platter
HAVING SUM(IF(ready = 0, 1, 0)) = 0

3 小结

本文提供了三种不同的SQL查询方法来实现这一目标，其中最大、最小值这种极限思维的分析方法最为优雅，作为一种技巧需要掌握。

  如果觉得本文对你有帮助，那么不妨也可以选择去看看我的博客专栏 ，部分内容如下：

数字化建设通关指南
专栏原价99，现在活动价29.9，按照阶梯式增长，直到恢复原价

 

主要内容：
（1）SQL进阶实战技巧
可以参考如下教程，具体链接如下

SQL很简单，可你却写不好？也许这才是SQL最好的教程

上面链接中的文章及技巧会不定期更新。

（2）数仓建模实战技巧和个人心得
       1）新人入职新公司后应如何快速了解业务？

       2）以业务视角看宽表化建设？

       3)  维度建模 or 关系型建模？

       4）业务模型与数据模型有什么区别？业务阶段的模型该如何建设？

       5）业务指标体系该如何建设？指标体系该如何维护？指标平台应如何建设？指标体系                           该由谁来搭建？

       6）如何优雅设计DWS层？DWS层模型好坏该如何评价？

       7）指标发生异常，该如何排查？应从哪些方面入手寻找问题点？

       8） 数据架构的选择，mpp or hadoop?

       9）数仓团队应如何体现自己的业务价值，讲好数据故事？

       10）BI与大数据有什么关系？BI与信息化、数字化之间有什么关系？BI与报表之间的关                          系？

       11）数据部门如何与业务部门沟通，并规划指引业务需求？

文章不限于以上内容，有新的想法也会及时更新到该专栏。

具体专栏链接如下：

数字化建设通关指南_莫叫石榴姐的博客-CSDN博客