0404MYSQL코테문풀_중요사항들
1. GROUP BY 와 집계함수, 서브쿼리로 CNT가 MAX인 행 찾기
1-01. MAX인 행의 CNT 구하기
1단계: 기본 그룹화 및 카운트
먼저, 원하는 컬럼에 대해 GROUP BY를 사용하여 그룹화하고, 각 그룹별로 COUNT() 함수를 사용하여 요소의 수를 계산합니다. 예를 들어, your_table_name이라는 테이블에서 your_column_name 컬럼을 기준으로 그룹화하고 각 그룹의 개수를 계산하는 쿼리는 다음과 같습니다.
SELECT your_column_name, COUNT(*) AS group_count
FROM your_table_name
GROUP BY your_column_name;
2단계: 최대값 추출
그 다음, 이전 단계에서 구한 결과를 서브쿼리나 CTE(Common Table Expression)로 사용하여, MAX() 함수를 이용해 최대 카운트 값을 추출합니다.
서브쿼리를 사용하는 방법
SELECT MAX(group_count) AS max_count
FROM (
SELECT your_column_name, COUNT(*) AS group_count
FROM your_table_name
GROUP BY your_column_name
) AS subquery;
CTE를 사용하는 방법
WITH grouped_counts AS (
SELECT your_column_name, COUNT(*) AS group_count
FROM your_table_name
GROUP BY your_column_name
)
SELECT MAX(group_count) AS max_count
FROM grouped_counts;
1-02. JOIN 수행하기
1단계: 최대 CNT 값 찾기
먼저, 최대 CNT 값을 찾습니다.
SELECT MAX(CNT) AS max_cnt
FROM people_table;
2단계: 최대 CNT 값을 가진 사람들의 ID 추출
다음으로, 최대 CNT 값을 가진 사람들의 ID를 찾습니다. 이 작업은 서브쿼리나 CTE(Common Table Expression)를 사용하여 수행할 수 있습니다.
서브쿼리 사용
SELECT ID
FROM people_table
WHERE CNT = (SELECT MAX(CNT) FROM people_table);
CTE 사용
WITH max_cnt AS (
SELECT MAX(CNT) AS max_value
FROM people_table
)
SELECT p.ID
FROM people_table AS p
JOIN max_cnt ON p.CNT = max_cnt.max_value;
3단계: 최대 CNT 값을 가진 사람들을 다른 테이블과 조인
마지막으로, 위에서 얻은 ID를 기준으로 other_table과 조인합니다. 이 예에서는 CTE 방법을 사용한 최대 CNT 값을 가진 사람들의 ID 추출과 조인 과정을 합쳐 보여줍니다.
WITH max_cnt AS (
****SELECT MAX(CNT) AS max_value
FROM people_table
),
max_cnt_people AS (
SELECT p.ID
FROM people_table AS p
JOIN max_cnt ON p.CNT = max_cnt.max_value
)
SELECT mcp.*, ot.*
FROM max_cnt_people AS mcp
JOIN other_table AS ot ON mcp.ID = ot.ID;
2. GROUP_CONCAT 함수 사용
MySQL에서 GROUP_CONCAT() 함수는 여러 행의 문자열 값을 하나의 문자열로 결합할 때 사용됩니다. 이 함수는 특히 그룹화된 결과에 대해 여러 값을 한 행에 표시하고자 할 때 유용합니다. 기본 사용법과 옵션들을 통해 GROUP_CONCAT()의 활용 방법을 알아보겠습니다.
기본 사용법
GROUP_CONCAT(column_name)
column_name: 결합하고자 하는 컬럼의 이름입니다.
예시
SELECT author, GROUP_CONCAT(book_title)
FROM books
GROUP BY author;
이 쿼리는 각 author별로 작성한 book_title들을 하나의 문자열로 결합하여 반환합니다.
옵션
- 구분자 지정: 기본적으로,
GROUP_CONCAT()함수는 결과를 쉼표(,)로 구분합니다. 다른 구분자를 사용하고 싶다면SEPARATOR키워드를 사용할 수 있습니다.
예를 들어, 공백으로 값을 구분하고 싶은 경우입니다.GROUP_CONCAT(column_name SEPARATOR ' ')
- 정렬: 결합되는 값들의 순서를 지정하고 싶다면
ORDER BY절을GROUP_CONCAT()함수 내부에 추가할 수 있습니다.
이는GROUP_CONCAT(column_name ORDER BY some_column DESC)column_name을some_column의 내림차순으로 정렬한 후 결합하겠다는 의미입니다.
- 중복 제거: 결합하는 값들 중 중복을 제거하고 싶다면
DISTINCT키워드를 사용할 수 있습니다.
이는 중복된 값을 제거한 후GROUP_CONCAT(DISTINCT column_name)column_name의 값을 결합합니다.
제한 사항
GROUP_CONCAT()함수는 기본적으로 최대 1024바이트까지의 문자열을 생성할 수 있습니다. 이 한도를 넘어가는 데이터는 잘립니다. 결과 문자열의 최대 길이를 변경하고 싶다면,group_concat_max_len시스템 변수의 값을 조정해야 합니다.
이는 현재 세션에 대해 최대 결과 문자열 길이를 10000바이트로 설정합니다.SET SESSION group_concat_max_len = 10000;
GROUP_CONCAT() 함수는 다양한 방법으로 사용될 수 있으며, 복잡한 데이터를 간결하게 요약하여 표현하는 데 유용합니다.
3. MYSQL 문자열 함수들
SUBSTRING()또는SUBSTR(): 문자열의 특정 부분을 추출합니다.- 사용법:
SUBSTRING(str, pos, len)또는SUBSTR(str, pos, len) str은 대상 문자열,pos는 시작 위치(1부터 시작),len은 추출할 길이입니다.- 예:
SUBSTRING('Hello, World!', 1, 5)결과는'Hello'입니다.
- 사용법:
LEFT(): 문자열의 왼쪽 부분을 지정된 길이만큼 추출합니다.- 사용법:
LEFT(str, len) str은 대상 문자열,len은 추출할 길이입니다.- 예:
LEFT('Hello, World!', 5)결과는'Hello'입니다.
- 사용법:
RIGHT(): 문자열의 오른쪽 부분을 지정된 길이만큼 추출합니다.- 사용법:
RIGHT(str, len) str은 대상 문자열,len은 추출할 길이입니다.- 예:
RIGHT('Hello, World!', 6)결과는'World!'입니다.
- 사용법:
MID():SUBSTRING()의 동의어로, 문자열의 중간 부분을 추출합니다.- 사용법:
MID(str, pos, len) str,pos,len의 의미는SUBSTRING()과 동일합니다.- 예:
MID('Hello, World!', 8, 5)결과는'World'입니다.
- 사용법:
LOCATE(): 하나의 문자열 내에서 다른 문자열이 처음으로 나타나는 위치를 반환합니다.- 사용법:
LOCATE(substr, str[, pos]) substr은 찾고자 하는 문자열,str은 대상 문자열,pos는 검색을 시작할 위치(옵션)입니다.- 예:
LOCATE('World', 'Hello, World!')결과는8입니다.
- 사용법:
INSTR():LOCATE()와 비슷하지만, 매개변수의 순서가 다릅니다.- 사용법:
INSTR(str, substr) str과substr의 의미는LOCATE()와 동일합니다.- 예:
INSTR('Hello, World!', 'World')결과는8입니다.
- 사용법:
