sql - 2 개의 복잡한 GROUP BY/PARTITION BY 쿼리를 하나의 쿼리로 구성

GROUP BY 를 사용하여 단 하나의 쿼리로 작업을 해결하려고했습니다. 그러나 WITH 를 사용하여 복잡한 솔루션을 얻었습니다.  조항-그래서 2 개의 쿼리가 결합되었습니다. 하지만 과도하게 엔지니어링하는 경향이 있다고 생각합니다.

3 개의 테이블이 있습니다 :

(아래스키마섹션의 테이블 레이아웃에 대한 전체 SQL 참조)

• 저자
• 대상
• 책

예를 들어, 작업을 읽습니다

find authors who have written books on more than 2 subjects

내 연구 중에 나는 복잡하고 과도하게 설계된 2 가지 솔루션을 생각 해냈다 :

솔루션 1- PARTITION BY 사용  조항은 행 번호를 해결

WITH temp1 AS 
(
    SELECT author_id, subject_id, ROW_NUMBER () OVER (PARTITION BY author_id) as rn
    FROM books
    GROUP BY author_id, subject_id
)
SELECT DISTINCT author_id FROM temp1 WHERE rn > 2
;

솔루션 2- GROUP BY 를 포함하는 2 개의 쿼리 결합  조항

WITH temp1 AS 
(
    SELECT author_id, subject_id
    FROM books
    GROUP BY author_id, subject_id
)
SELECT DISTINCT author_id, COUNT(*) FROM temp1
GROUP BY author_id
HAVING COUNT(*) > 2
;

단 하나의 쿼리로 이러한 작업을 해결할 수있는 방법이 있습니까?

<시간> 스키마

아래 SQL은 PostgreSQL에 적용됩니다

CREATE SEQUENCE authors_id_seq;
CREATE TABLE authors
(
    id bigint NOT NULL DEFAULT nextval('authors_id_seq'::regclass),
    name TEXT NOT NULL,
    CONSTRAINT pk_authors PRIMARY KEY (id)
);
CREATE SEQUENCE subjects_id_seq;
CREATE TABLE subjects
(
    id bigint NOT NULL DEFAULT nextval('subjects_id_seq'::regclass),
    name TEXT NOT NULL,
    CONSTRAINT pk_subjects PRIMARY KEY (id)
);
CREATE SEQUENCE books_id_seq;
CREATE TABLE books
(
    id bigint NOT NULL DEFAULT nextval('books_id_seq'::regclass),
    name TEXT NOT NULL,
    popularity int NOT NULL,
    author_id bigint NOT NULL,
    subject_id bigint NOT NULL,
    CONSTRAINT pk_books PRIMARY KEY (id),
    CONSTRAINT fk_books_authors_author_id FOREIGN KEY (author_id)
        REFERENCES authors (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fk_books_subjects_subject_id FOREIGN KEY (subject_id)
        REFERENCES subjects (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

종자 데이터 :

INSERT INTO authors (name) VALUES 
('John Doe'),
('Friedrich Nietzsche');
INSERT INTO subjects (name) VALUES 
('anatomy'),
('geography'),
('nature');
INSERT INTO books (name, popularity, author_id, subject_id) VALUES
('body', 5, 1, 1),
('earth', 10, 1, 2),
('ocean', 9, 1, 2),
('animals', 9, 1, 3),
('birds', 7, 2, 3);