SQL聚合统计（count/max/min等）并使用springboot jpa原生查询

  1、  SQL聚合统计

 我们经常遇到按照分组聚合取出数据的情况，比如我们有一个人员表的数据如下：

我们希望按照地址（addr）分组统计出beijing/shanghai/shenzhen三个城市每个城市的总人数、年龄小于18岁的未成年人、大于18岁的成年人这些信息，该怎么办呢？

此时可以使用SQL中的聚合查询group by 然后结合统计函数即可实现上述需求，我们直观的想法是使用如下的SQL语句进行统计：

select  addr, count(*) total, count(age < 18) childNum, count(age >= 18) adultNum  
from tb_person
where 1 = 1
group by addr;

result:
addr    |total|childnum|adultnum|
--------|-----|--------|--------|
beijing |    3|       3|       3|
shanghai|    5|       5|       5|
shenzhen|    2|       2|       2|

注： 这里的where 1 =1 仅仅是占个位，不加也行，在实际中可能需要其他额外的条件，直接附在where 后面就行了；

但是发现结果并不是我们想要的，北京市一共就3人，怎么可能未成年人数和成年人人数都有3人，那加起来不就是6人了吗？

实际上：

条件count(age < 18 ) 应改为 count(age < 18 or null)。 这里必须要加 "or null" ， why？如果不加，当age不满足小于18 ,里面是count(false)，count(false)与count(*)是等价的也会统计出所有的数目。

这是因为count('任意内容')都会统计出所有记录数，只有count(null)才不计数，当age 大于18时（结果为false） or null 的结果是null， count（null）= 0 , 才能筛选过滤得到我们想要的结果。

我们更改count()统计条件重新查询：结果如下

select  addr, count(*) total, count(age < 18 or null ) childNum, count(age >= 18 or null) adultNum  
from tb_person
where 1 = 1
group by addr;

result:
addr    |total|childnum|adultnum|
--------|-----|--------|--------|
beijing |    3|       0|       3|
shanghai|    5|       3|       2|
shenzhen|    2|       1|       1|

各个城市都统计出了总人数、未成年人数、成年人数，参照原始数据，发现统计结果正确。

2、 其他聚合函数

除了使用count() 聚合函数，一般常用的聚合函数还有sum()求和函数、max()最大值函数、min()最小值函数、avg()平均值函数等，也可以配合group by 聚合使用。比如这里求每个城市最大年龄的值:

后面还可以加上升序排列（asc）或者倒序（desc）

-- 按照城市地址分别统计各个城市的最大年龄值，并按倒序排列
select addr, max(age) maxAge
from tb_person
where 1=1
group by addr
order by maxAge desc;

result:
addr    |maxage|
--------|------|
shenzhen|    44|
shanghai|    38|
beijing |    28|

发现得到了我们想要的结果。

3、springboot jpa原生SQL聚合统计

springboot jpa 提供和封装了众多的操作数据库的方法，但是对于我们上文提到的复杂查询，则需创建原生SQL查询才行，这里使用EntityManager来实现。

@Service
@Slf4j
public class PersonService {   
 @Autowired
    private EntityManager entityManager;
  /**
     * 原生SQL聚合查询统计
     * @return
     */
    public  List<PersonStatisVO> statisAddrPersonNum() {
        String sql = "select  addr, count(*) total, count(age < 18 or null) childNum, count(age >= 18 or null) adultNum  \n" +
                " from tb_person \n" +
                " where 1 = 1 \n" +
                " group by addr;";
        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(new DefineResultTransformer<>(PersonStatisVO.class));
        List<PersonStatisVO> resultList = nativeQuery.getResultList();
        return resultList;
    }
}

result:
[
  {
    "addr": "beijing",
    "total": 3,
    "childnum": 0,
    "adultnum": 3
  },
  {
    "addr": "shanghai",
    "total": 5,
    "childnum": 3,
    "adultnum": 2
  },
  {
    "addr": "shenzhen",
    "total": 2,
    "childnum": 1,
    "adultnum": 1
  }
]

对于查询到的结果需要自定义一个转换类，把SQL查询结果映射成Java 实体类

/**
 * 自定义hinernate结果转换类
 */
public class DefineResultTransformer<T> implements ResultTransformer, Serializable {
    private static final long serialVersionUID = 775781526148172513L;

    private Class<T> resultClazz;
    private BeanUtilsBean beanUtilsBean;

    public DefineResultTransformer(Class<T>  resultClass){
        this.resultClazz = resultClass;
        this.beanUtilsBean = BeanUtilsBean.getInstance();
    }

    @Override
    public Object transformTuple(Object[] tuple, String[] aliases) {
        Object result = null;

        Field[] fields = resultClazz.getDeclaredFields();
        try {
            result = resultClazz.newInstance();
            for(int i =0 ;i<aliases.length; i++){
                for(Field field : fields){
//   1、忽略大小写映射，如SQL查询字段为 "username" 可直接映射为Java实体类中userName属性
//   2、去掉"_" 映射，如SQL查询出的字段为 "user_name" 也可直接映射为Java实体类中userName属性
                    if(field.getName().equalsIgnoreCase(aliases[i].replaceAll("_",""))){
                        beanUtilsBean.setProperty(result,field.getName(),tuple[i]);
                        break;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public List transformList(List collection) {
        return collection;
    }

}

欢迎关注博主公众号，后台回复  207  可领取2021最新Java全栈视频教程