SQL聚合统计（count/max/min等）并使用springboot jpa原生查询 |
您所在的位置：网站首页 › 分组统计函数怎么写 › SQL聚合统计（count/max/min等）并使用springboot jpa原生查询 |
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 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 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 implements ResultTransformer, Serializable { private static final long serialVersionUID = 775781526148172513L; private Class resultClazz; private BeanUtilsBean beanUtilsBean; public DefineResultTransformer(Class 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 |
【本文地址】
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |