[JPA] JPQL 집합 함수 group by 와 having

 집합함수 

집합함수는 다음과 같은 특징이 있습니다.

• 집합함수는 값을 계산해서 하나의 값으로 반환하는 함수입니다.
• 보통  group by 자주 같이 사용됩니다.
• where 절에서 바로 사용할 수 없기 때문에 group by 이후에 having 또는 서브쿼리를 사용합니다.

함수	리턴타입	설명
count	Long	개수를 구함
max, min	Long	최대, 최소값 구함
avg	Double	평균 구함
sum	Long	합 구함

 

▼ 예제의 Player 엔티티는 Team 엔티티와 관계하고 있습니다.

 

	@Entity
	@NoArgsConstructor
	@Getter
	@Setter
	@Table(name = "player")
	public class Player {
	@Id
	@Column(name = "player_id")
	private String id;
	private String name;
	private Long salary;
	@ManyToOne
	@JoinColumn(name = "team_id")
	private Team team;
	public Player(String id, String name) {
	this.id = id;
	this.name = name;
	}
	}

view raw player.java hosted with ❤ by GitHub

 

 

▼ max, min, avg, sum은 해당 값이 존재하지 않는 경우 null을 값으로 리턴합니다. 다음과 같이 TypedQuery를 생성해서 getSingleResult() 메서드를 실행하면 각각 salary의 평균과 최고, 최소 값을 구합니다. 

select count(p), avg(p.salary), max(p.salary), min(p.salary) from Player p

 

	@Test
	void func() {
	EMF.init();
	EntityManager em = EMF.createEntityManager();
	TypedQuery<Object[]> query = em.createQuery("select count(p), avg(p.salary), max(p.salary), min(p.salary) from Player p", Object[].class);
	Object[] aggValues = query.getSingleResult();
	Long count = (Long)aggValues[0];
	Double avgSal = (Double)aggValues[1];
	Long maxSal = (Long)aggValues[2];
	Long minSal = (Long) aggValues[3];
	System.out.println("count = " + count);
	System.out.println("avgSal = " + avgSal);
	System.out.println("maxSal = " + maxSal);
	System.out.println("minSal = " + minSal);
	em.close();
	EMF.close();
	}

view raw func.java hosted with ❤ by GitHub

 

Hibernate: 
select
	count(p),
	avg(p.salary),
	max(p.salary),
	min(p.salary) 
from
Player p
    count(player0_.player_id) as col_0_0_,
    avg(player0_.salary) as col_1_0_,
    max(player0_.salary) as col_2_0_,
    min(player0_.salary) as col_3_0_ 
from
    player player0_

 

 

 

 

 Group by 

▼ group by를 사용할 경우 getSingleResult가 아닌 getResultList 로 쿼리를 실행합니다. List<Object[]> 데이터 형으로 값을 저장하고 for 루프 문으로 해당 값들을 확인할 수 있습니다. 만약 직접 left join을 통해서 team을 조인 하는 경우에는 group by 에 직접 Team 엔티티로 조회합니다.

select p.team.id, count(p), avg(p.salary), max(p.salary), min(p.salary) from Player p group by p.team.id

 

	@Test
	void groupby() {
	EMF.init();
	EntityManager em = EMF.createEntityManager();
	TypedQuery<Object[]> query = em.createQuery("select p.team.id, count(p), avg(p.salary), max(p.salary), min(p.salary) from Player p group by p.team.id", Object[].class);
	List<Object[]> rows = query.getResultList();
	for (Object[] aggValues : rows) {
	String teamId = (String)aggValues[0];
	Long count = (Long)aggValues[1];
	Double avgSal = (Double)aggValues[2];
	Long maxSal = (Long)aggValues[3];
	Long minSal = (Long) aggValues[4];
	System.out.println("teamId = " + teamId);
	System.out.println("count = " + count);
	System.out.println("avgSal = " + avgSal);
	System.out.println("maxSal = " + maxSal);
	System.out.println("minSal = " + minSal);
	}
	em.close();
	EMF.close();
	}

view raw groupby.java hosted with ❤ by GitHub

 

 

 

 

 Having 

▼ 다음은 team의 player가 1개 이상인 경우를 Having 절을 통해서 조회합니다. group by 절 이후에 다음 구문을 통하여 조회합니다. 이 역시 복수개의 결과를 받기 위해서 getResultList 메서드로 쿼리를 실행합니다.

 

	@Test
	void having() {
	EMF.init();
	EntityManager em = EMF.createEntityManager();
	TypedQuery<Object[]> query = em.createQuery("select p.team.id, count(p), avg(p.salary), " +
	"max(p.salary), min(p.salary) from Player p " +
	"group by p.team.id having count(p) > 1", Object[].class);
	List<Object[]> rows = query.getResultList();
	for (Object[] aggValues : rows) {
	String teamId = (String)aggValues[0];
	Long count = (Long)aggValues[1];
	Double avgSal = (Double)aggValues[2];
	Long maxSal = (Long)aggValues[3];
	Long minSal = (Long) aggValues[4];
	System.out.println("teamId = " + teamId);
	System.out.println("count = " + count);
	System.out.println("avgSal = " + avgSal);
	System.out.println("maxSal = " + maxSal);
	System.out.println("minSal = " + minSal);
	}
	em.close();
	EMF.close();
	}

view raw having.java hosted with ❤ by GitHub