对比Pandas，轻松理解MySQL分组聚合的实现原理

àì夳堔傛蜴生んèń 2023-01-08 11:23 134阅读 0赞

![format_png][]

> 小小明，「凹凸数据」专栏作者，Pandas数据处理专家，致力于帮助无数数据从业者解决数据处理难题。

之前交流群（后台回复“进群”即可加群）里有同学给我扔了一个问题：

![format_png 1][]

其实MySQL分组统计的实现原理，与Pandas几乎是一致的，只要我们理解了Pandas分组统计的实现原理，就能理解MySQL分组统计的原理。大体过程就是：

![format_png 2][]

今天我将带大家从`MYSQL`的执行顺序（**FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT**）上，一步步通过Pandas向大家展示具体的执行过程，并借助Python基础编码，详解更细节的过程。

**本文目录**

*  MySQL实现分组统计的原理
 *  使用Pandas演示MySQL实现分组统计的过程
 *   *  From
     *  GROUP BY
     *  SELECT
     *  Return
 *  Pandas的分组聚合的执行过程
 *  Python演示MySQL和Pandas实现分组的具体原理
 *  总结

## MySQL实现分组统计的原理 ##

其实上面给的示例代码等价于：

SELECT 
      deal_date,
      COUNT(IF(area= 'A区', order_id, NULL)) 'A区',
      COUNT(IF(area= 'B区', order_id, NULL)) 'B区',
      COUNT(IF(area= 'C区', order_id, NULL)) 'C区' 
    FROM
      order_info 
    GROUP BY deal_date;

结果：

![format_png 3][]

对于mysql标准的执行顺序是：

> FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

上面这个sql只涉及到FROM → GROUP BY → SELECT ，可以调整一下sql的阅读顺序：

FROM order_info 
    GROUP BY deal_date
    SELECT
      deal_date,
      COUNT(IF(area= 'A区', order_id, NULL)) 'A区',
      COUNT(IF(area= 'B区', order_id, NULL)) 'B区',
      COUNT(IF(area= 'C区', order_id, NULL)) 'C区';

1.  FROM

首先`FROM order_info`表示读取order\_info表的数据

1.  GROUP BY

`GROUP BY deal_date`表示按照deal\_date分组

1.  SELECT

对每个分组选取指定的字段，并根据聚合函数对每个分组结果进行集合

其实MySQL的整个计算过程与Pandas相似，大体上都是下面的步骤：

![format_png 4][]

## 使用Pandas演示MySQL实现分组统计的过程 ##

下面我使用Pandas来演示上面的执行过程。

### From ###

`FROM order_info`本质就是读取数据：

import pandas as pd
    
    data = pd.read_csv("data.csv", encoding="gb18030")
    data

结果：

<table> 
 <thead> 
  <tr> 
   <th><br></th> 
   <th>order_id</th> 
   <th>price</th> 
   <th>deal_date</th> 
   <th>area</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0</td> 
   <td>S001</td> 
   <td>10</td> 
   <td>2019/1/1</td> 
   <td>A区</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>S002</td> 
   <td>20</td> 
   <td>2019/1/1</td> 
   <td>B区</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>S003</td> 
   <td>30</td> 
   <td>2019/1/1</td> 
   <td>C区</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>S004</td> 
   <td>40</td> 
   <td>2019/1/2</td> 
   <td>A区</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>S005</td> 
   <td>10</td> 
   <td>2019/1/2</td> 
   <td>B区</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>S006</td> 
   <td>20</td> 
   <td>2019/1/2</td> 
   <td>C区</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>S007</td> 
   <td>30</td> 
   <td>2019/1/3</td> 
   <td>A区</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>S008</td> 
   <td>40</td> 
   <td>2019/1/3</td> 
   <td>C区</td> 
  </tr> 
 </tbody> 
</table>

对于Mysql的任何InnoDB引擎表来说都存在一个主键索引，在没有指定任何字段作为主键时，InnoDB表会生成一个6字节空间的自增主键row\_id作为主键。上面的Pandas表的Index(`data.index`)就相当于mysql表的自增主键row\_id。

当然这张MySQL表指定order\_id为主键时：

ALTER TABLE order_info ADD PRIMARY KEY (order_id);

就相当于：

data.set_index("order_id")

结果：

![format_png 5][]

### GROUP BY ###

`GROUP BY deal_date`表示按照deal\_date分组，即：

