단일 SQL 쿼리에서 여러 배열을 효율적으로 집계하는 방법은 무엇입니까?

단일 쿼리에서 다중 array_agg() 호출: 중첩 배열 집계

관계형 데이터베이스 영역에서는 다중 배열이 발생하는 상황이 발생합니다. 단일 쿼리로 집계해야 합니다. 이는 특히 조인이 관련된 경우 까다로운 작업이 될 수 있습니다.

문제

여러 조인을 통해 여러 테이블의 배열을 집계하려고 할 때 문제가 발생합니다. 결과 배열에는 불필요한 중복과 불일치가 포함될 수 있습니다. 예를 들어 여러 주소(주소)와 근무일(근무일)이 포함된 직원 테이블이 있을 수 있습니다.

SELECT name, age, array_agg(ad.street), arrag_agg(wd.day)
FROM employees e
JOIN address ad ON e.id = ad.employeeid
JOIN workingdays wd ON e.id = wd.employeeid
GROUP BY name, age

이 쿼리는 거리 이름과 근무일의 배열로 이어지지만 값이 중복됩니다.

해결책: 먼저 집계

이 문제를 해결하는 열쇠 조인을 수행하기 전에 배열을 집계하는 것입니다. 먼저 집계하면 행이 불필요하게 늘어나는 것을 방지할 수 있습니다.

SELECT e.id, e.name, e.age, e.streets, array_agg(wd.day) AS days
FROM (
   SELECT e.id, e.name, e.age, array_agg(ad.street) AS streets
   FROM   employees e 
   JOIN   address  ad ON ad.employeeid = e.id
   GROUP  BY e.id  -- PK covers whole row
   ) e
JOIN   workingdays wd ON wd.employeeid = e.id
GROUP  BY e.id, e.name, e.age;

이 쿼리에서는 주소 집계가 작업일 테이블과 조인되기 전에 하위 쿼리에서 수행됩니다. 이렇게 하면 한 세트의 거리 이름과 근무일만 각 직원과 연결됩니다.

또는 상관 하위 쿼리 및 JOIN LATERAL

직원에 대한 선택적 필터링의 경우 상관 관계 하위 쿼리 또는 JOIN LATERAL(Postgres 9.3 이상)을 사용할 수 있습니다. 채용됨:

SELECT name, age
    , (SELECT array_agg(street) FROM address WHERE employeeid = e.id) AS streets
    , (SELECT array_agg(day) FROM workingdays WHERE employeeid = e.id) AS days
FROM   employees e
WHERE  e.namer = 'peter';  -- very selective

JOIN LATERAL은 Postgres에서도 사용할 수 있습니다:

SELECT e.name, e.age, a.streets, w.days
FROM   employees e
LEFT   JOIN LATERAL (
   SELECT array_agg(street) AS streets
   FROM   address
   WHERE  employeeid = e.id
   GROUP  BY 1
   ) a ON true
LEFT   JOIN LATERAL (
   SELECT array_agg(day) AS days
   FROM   workingdays
   WHERE  employeeid = e.id
   GROUP  BY 1
   ) w ON true
WHERE  e.name = 'peter';  -- very selective

이러한 쿼리는 결과에 자격을 갖춘 모든 직원을 유지합니다.

위 내용은 단일 SQL 쿼리에서 여러 배열을 효율적으로 집계하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!