分组统计查询
1.统计函数（分组函数）
常用的统计函数：

统计个数：COUNT()；根据表中的实际数据量返回结果
求和：SUM()、是针对于数字的统计
最小值：MIN()、各种数据类型都支持
最大值：MAX()、各种数据类型都支持
平均值：AVG()、是针对于数字的统计

范例：验证各个函数

这些统计函数是允许和其他函数嵌套的。
范例：求员工的平均工作年限

范例：求出最早和最晚的雇佣日期

注意：以上几个函数，在表中没有数据的时候，只有COUNT()函数会返回结果，其他都是null。
范例：表中数据为空时，函数返回结果

COUNT函数的三种使用形式：

COUNT(*)：可以准确的返回表中的全部记录数
COUNT(字段)：统计不为null的所有数据
COUNT(DISTINCT 字段)：消除重复数据之后的结果



2.分组统计
什么情况下可能分组？
分组的前提是存在有重复，但是也允许一行记录进行单独分组
如果要进行分组则应该使用GROUP BY子句完成，那么此时的SQL语法格式变为如下形式：
【④选出所需要的数据列】SELECT  [DISTINCT] * | 分组列[别名]，分组列 [别名]，分组列 [别名]…
【①确定数据来源】FROM  表名称 [别名]
【②筛选数据行】WHERE 限定条件（s）；
【③针对于筛选的行进行分组】GROUP BY 分组字段、分组字段、分组字段 …;
【⑤数据排序】ORDER BY  排序字段[ASC | DESC] ,排序字段 [ASC | DESC] …;
范例：根据部门编号分组，查询出每个部门的编号、人数、平均工资

范例：根据职位分组，统计出每个职位的人数，最低工资与最高工资

实际上GROUP BY 子句之所以使用麻烦，是因为分组的时候有一些约定条件：


如果查询不使用GROUP BY子句，那么SELECT 子句中只允许出现统计函数，其他任何字段不允许出现




错误代码
正确代码




SELECT empno，COUNT(*) FROM emp；
SELECT COUNT(*) FROM emp；




如果查询中使用了GROUP BY子句，那么SELECT 子句中只允许出现分组字段、统计函数，其他任何字段都不允许出
现。




错误代码
正确代码




SELECT empno， job，COUNT(*) FROM emp GROUP BY job；
SELECT job，COUNT(*) FROM emp GROUP BY job；




统计函数允许嵌套，但是嵌套之后的SELECT子句里面只允许出现嵌套函数，而不允许出现任何字段，包括分组字段




错误代码





SELECT deptno ，MAX(AVG(sal)) FROM emp GROUP BY deptno;
SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;





3.多表查询与分组统计
对于GROUP BY子句而言是在WHERE子句之后执行的，所以使用时可以进行限定查询，也可以进行多表查询。
范例：查询出每个部门的名称、部门人数、平均工资。


确定要使用的数据表：
|-dept表：部门名称
|-emp表：统计数据


确定要使用的关联字段
|-雇员与部门：emp.deptno=dept.deptno


第一步：换个思路，查询出每个部门的名称、雇员编号（COUNT(empno)）、基本工资AVG（sal）

第二步:此时的查询结果中对于部门名称部分出现了重复的内容，按照分组来讲，只要是出现了数据的重复，那么就可以进行分组，只不过此时的分组是针对于临时表（查询结果）。

第三步：在dept表中实际上存在有四个部门信息，而此时的要求也是统计所有的部门名称，如果发现数据不完整，则需要采用外连接。

范例：查询每个部门的编号、名称、位置、部门人数、平均工资。


确定要使用的数据表：
|-dept表：编号、名称、位置；
|-emp表：统计信息


确定已知的关联字段：
|-雇员与部门：emp.deptno=dept.deptno


第一步：查询每个部门的编号、名称、位置、雇员编号、工资；

第二步：此时发现有三列（dept表）同时发生着重复，那么就可以进行多字段分组。

4.HAVING 子句
现在要求查询出每个职位的名称，职位的平均工资，但是要求显示的平均工资大于2000.
即：按照职位先进行分组，同时统计出每个职位的平均工资；要求只显示出那些平均工资高于2000的职位信息。

错误分析：实质是没有搞清楚语句的执行顺序，WHERE子句上不允许出现统计函数，WHERE子句是在GROUP BY之前执行的。
此时的SQL语法结构：
【⑤选出所需要的数据列】SELECT  [DISTINCT] * | 分组列[别名]，分组列 [别名]，分组列 [别名]…
【①确定数据来源】FROM  表名称 [别名]
【②筛选数据行】WHERE 限定条件（s）；
【③针对于筛选的行进行分组】GROUP BY 分组字段、分组字段、分组字段 …;
【④针对于筛选的行分组】HAVING 分组过滤
【⑥数据排序】ORDER BY  排序字段[ASC | DESC] ,排序字段 [ASC | DESC] …;
范例：使用HAVING子句