df_group = data.groupby("deal_date")
    df_group

结果：

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x0000000016CE8278>

其实这步的本质是获取每个分组对应的主键id列表，可以通过`DataFrameGroupBy`对象的`groups`方法查看：

df_group.groups

结果：

{'2019/1/1': [0, 1, 2], '2019/1/2': [3, 4, 5], '2019/1/3': [6, 7]}

Pandas返回的是每个分组对应的索引列表，它等价于MySQL的主键id列表。

### SELECT ###

我们拿到每个分组对应的索引列表后，就可以拿到每个分组对应的全部数据：

for deal_date, ids in df_group.groups.items():
        print(deal_date)
        display(data.loc[ids])

结果：

![format_png 6][]

当然，由于Pandas本身有现成的API，我们实际并不会这样遍历每个分区，而是：

for deal_date, split in df_group:
        print(deal_date)
        display(split)

这段Pandas遍历每个分区的本质就是上面的代码，返回结果也与上面完全相同。

对于MySQL的select这步：

SELECT 
      deal_date,
      COUNT(IF(AREA= 'A区', 1, NULL)) 'A区',
      COUNT(IF(AREA= 'B区', 1, NULL)) 'B区',
      COUNT(IF(AREA= 'C区', 1, NULL)) 'C区'

由于前面分组的存在，`count()`聚合函数将作用于每一个分组，用Pandas表达就是：

for deal_date, split in df_group:
        split.loc[split.area == 'A区', 'A区'] = split.order_id
        split.loc[split.area == 'B区', 'B区'] = split.order_id
        split.loc[split.area == 'C区', 'C区'] = split.order_id
        split = split.set_index('deal_date')
        split = split[['A区', 'B区', 'C区']]
        display(split)
        display(split.count().to_frame(deal_date).T)

结果：

![format_png 7][]

### Return ###

最后MySQL计算完成后，就会合并每个分组的结果集，用Pandas表达就是：

result = []
    for deal_date, split in df_group:
        split.loc[split.area == 'A区', 'A区'] = split.order_id
        split.loc[split.area == 'B区', 'B区'] = split.order_id
        split.loc[split.area == 'C区', 'C区'] = split.order_id
        split = split.set_index('deal_date')
        split = split[['A区', 'B区', 'C区']]
        result.append(split.count().to_frame(deal_date).T)
    result = pd.concat(result)
    result

结果：

## Pandas的分组聚合的执行过程 ##

对于上面完整MySQL语句，整体执行流程等价于Pandas的：

def group_func(split):
        split.loc[split.area == 'A区', 'A区'] = split.order_id
        split.loc[split.area == 'B区', 'B区'] = split.order_id
        split.loc[split.area == 'C区', 'C区'] = split.order_id
        split = split[['A区', 'B区', 'C区']]
        return split.count()
    
    data.groupby('deal_date', as_index=False).apply(group_func)

结果与上面一致，整体流程是：

![format_png 2][]

## Python演示MySQL和Pandas实现分组的具体原理 ##

上面的演示中：

data.groupby("deal_date").groups

结果：

{'2019/1/1': [0, 1, 2], '2019/1/2': [3, 4, 5], '2019/1/3': [6, 7]}

可以看到Pandas和MySQL分组这步其实都是计算出了每个分组对应的主键id（索引id）。但它们具体又是怎么实现的呢？

这时候，我用纯python来给大家演示一下。

不管是MySQL还是Pandas，都带有主键索引，只不过Pandas的索引不会因为重复而报错，而MySQL的索引是肯定唯一的，会覆盖前面索引相同的数据。

虽然MySQL将带有索引的数据存储到了磁盘上面，但为了方便，我只在内存上演示索引构建的过程。另外MySQL主键索引的数据结构一般是B+树，这里我用hash表（字典）来简单演示。

首先，读取数据并构建索引：

import csv
    
    data = {}
    columns = None
    with open("data.csv", encoding="gb18030") as f:
        f_csv = csv.reader(f)
        columns = next(f_csv)
        columns = dict(zip(columns, range(len(columns))))
        for i, row in enumerate(f_csv):
            data[i] = row
    print(columns)
    display(data)

结果：

