2024-01-08

SQL 코트카타

 

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기(SQL) (concat, max, where절 서브쿼리 조건문, join)

https://school.programmers.co.kr/learn/courses/30/lessons/164671

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.

SELECT concat('/home/grep/src/',brd.BOARD_ID,'/',file.FILE_ID,file.FILE_NAME,file.FILE_EXT) FILE_PATH
FROM USED_GOODS_FILE file join USED_GOODS_BOARD brd
    on file.BOARD_ID = brd.BOARD_ID
WHERE brd.VIEWS = (select max(brd_sub.VIEWS) 
                    from USED_GOODS_BOARD brd_sub 
                    where brd_sub.VIEWS=VIEWS)
ORDER BY file.FILE_ID desc

우선 최종적으로 제출한 쿼리는 위와 같고 간단히 설명하면 USED_GOODS_FILE (이하file)테이블과 USED_GOODS_BOARD(이하 brd)테이블을 BOARD_ID를 공통 컬럼으로 join해준 데이터를 가져와서 view가 제일 높은 값이 얼마인지 출력되는 서브쿼리문을 작성하여 view컬럼에 대한 where절의 조건으로써 사용하였다 그뒤 파일경로를 출력해주기 위하여 concate 함수? 명령어?를 사용하여 /home/grep/src/와 게시판id(brd.BOARD_ID), 파일id(file.FILE_ID), 파일이름(file.FILE_NAME), 파일확장자(file.FILE_EXT)를 합쳐주어 표시되게 하였다

여담으로 프로그래머스에서는 작성할 때 보라색으로 표시가 안되어서 몰랐는데 노션 코드부분에서 파랗게 표시되는 것을 보니 file도 무슨 함수?명령어?로 있나보다.. 그리고 노션에선 표시가 되지 않았는데 프로그래머스에서는 VIEW나 VIEWS가 보라색 글씨로 표시되었다. 아마 뷰만들 때 사용하는 것 때문에 그렇게 표시된 듯하다. 다행히 이 둘에 관련해서는 에러가 나지 않았지만, 다음에 쓸 일이 있다면 염두해두고 가능하면 피해서 사용해야 할 듯 하다.

SELECT concat('/home/grep/src/',brd.BOARD_ID,'/',file.FILE_ID,file.FILE_NAME,file.FILE_EXT) FILE_PATH
FROM USED_GOODS_FILE file join USED_GOODS_BOARD brd on file.BOARD_ID = brd.BOARD_ID
WHERE brd.VIEWS = max(brd.VIEWS)
ORDER BY file.FILE_ID desc

제일 처음 max에 대해 조금 애매한 감이 있지만 max는 집계함수지만 따로 group을 안 나눠주고 max만 사용해도 전체 중 가장 큰 값에 대해 출력이 정상적으로 작동하는 듯하여 조건에 바로 넣고 사용 했더니 max는 group function이라고 안 된다고 에러가 났었다. 그래서 어쩔 수 없이 서브쿼리 형태를 사용해서 하기 위해 아래와 같이 수정하였다

SELECT concat('/home/grep/src/',brd.BOARD_ID,'/',file.FILE_ID,file.FILE_NAME,file.FILE_EXT) FILE_PATH, file.BOARD_ID
FROM USED_GOODS_FILE file join USED_GOODS_BOARD brd on file.BOARD_ID = brd.BOARD_ID
WHERE brd.VIEWS = (select max(brd_sub.VIEWS) from USED_GOODS_BOARD brd_sub where brd_sub.BOARD_ID=brd.BOARD_ID)
ORDER BY file.FILE_ID desc

서브쿼리를 처음 써줄 때 연결을 어떻게 해줘야하지? 전체 테이블로 하기에는 따로 join한 테이블에 대해 이름 붙이는 법을 정확히 모르고, 혹시 join전 테이블에 대하여 연결해두면 그것도 잘 연결 될까 해서 해보았지만, 사실상 의미가 없는 듯이 전혀 필터가 적용되지 않은 형태로 출력되었다