HAVING是在GROUP BY分组之后才执行的筛选，在HAVING里面可以直接使用统计函数。
说明：关于WHERE与HAVING的区别？

WHERE子句是在GROUP BY分组之前进行筛选，指的是选出那些可以参与分组的数据，WHERE子句不允许使用统计函数。
HAVING 子句是在GROUP BY分组之后执行的，可以使用统计函数。


5.分组案例
范例：显示所有非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要求满足从事同一工作月工资的合计大于5000，显示的结果按照月工资的合计的升序排列。
第一步：查询所有非销售人员的信息，WHERE进行限定查询。

第二步：按照职位进行分组，而后求出月工资的总支出；

第三步：分组后的数据进行再次的筛选，使用HAVING子句

第四步：按照月工资的合计升序排列，使用ORDER BY

范例：统计所有领取佣金和不领取佣金的人数、平均工资
按照简单的单细胞思维模式，现在肯定使用comm分组（只有无佣金的数据正确，因为分组的前提必须是数据重复）

把问题进行拆分：


查询出所有领取佣金的雇员的人数、平均工资，直接使用where子句。



查询出所有不领取佣金的雇员的人数、平均工资，直接使用where子句。

既然此时两个查询结果返回的结构完全相同，那么可以直接进行连接

最近忙的厉害，除了发一发之前写的存货只能写一些简单的东西了。

这里就简单分享下最近遇见针对数据进行指标统计遇见的问题。
针对字段中某部分内容的指标统计
在使用mongodb进行指标统计的时候可能遇见下面的数据结构
/* 1 */
{
    "_id" : ObjectId("5edf4b5c64574814bc8ae4ae"),
    "address" : "河南,信阳",
    "state" : 0,
    "remark" : "发送成功",
    "createAt" : NumberLong(1591199999000)
}

/* 2 */
{
    "_id" : ObjectId("5edf4ca064574814bc8ae4d5"),
    "address" : "湖北,武汉",
    "state" : 0,
    "remark" : "发送成功",
    "createAt" : NumberLong(1591199999000)
}

/* 3 */
{
    "_id" : ObjectId("5edf4cac64574814bc8ae4d9"),
    "address" : "湖北,宜昌",
    "state" : 0,
    "remark" : "发送成功",
    "createAt" : NumberLong(1591199999000)
}


在某些时候我们可能需要根据地区信息来统计一些数据，但是数据并非是绝对干净的，可能只需要我们根据部分字段进行统计。
这个时候就需要使用$split或者使用$substr进行分组计算。
比如上面的数据中需要我们统计每个省下面业务数据内容。
使用split进行字符串截取
db.getCollection('AreaDemoLog').aggregate([
    {
        "$project": {
            // 首先使用$split对address字段进行切割，得到名称为regions的地区数组
            "regions": {
                "$split": ["$address",","]
            }
        }
    },
    {
        "$project": {
            "regions": 1,
            // 然后使用$arrayElemAt获得regions的地区数组中第一个元素，命名为 province 
            "province": {
                "$arrayElemAt": [ "$regions",0]
            }
        }
    },
    {
        "$group": {
            // 最后根据province字段分组求总
            "_id": "$province",
            "count": {
                "$sum": 1
            }
        }
    },
    {
        "$project": {
            "count": 1,
            "_id": 0,
            "province": "$_id"
        }
    }
])

使用substr进行字符串截取
db.getCollection('AreaDemoLog').aggregate([
    {
        "$project": {
            // 首先使用$substrCP对address字段进行截取，然后直接得到目标字段
            "province": {
                $substrCP:  [ '$address', 0, 2 ]
            }
        }
    },
    {
        "$group": {
            // 最后根据province字段分组求总
            "_id": "$province",
            "count": {
                "$sum": 1
            }
        }
    },
    {
        "$project": {
            "count": 1,
            "_id": 0,
            "province": "$_id"
        }
    }
])

关于字符串截取注意
对于截取纯英文和数字的字符串结构的时候可以使用$substr但是使用纯汉字的字段进行截取的时候，使用$substr根据设置的编码情况会出现下面异常：
$substrBytes:  Invalid range, ending index is in the middle of a UTF-8 character.

因为$substr仅适用于ASCII编码。所以这个时候需要使用mongodb 3.4中引入的$substrCP来进行字符串切割。