{'order_id': 0, 'price': 1, 'deal_date': 2, 'area': 3}
    {0: ['S001', '10', '2019/1/1', 'A区'],
     1: ['S002', '20', '2019/1/1', 'B区'],
     2: ['S003', '30', '2019/1/1', 'C区'],
     3: ['S004', '40', '2019/1/2', 'A区'],
     4: ['S005', '10', '2019/1/2', 'B区'],
     5: ['S006', '20', '2019/1/2', 'C区'],
     6: ['S007', '30', '2019/1/3', 'A区'],
     7: ['S008', '40', '2019/1/3', 'C区']}

这样我们就读取数据并构建了主键索引，以及表的列名元信息。

下面我们开始实现分组：

# 获取分组数据所在的列
    group_num = columns['deal_date']
    id_groups = {}
    for index, row in data.items():
        group_key = row[group_num]
        ids = id_groups.setdefault(group_key, [])
        ids.append(index)
    id_groups

结果：

{'2019/1/1': [0, 1, 2], '2019/1/2': [3, 4, 5], '2019/1/3': [6, 7]}

最后完成聚合计算：

result = {}
    for deal_date, ids in id_groups.items():
        areas = result.setdefault(deal_date, [0, 0, 0])
        for index in ids:
            area = data[index][columns['area']]
            if area == 'A区':
                areas[0] += 1
            elif area == 'B区':
                areas[1] += 1
            elif area == 'C区':
                areas[2] += 1
    result

结果：

{'2019/1/1': [1, 1, 1], '2019/1/2': [1, 1, 1], '2019/1/3': [1, 0, 1]}

借助Pandas展示一下最终结果：

pd.DataFrame.from_dict(result, 'index', columns=["A区", "B区", "C区"])

结果：

![format_png 8][]

## 总结 ##

今天我通过Pandas和Python向你详细演示了MySQL分组聚合的整体执行流程，相信你已经对分组聚合有了更深层次的理解。

如果你觉得本文对你有所启发，希望得到你的点赞关注和评论。

作者博客地址：https://blog.csdn.net/as604049322，欢迎扫描关注

![format_png 9][]

????《**Python科学计算入门与实战**》深入分享作者10余年Python科学计算编程经验，10小时配套教学视频、98个典型实例、10个综合案例、32个延伸知识点、92个配套习题、教学PPT，理论结合实践，即便Python基础为零也能快速掌握。点击下图可看详情/购买！????

![format_png 10][]

感谢机械工业出版社支持！**周日**统一兑换！后台回复“**读书会**”进群，聊书聊币吹吹牛！后台回复“规则”轻松赚币，免费包邮带回家！

![format_png 11][]

**后台回复暗号「****进群****」，即刻加入读者交流群~**

![format_png 12][]

[format_png]: /images/20221119/3891d83d12e8413eb2b95c5aae1b092b.png
[format_png 1]: /images/20221119/4397f90bc2044b5abdf1f770a99f582a.png
[format_png 2]: /images/20221119/166bdfb94cea468f84f7e0925dfe7883.png
[format_png 3]: /images/20221119/34713ce55e7a4ca7be5261d99e9a66d6.png
[format_png 4]: /images/20221119/01fd506acf454df69c01ec5cd6dc947f.png
[format_png 5]: /images/20221119/dfeda47ffeeb4cc9ac40dc099d77ba71.png
[format_png 6]: /images/20221119/ee45904e42644f2197657cd38eb403e9.png
[format_png 7]: /images/20221119/de329d00c25547dfa1bc99ab08e07760.png
[format_png 8]: /images/20221119/5a890d5f3ea94af3a324ed5b0a4d2f36.png
[format_png 9]: /images/20221119/9e8426614a524d48905c844bb1c55a3b.png
[format_png 10]: /images/20221119/66e304448fce48cd9f151b9b64e5c33f.png
[format_png 11]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9iOTZDaWJDdDcwaWFiaWFuSUhpYzFoQkFBcDg0aHBNQ2R6NVZpYWFqR3Q2QzJsWGFBWDNpYTExbXR5bnVlQndDckMyRHd1M0FGRmRJd1J3bjM0czJSTUdTMmtWQS82NDA?x-oss-process=image/format,png
[format_png 12]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi90WFlpY3Q0MHhmTGlhV2FsSzhBOFhXdkV6VDdpYkRPdmJpYUVpYVJLUkw0cG43eHJTamljZGR3Sk1NZHBqdE45R1l2Q3NiVnJzUU9ub1VYdkYzVmlhMll0NkVqT3cvNjQw?x-oss-process=image/format,png