SELECT concat('/home/grep/src/',brd.BOARD_ID,'/',file.FILE_ID,file.FILE_NAME,file.FILE_EXT) FILE_PATH
FROM USED_GOODS_FILE file join USED_GOODS_BOARD brd on file.BOARD_ID = brd.BOARD_ID
WHERE brd.VIEWS = (select max(brd_sub.VIEWS) from USED_GOODS_BOARD brd_sub where brd_sub.VIEWS=VIEWS)
ORDER BY file.FILE_ID desc

그래서 전에 굳이 두 개다 테이블명.연결할 컬럼으로 적지 않고 한쪽만 적어도 됬던 것 같았기에, 그 부분을 이용해서 연결해주었다 이 것을 (들여쓰기정도만 살짝 수정해서) 최종쿼리로 제출하였다

SELECT concat('/home/grep/src/',brd.BOARD_ID,'/',file.FILE_ID,file.FILE_NAME,file.FILE_EXT) FILE_PATH
FROM USED_GOODS_FILE file join USED_GOODS_BOARD brd on file.BOARD_ID = brd.BOARD_ID
WHERE brd.VIEWS = (select max(brd_sub.VIEWS) from USED_GOODS_BOARD brd_sub where brd_sub.BOARD_ID=BOARD_ID)
ORDER BY file.FILE_ID desc

그리고 연결해줄 때 항상 기준으로 출력되는 컬럼에 대해서 해준 듯하여 기본키로 쓰일 수 있을만한 컬럼에 대하여도 연결해보았다 결과는 동일하게 잘 나왔다

그리고 혹시 그냥 단순하게 join한 테이블에 다른 것 alias붙여주듯 붙여주면 되는가 해서 해보았지만 아래와 같이 에러가 났었다

SELECT concat('/home/grep/src/',brd.BOARD_ID,'/',file.FILE_ID,file.FILE_NAME,file.FILE_EXT) FILE_PATH
FROM (USED_GOODS_FILE file join USED_GOODS_BOARD brd on file.BOARD_ID = brd.BOARD_ID) main
WHERE brd.VIEWS = (select max(brd_sub.VIEWS) from USED_GOODS_BOARD brd_sub where brd_sub.BOARD_ID=main.BOARD_ID)
ORDER BY file.FILE_ID desc
#이렇게 할시
#SQL 실행 중 오류가 발생하였습니다.
#You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'main
#WHERE brd.VIEWS = (select max(brd_sub.VIEWS) from USED_GOODS_BOARD brd_sub ' at line 3
#메인에서 from절 ()없애도 보고 괄호 있는 채로 AS도 붙여봤지만 에러동일하게 발생

join한 테이블에 대해서는 alias 못 붙여주는 것인지? 

 

주문량이 많은 아이스크림들 조회하기(SQL) (right join, limit)

https://school.programmers.co.kr/learn/courses/30/lessons/133027

7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요. 라는 문제이다.

SELECT JULY.FLAVOR
FROM FIRST_HALF fst right join JULY on fst.SHIPMENT_ID = JULY.SHIPMENT_ID
GROUP BY JULY.FLAVOR
ORDER BY SUM(fst.TOTAL_ORDER)+SUM(JULY.TOTAL_ORDER) desc
LIMIT 3

문제 난이도에 비해서 초반에 join할 때 =를 and로 써서 왜 안되지 왜 이상하게 나오지? 라면서 머리 싸매다가 시간 날려버렸다.. 째든 간단히 쿼리를 설명하자면

먼저 FIRST_HALF (이하 fst) 테이블에 JULY 테이블을 SHIPMENT_ID을 공통 컬럼으로 하여 right join해주었다(SHIPMENT_ID에 대하여 july테이블이 209라는 값이 더 있어서 혹시 몰라서 이걸로 해주었다, 문제에도 7월에는 주문량이 많아 같은 맛을 다른 두 공장에서 출하하는 경우가 있다고 하여 확인해봤었다) 그 뒤 맛(FLAVOR)에 대하여 그룹을 지어주고 맛을 출력하는데 이 때 그룹별 상반기 총 주문량의 합과 7월 총 주문량의 합을 더한 것 (SUM(fst.TOTAL_ORDER)+SUM(JULY.TOTAL_ORDER))을 기준으로 내림차순 정렬해준뒤 위의 세개의 데이터만을 잘라서 출력해주었다