上面两个查询都可以得到正确结果

/* 1 */
{
    "count" : 16.0,
    "province" : "湖北"
}

/* 2 */
{
    "count" : 1.0,
    "province" : "河南"
}

将上面查询转换为JAVA代码
将上面查询语句放到JAVA代码中是下面的结构
使用split进行字符串截取
    public static String test() {
        List<AggregationOperation> lstOperations = new ArrayList<>(10);
        // 切分地区
        AggregationOperation splitAgg =
            Aggregation.project().andExpression("{ $split: {'$address', ','}}").as("regions");
        lstOperations.add(splitAgg);

        ProjectionOperation province =
            Aggregation.project("$regions").andExpression("{ $arrayElemAt: { '$regions', 0 }}").as("province");
        lstOperations.add(province);
        // 求总
        AggregationOperation groupAgg = Aggregation.group("$province").count().as("count");
        lstOperations.add(groupAgg);
        // 定义查询内容
        ProjectionOperation projectionOperation =
            Aggregation.project("count").andExclude("_id").and("$_id").as("province");
        lstOperations.add(projectionOperation);

        AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        //开始查询
        Aggregation agg = Aggregation.newAggregation(lstOperations).withOptions(aggregationOptions);
        AggregationResults<Map> groupResult = this.mongoTemplate.aggregate(agg, "AreaDemoLog", Map.class);
        return "";
    }


使用substr进行字符串截取
    public static String test() {
        List<AggregationOperation> lstOperations = new ArrayList<>(10);
        // 切分地区
		ProjectionOperation province = 
			Aggregation.project().andExpression("{ $substrCP: { '$address', 0, 2 } }").as("province");
		lstOperations.add(province);
        // 求总
        AggregationOperation groupAgg = Aggregation.group("$province").count().as("count");
        lstOperations.add(groupAgg);
        // 定义查询内容
        ProjectionOperation projectionOperation =
            Aggregation.project("count").andExclude("_id").and("$_id").as("province");
        lstOperations.add(projectionOperation);

        AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        //开始查询
        Aggregation agg = Aggregation.newAggregation(lstOperations).withOptions(aggregationOptions);
        AggregationResults<Map> groupResult = this.mongoTemplate.aggregate(agg, "AreaDemoLog", Map.class);
        return "";
    }


将上面查询转换为JAVA代码需要注意内容
在mongodb查询中我们使用了下面的语句
"$split": ["$address",","]

"$arrayElemAt": [ "$regions",0]

$substrCP:  [ '$address', 0, 2 ]

在使用MongodbTemplate进行查询的假如直接使用下面的拼写
andExpression("{ $split: [ '$address', ',' ] }")

andExpression("{ $arrayElemAt: [ '$regions', 0] }")

andExpression("{ $substrCP: [ '$address', 0, 2 ] }")


最终的查询会出现下面异常
{
    "code": 1,
    "msg": "Expression [{ $split: ['$address', ',']}] @23: EL1043E: Unexpected token. Expected 'rsquare(])' but was 'comma(,)'"
}

所以在将上面语句转到JAVA中的语句时候需要将"[...]"修改为"{...}"


个人水平有限，上面的内容可能存在没有描述清楚或者错误的地方，假如开发同学发现了，请及时告知，我会第一时间修改相关内容。假如我的这篇内容对你有任何帮助的话，麻烦给我点一个赞。你的点赞就是我前进的动力。

如何根据某一字段对数据进行分组统计？

在项目要求中要求实现此页面功能

在这里要求统计公司20GP，40GP，40HC的柜量和TEU情况，



下面是我实现此功能的页面



啊啊，页面设计不好看，虽然实现的内容与要求的有些许差异，但是分组查询统计的功能是实现了

