₍ᐢ•༝•ᐢ₎

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.

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 형식으로 되어 있는 곳이었다.

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

01. 5월 식품들의 총매출 조회하기

- FOOD_PRODUCT와 FOOD_ORDER 두 개의 테이블 존재

- 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문 작성

- 결과는 총매출을 기준으로 내림차순 정렬, 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬

* 두 테이블을 JOIN으로 연결한 후, WHERE절에 생산일자 조건 주기

* 총매출 = PRICE*AMOUNT

풀이 코드

위에 작성한 WHERE절 코드 외에도 2022년 5월만 추출하는 방법은 여러가지가 있다.

(1) WHERE PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31'

(2) WHERE PRODUCE_DATE LIKE '2022-05%'

(2)번 방법이 가장 깔끔하고 간단!

* 참고 ! 실행 결과

02. 주문량이 많은 아이스크림들 조회하기

- FIRST_HALF, JULY 두 테이블 존재

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

* 상위 3개 -> LIMIT 활용해서 풀이

* SUM 함수는 '+'로 연결해서 사용 가능

풀이 코드

풀고 난 후에 다른 분들 풀이가 궁금해서 서치해봤는데, 서브쿼리로 푸신 분들이 많았다.

위 문제에서 좀 더 복잡한 조건이 추가된다고 가정하면.. 서브쿼리로 설계해서 푸는 게 연습은 더 될 것 같다.

03. 상품 별 오프라인 매출 구하기

- PRODUCT, OFFLINE_SALE 두 테이블 존재

- 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문 작성

- 결과는 매출액을 기준으로 내림차순 정렬, 매출액이 같다면 상품코드를 기준으로 오름차순 정렬

풀이 코드

-

JOIN 카테고리 10문제 중 이제 1문제 남았는데..도저히 안 풀림+집 네트워크 장애로.... 내일 머리 싸매고 풀어봐야지..

작년 인턴을 한 후로 SQL을 완전히 까먹었다.

데이터 분야에서 SQL 능력은 기본 중의 기본이기 때문에..

복습 및 실력 향상을 위해 오늘부터 SQL 코딩 테스트 문제 풀이를 시작했다.

기본적인 것부터 시작해서 난이도 있는 문제 풀이까지 해야겠다.

파이팅..

01. 조건에 맞는 도서와 저자 리스트 출력하기

- BOOK, AUTHOR 두 개의 테이블 존재

- 경제 카테고리에 속하는 도서들의 도서 ID, 저자명, 출판일 리스트를 출력하는 SQL문 작성

- 출판일 기준으로 오름차순 정렬

- 출판일의 데이트 포맷이 예시와 동일해야 정답처리(ex. 2023-01-28)

풀이 코드

JOIN문을 활용하면 풀 수 있는 쉬운 문제이다.

JOIN은 기본적으로 두 테이블 간의 공통 컬럼을 KEY로 연결하여 사용한다.

이 문제에서는 AUTHOR_ID가 공통적으로 두 테이블에 존재하기 때문에 이를 사용해서 문제를 풀었다.

02. 없어진 기록 찾기

- ANIMAL_INS, ANIMAL_OUTS 두 개의 테이블 존재

- 천재지변으로 인해 일부 데이터가 유실

- 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문 작성

* ANIMAL_INS : 보호소에 들어온 동물 리스트 모아놓은 테이블

* ANIMAL_OUTS : 입양 보낸 동물 리스트 모아놓은 테이블

* ANIMAL_ID : 외래키

풀이 코드

문제 내용만 제대로 이해하면 쉬운 문제이다.

ANIMAL_OUTS에는 기록이 있는데, ANIMAL_INS에는 기록이 없는 동물을 찾는 문제로, 두 테이블을 JOIN 해준 후 WHERE절에서 ANIMAL_INS의 ANIMAL_ID(외래키) IS NULL 조건절을 추가해주면 된다.

* 참고 ! 실행결과

03. 그룹별 조건에 맞는 식당 목록 출력하기

- MEMBER_PROFILE, REST_REVIEW 두 테이블 존재

- MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문 작성

- 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성

- 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬

* 서브쿼리 필요

* GROUP BY, ORDER BY, LIMIT 구문 필요

이 문제는...약간 헤맸다

머리가 안 돌아가서.....

풀이 코드

JOIN을 통해 두 테이블을 연결해준 후, WHERE절 안에 서브쿼리를 작성해서 문제를 풀었다.

