配列を使った複雑なクエリ例：集約、フィルタリング、ソート

今日は、SQLのクエリで配列をどう使いこなすかを見ていくよ。値をグループ化したり、中身でフィルタしたり、配列の中で直接ソートしたり。これ、ただの理論じゃなくて、レポートや分析、パーソナライズとか、現場でガチで使うテク。コツさえ掴めば超簡単！今から一緒にやってみよう。

配列でデータを集約する

配列の真価が発揮されるのは、データをグループ化したいとき。複数行をバラバラに取るんじゃなくて、欲しい値をひとつのキレイな配列にまとめちゃう。これで分析も楽だし、出力もスッキリ、余計なサブクエリも減る。実際にどうやるか見てみよう！

例1：array_agg()でデータを配列にまとめる

複数行の値をグループごとに配列にまとめたいときは、array_agg()が超便利。配列集約で一番使える関数だよ。

-- studentsテーブルがあって、id, name, courseのカラムがあるとする
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：ネストした配列を作る

もしもう一つテーブルがあって、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だけでできるから、アプリ側で余計なロジック書かなくてOK！

例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['プログラミング', '音楽'];

&&演算子は、2つの配列の重なりをチェックする。左の配列のどれかが右の配列にあれば、その行はフィルタを通るよ。

結果：

配列のソート

配列の中の値の順番が大事なときもあるよね。特に、いろんな行から配列を作ったり、表示用にデータを整えたいとき。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;

結果：

この例だと全員興味の数は同じだけど、大きなテーブルならこのクエリを応用できるよ！