₍ᐢ•༝•ᐢ₎

문제

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. 이력 테이블 : 변경 내용만을 저장한다. (디멘전 테이블로 사용하기는 힘듦)

*

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

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

1. Weather Observation Station 6

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

2. Weather Observation Station 7

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

3. Weather Observation Station 8

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

4. Weather Observation Station 18

Consider P1(a,c) and P2(b,d) to be two points on a 2D plane.
 a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
 b happens to equal the minimum value in Western Longitude (LONG_W in STATION).
 c happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
 d happens to equal the maximum value in Western Longitude (LONG_W in STATION).
Query the Manhattan Distance between points  P1 and P2 and round it to a scale of 4 decimal places.

5. Weather Observation Station 19

Consider  P1(a,c) and P2(b, d) to be two points on a 2D plane where  (a,b) are the respective minimum and maximum values of Northern Latitude (LAT_N) and  (c,d) are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION.
Query the Euclidean Distance between points  P1 and  P2 and format your answer to display  4 decimal digits.

이사

지난주에 있었던 가장 큰 이벤트라면....드디어 이사를 했다. 셀프이사라서 이게 될까했는데 무사히 이사를 완료해서 마음이 편안하다.

부모님이 트럭을 끌고 본가에서 서울까지 오신다고 해서, 혼자 자취방의 모든 짐을 포장했다. 포장하다보니....진짜 끝도 없어서 눈앞이 아득했다. 짐을 최대한 줄이고 줄여도 박스가 한가득....!

학교 기숙사 1년 생활 후 4년 동안 자취생활을 했는데, 이번에 졸업을 앞두고 자취방 계약이 만료돼서 새로운 보금자리로 가게 되었다.

이로 인해 서울 밖으로 나오게 됐지만, 이사한 지 3일차...아주 만족스럽다. 일단 예전 방보다 두 배나 크다...

그리고 사방이 탁 트여서 답답하지 않다!

새로운 공간에서 늘 그래왔듯이 열심히 공부하고 취준해야지.

다시 한 번 다짐

주변에서 누군가가 어디 취업했다, 이미 일하고 있다더라...하는 소리가 슬슬 들려온다.

나도 모르게 위축되고, 지난 일이 잠시나마 후회되기도 했다.

하지만 모든 건 온전히 내 선택이었고, 이런 내 선택이 틀렸다고 생각하지 않는다.

성공은 지속성으로부터 온다. 내가 하는 일, 해야 하는 일 모두 꾸준히 해내면서 성공의 기반을 닦아두자.

Occupations

The first column is an alphabetically ordered list of Doctor names.
The second column is an alphabetically ordered list of Professor names.
The third column is an alphabetically ordered list of Singer names.
The fourth column is an alphabetically ordered list of Actor names.
The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.

* The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

풀이 코드

- CASE WHEN 문을 통해 피벗테이블을 생성한다.

- 같은 열에 동일 직업을 가진 사람들의 이름을 넣어야 한다. ROW_NUMBER() OVER (PARTITION BY ~ ORDER BY)절을 사용해서 문제를 풀어야 한다. * 서브쿼리의 경우 Alias 명칭을 제대로 부여해야 오류가 나지 않는다.

-

The PADS.

1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example :  AnActorName(A),  ADoctorName(D),  AProfessorName(P), and ASingerName(S).
2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:
   where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.
3. There are a total of [occupation_count] [occupation]s.

*

occupations 테이블에 있는 name + occupation의 첫 글자를 함께 출력하고, occupation을 count하여 문장을 출력해주는 문제이다.

풀이 코드

- LEFT(occupation, 1) -> SUBSTRING(occupation, 1, 1) 대체 가능하다.

SUBSTRING(occupation, 1, 1) : occupation의 첫 번째에 위치한 글자부터 1글자만 자른다는 뜻으로 해석할 수 있다.

- CONCAT으로 필요한 문자열과 변수를 연결해줘야 한다.

