문제

📌 MySQL 으로 풀이

📌 문제 링크 :

📌 문제 설명 :

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.

풀이

추측) 

가장 최근이라는 시간을 MAX로 구하는게 될까 싶어 바로 적용

쿼리) 

* 1차) 

SELECT MAX(DATETIME) AS '시간'
FROM ANIMAL_INS

리뷰) 

이걸 기록하는 이유는 이론상으로는 시간이 쌓이니까 MAX함수로 구하는게 맞는 거 같긴한데.. (제목도 최댓값 구하기지만 ㅎㅎ)

근데 이게 되네? 싶어 기록해본다.

문제

📌 MySQL 으로 풀이

📌 문제 링크 :

📌 문제 설명 :

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

📌 테이블 :

다음은 식품의 정보를 담은 FOOD_PRODUCT 테이블입니다. FOOD_PRODUCT 테이블은 다음과 같으며 PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE는 식품 ID, 식품 이름, 식품코드, 식품분류, 식품 가격을 의미합니다.

풀이

추측) 

먼저 카테고리 별 최고값을 서브쿼리고 검색하고 where절로 이와 일치하는 row를 찾아 출력하는 방식으로 진행

쿼리) 

* 1차) 성공

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN
    (SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY)
ORDER BY PRICE DESC

리뷰) 

WHERE에서 IN 연산자 쓸때 () 안에 컬럼을 맞춰주면 여러개를 일치하는 걸 찾아줄 수 있다. 

IN 연산자는 다중행 연산자로 다중행 연산자에는 아래와 같은 종류가 있다. 

문제

📌 MySQL 으로 풀이

📌 문제 링크 :

📌 문제 설명 :

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.

📌 테이블 :

다음은 중고 거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고 거래 게시판 첨부파일 정보를 담은 USED_GOODS_FILE 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며 BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS는 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

USED_GOODS_USER 테이블은 다음과 같으며 USER_ID, NICKNAME, CITY, STREET_ADDRESS1, STREET_ADDRESS2, TLNO는 각각 회원 ID, 닉네임, 시, 도로명 주소, 상세 주소, 전화번호를 를 의미합니다.

풀이

소스코드) 

* 1차) 성공

SELECT U.USER_ID, U.NICKNAME, SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD AS B
INNER JOIN USED_GOODS_USER AS U
ON B.WRITER_ID = U.USER_ID
WHERE B.STATUS = 'DONE'
GROUP BY U.USER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES ASC

리뷰) 

where하고 having이 거르는 차이점에 대해서 알 수 있는 문제

where은 그룹화 하기 전에 필터링 하여 거래가 끝난지를 판단하여 필터링하고

having은 거래 끝난 걸 대상으로 그룹화하여 테이블을 만들면 거기서 총액의 70만 이상인걸 필터링한다.

인덱스 란?

추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조로 마치 책의 목차처럼 테이블 칼럼을 색인화 하여 검색 속도를 향상시키는 기술이다.

인덱스 종류

• 클러스터형 인덱스
  • 인덱스 생성시 데이터 전체가 다시 정렬된다.
  • 인덱스 자체의 리프페이지가 곧 데이터 페이지이다. (인덱스 자체에 데이터 포함)
  • 보조 인덱스보다 검색 속도는 빠르나 입력/수정/삭제 작업에서는 더 느리다.
  • 성능이 좋으나 테이블에 단 하나만 생성할 수 있다.
  • Primary key로 지정하거나 unique not null 지정시 클러스터형 인덱스가 생성된다. 우선순위는 Primary key가 더 우선시 된다.

• 보조 인덱스 (논 클러스터형 인덱스)
  • 데이터 페이지와 별개로 별도의 페이지에 인덱스를 구성한다.
  • 리프페이지에 데이터가 아니라 데이터가 위치하는 주소값을 가지고 있다.
  • 보조 인덱스를 테이블 당 여러가지 생성할 수 있으나, 남용 경우 시스템 성능을 떨어뜨리는 결과를 초래한다.
  • not null이 아닌 unique 경우 보조 인덱스가 생성된다.

