배열을 활용한 복잡한 쿼리 예시: 집계, 필터링, 정렬

오늘은 쿼리에서 배열을 최대한 뽑아먹는 방법을 알아볼 거야: 값을 그룹으로 모으고, 내용으로 필터링하고, 심지어 배열 안에서 바로 정렬까지! 이건 그냥 이론이 아니라, 리포트, 분석, 개인화 등 실제로 자주 쓰이는 테크닉이야. 원리만 이해하면 진짜 쉬워 — 그걸 지금 같이 해볼 거야.

배열로 데이터 집계하기

배열은 특히 데이터를 그룹화할 때 진가를 발휘해. 여러 줄로 결과를 받는 대신, 필요한 값을 깔끔하게 하나의 배열로 모을 수 있거든. 분석이 훨씬 간단해지고, 결과도 더 콤팩트해져. 쓸데없는 서브쿼리도 줄일 수 있고! 실제로 어떻게 쓰는지 예제로 보자.

예시 1: array_agg()로 데이터를 배열로 그룹화하기

여러 줄에서 값을 한 그룹의 배열로 모으고 싶을 때 array_agg()가 딱이야. 배열 집계할 때 제일 유용한 함수라고 할 수 있지.

-- 우리에겐 id, name, course 필드가 있는 students 테이블이 있어
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

-- 여러 줄을 삽입해보자
INSERT INTO students (name, course) VALUES
('알리사', '수학'),
('밥', '수학'),
('찰리', '물리'),
('데이브', '물리'),
('엠마', '수학');

-- 학생들을 과목별로 배열로 그룹화하기
SELECT course, array_agg(name) AS students
FROM students
GROUP BY course;

결과:

배열로 값을 그룹화하면, 예를 들어 JSON처럼 쉽게 파싱할 수 있는 포맷으로 데이터를 넘길 때 진짜 편해.

예시 2: 중첩 배열 만들기

이번엔 테이블이 하나 더 있다고 해보자. 두 테이블에서 데이터를 배열로 모으고 싶을 때! 예를 들어 courses 테이블에 강사 정보가 있다고 하자.

-- 강사 테이블 만들기
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    teacher VARCHAR(100)
);

-- 데이터 삽입
INSERT INTO courses (name, teacher) VALUES
('수학', '교수 민'),
('물리', '교수 피터슨');

-- 배열을 만들기 위한 중첩 쿼리
SELECT
    c.name AS course_name,
    array_agg(s.name) AS students,
    c.teacher
FROM
    courses c
LEFT JOIN
    students s
ON
    c.name = s.course
GROUP BY
    c.name, c.teacher;

결과:

이제 각 과목, 강사, 학생을 배열로 보여주는 깔끔한 테이블이 완성됐어.

배열로 데이터 필터링하기

배열 자체만으로도 강력하지만, 진짜 재미는 배열을 기준으로 데이터를 필터링할 때 시작돼. 예를 들어, 관심사 리스트에 특정 단어가 있는 유저만 뽑고 싶다거나, 각 주문의 가격이 특정 기준을 넘는 것만 고르고 싶을 때! 이런 거 다 SQL에서 바로 할 수 있어 — 굳이 앱에서 따로 처리할 필요 없어.

예시 1: 배열 요소로 행 필터링하기

예를 들어, 배열에 특정 값이 들어있는 행만 찾고 싶다고 해보자. 수학 과목에 등록된 학생을 찾는 경우처럼!

-- `ANY`를 써서 특정 과목에 등록된 학생 필터링
SELECT *
FROM students
WHERE course = ANY(ARRAY['수학', '물리']);

여기서 ANY는 값 배열을 지정하고, course가 배열 안 값 중 하나라도 맞으면 그 행을 반환해.

예시 2: 배열 교집합 체크하기

이번엔 student_interests 테이블이 있다고 하자. 학생의 관심사가 배열로 저장돼 있어. 우리 기준과 관심사가 겹치는 학생을 찾고 싶을 때!

-- 학생 관심사 테이블 만들기
CREATE TABLE student_interests (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests TEXT[]
);

-- 데이터 삽입
INSERT INTO student_interests (name, interests) VALUES
('알리사', ARRAY['프로그래밍', '음악']),
('밥', ARRAY['스포츠', '프로그래밍']),
('찰리', ARRAY['독서', '사진']),
('엠마', ARRAY['음악', '스포츠']);

-- 프로그래밍이나 음악에 관심 있는 학생 찾기
SELECT *
FROM student_interests
WHERE interests && ARRAY['프로그래밍', '음악'];

&& 연산자는 두 배열의 교집합을 체크해. 왼쪽 배열에 오른쪽 배열 값이 하나라도 있으면 그 행이 필터를 통과해.

결과:

배열 정렬하기

가끔은 배열 안 값의 순서가 중요할 때가 있어 — 여러 줄에서 배열을 만들거나, 데이터를 보여주기 전에 정렬하고 싶을 때! PostgreSQL은 쿼리에서 바로 배열 요소를 정렬할 수 있어, 따로 처리 안 해도 돼.

예시 1: 배열 내부 값 정렬하기

배열 안 요소를 정렬해야 할 때가 있어. 예를 들어, 학생의 관심사 배열을 알파벳 순으로 정렬해보자.

-- `array_sort()` 함수로 배열 요소 정렬하기
SELECT 
    name,
    array_sort(interests) AS sorted_interests
FROM 
    student_interests;

결과:

예시 2: 배열 길이로 행 정렬하기

이번엔 학생을 관심사 개수로 정렬해보자 — 제일 열정적인 학생부터 제일 "심심한" 학생까지!

-- 배열 길이로 행 정렬하기
SELECT 
    name, 
    interests, 
    array_length(interests, 1) AS interests_count
FROM 
    student_interests
ORDER BY 
    interests_count DESC;

결과:

예제에선 학생마다 관심사 개수가 같지만, 더 큰 테이블에선 이런 쿼리를 다양하게 응용할 수 있어.