创建交叉表_统计知识——交叉分组表

最近在学习统计学基础，同使用Excel和MySQL做练习题。接触到交叉分组表，如果使用Excel可以使用数据透视表进行组合交叉分组表，但在MySQL中如何创建呢？

交叉分组表

交叉分组表是一种常用的分类汇总表格，可以显示多变量之间的关系。其表格形式的行和列标签为一个或多个变量。

举个例子，下面数据为300家饭店的质量等级和餐价：

300家饭店质量等级和餐价（部分）

在该数据中，对质量等级和餐价绘制交叉分组表。在Excel中使用数据透视表，将质量等级设为行、餐价设为列，对饭店数量进行值计数统计，并对餐价字段进行组合分组，结果如下：

该表中，质量等级变量作为表格的行，餐价作为表格的列。

---

我们知道了如何使用Excel绘制交叉分组表，但在MySQL中如何查询提取交叉表呢？

行转列

绘制交叉表，需要将其中一个变量转换为列，故应该了解数据的行转列的操作。

1、使用CASE WHEN ... ... TEHN ... ELSE ... END进行转换

CASE是条件分支语句，当字段中对应行的值满足某个条件则执行语句。

拿上述的数据，对字段餐价进行分组，组名为每列的列名，分组情况为10~19、20~29... ... 。如果餐价满足10~19，返回1，否则返回0，依次类推；

SELECT 
       SUM(CASE WHEN 餐价 BETWEEN 10 AND 19 THEN 1 ELSE 0 END) AS '10~19',
       SUM(CASE WHEN 餐价 BETWEEN 20 AND 29 THEN 1 ELSE 0 END) AS '20~29',
       SUM(CASE WHEN 餐价 BETWEEN 30 AND 39 THEN 1 ELSE 0 END) AS '30~39',
       SUM(CASE WHEN 餐价 BETWEEN 40 AND 49 THEN 1 ELSE 0 END) AS '40~49'
FROM restaurant;

分组结果

2、使用IF()进行行转列

使用IF()可以得到CASE语句一样的效果，而且比CASE语句更加简洁。

SELECT 
       SUM(IF(餐价 BETWEEN 10 AND 19,1,0)) AS '10~19',
       SUM(IF(餐价 BETWEEN 20 AND 29,1,0)) AS '20~29',
       SUM(IF(餐价 BETWEEN 30 AND 39,1,0)) AS '30~39',
       SUM(IF(餐价 BETWEEN 40 AND 49,1,0)) AS '40~49'
FROM restaurant;

注意：上面使用SUM函数是为了对每个组进行频数统计，也可以使用其他函数进行其它计算。比如，可以将SUM()改为AVG()对餐价字段进行求平均值。

交叉表

在将对一个字段进行行变列的基础上，对另一个字段进行分组。简单的操作是，在原来行变列的基础上，再加一句GROUP BY，便可以实现交叉表的查询。

SELECT 质量等级,
       SUM(IF(餐价 BETWEEN 10 AND 19,1,0)) AS '10~19',
       SUM(IF(餐价 BETWEEN 20 AND 29,1,0)) AS '20~29',
       SUM(IF(餐价 BETWEEN 30 AND 39,1,0)) AS '30~39',
       SUM(IF(餐价 BETWEEN 40 AND 49,1,0)) AS '40~49'
FROM restaurant 
GROUP BY 质量等级;

交叉分组表

交叉分组表常用于研究两个字段或多个之间的关系。比如，不同城市之间不同职业的薪资水平、不同年龄段的不同性别的消费水平等等。