[240220] SQL: 코드카타 123~124(not exists, offset, group_concat) & 파이썬: 코드카타 31~32(zip)

[SQL 코드가타] 

1. 176. Second Highest Salary

https://leetcode.com/problems/second-highest-salary

 

1) 어떤 문제가 있었나

급여가 두 번 째로 높은 값만 추출하는 문제로 두 번째 값이 없으면 null 반환

 

2) 내가 시도해본 건 무엇인가

rank로 급여액에 따라 순위를 매기고, 랭킹 숫자가 2이면 salary 칼럼 값을, 아니면 null을 출력하는 쿼리 작성 

> 값이 하나일 때 null 처리가 안 되고, 값이 빈 상태로 출력되는 오류 발생 

select if(ranking = 2, salary, 'null') as SecondHighestSalary
from (select salary,
            rank() over(order by salary desc) ranking 
        from Employee) rank_table
where ranking = 2;

 

3) 어떻게 해결했나

- 구글링을 통해 조회 결과가 없을 때 값을 출력시키기 위해 1) 집계함수 2) union all 활용법이 있다는 사실을 알고 적용해봄   

- 값이 같은 경우 같은 순위를 부여하도록 dense_rank를 적용해야 했음

- 2) 쿼리의 경우 랭킹이 중복인 경우 값이 별도 행 값으로 출력되어, limit 값을 서브쿼리와 본 쿼리에 모두 적용해야 했음 

 

# 집계 함수를 활용한 방법
select if(ranking = 2, salary, sum(salary)) as SecondHighestSalary
from (select salary,
            dense_rank() over(order by salary desc) ranking 
        from Employee) rank_table
where ranking = 2;

# union all을 활용한 방법
# with절로 ranking 테이블 별도 생성 
with ranking_table as (select salary,
                             dense_rank() over(order by salary desc) ranking 
                         from Employee) 

# ranking 2이면 0을 출력하고, 2인 값이 없으면 null을 출력하여 union all
select salary as SecondHighestSalary
from Employee
where salary = (select salary
                    from ranking_table
                    where ranking=2
                    limit 1) 
union all 
select null as SecondHighestSalary
from Employee
where not exists (select salary
                    from ranking_table
                    where ranking=2
                    limit 1)
limit 1;

 

4) 무엇을 새롭게 알았나

- 값이 없을 때 '집계함수'를 쓰면 null이 반환된다는 사실을 알게됨

- where 절에 값이 없는 것에 대한 조건을 달 때는 'not exists'를 쓰면 됨 

- limit와 offset을 적절히 활용하여 두 번째 값 추출이 가능했음

└ (활용법) Select  칼럼명 from 테이블명 order by 칼럼명 desc + ①~ ③

  ① LIMIT n; n개의 행 출력 (n=1, 맨 위의 1행 출력) 

  ② LIMIT m, n;  m번째 행부터 n개의 열 출력 (m=1, n=2, 2번째 행부터 2개의 행 출력)

  ③ LIMIT n OFFSET M; m번째 행부터 n개의 열 출력  (m=1, n=2, 2번째 행부터 2개의 행 출력) 

# select를 한 번 더 감싸서 값이 없을 때는 null이 나오도록 조정
select
(select distinct Salary 
    from Employee
    order by salary desc 
    limit 1 offset 1) 
as SecondHighestSalary;

 

- max 값을 활용해서 두 번째로 높은 값을 출력 > 집계함수 활용으로 없는 값은 null 처리됨 

# max의 max를 구하기
SELECT MAX(salary) AS SecondHighestSalary 
FROM Employee 
WHERE salary < (SELECT MAX(salary)FROM Employee);

# inner join 조건절 활용해 2순위 max값 구하기 
SELECT MAX(e1.salary) AS SecondHighestSalary 
FROM Employee e1 
INNER JOIN Employee e2 
ON e1.salary < e2.salary;

 

[+] LeetCode에서 코드를 제출까지 해야 여러 상황에 대한 오류 여부 파악이 가능했음! 

 

2. 1484. Group Sold Products By The Date

https://leetcode.com/problems/group-sold-products-by-the-date/

 

1) 어떤 문제가 있었나
- 날짜별로 판매된 품목의 수량과 이름을 출력하는 문제

- 마지막 열에 여러 칼럼에 들어가 있는 문자열 값을 합쳐서 쉼표로 구분 &  제품 이름은 오름차순으로 정렬

 

2) 내가 시도해본 건 무엇인가
- 구글링을 통해 string_agg 함수가 있다고 하여 시도해보았으나, 버전이 다른지 오류 발생함

select sell_date, 
        count(distinct product) num_sold, 
        string_agg(distinct product, ','), within group (order by sell_date) products
  from Activities 
  group by sell_date  
  order by sell_date asc

 

- 테이블을 join 하여 concat으로 제품명을 모아둔 칼럼을 생성하였으나 제품 이름 순으로 정렬이 안되어 오답처리 

with product_name as (   
select a1.sell_date, concat_ws(',', a1.product,a2.product,a3.product) products                     
from Activities a1
left join Activities a2 on a1.sell_date=a2.sell_date and a1.product<>a2.product
left join Activities a3 on a1.sell_date=a3.sell_date and a2.product<>a3.product and a1.product<>a3.product
group by a1.sell_date 
order by products)

select sell_date, 
        count(distinct product) num_sold, 
        products                     
  from Activities 
  left join product_name using(sell_date)
  group by sell_date  
  order by sell_date;

