CodeKata

SQL

38번 풀고 팀 활동

• 작성한 쿼리

SELECT
  order_id
  , product_id
  , DATE_FORMAT(out_date, '%Y-%m-%d') AS out_date
  , CASE
    WHEN out_date IS NULL THEN '출고미정'
    WHEN out_date < '2022-05-02' THEN '출고완료'
    ELSE '출고대기'
  END AS "출고여부"
FROM
  food_order
ORDER BY
  order_id
;

• 추가: CTE

WITH fo AS (
    SELECT  order_id
            , product_id
            , DATE_FORMAT(out_date, '%Y-%m-%d') out_date
    FROM food_order
    ) 
SELECT  fo.*
        , CASE
            WHEN out_date <= '2022-05-01' THEN '출고완료'
            WHEN out_date > '2022-05-01' THEN '출고대기'
            ELSE '출고미정'
        END "출고여부"
FROM fo
ORDER BY order_id;

• 추가: 인라인 뷰 서브쿼리

SELECT  fo.*
        , CASE
            WHEN out_date <= '2022-05-01' THEN '출고완료'
            WHEN out_date > '2022-05-01' THEN '출고대기'
            ELSE '출고미정'
        END "출고여부"
FROM (
    SELECT  order_id
            , product_id
            , DATE_FORMAT(out_date, '%Y-%m-%d') out_date
    FROM food_order
    ) fo
ORDER BY order_id;

2022년 5월 1일까지 출고완료를 어떻게 표현하는 게 좋을까?

• out_date < '2022-05-02'
• out_date <= '2022-05-01'

팀 활동 인사이트

• UNION으로도 풀 수 있다는 거 아시나요?

(
SELECT order_id, product_id, date_format(out_date,'%Y-%m-%d') as out_date, '출고완료' as '출고여부'
FROM food_order
where out_date<='2022-05-01'
) 
union
(
SELECT order_id, product_id, date_format(out_date,'%Y-%m-%d') as out_date, '출고대기' as '출고여부'
FROM food_order
where out_date>'2022-05-01'
) 
union
(
SELECT order_id, product_id, date_format(out_date,'%Y-%m-%d') as out_date, '출고미정' as '출고여부'
FROM food_order
where out_date is null
) 

82. Replace Employee ID With The Unique Identifier

• 작성한 쿼리

SELECT
  unique_id
  , name
FROM
  employees e
  LEFT JOIN employeeUNI eu
  USING(id)

참고할 만한 다른 풀이

• right join과 on

SELECT unique_id, name from EmployeeUNI as eu
right join Employees as e
On e.id = eu.id;

• 스칼라 서브쿼리

SELECT (
    SELECT 
        unique_id 
    FROM 
        EmployeeUNI
    WHERE
        EmployeeUNI.id = Employees.id
    ) AS unique_id
    , name
FROM 
    Employees
;

83. Product Sales Analysis I

• 작성한 쿼리

SELECT
  product_name
  , year
  , price
FROM
  sales s
  JOIN product p
  USING(product_id)

참고할 만한 다른 풀이

• left join

select b.product_name , a.year , a.price from Sales a left join Product b on a.product_id=b.product_id;

Python

26. 음양 더하기

• 작성한 코드

def solution(absolutes, signs):
    answer = []
    for i in range(len(absolutes)):
        if signs[i]:
            answer.append(absolutes[i])
        else:
            answer.append(-absolutes[i])
    return sum(answer)

def solution(absolutes, signs):
    answer = 0
    for i in range(len(absolutes)):
        if signs[i]:
            answer += absolutes[i]
        else:
            answer += absolutes[i]*(-1)
    return answer

참고할 만한 다른 풀이

• zip() 활용
  • 다중 리스트에서 순서대로 요소를 뽑음

def solution(absolutes, signs):
    answer=0
    for absolute,sign in zip(absolutes,signs):
        if sign:
            answer+=absolute
        else:
            answer-=absolute
    return answer

→ one-line if절

def solution(absolutes, signs):
    return sum(absolutes if sign else -absolutes for absolutes, sign in zip(absolutes, signs))

Python 라이브 세션

5회차

SDL

Pandas 이것저것 정리

회고

• .io 도메인 폐지 가능성
  • velog 글을 tistory로 백업해두어야겠다.
    • 하나씩 옮기면서 복습하면 딱 좋을 듯
• ADsP 외울 게 많아서 힘들다...