- 각각의 SELECT문에 서로 다른 정렬 기준을 주어야 한다.

- 두 번째 SELECT문은 COUNT함수를 사용하므로 GROUP BY(occupation) 함수를 사용해야 한다.

*

해커랭크는 문제가 모두 영어라서 문제해석을 정확하게 했느냐...가 관건인 듯하다.

Chapter 2. 빅데이터의 탐색

크로스 테이블 : 행과 열이 교차하는 부분에 숫자 데이터가 들어간다. 

트랜잭션 테이블 : 행 방향으로만 데이터가 증가한다. (* 트랜잭션 테이블에서 크로스 테이블로의 변환 과정을 크로스 집계라고 한다.)

* 데이터 마트의 크기에 따라 시스템 구성이 결정된다.

RDB는 원래 지연이 적고, 동시 접속 수가 많아도 성능 악화가 되지 않는다. 다만, 메모리가 부족하면 성능 저하가 된다.

   많은 양의 데이터 처리를 위해 열 지향 스토리지가 필요하다. 따라서 MPP DB를 활용한다.

행 지향 데이터베이스 

1. 매일 발생하는 대량의 트랜잭션을 지연 없이 처리하기 위해 데이터 추가를 효율적으로 할 수 있게 한다.

2. 데이터 검색 고속화를 위한 인덱스를 생성하지만, 데이터 분석 시에는 거의 도움이 되지 않는다.

열 지향 데이터베이스

1. 데이터를 미리 컬럼 단위로 정리하여 필요한 컬럼만을 로드함으로써 디스크 I/O를 줄인다.

2. 데이터 압축의 효율이 우수하다.

MPP DB : 고속화를 위해 CPU와 디스크 모두 균형있게 늘려야 한다. 하드웨어 수준에서 데이터 집계에 최적화된 DB이다.

데이터베이스에 사용되는 주요 기술

OLAP : 데이터 집계를 효율적으로 하는 접근 방법 중 하나로, 다차원 모델의 데이터 구조를 MDX 등 쿼리 언어로 집계한다.

   OLAP 큐브 : 데이터 분석을 위해 만들어진 다차원 데이터이다. (* 이를 크로스 집계하는 구조가 OLAP)

데이터 마트를 만들 때, 팩트 테이블 중심으로 여러 디멘전 테이블을 결합하여 스타 스키마를 생성한다.(단순/성능상의 이유로 사용)

  MPP DB 같은 열 지향 스토리지를 갖는 시스템 보급에 따라 처음부터 모든 컬럼을 팩트 테이블에 포함하여 비정규화 테이블이리고 부른다.

Chapter 1. 빅데이터의 기초 지식

빅데이터 기술

1. Hadoop : 다수의 컴퓨터에서 대량 데이터 처치를 위한 시스템으로, 확장성이 뛰어나다. (*SQL+Hadoop = Hive)

2. NoSQL : 전통적인 RDB의 제약을 제거하는 것을 목표로 한 DB의 총칭으로, 애플리케이션에서 온라인으로 접속한다.

- 빅데이터 기술은 기존의 데이터 웨어하우스와는 달리 다수의 분산 시스템을 조합하여 확장성이 뛰어난 데이터 처리 구조를 만든다. 

데이터 파이프라인 : 차례대로 전달해나가는 데이터로 구성된 시스템을 의미한다.

데이터 수집에는 총 2가지의 종류가 있는데,

1. 벌크형 : 이미 어딘가에 존재하는 데이터를 정리해 추출하는 것으로, 정기적인 데이터 수집에 사용한다.

2. 스트리밍형 : 차례대로 생성되는 데이터를 끊임없이 보내는 것으로, 주로 모바일 애플리케이션, 임베디드 장비 등에서 데이터 수집하는 데에 사용한다.

분산 스토리지는 1. 객체 스토리지 2. NoSQL 데이터베이스가 있다.

워크플로 관리 : 전체 데이터 파이프라인의 동작 관리