• 둘 혼합 경우
  • 보조 인덱스 리프 페이지에 데이터 주소값이 아닌 클러스터형 인덱스의 키 값을 가지게 된다.
  • 보조 인덱스 조회 후 다시 클러스터형 인덱스 조회
  • 왜 보조 인덱스에 주소값을 넣지 않을까? 주소값으로 리프페이지에 저장되어 있으면, 데이터에 삽입 삭제 등으로 페이지 분할 등 변화가 생길 때, 클러스터형 인덱스의 하나 변화로 클러스터형 데이터페이지가 일단 페이지 변화, 오프셋 등이 대폭 변경이 된다. 이에 따라 보조 인덱스의 주소값 까지 대폭 변경되는 상황이 벌어져 큰 부하가 일어난다. 그래서 둘 혼합 경우 보조 인덱스의 리프페이지는 클러스터형 인덱스의 키 값만을 가지게 된 것이다.

인덱스 적용 방법

✅ 클러스터형 인덱스

- 생성 1 : 테이블 생성시 PRIMARY KEY 적용

CREATE TABLE 테이블이름 (
		열이름 타입 PRIMARY KEY,       // 클러스터형 인덱스
		열이름 타입,
		열이름 타입 UNIQUE,            // 보조 인덱스
		열이름 타입 UNIQUE NOT NULL,   // 클러스터형 인덱스 
};

- 생성 2 : 이미 생성된 테이블에 PRIMARY KEY 지정

ALTER TABLE 테이블이름 ADD PRIMARY KEY (열이름);

- 삭제 :

ALTER TABLE 테이블이름 DROP PRIMARY KEY;

✅ 보조 인덱스

- 생성

기본생성

형식:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 인덱스이름 
	[index_type]
ON 테이블이름 (key_part, ...)
	[index_option]
	[algorithm_option | lock_option] ...

key_part :
	{컬럼이름 [(length)|(expr)} [ASC | DESC}

index_option :
		KEY_BLOCK_SIZE [=] value
	| index_type
	| WITH PARSER parser_name
	| COMMENT 'string'
	| {VISIBLE | INVISIBLE}

index_type :
	USING {BTREE | HASH}

algorithm_option :
	ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option :
	LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

간단하게

CREATE INDEX 인덱스이름 ON 테이블이름 (열이름);

- 이미 생성된 테이블에 INDEX 생성

ALTER TABLE 테이블이름 ADD INDEX 인덱스이름 (열이름);

- UNIQUE 제약조건으로 보조 인덱스 생성

ALTER TABLE 테이블이름 ADD CONSTRAINT UK_인덱스이름 UNIQUE (열이름);

- 삭제

DROP INDEX 인덱스이름 ON 테이블이름;

인덱스 성능 비교

조건) 정렬 안된 동일한 데이터가진 테이블 3개를 가지고 비교 (110만건 기준)

아래 명령어를 통해 검색시 몇개의 페이지를 읽었는 지 검색 이전과 이후의 값 차이로 알수있다.

show global status like 'Innodb_pages_read';

• 아무 인덱스도 없을때

- 읽기전

- 쿼리와 실행 계획 (Full Table Scan으로 작동)

select * from test_product_none where id = 100000;

- 읽은후

읽은후 값 - 읽기전 값 = 읽어들인 페이지 수
클러스터형도 보조도 없을 경우 조회를 위해 10658개를 읽어들였다.

• 클러스터형 인덱스가 있을때

- 읽기전

- 쿼리와 실행 계획 (클러스터형 인덱스 작동)

select * from test_product_cluster where id = 100000;

- 읽은후

! 클러스터형은 겨우 2페이지 만에 데이터를 찾아냈다.

• 보조 인덱스가 있을때

- 읽기전

