₍ᐢ•༝•ᐢ₎

문제

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.
Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.

*

Join문을 활용해서 구할 수 있는 문제이다.

실행 코드

1) X!=Y, 즉 X<Y인 케이스 -> INNER JOIN으로 짝 구하기

2) X=Y 인 케이스 -> HAVING COUNT(*) >= 2 조건을 추가해서 짝 구하기

두 가지 케이스를 UNION으로 합쳐주면 간단하게 풀 수 있다.

문제

REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.

실행 코드

GROUP BY 사용해서 REST_ID를 묶어줘야 정답 처리가 된다~

문제

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

실행 코드

5개의 테이블을 LEFT JOIN으로 줄줄이 연결해주면 된다.

문제

Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

*

실행 코드

3개의 테이블이 제시되어 있는데, 먼저 본인을 기준으로 연봉 테이블을 구해준 뒤, 본인의 친구 기준으로 연봉 테이블을 연결해주어야 한다.

나는 from절에서 서브쿼리를 사용해서 조건을 추가했다.

1. 서브쿼리를 통해 본인의 연봉 제시

2. 본 쿼리에서 'INNER JOIN Packages P ON P.ID = A.Friend_ID' 구문을 추가하여 친구의 연봉 구분한 후 WHERE절로 비교

문제

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

1. 2개 이상의 챌린지 성공 및 만점 획득한 hacker를 찾아야 한다.

2. Difficulty테이블에서 difficulty_level에 따른 score와 hacker들이 획득한 score, difficulty_level이 일치하는지 확인해야 한다.

총 4개의 테이블이 있으며, 이들을 모두 inner_join 해준 후 차례대로 조건을 걸어주면 된다.

실행 코드

HAVING COUNT(H.HACKER_ID) > 1 조건을 추가해서 2개 이상의 챌린지를 한 hacker를 찾아주었다.

테이블이 4개라서 join절을 작성한 구문이 조금 복잡하다고 느껴지는데, 코드를 뜯어보면 전혀.. 그렇지 않다!

문제

FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.

실행 코드

이전에 풀었던 즐겨찾기 식당 문제와 유사한 문제이다.

where절에 이중쿼리를 조건문으로 넣어서 풀이했다.

이중쿼리 부분이 여전히 약한데, 여러 문제 풀어보면서 보완해야겠다 !

문제

REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.

풀이 코드

처음 작성했던 코드는 where in 절 대신 주석 처리된 줄을 포함해서 실행했었다.

두 코드 모두 동일한 답을 출력하긴 하지만, where in 절을 포함시키지 않으면 오류 발생 가능성이 있기 때문에(중복 처리 가능성으로 생각된다..) 오답 처리가 되는 것 같다.

이런 부분까지 생각하고 쿼리를 잘 설계해야 하는데.. 아직 더 많은 공부와 노력이 필요하다는 걸 느꼈다. ~~

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

* 

조건1. CAR_RENTAL_COMPANY_RENTAL_HISTORY에서 start_date와 end_date를 활용하여 명시된 대여 가능 기간에 따른 car_id 뽑기

조건2. 30일 간의 기간 타입, 대여 금액 범위는 500,000 이상, 2,000,000 미만

실행 코드

car_rental_company_rental_history 테이블을 이중쿼리로 작성하여 대여 가능 기간을 설정해줬다. 대여 가능 기간 조건만 설정하면 나머지는 문제에 따라 차례대로 조건을 추가해주면 정답이 나온다. 코드 보면 뭔가 복잡한 것 같지만... 조건에 따라서 차근차근 작성하니까 구할 수 있었다!

- 

근데 문제에 duration_type이 아니라.. dutaion_type으로 오타가 나있어서 처음에 오타난 컬럼명으로 쿼리 작성하다가 계속 오류가 나서 당황했다.. 그냥 스펠링 틀린 채로 컬럼명을 설정했다고 생각했는데...아니었다. ㅎㅎ

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order. Write a query to help Eve.

Students 테이블과 Grades 테이블을 활용하여 학생별 Marks를 Grade 등급 구간별로 구분한 후, 출력하면 되는 문제이다.

이 문제를 풀면서 join문에 따로 조건절을 넣어서 사용할 수 있다는 걸 처음 알았다..! 항상 공통적으로 가지고 있는 외래키 연결하는 용도로만 사용했어서.. 덕분에 지식 하나 습득했다!

-

풀이 코드

students의 marks 점수를 grades테이블을 활용해서 구간별로 구분해주면 된다.

s.marks between g.min_mark and g.max_mark < marks가 min_mark, max_mark 구간에 따라 매치되면서 본인과 맞는 grade를 부여받는다.

Chapter 3. 빅데이터의 분산 처리

구조화 데이터 : 스키마가 명확하게 정의된 데이터

비구조화 데이터 : 스키마가 존재하지 않는 데이터

      - 이를 분산 스토리지 등에 저장하고, 분산 시스템에서 처리하는 것이 데이터 레이크의 개념이다.

         - 데이터를 가공하는 과정에서 스키마를 정의하고, 구조화된 데이터로 변환함으로써 분석 가능하다.

스키마리스 데이터 : 기본 서식인 존재하나 스키마 정의가 되지 않은 데이터로, 컬럼 수나 데이터형이 명확하지 않다.