사실 이렇게 하는게 편하긴한데 빠른 건지는 모르겠다 직접 순위 매겨서 위에서 3개 까지 출력하도록 하는게 빠른 것인지 확인해볼 필요가 있을 듯 하다.

그리고 처음에 full outer join으로 해볼려고 했는데 특강시간에 배운대로 써보았지만 에러가 계속나고 원인을 찾지못하여 결국 right join으로 바꿔 사용하였는데 튜터님께 질문해 볼 생각이다.

 처음에 join하고 나서 on 뒤에 =를 써야한느데 실수로 and를 쓰고는 인지 못해서 뻘짓을 좀 오래했다

 

저자 별 카테고리 별 매출액 집계하기(SQL) (여러개 3개 join, date_format)

https://school.programmers.co.kr/learn/courses/30/lessons/144856

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요. 라는 문제이다.

SELECT BOOK.AUTHOR_ID,atr.AUTHOR_NAME, BOOK.CATEGORY,
        sum(BOOK.PRICE*s.SALES) TOTAL_SALES
FROM BOOK join AUTHOR atr on BOOK.AUTHOR_ID =atr.AUTHOR_ID
        join BOOK_SALES s on BOOK.BOOK_ID=s.BOOK_ID
WHERE date_format(s.SALES_DATE,'%Y-%m')='2022-01'
GROUP BY BOOK.AUTHOR_ID, BOOK.CATEGORY
ORDER BY BOOK.AUTHOR_ID, BOOK.CATEGORY desc

BOOK 테이블과 AUTHOR(이하 atr)테이블을 AUTHOR_ID를 공통 컬럼으로 하여 join하고 그리고 BOOK_SALES(이하 s)테이블과 BOOK_ID를 공통 컬럼으로 하여 join해준 데이터를 sale_date를 %Y-%m형태로 바꿨을 때 2022-01인 데이터에 대해서만 가져와서 저자별(BOOK.AUTHOR_ID), 카테고리별(BOOK.CATEGORY)로 그룹을 지어주었다 그 뒤 저자id, 저자명, 카테고리, 그룹의 판매수*가격 의 총합 하여 출력하는데 저자id순으로 먼저 오름차순하는데 같다면 카테고리별로 내림차순하도록 정렬해주었다.

 

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기(SQL) (where절 조건 서브쿼리문, date_format, month, 복습필요)

https://school.programmers.co.kr/learn/courses/30/lessons/151139

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.라는 문제이다.

SELECT month(hst.START_DATE) MONTH,hst.CAR_ID,
        count(hst.START_DATE) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY hst
WHERE 
    hst.CAR_ID=(SELECT hst_sub.CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY hst_sub
                WHERE date_format(hst_sub.START_DATE,'%Y-%m') 
                        between '2022-08' and '2022-10'
                GROUP BY hst_sub.CAR_ID
                HAVING count(hst_sub.START_DATE)>=5
                        and hst_sub.CAR_ID=hst.CAR_ID)
    and date_format(hst.START_DATE,'%Y-%m') 
            between '2022-08' and '2022-10'
GROUP BY month(hst.START_DATE),hst.CAR_ID
ORDER BY MONTH, CAR_ID desc

문제가 조금 아쉽다는 생각이 들었다 문제에서 설정하라는 범위인 22년 8월부터 22년 10월까지가 그냥 주어진 데이터의 범위와 일치하기 때문에 사실 결과값으로만 본다면 날짜관련 필터는 제외해도 동일하게 나온다. 데이터범위가 문제와 주어진게 달랐다면 좀 더 확실히 확인이 되었을텐데라는 생각이 들었다.