- 쿼리와 실행 계획 (클러스터형 인덱스 작동)

select * from test_product_secondary where id = 100000;

- 읽은후

! 보조 인덱스 경우도 5페이지 만에 데이터를 찾아냈다.

인덱스 없이 데이터가 무려 10658개의 페이지를 읽었던 것과 비교하면 인덱스가 얼마나 유용한지 알 수 있다.

인덱스 설정시 고려사항

• WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
• WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
• 데이터 중복도가 높은 열은 인덱스를 만들어도 효과가 없다.
• 외래 키를 지정한 열엔 자동으로 외래키 인덱스가 생성된다.
• JOIN이 자주 사용되는 열에 인덱스를 생성해 주는 것이 좋다.
• INSERT / UPDATE / DELETE 가 얼마나 자주 일어나는지 고려해야한다. 이 작업이 자주 일어나는 테이블에 인덱스를 쓰면 오히려 성능이 나빠진다 (페이지 분할이 빈번하게 발생하기 때문)
• 클러스터형은 테이블당 하나만 생성할 수 있다.
• 클러스터형 인덱스가 없는게 더 좋은 경우도 있다.
  (새로운 데이터가 들어올때마다 정렬이 계속 수행되어 페이지 분할이 일어날 수도 있기 때문)
• 사용하지 않는 인덱스는 제거해야 한다.
• 클러스터형과 보조 인덱스 혼합시
  클러스터형 키 값으로 결정될 열은 최대한 적은 자릿수의 열을 선택하는 것이 좋다. 이 열 값으로 보조 리프페이지에도 저장되기 때문이다.

복합인덱스 (Composite Index, 결합인덱스, 멀티인덱스) 란?

인덱스 컬럼이 2개 이상 걸려있는 경우로 데이터가 많고 조건에 컬럼의 개수가 많을 경우에 사용한다.

인덱스에서는 선행 칼럼에서 걸러진 범위에서 그다음 컬럼이 걸러지고 연속해서 걸러진 범위안에서 값을 검색하기 때문에 인덱스 순서가 매우 중요하다.

• 인덱스 첫번째 컬럼을 Where절로 사용하지 않는다면 인덱스가 사용되지 않기 때문에 일단 공통적으로 사용하는 필수 조건절 컬럼을 우선시 해야한다.
• 조건(WHERE) 절에서 Equal('=')이 아닌 다른 연산자(BETWEEN, LIKE, <, >)의 첫 번째 컬럼까지만 인덱스를 사용하고 그 이후 컬럼들은 인덱스를 사용하지 않는다. (=,=,between,=,= 순서로 where절 조건을 넣었다면 3번째의 between까지만 인덱스를 사용한다. = 연산자 컬럼을 다른 연산자보다 우선으로 한다. (=이 아닌 첫번째 연산자 까지만 인덱스를 타지 않고 필터링 한다.)

추가 자료들

• 인덱싱 알고리즘 종류
  • B-tree 알고리즘
    가장 일반적으로 사용, 오래전부터 도입되어 많이 안정화 된 알고리즘. 컬럼 값을 변환하지 않고 원래 값을 이용해 인덱싱.
  • Hash 인덱스 알고리즘
    컬럼 값으로 Hash 값을 계산하여 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원. 변환된 값을 이용하여 인덱싱 하므로 LIKE 검색과 같은 주로 메모리 기반의 DB에서 많이 사용.
    동등비교에 최적화되어 있고 부분일치 값을 검색하고자 할때는 사용 불가
  • Fractal-Tree 알고리즘
    B-tree 알고리즘의 단점을 보완을 위해 등장, 원래 값을 이용해 인덱싱. 데이터의 저장과 삭제 시 처리비용을 감축, 조만간 B-tree 알고리즘을 대체할 수 있을거라 예상.

📌 파이썬 으로 풀이

📌 문제 링크 :

📌 문제 설명 :

정수 n, left, right가 주어집니다. 다음 과정을 거쳐서 1차원 배열을 만들고자 합니다.

1. n행 n열 크기의 비어있는 2차원 배열을 만듭니다.
2. i = 1, 2, 3, ..., n에 대해서, 다음 과정을 반복합니다.
   • 1행 1열부터 i행 i열까지의 영역 내의 모든 빈 칸을 숫자 i로 채웁니다.
3. 1행, 2행, ..., n행을 잘라내어 모두 이어붙인 새로운 1차원 배열을 만듭니다.
4. 새로운 1차원 배열을 arr이라 할 때, arr[left], arr[left+1], ..., arr[right]만 남기고 나머지는 지웁니다.

정수 n, left, right가 매개변수로 주어집니다. 주어진 과정대로 만들어진 1차원 배열을 return 하도록 solution 함수를 완성해주세요.

📌 제한 사항

• 1 ≤ n ≤ 107
• 0 ≤ left ≤ right < n2
• right - left < 105

📌 입출력 예

추측) 