下面是我经过思考编写的代码（在这里引用了BsgridPage）
// An highlighted block
public ActionResult Statistics(BsgridPage bsgridPage, int EntrustID, string WorkNumber, string StarDate, string EndDate,
             int PretendHarborID, int PurposeID, string ShipID, string ShipNext)//柜型柜量统计
        {
            #region
            try
            {
                var lisqtem = (from tbSetBox in myModels.SYS_Setbox
                                   //group by分组
                                   //group tbSetBox by tbSetBox.ShipcompanyID into tbShipcommanpay 
                                   //表示对tbSetBox按ShipcompanyID字段归类，其结果命名为tbShipcommanpay 
                                   //一旦重新命名，tbSetBox的作用域就结束了，所以，最后select时，
                               group tbSetBox by tbSetBox.ShipcompanyID into tbShipcommanpay
                               select new
                               {
                                   ShipcompanyID = tbShipcommanpay.Key,//接口提供一个属性 Key ，返回进行分组的关键字段的值
                                   TEU = tbShipcommanpay.Select(m => m.TEU),
                                   Score20GP = tbShipcommanpay.Where(m => m.Boxmodel == "20GP"),
                                   Score40GP = tbShipcommanpay.Where(m => m.Boxmodel == "40GP"),
                                   Score40HC = tbShipcommanpay.Where(m => m.Boxmodel == "40HC"),
                               }).ToList();

                var linqteml = (from tbSetBox in lisqtem
                                join tbShipcompany in myModels.SYS_Shipcompany on tbSetBox.ShipcompanyID equals tbShipcompany.ShipcompanyID
                                join tbShip in myModels.SYS_Ship on tbShipcompany.ShipID equals tbShip.ShipID
                                join tbWorkNumber in myModels.SYS_WorkNumber on tbShip.WorkNumberID equals tbWorkNumber.WorkNumberID
                                join tbEntrust in myModels.SYS_Entrust on tbShip.EntrustID equals tbEntrust.EntrustID
                                orderby tbSetBox.ShipcompanyID descending
                                select new SetBoxVo
                                {
                                    ShipcompanyID = tbShipcompany.ShipcompanyID,
                                    ShipName = tbShip.ShipName,
                                    ShipcompanyMC = tbShipcompany.ShipcompanyMC,
                                    Score20GP = tbSetBox.Score20GP.Count(),
                                    Score40GP = tbSetBox.Score40GP.Count(),
                                    Score40HC = tbSetBox.Score40HC.Count(),
                                    TEU = tbSetBox.TEU.Count(),
                                    Date = tbShip.DateShip.ToString(),
                                    DateShip = tbShip.DateShip,
                                    WorkNumber = tbWorkNumber.WorkNumber,
                                    EntrustMC = tbEntrust.EntrustMC,
                                    ShipNext = tbShip.ShipNext,
                                    EntrustID = tbEntrust.EntrustID,
                                    ShipID = tbShip.ShipID,
                                }).ToList();
                if (EntrustID > 0)
                {
                    linqteml = linqteml.Where(m => m.EntrustID == EntrustID).ToList();
                }
                if (PretendHarborID > 0)
                {
                    linqteml = linqteml.Where(m => m.PretendHarborID == PretendHarborID).ToList();
                }
                if (!string.IsNullOrEmpty(ShipID))
                {
                    linqteml = linqteml.Where(m => m.ShipName.Contains(ShipID)).ToList();
                }
                if (PurposeID > 0)
                {
                    linqteml = linqteml.Where(m => m.PurposeID == PurposeID).ToList();
                }
                if (!string.IsNullOrEmpty(WorkNumber))
                {
                    linqteml = linqteml.Where(m => m.WorkNumber.Contains(WorkNumber)).ToList();
                }
                if (!string.IsNullOrEmpty(ShipNext))
                {
                    linqteml = linqteml.Where(m => m.ShipNext.Contains(ShipNext)).ToList();
                }
                if (!string.IsNullOrEmpty(StarDate) && !string.IsNullOrEmpty(EndDate))
                {
                    DateTime starDate = Convert.ToDateTime(StarDate);
                    DateTime endDate = Convert.ToDateTime(EndDate);
                    linqteml = linqteml.Where(m => m.DateShip >= starDate && m.DateShip <= endDate).ToList();
                }
                int totals = linqteml.Count();
                List<SetBoxVo> listSet = linqteml
                    .Skip(bsgridPage.GetStartIndex())
                    .Take(bsgridPage.pageSize).ToList();
                Bsgrid<SetBoxVo> bsgrid = new Bsgrid<SetBoxVo>
                {
                    success = true,
                    totalRows = totals,
                    curPage = bsgridPage.curPage,
                    data = listSet
                };
                return Json(bsgrid, JsonRequestBehavior.AllowGet);
                #endregion
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
            return Json("", JsonRequestBehavior.AllowGet);
        }

查询JSON如下：

{

  "query": {

    "bool": {

      "filter": [

        {

          "terms": {

            "T2.keyword": [

             “北京”,“天津”,“上海”

            ]

          }},

          {"range": {

            "T12": {

              "gt": "2019-11-01 00:00:00 000",

              "lt": "2019-12-01 00:00:00 000"

            }

          }}

        

      ]

    }

  },

  "from": 0,

  "size": 10,

  "sort": [],

  "aggs": {"group_by_tags": {

      "terms": {

        "field": "T2.keyword",

        "size": 400

      }

    

}

}