[TIL]같은 데이터set에서 특정 조건값 추출

Product Price at a Given Date

1.  "2019-08-16" 기준으로 제품별 최근 갱신일, 해당 일자에 갱신된 가격을 추출하고, 원본 데이터의 제품에 붙여넣음

# 2019-08-16 기준 ,product_id별 최근 갱신일
with pp as (SELECT product_id, max(change_date) Mdate
            FROM Products 
            WHERE change_date <= '2019-08-16'
            GROUP BY product_id
            )
# product_id별 최근 갱신일에 해당하는 최근 갱신 가격 
, ppp as (select p.product_id, new_price, change_date 
          from Products p ,pp
          where p.product_id = pp.product_id 
                and p.change_date = pp.Mdate
)            
# 현재 기준 모든 제품과 별 2019-08-16 기준 가격
select p.product_id
       , coalesce(ppp.new_price,10) price 
from Products p
    left join ppp
    on p.product_id  = ppp.product_id 
group by p.product_id

 

2. "2019-08-16" 이전과 이후 데이터를 구분해 union

# "2019-08-16" 이후 신규 제품
select distinct product_id
       , 10 as price
from products
group by product_id
# 최초의 변경일자가 2019-08-16 이후 = 2019-08-16이후에 처음 들어온 제품임
having min(change_date) > "2019-08-16"

union

# "2019-08-16" 이전 제품의 가장 최근 가격
select product_id
       , new_price
from Products 
                                # "2019-08-16" 이전 제품의 가장 최근 갱신 일
where (product_id, change_date) in( select product_id
                                    , max(change_date) as recent_date
                                from Products
                                where change_date <= "2019-08-16"
                                group by product_id
                                )

 

 

Employees Whose Manager Left the Company

1. not in 조건절 사용해서 같은 테이블의 조건추출하기

select employee_id 
from ( # 급여가 엄격히 낮은 직원 $30000
        select employee_id, manager_id
        from Employees 
        where salary < 30000
) s
# 관리자가 회사를 떠난 직원의 ID
where manager_id NOT IN (
                         SELECT employee_id FROM Employees
)
# 정렬하여 반환합니다 employee_id.
ORDER BY employee_id

 

2. self join이용

SELECT e1.employee_id
FROM Employees E1
     LEFT JOIN Employees E2
     # manager_id 기준 데이터 조회하기
     ON E1.manager_id  = E2.employee_id
# salary < 30000
WHERE e1.salary < 30000 
    AND e2.employee_id IS NULL 
    AND e1.manager_id IS NOT NULL
ORDER BY employee_id