조건분기/집합연산/윈도우함수/갱신

조건분기

SQL의 조건 분기는 특수한 값을 반환합니다.

SELECT name,address,

	CASE WHEN address = '서울' THEN '경기'

		 WHEN address = '창원' THEN '경남'

		 ELSE 'NA'

	END AS district

FROM Address;

위 코드는 기존 데이터를 교환하여 표현했다 라고 말할수 있습니다. 물론 기존 데이터를 수정한다란 의미는 아닙니다.

그러면, 위와같은 코드처리를 JPA에서는 어떻게 할까요?

@Entity
public class Address {
	@Id
    @GeneratedValue
    @Column(name = "ADDRESS_ID")
    private Integer id;
	
	private String name;	
	private String address;

   //Customer Property ( NotAccess DB)
	public String	getDistrict() {
		String result="NA";
		switch(address) {
			case "서울": result = "경기";
			break;
			case "창원": result = "경남";
			break;
		}
		return result;
	}  
  //Setter,Getter 생략		
}

이와 같은 단순 교환식은 JPA의Entity의 Get 퍼로퍼티 정의를 통해 기존 데이터 변환이 가능합니다.

DB를 접근하지 않는 getProperty는 우선, write할 멤버가 없기때문에 상태를 보관할 멤버변수가 없어도 되며

주석으로 구분하여 DB를 접근하지 않는 퍼로퍼티로 공간 분리를 합니다.

 이렇게 사용했을 때 장점은 다음과 같습니다.

• district를 얻기위해 , 데이터베이스에게 일을 시키지 않습니다. 어플리케이션이 분산해서 연산할수 있음을 의미합니다.
• 조회가 100개가되었다고 해서, getDistrict() 를 호출하지 않으면(사용하는곳이없으면), 어플리케이션내에서 변환 연산조차 발생하지 않습니다.  ( Lazy컨셉,호출이 필요하지 않으면 사용하지 않겠다.)
• 다양한 자바 함수사용이 가능합니다.

위와같이 변환할수있다고 하더라도, SQL문의 CASE문은 여러요소에사용되는 식이기때문에,

모든 케이스에서 JAVA의 CASE문으로 변환할수 있다란 의미는 아닙니다. - 식과 문장의차이

이것은 단지 데이터베이스의 Entity모델내에, 자연스럽게 기존값을 특수변환 처리를 하는 로직을 이질성없이 결합할수 있다란것이며

SQL에서 조건문처리는 JAVA의 CASE문과 작동방식은 동일하지만 아주 큰차이가 있습니다.

집한연산

UNION

단일 테이블내의 WHERE 구에서 합집합을 OR,교집합을 AND가 담당하였습니다.

두 테이블의 합집합을 구할때는 UNION을 연산자를 사용합니다.

샘플링 데이터는 JPA를 사용하여 만들었습니다.

• Address → Address2 로 엔티티복사
• Address는 100개의 랜덤 주소록생성( 각 프로필은 모두 유니크함 )
• Address2는 짝수번째 남자 데이터만 복제해서, 여성이 존재하지 않음
• 3의배수일때는  Address2에 철수만 존재하게함 
• 샘플 데이터 Address 100개 , Address 67개
  
  

