본문 바로가기
카테고리 없음

집약

DRAGON.Q 2019. 5. 30. 22:54

합쳐서 하나

CREATE TABLE PriceByAge
(product_id VARCHAR(32) NOT NULL,
 low_age    INTEGER NOT NULL,
 high_age   INTEGER NOT NULL,
 price      INTEGER NOT NULL,
 PRIMARY KEY (product_id, low_age),
   CHECK (low_age < high_age));
   
INSERT INTO PriceByAge VALUES('제품1',  0  ,  50  ,  2000);
INSERT INTO PriceByAge VALUES('제품1',  51 ,  100 ,  3000);
INSERT INTO PriceByAge VALUES('제품2',  0  ,  100 ,  4200);
INSERT INTO PriceByAge VALUES('제품3',  0  ,  20  ,  500);
INSERT INTO PriceByAge VALUES('제품3',  31 ,  70  ,  800);
INSERT INTO PriceByAge VALUES('제품3',  71 ,  100 ,  1000);
INSERT INTO PriceByAge VALUES('제품4',  0  ,  99  ,  8900);

제품을 연령별로 관리하는 테이블이 있다고 할 때 0 ~ 100세 까지 모든 연령이 가지고 놀 수 있는 제품을 구하라

 

먼저 처음 생각해본것은 상품별로 그룹을 맺고 low_age의 max값과 high_age의 min값을 구하여 더한 다음 100과 같거나 크다면

상품을 구하도록 함.

 

    select 
     case 
      when max(low_age) + min(high_age) >= 100
       then product_id
      end     
    from priceByAge
    group by product_id

위와 같이 구현하면 null상품이 나오긴 하지만 null도 같이 나오기 때문에 having에 조건을 넣어줌

select 
     producti_id 
    from priceByAge
    group by product_id
    having 
    max(low_age) + min(high_age) >= 100  ;

위와 같이 구현하는것도 결과는 나오지만 더 좋은 방법은 high+age에서 low_age를 뺀 값을 모두 SUM해서  +1 을 해서 101이 나오면 됨

select product_id
      from priceByAge
      group by product_id
      having sum(high_age - low_age + 1 ) = 101;

응용 문제

 - 호텔에서 10일 이상 묶는 방번호 구하기

CREATE TABLE HotelRooms
(room_nbr INTEGER,
 start_date DATE,
 end_date   DATE,
     PRIMARY KEY(room_nbr, start_date));


INSERT INTO HotelRooms VALUES(101, '2008-02-01', '2008-02-06');
INSERT INTO HotelRooms VALUES(101, '2008-02-06', '2008-02-08');
INSERT INTO HotelRooms VALUES(101, '2008-02-10', '2008-02-13');
INSERT INTO HotelRooms VALUES(202, '2008-02-05', '2008-02-08');
INSERT INTO HotelRooms VALUES(202, '2008-02-08', '2008-02-11');
INSERT INTO HotelRooms VALUES(202, '2008-02-11', '2008-02-12');
INSERT INTO HotelRooms VALUES(303, '2008-02-03', '2008-02-17');
select room_nbr, sum(end_date - st
from hotelrooms
group by room_nbr
having sum(end_date - start_date) >= 10;

 

자르기

 

CREATE TABLE Persons
(name   VARCHAR(8) NOT NULL,
 age    INTEGER NOT NULL,
 height FLOAT NOT NULL,
 weight FLOAT NOT NULL,
 PRIMARY KEY (name));


INSERT INTO Persons VALUES('Anderson',  30,  188,  90);
INSERT INTO Persons VALUES('Adela',    21,  167,  55);
INSERT INTO Persons VALUES('Bates',    87,  158,  48);
INSERT INTO Persons VALUES('Becky',    54,  187,  70);
INSERT INTO Persons VALUES('Bill',    39,  177,  120);
INSERT INTO Persons VALUES('Chris',    90,  175,  48);
INSERT INTO Persons VALUES('Darwin',  12,  160,  55);
INSERT INTO Persons VALUES('Dawson',  25,  182,  90);
INSERT INTO Persons VALUES('Donald',  30,  176,  53);

 

- 이름 앞글자로 자르기

select count(*),substr(name, 0, 1)
from persons
group by substr(name, 0, 1);

 

파티션

 - GROUP_BY 구로 잘라 만든 하나하나의 부분 집합을 파티션이라고 함

 

- 나이 기준으로 어린이(20세 미만), 성인(20 ~69세), 노인(70세 이상)

 

처음 생각한것은 case 구문으로  age를 비교하여 테이블을 만들고 그 테이블을 한번 감싸서 group by 하는것으로 해결 하였다.

select p.age_label, count(p.age_label) from (
select 
      name 
     ,case
        when age < 20 
        then '어린이'
        when age between 20 and 69
        then '성인'
        when age > 70
        then '노인'
     end as age_label
     from persons
     ) p
     group by p.age_label;

다른 방법으로는  group by 에 case구문을 넣고 파티션을 하면 된다.

사실 간결한지는 잘 모르겠다 

 

 

Partition By로 자르기 

 - Partition By 를 사용하면 Group By를 쓸 때 처럼 집약 기능이 없다 이것 빼면 다른건 동일 하다

 

select name, age,
             case
        when age < 20 
        then '어린이'
        when age between 20 and 69
        then '성인'
        when age > 70
        then '노인'
        end as age_label,
        
        rank() over(partition by case
        when age < 20 
        then '어린이'
        when age between 20 and 69
        then '성인'
        when age > 70
        then '노인'
        end  order by age) age_rank
        from persons
        ORDER BY age_label desc , age_rank;

 

티스토리툴바