데이터 웨어하우스 : 대량의 데이터를 장기 보존하는 것에 최적화되어 있으며, 과부하를 방지하기 위해 필요한 데이터만을 추출하여 데이터 마트를 구축한다.

   > 데이터 소스 : 업무 시스템을 위한 RDB나 로그 등을 저장하는 파일 서버

      > 로우 데이터를 추출하고 가공 후 데이터 웨어하우스에 저장하기까지의 흐름을 ETL 프로세스라고 한다.

애드 혹 분석(ad hoc analysis) : 일회성 데이터 분석으로, SQL 쿼리를 직접 작성 후 실행하거나 스프레드시트에서 그래프 만들기 등의 분석이 이에 해당한다.

빅데이터 도구를 선택할 때엔,

1. 저장할 수 있는 데이터 용량에 제한이 없을 것

2. 데이터를 효율적으로 추출할 수단이 있을 것 

데이터 파이프라인의 큰 흐름은 변하지 않기 때문에 위 2가지를 파악해서 선택한다.

데이터 수집 목적은 검색, 가공, 시각화 3가지가 있다.

확증적 데이터 분석 : 가설을 세우고 검증하는 분석으로, 통계학적 모델링을 따른다.

탐색적 데이터 분석 : 데이터를 보며 의미를 파악하는 분석으로, 데이터 시각화를 통해 데이터를 파악한다.

01. 

The table eteam gives details of every national team including the coach. You can JOIN goal to eteam using the phrase goal JOIN eteam on teamid=id

Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

풀이 코드

02.

To JOIN game with eteam you could use either
game JOIN eteam ON (team1=eteam.id) or game JOIN eteam ON (team2=eteam.id)

Notice that because id is a column name in both game and eteam you must specify eteam.id instead of just id

List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.

풀이코드

03.

List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'

풀이 코드

04.

Show the stadium and the number of goals scored in each stadium.

풀이 코드

05.

For every match involving 'POL', show the matchid, date and the number of goals scored.

풀이 코드

06.

For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'

풀이 코드

-

SQLZOO는 처음 안 사이트인데 wiki 형식으로 되어 있는 곳이었다.

그래서 그런지 다른 사람이 푼 흔적이 그대로 남아 있어서 다 지우고 풀기 필수..

What I studied

오늘은 CS 기초 개념부터 시작해서 SQL JOIN 쿼리 복습, 코딩 테스트 문제 풀이, 그리고 독서까지 완료했다.

1. CS는 이미 학부 시절에 배웠지만 가장 중요한 기본기이기 때문에 이미 알고 있는 내용은 한 번 더 복습하고 까먹은 내용은 새롭게 배우자는 마음으로 공부했다. 그래도 한 번 공부했을 때 열심히 한 보람이 있는지 대부분의 내용을 쉽게 이해할 수 있었다!

2. SQL도 학부 시절에 열심히 공부했는데.... 그때도 지금도 내가 가장 약한 부분은 역시 서브 쿼리 부분인 것 같다. join 쿼리를 복습하면서 코딩테스트 문제를 풀다보니 서브쿼리의 중요성에 대해 새삼 깨달았다. 내일은 서브쿼리 설계를 중점적으로 공부하고, 복잡한 쿼리도 머릿속으로 잘 설계할 수 있는 역량을 차근차근 길러야겠다.

3. 빅데이터를 지탱하는 기술을 읽기 시작했다. 며칠 후에 다른 곳으로 이사를 가게 돼서 짐을 늘리면 안되기 때문에....당장 책은 구매하지 않는 대신 학교 전자도서관에서 해당 책을 대여할 수 있길래 바로 대출했다. 목표는 차주 내로 완독하는 것...! 500 페이지가 넘는 책이라 가능할지는 모르겠으나, 최소한 5장까지는 읽을 수 있도록 노력해야지.

What I did

컴퓨팅 사고 강의 완강

SQL코딩테스트 문제 풀이 & 개념 복습

집 청소

독서