public void InsertAddressData(){		
	String[] addArray = {
			"서울","대구","부산","창원","마산","제주"				
	};			
	for(int i=0; i<100;i++) {
		Boolean isAddRepo2 = false;
		double random = Math.random();
	    double x = random*100;
	    int y = (int)x + 1; //Add 1 to change the range to 1 - 100 instead of 0 - 99
	    
		Address address = new Address();
		Address2 address2 = new Address2();
		
		address.setAge(y);
		address.setAddress( addArray[i%6] );
		address.setPhoneNbr(String.format("010-3333-1%04d", i));			
		
		if( i%2 == 0 ) {
			address.setName(String.format("민수%d", i+1));
			address.setSex("남");
			//Address2에는 민수가 중복존재함
			isAddRepo2 = true;
			address2.setName(String.format("민수%d", i+1));
			address2.setSex("남");
			address2.setAge(y);
			address2.setAddress( addArray[i%6] );
			address2.setPhoneNbr(String.format("010-3333-1%04d", i));				
		}else {
			address.setName(String.format("영희%d", i+1));
			address.setSex("여");
			//Address2에는 여자가 없음
			isAddRepo2 = false;
		}
		
		if( i%3 == 0) {
			//Address2에는 철수만 있음
			isAddRepo2 = true;
			address2.setName(String.format("철수%d", i+1));
			address2.setSex("남");
			address2.setAge(y);
			address2.setAddress( addArray[i%6] );
			//철수의 전화번호는 다름
			address2.setPhoneNbr(String.format("010-2222-1%04d", i));
		}					
		addressRepo.save(address);
		if(isAddRepo2==true) addressRepo2.save(address2);		
	}					
}

순수 프로필정보를 가지고 중복체크 여부를 확인하기위한 샘플이며

합집합,교집합,차집합을 설명하기위해 id 값은 select에서 제외하였습니다.

합집합 구하기

SELECT address,age,name,phoneNbr,sex FROM db_example.address
UNION
SELECT address,age,name,phoneNbr,sex FROM db_example.address2;

100개와 67개의 합집합을 수행하여 167개가 나와야하는데 실제로는 134개의 결과만 나왔습니다.

이것은, 짝수번째(3의배수가 짝수번째가되는상황제외)에서 복제를 하였기때문에 중복을 제거한 합이기 때문입니다.

UNION에서 중복은 제거된다라는 속성은 중요한 내용이며, 지정된 전체필드에서 중복을 찾는다란 것은

아주 큰 비용이드는 연산이 될것입니다. 중복을 무시할시 UNION ALL을 사용합니다.

그럼 UNION수행을 JPA에서는 어떻게 구현해야하는지 확인해보겠습니다.

JPA에서 UNION 사용하기

아쉽게도 JPQL에서 UNION쿼리를 지원하지 않으며, 유니온된 결과 역시Entity에 정의하지 않은

새로운 튜플이기 때문에, 맵핑처리도 원활하지 않습니다.

Native 쿼리 방식을 사용하여 오브젝트를 변환하는 전통적인 방법을 그대로 사용해해보겠습니다.

public interface AddressRepo2 extends CrudRepository<Address2, Long>{	
	@Query(value="SELECT address,age,name,phoneNbr,sex FROM db_example.address " + 
			"UNION " + 
			"SELECT address,age,name,phoneNbr,sex FROM db_example.address2 ",
			nativeQuery = true)
	List<?> makeUniOn();	//이 함수를 통해, UNION의 결과를 볼수가 있습니다.
}


List<?> addressUnion =  addressRepo2.makeUniOn();
ObjectMapper mapper = new ObjectMapper();
addressUnion.forEach( item ->{
	try {
		String jsonInString = mapper.writeValueAsString(item);
		System.out.println(jsonInString);				
	}catch(Exception e) {				
	}
});

결과:

사용방식에 만족스럽진 못하지만, 동일한 기능을, JPA Repository 인터페이스를 활용하여 수행하였습니다.

교집합 구하기

SELECT address,age,name,phoneNbr,sex FROM db_example.address a1
WHERE a1.name IN (SELECT name FROM db_example.address2);

SELECT a.address,a.age,a.name,a.phoneNbr,a.sex 
FROM db_example.address a, db_example.address2 b 
WHERE a.name = b.name;


SELECT address,age,name,phoneNbr,sex FROM db_example.address
INTERSECT
SELECT address,age,name,phoneNbr,sex FROM db_example.address2;

차집합 구하기

SELECT address,age,name,phoneNbr,sex FROM db_example.address a1
WHERE a1.name NOT IN (SELECT name FROM db_example.address2);