n행 n열로 숫자를 채울때 1행 1열은 1, 2행 2열까지 2, 3행 3열까지 3을 채운 다음

그걸 행을 기준으로 잘라 한행으로 붙이고 그 전체 배열에서 left부터 right까지의 인덱스에 해당하는 배열을 return하면 되는 문제

그럼 여기서 일단 n행 n열을 채워주는 방법

n이 6일라고 하면

1, 2, 3, 4, 5, 6

2, 2, 3, 4, 5, 6

3, 3, 3, 4, 5, 6

4, 4, 4, 4, 5, 6

5, 5, 5, 5, 5, 6

6, 6, 6, 6, 6, 6

1, 2, 3, 4, 5, 6 / 2, 2, 3, 4, 5, 6 / 3, 3, 3, 4, 5, 6 / 4, 4, 4, 4, 5, 6 / 5, 5, 5, 5, 5, 6 / 6, 6, 6, 6, 6, 6

패턴을 보면 1번 행은 1이 1번까지, 2번 행은 2까지 모두 2, 3번 행은 3까지 3 이런식의 패턴을 반복한다. 

그럼 i번 행일 경우 i까지 i를 반복 나머지는 +1씩하여 그리고 n이 되면 한 행을 완성 시키는 방식으로 행을 만든다. 

그리고 필요한 부분의 배열만 잘라 return

소스코드) 

* 1차) 실패 : 45점 하나빼고 모두 시간초과

def solution(n, left, right):
    answer = []
    
    for i in range(0,n) :
        for j in range(0,n) :
            if j < i :
                answer.append(i+1)
            else:
                answer.append(j+1)
        
        if len(answer) > right+1 :
            return answer[left:right+1]

* 2차) 성공 

def solution(n, left, right):
    answer = []

    for i in range(left, right+1) :
        
        remain = (i % n) + 1
        share = int(i / n) + 1
        
        if remain > share :
            answer.append(remain)
        else :
            answer.append(share) 
    
    return answer

리뷰) 

1차)

시간 초과를 안하게 하려면 일단 어떻게 해야할까 

n이 일단 10의 7승까지 가능하단 걸 생각하면 모든 배열을 정리하고 하는 방식이 시간 초과에 지대한 영향을 미칠 것으로 보인다. 

딱 left와 right 사이만 판단하는 방법이 없을까? 

어휴 삽질 많이 했다 ㅠ

처음엔 3부분으로 나눠서 (처음줄, 반복줄, 마지막줄) for문으로 배열을 만들어 그 배열들을 또 붙이는 방식으로도 해봤다.

근데 일단 정확도도 안맞고 방식이 너무 지저분하다는 느낌이 들어서 최대한 모든 수를 같은 방식으로 판별하는 방법을 고민하게 되었다. 

아예 left ~ right 사이의 인덱스 값을 받게 되면 인덱스 값으로 해당 수를 판단할 수 있게 만들고 바로 append하는 방식으로 진행! 성공!!