3) 어떻게 해결했나

- 다시 구글링을 통해 group_concat 함수가 있다는 것을 알고 다시 시도 

select sell_date, 
        count(distinct product) num_sold, 
        group_concat(distinct product) products
  from Activities
  group by sell_date  
  order by sell_date;

4) 무엇을 새롭게 알았나

- group_cocnat 을 사용하면 그룹 내 여러 문자열을 합쳐서 볼 수 있음 

└ 기본형 : GROUP_CONCAT(칼럼명)
└ 구분자 변경 : GROUP_CONCAT( 칼럼명 , '구분자')
└ 중복제거 : GROUP_CONCAT(DISTICT 칼럼명 )
└ 문자열 정렬 : GROUP_CONCAT(칼럼명 ORDER BY 칼럼명) 

 

---

[파이썬 코드가타] 

1. 수박수박수박수박수박수?

https://school.programmers.co.kr/learn/courses/30/lessons/12922?language=python3

 

1) 어떤 문제가 있었나

홀수일 때는 '수', 짝수일 때는 '박'이 추가되도록 함수 작성 

 

2) 내가 시도해본 건 무엇인가

for 문에 %2로 홀짝을 구분하여 각 음절이 하나씩 추가되도록 구성 

 

3) 어떻게 해결했나

def solution(n):
    answer = ''
    for i in range(1, n+1):
        if i % 2 != 0:
            answer += '수'
        else: 
            answer += '박'
    return answer

 

4) 무엇을 새롭게 알았나

- str을 아예 곱하거나 더해서 정답 도출 가능 

- range를 잘 쓰면 시작을 홀수로 할수도, 짝수로 할수도 있음 

## 다른 사람 풀이 
#01 
#str 값이 많이 늘어남
def water_melon(n):
    str = "수박"*n
    return str[:n]
    
#02
#[i%2]이 짝수면 '수', 홀수면 '박'이 출력
#range(n)으로 설정해서 첫 값이 0, 즉 '수'가 들어가도록 조정
def solution(n):
    return "".join(["수박"[i%2] for i in range(n)])

#03
#짝수면 몫 만큼 '수박'을 곱해주고, 홀수면 마지막에 '수'를 더하기
def water_melon(n):
    return "수박" * (n//2) + "수" * (n%2)

 

 

2. 내적

https://school.programmers.co.kr/learn/courses/30/lessons/70128?language=python3

 

1) 어떤 문제가 있었나

- 내적(Dot Product)이란?

└ 정규화된 하나의 벡터(vector, 크기와 방향을 갖는 물리량)가 다른 벡터에 투영된 길이 값

└ 각 벡터를 Normalize한 후 스칼라 곱의 결과값인데, 그 값은 두 벡터의 코사인 값과 일치

└ 내적 계산을 통해 음수인지, 양수인지, 각도의 크기는 얼마인지 도출 가능

출처: https://tartist.tistory.com/81

 

- 문제에서 a와 b의 내적은 a[0]*b[0] + a[1]*b[1] + ... + a[n-1]*b[n-1] 로 계산(n은 a, b의 길이)

 

2) 내가 시도해본 건 무엇인가

- len 함수로 각 변수의 길이 값을 n으로 지정

- answer 변수에 for문으로 순서대로 꺼낸 숫자를 인덱스에 넣은 뒤, 나온 숫자 누적합 

 

3) 어떻게 해결했나

def solution(a, b):
    n = len(a)
    answer = 0
    for i in range(0, n):
        answer += a[i]*b[i]    
    return answer

 

4) 무엇을 새롭게 알았나

- 다른 사람 풀이를 통해 zip 함수를 확인

# 문제 정답
def solution(a, b):
    return sum([x*y for x, y in zip(a,b)])

└ zip이란? 두 그룹의 데이터를 서로 엮어주는 파이썬 내장 함수

 : 여러 개의 반복 가능한(iterable) 객체를 인자로 받은 다음, 각 객체가 가진 원소를 듀플 형태로 차례차례 접근하여 반복자를 반환 

 : zip은 길이가 다를 때는 짧은 인자 기준으로만 엮고 나머지는 버리므로 사용상 주의 필요 

# zip으로 2개 변수의 값을 하나씩 꺼내서 반환 
>>> numbers = [1, 2, 3]
>>> letters = ["A", "B", "C"]
>>> for pair in zip(numbers, letters):
...     print(pair)
...
(1, 'A')
(2, 'B')
(3, 'C')

#  zip은 인덱스를 활용해 하나씩 처리한 것과 결과 값이 똑같음 
>>> for i in range(3):
...     pair = (numbers[i], letters[i])
...     print(pair)
...
(1, 'A')
(2, 'B')
(3, 'C')

# (인자를 풀 때는 zip) 다시 해체하고 싶을 때는 unzip 
>>> pairs = [(1, 'A'), (2, 'B'), (3, 'C')]
>>> numbers, letters = zip(*pairs)
>>> numbers
(1, 2, 3)
>>> letters
('A', 'B', 'C')

# zip을 활용해 딕셔너리 형태 만들기도 가능!
>>> keys = [1, 2, 3]
>>> values = ["A", "B", "C"]
>>> dict(zip(keys, values))  # dic으로 마지막 감싸주기 
{1: 'A', 2: 'B', 3: 'C'}

 

 

* zip 함수 설명 출처: https://www.daleseo.com/python-zip/