SELECT address,age,name,phoneNbr,sex FROM db_example.address
EXCEPT
SELECT address,age,name,phoneNbr,sex FROM db_example.address2;

UNION VS JOINS

이후 JOIN을 별도로 설명을 하겠지만, UNION과 JOIN은 아래와 같은 큰차이가 있습니다.

윈도우 함수

MYSQL 5에서는 윈도우 함수가 지원되지 않아, Postgres 를 이용하였습니다. 

윈도우처리는 논리적인 파티셔닝을 의미하며, MYSQL에서 PARTITION이란 키워드는

성능을 위한 실제 물리적인 파티셔닝을  의미해서 동일한 역활을 하는 쿼리를 찾기가 어려웠습니다.

Oracle과 SQL문호환성이 높은 Postgres DB를 추가로 설정하였으며

다중DB 환경설정 에서 두가지 DB를 설정하여 운영하는 방법을 정리하였으니 참고하세요 

여기서 윈도우란 의미는 OS윈도우와 상관없으며, 특정 구간을 ROW단위로 중간집계할수 있는기능으로

논리적인 방식으로 윈도우 파티션되었다라고 이해하면 될것같습니다.

이와 같이 윈도우 함수는  복잡한 집계및 집약처리에 유용하게 사용이되며, 성능계에서 특히 효율적인 집계를 위해

유용하게 사용이됩니다. 호환성의 관점에서는 Group BY에의한 집계 함수는

대부분의 DB에서 표준으로 사용가능하지만  논리적인 파티션 기능인 윈도우함수는 SQL표준이 모두 적용이 안되어 있으며

DB마다 사용방법과 지원함수가 각각 틀려서 JPA에서 표준인터페이스가 존재하지 않으며, ORM이란 특성에도 적합하지 않는

명령문입니다. 그래서 윈도우함수는 네이티브 SQL로 사용하거나 JPQL을 사용하기위해서는 방언 설정이 필요하게됩니다.

어플리케이션에서 랭킹처리하기

SQL문으로 간단하게 처리할수 있고 성능적으로도 우수한, 윈도우함수 활용이 권장이 되지만

만약 사용하는 DB가 윈도우함수를 지원하지 못하고, 유사한 기능수행을 위해 성능이 느린 SQL문 작성이 요구될수도있습니다.

이경우 데이터베이스로부터 랭킹 데이터를 Read를 하고 어플리케이션 레벨에서 랭킹을 할당하는 방식을 구현해보겠습니다.

public class AddressAgeRank {

	private String name;
	
	private int	   age;

	private int	   rank;
	
	public AddressAgeRank(String name, int age, int rank) {
		this.name = name;
		this.age = age;
		this.rank = rank;
	}
	
	@Override
    public String toString() {
        String result = String.format(
                "AddressAgeRank[name='%s',age=%d,rank=%d]%n",
                name, age,rank);        
        return result;
    }
}

랭킹에 사용되는 원본 데이터의 조건을 findByAgeBetween 을통해 10세에서 90미만의

데이터로 검색조건을 축소하였으며  이후에 sorted , map식을 사용하여

랭킹을 부여한후 우리가 원하는 랭킹 데이터로 변환하게 됩니다.

람다식을 활용하였습니다.

	public void RankTest() {		
	     int[] score = {Integer.MIN_VALUE};
	     int[] no = {0};
	     int[] rank = {0};	
	     List<AddressAgeRank> ageRankList =  
				addressRepo.findByAgeBetween(10, 90).stream()
				.sorted((a,b) -> b.getAge() - a.getAge() )
				.map(p -> {
		             ++no[0];
		             if (score[0] != p.getAge()) rank[0] = no[0];
		             return new AddressAgeRank(p.getName(),score[0] = p.getAge(),  rank[0] );					
				})
				.collect(Collectors.toList());
	     
	     ageRankList.forEach(item ->{
	    	 System.out.println(item.toString());	    	 
	     });	    
	}