리뷰를 가장 많이 쓴 회원의 리뷰들을 보여줘야 하므로, MEMBER_ID를 GROUP BY로 묶은 후 ORDER BY 구문을 활용하여 COUNT함수를 써서 내림차 정렬을 했다.

LIMIT 1이라는 조건을 주어 내림차 정렬에 따른 가장 많은 리뷰들을 쓴 회원의 아이디가 선택되게 했다.

* 참고 ! 실행결과

left join을 활용하면 되는 문제

join 함수는 sql에서 단연 가장 많이 쓰이는 함수라고 할 수 있다.

최소 둘 이상의 테이블을 연결할 때 사용하는 함수로, 테이블 간에 공통분모 컬럼이 하나 이상 존재해야 한다.

크게 INNER / LEFT / OUTER / RIGHT 4가지로 나뉘어진다.

집합과 빗대어보자면,

1. INNER JOIN : 교집합

2. LEFT / RIGHT JOIN : 부분집합

3. OUTER JOIN : 합집합

이라고 할 수 있다.

나는 주로 MySQL을 쓰는데, 실습하려고 봤더니

MySQL에는 OUTER JOIN이 없더라. 대신 LEFT JOIN과 RIGHT JOIN을 함께 써주면 된다고 한다.

(*오라클에서는 위 4가지 JOIN을 모두 활용할 수 있다.)

INNER JOIN의 경우, 테이블 간의 공통적인 데이터만 SELECT되고,

LEFT JOIN은 LEFT를 기준으로 왼쪽에 있는 데이터가 모두 SELECT된다.

RIGHT는 반대로 오른쪽에 있는 데이터가 모두 SELECT,

OUTER는 테이블 간의 모든 데이터가 SELECT된다.

이 개념을 활용해서 문제를 풀어보면,

ANIMAL_INS는 소실된 데이터라고 나와있기 때문에 ANIMAL_OUTS를 활용해주어야 한다.

INS와 OUTS의 외래키 및 공통 분모 컬럼을 이용하여 LEFT JOIN과 WHERE 조건절을 주면 간단하게 끝나는 문제이다.

프로그래머스 SQL 코딩테스트 연습에 올라온 예제 문제.

Group by를 사용하는 예제이다.

1. 동명 동물 수 찾기

동물 보호소에 들어온 동물의 정보를 담은 ANIMAL_INS 테이블이 있는데,

이때 동물 보호소에 들어온 동물 이름 중 두 번 이상(i > 1) 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해야 하는 문제이다.

먼저 SELECT문을 통해 NAME과 COUNT 함수를 테이블로부터 함께 조회해주고, group by로 이름끼리 묶어준다.

문제에서 같은 이름이 두 번 이상 쓰인 경우 카운터하라고 했기 때문에 HAVING문을 활용해서 조건을 넣어준다.

그 다음 이름순으로 정렬해주면 끝!

2. 입양 시각 구하기 (1)

보호소에서 입양 보낸 횟수를 알아보기 위해 오전 9시부터 오후 7시 59분까지의 입양 횟수를 카운터해야 하는 문제이다.

시간대별로 몇 건이 발생했는지를 나타내야 한다.

문제 풀이에서 SUBSTR함수를 사용하여 필요한 HOUR만 잘라서 사용했는데, SQL은 HOUR 함수가 있기 때문에 이 함수를 사용하는 게 가장 간결하고 베스트이다.

그냥 이런 방법도 있다 하는 마음으로 사용했다.

HAVING문을 활용하여 조건에 명시된 시간대를 설정해주고, GROUP BY, ORDER BY를 작성해주면 끝!

Tag, offset, index를 어떻게 구하는지부터가 난관이었고..

지금도 이해가 100%된건 아니지만.. 뭐 direct mapped와 fully-associative 방식으로 어떻게 access하는지 정도는 이해했다.

n-way associative는 내일 다시 해봐야겠다

사실 cpu, cache, 가상 메모리 부분이 제일 어렵다고들 하는데 

진짜 어렵긴 어렵지만 뭔가 제일 재밌는 챕터라서 공부하는 맛이 난다

물론 이해를 했는지 안했는지의 문제는 별개로... 그냥 재밌다

ex. 16B/blocks

256blocks

--> offset = 4bits, index = 8bits, Tag = 32-4-8=20bits

먼저 index를 보고, 그 다음 Tag를 보면 되는데, 이때 Tag까지 최종적으로 동일하다면 Hit.

https://www.youtube.com/watch?v=RqKeEIbcnS8&t=1s