일단 쿼리문에 대해 간단히 설명하자면 우선 CAR_RENTAL_COMPANY_RENTAL_HISTORY테이블을 통해 데이터를 가져오는데 날짜가 22-08~22-10인 데이터만 필터해서 가져오고 car_id에 대하여 그룹지은 다음에 각 그룹에 대하여 시작날짜의 갯수가(사실 구별되는 아무 컬럼이나 해도 상관 없을 듯하다) 5개 이상인 데이터들에 대해서만 필터링해주었다 그리고 이 서브쿼리를 조건으로 사용하여 일치하는 Car_id에 대해서만 데이터를 가져오고 start_date의 월, car_id, 데이터의 갯수(월별, car_id별)를 출력하는데 월별로 오름차순, car_id별로 내림차순하여 정렬해 주었다

아래는 먼저 작성했었던 where에 쓰인 서브쿼리문이다.

SELECT hst_sub.CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY hst_sub
WHERE 
    date_format(hst_sub.START_DATE,'%Y-%m') between '2022-08' and '2022-10'
GROUP BY hst_sub.CAR_ID
HAVING count(hst_sub.START_DATE)>=5

 

그리고 또 살짝 애매모호한게 따로 특정월의 총 대여 횟수가 0인 경우를 제외하도록 쿼리를 작성해주지 않았는데 알아서 특정 월의 총 대여 횟수가 0인 경우가 나오지 않았는데 먼가 당연한 거같으면서도 우연인거 같기도 한데 지금 상태가 좀 안 좋은 편이라 다음 다시 확인해보는게 나을 듯 하다.

 

파이썬 코드카타

 

기능개발(python)

https://school.programmers.co.kr/learn/courses/30/lessons/42586

문제를 간략히 설명하자면 각 기능의 개발 진행도를 담은 리스트와 각 기능의 개발속도를 담은 리스트를 입력받아서 배포할 때 몇 개의 기능씩 배포를 하는지 리턴하는 문제이다.

def solution(progresses, speeds):
    answer = []
    if len(progresses)!=len(speeds): #혹시모를 입력데이터 이상방지
        print("주어진 데이터가 맞지 않습니다")
        return False
    
    complete=[]
    for cal in range(len(progresses)): #기능별 완성까지 몇일 걸리는 지 저장
        if (100-progresses[cal])%speeds[cal]==0:
            date_require=(100-progresses[cal])//speeds[cal]
        else:
            date_require=1+(100-progresses[cal])//speeds[cal]
        complete.append(date_require)
        
    while len(complete)>0: #첫 순서부터 기능별 완성까지 소요일 비교 및 배포가능 기능수 계산
        check=complete.pop(0)
        count=1
        while len(complete)>0:
            if check>=complete[0]:
                complete.pop(0)
                count+=1
            else:
                break
        answer.append(count)
        count=0
    return answer

코드에 대해 간단히 설명하자면 우선 제한조건에 progresses와 speeds의 길이가 같아야 한다는 조건이 없어서 혹시나 하고 넣어둔 것이고, 그 뒤 각 기능 별로 완성하는데 몇 일이 걸리는지 계산하여 저장해둔다. 그 뒤 젤 첫 기능부터 배포할 때 뒷 기능들 중 같이 배포 가능한 기능이 몇개나 있는지 세어주고 없다면(세어줄 때 같이 배포가 가능할 경우 리스트에서 제외시켜 준다) 세어준 값을 answer리스트에 추가, 그 다음 값에 대하여 같은 과정 반복하여 더이상 남은 기능이 없을 때까지 반복한다 그리고 최종적으로answer리스트를 리턴한다.

del, pop, remove차이점이 궁금해서 다시 찾아봤는데 대강 슥 한번 훑어보기만 해서 그런지 한 30%정도 이해된 듯하다 del은 뭔가 아예 메모리에 남아있던 것 자체를 지워버리는 느낌이라 메모리 확보에는 좋은 듯하지만 어차피 그 정도 데이터는 아직 다루지 않는 듯하고(?) 삭제하는 방식이 뭔가 슬라이싱 같은 느낌이 들어서 인덱스로 삭제하는 방식 중 pop을 활용하였다.

 

프로세스(python) (while, pop)

https://school.programmers.co.kr/learn/courses/30/lessons/42587#