분산 스토리지에 수집된 스키마리스 데이터 등은 SQL 집계가 불가하다. 따라서, 구조화 데이터로 변환하는 과정이 필요하다.

  - 구조화 데이터는 압축률을 높이기 위해 열 지향 스토리지로 저장한다.

열 지향 스토리지

1. Apache ORC : 구조화 데이터를 위한 스토리지로, 처음에 스키마를 정한 후 데이터를 저장한다.

2. Apache Parquet : 스키마리스에 가까운 데이터구조로 되어 있어 json 데이터도 그대로 저장할 수 있다.                               - 비구조화 데이터를 읽어 들여 열 지향 스토리지로 변환하는 과정에서 많은 컴퓨터 리소스를 소비하는데,

     - 이때 Hadoop, Spark 등의 분처리 프레임 워크를 사용한다.

Hadoop : 분산 시스템을 구성하는 다수의 소프트웨어로 이루어진 집합체로, 대규모 분산시스템 구축을 위한 공통 플랫폼 역할을 한다.

크게 3가지로 구분되며,

  1. HDFS : Hadoop에서 처리되는 데이터 대부분을 저장한다. 다수의 컴퓨터에 파일을 복사해서 중복성을 높인다.

  2. Yarn : 리소스 매니저로, CPU와 메모리를 '컨테이너(Container)' 단위로 관리한다. 어플리케이션마다 실행 우선순위를 결정하는 것이 가능하다.

  3. MapReduce : Yarn 상에서 동작하며, 대량의 데이터를 배치 처리하기 위한 시스템이다. (* 1회의 스테이지가 모두 끝나야 다음 처리가 진행 가능한데, 이러한 단점을 보완한 것이 Apache Tez이다. Tez는 1회의 스테이지가 모두 끝날 때까지 기다릴 필요 없이, 끝나자마자 바로 다음 처리로 넘어가는 것이 가능하여 고속화를 실현한 시스템이다.)

대화형 쿼리엔진에는 1. Impala 2. Presto가 대표적으로 있으며, 구조화 데이터를 대화식으로 집계할 때 지연이 적은 이 두가지를 사용한다.

Spark : 대량의 메모리를 활용하여 고속화를 실현한 프레임 워크이다. (MapReduce 대체) 메모리로 어떻게 관리하느냐가 중요한 포인트이며, 가능한 많은 데이터를 메모리 상에 올린 상태로 두어 디스크에는 아무것도 기록하지 않는다는 것이 특징이다. (* 컴퓨터가 비정상적으로 종료될 시에는 중간까지 처리한 데이터가 소멸되고, 이때 다시 처리를 시도하여 잃어버린 중간 데이터를 다시 생성한다.)

Hive : 시간이 걸리는 배치 처리 시에 사용하며, 대규모 데이터 처리에 적합하다.

Presto : 쿼리 실행의 지연을 감소시키는 것을 목적으로 개발되었으며, 플러그인이 가능한 스토리지 설계가 특징이다. 전용 스토리지를 가지고 있지 않기 때문에 Hive와 마찬가지로 다양한 데이터 소스에서 직접 데이터를 읽어 들인다. 특히 SQL 실행에 특화되어 있고, 일단 실행이 시작되면 중간에 끼어들 수 없다. 따라서 너무 큰 쿼리는 실행할 수 없다.  (* 디스크 사용하는 Hive와 달리 메모리 상에서 데이터를 처리하며, 분산 결합을 실시한다.)

팩트 테이블

1. 추가 : 새로 도착한 데이터만을 증분으로 추가한다.

2. 치환 : 과거의 데이터를 포함하여 테이블 전체를 치환한다.

추가의 잠재적인 문제는 크게 3가지로,

1. 추가에 실패한 것을 알아채지 못하면 팩트 테이블의 일부에 결손이 발생할 수 있다.

2. 추가를 잘못해서 여러 번 실행하면 팩트 테이블의 일부가 중복되는 문제가 발생할 수 있다.

3. 추후 팩트 테이블을 다시 만들고 싶은 경우 관리가 복잡해진다.

이러한 문제 발생 가능성을 줄이기 위해 테이블 파티셔닝 기술을 사용한다. (* 테이블 파티셔닝 : 하나의 테이블을 여러 물리적인 파티션으로 구분함으로써 파티션 단위로 정리하여 데이터를 쓰거나 삭제가 가능하도록 한다.)

마스터 테이블에 대한 두 가지 방안

1. 스냅샷 테이블 : 정기적으로 테이블을 통째로 저장한다. (일종의 팩트 테이블)

2. 이력 테이블 : 변경 내용만을 저장한다. (디멘전 테이블로 사용하기는 힘듦)

*

데이터의 구조화만 잘 되어 있으면 그 후는 데이터 웨어하우스와 같은 개념으로 데이터 마트를 구축할 수 있다.

팩트 테이블과 디멘전 테이블을 준비한 후, 결합 및 집계를 하면서 비정규화 테이블을 만든다. 디멘전으로 사용하는 데이터는 정기적인 스냅샷으로 이력을 축적한다. 따라서 디멘전의 카디널리티(각 칼럼이 취하는 값의 범위)만 작아지면 비정규화 테이블을 아주 작게 집약할 수 있다.