문제를 간략히 설명하자면 프로세스 관리할 때 실행대기 큐에 프로세스들을 대기시키는데 각 프로세스들은 우선순위가 매겨져있고 큐에서 앞에서 부터 꺼내어 실행할지 확인하는데 꺼내었을 때 대기중인 프로세스 중에 꺼낸 프로세스보다 우선순위가 높은 것이 있을 경우 꺼낸 프로세스를 다시 맨 끝에 집어넣고, 없을 경우 실행시키는데, 우선순위와 몇 번째로 실행되는지 궁금한 프로세스의 큐의 위치가 주어지는데 이에 따른 궁금한 프로세스가 몇 번째로 실행되는지 답하는 문제이다.

def solution(priorities, location):
    answer = 0
    index=list(range(len(priorities))) #큐라고 생각(이름을 인덱스로)
    while len(index)>0:
        process=index.pop(0) #큐의 첫번째 프로세스
        pro_prior=priorities.pop(0) #큐의 첫번째 프로세스의 우선순위
        if len(priorities)!=0:
            if pro_prior>=max(priorities): #우선순위 가장 높거나 같다면 실행
                answer+=1
                if process == location :
                    return answer
            else:
                index.append(process)
                priorities.append(pro_prior)
        else: #마지막이라 더이상 비교할 대상 없을경우 그냥 마저 실행하고 +1
            return answer+1
                
    print("다 체크했는데 뭔가 오류가 있음!")
    return answer

코드에 대해 간략시 설명하자면 우선 큐에 들어있던 프로세스들의 원래 위치를 표시 하기 위해 index라는 순서의 의미가 담긴 리스트를 만들어준다. 그 뒤 index의 길이가 0이 될 때까지 반복을 해주는데, 먼저 index와 priorities에서 젤 첫 순서인 원소들을 꺼내어 비교할 값으로 변수에 저장해주고 더이상 뒤에 남아있는 프로세스가 없다면(남은 priorities의 길이가 0이라면) 그냥 마지막이니 더 비교하지말고 실행 뒤 실행했다고 한번더 세어주며 answer을 리턴한다. 하지만 남아있다면 큐안에 남아있는 프로세스 중 우선순위가 더 높은게 없다면 그냥 실행시키게 되고 실행했다고 +1해서 세어준다 그리고 실행했으니 이게 알고 싶던 프로세스인지 확인하고 맞으면 그대로 answer을 리턴해주고 아니라면 다시 반복해준다. 그리고 우선순위가 더 높은게 있다면 다시 큐의 젤 끝으로 집어 넣고 (이 때 우선순위 정보도 같이 끝에 다시 집어넣어준다) 반복문을 다시 반복한다. 그리고 마지막 두줄 프린트와 return은 사실상 정상적으로 입력하여 작동된다면 작동할 일이 없는 코드이다 하지만 혹시 모를 상황을 확인하기 위해 남겨두었다.

처음에 젤 마지막 남은 프로세스에 대해서는 생각을 안 해서 제출 했을 때 런타임 오버 에러가 떠서 실패했는데 설계할 때 생각도 해두고선 빠트린 줄 모르고 잠깐 고민하다가 질문하기에서 힌트를 얻어 바로 수정하여 제출완료하였다.

 

오늘은 주말 내내 일찍 자지 못하고 그래서 그런지 상태가 좀 안좋아서 능률등이 떨어져서 아쉬운 하루였다.

그리고 추가로 알고리즘 특강을 들었는데 간략한 내용 위주에 필기 해둔 것이 진짜 간단히 탭으로 들여쓰기 정도만 하는 느낌으로 적어뒀는데 메모한 것만 옮겨 놓을려고 하니 들여쓰기한게 다 사라져서 알아보기도 힘들고 하여 그냥 내일 내용과 합쳐서 간략한 정보 정도만 간단히 정리할 계획이다. (내용 자체는 내가 재밌어하고 어느정도 관심 있어하는 알고리즘이기 때문에 좋았으나 처음하거나 분석가의 초점으로 봤을 때 필요한 정도에 따라 얕게 얕게만해서 조금 아쉽긴했다.)