-- Nachfahren
--
-- Um die Wirkungsweise der Listen-Aggregation ARRAY_AGG
-- (Standard-SQL) zu demonstrieren, werden alle Personen 
-- zusammen mit ihren Kindern aufgelistet.
SELECT   e_id, e_name,
         ARRAY_AGG(p_id)   AS n_ids,
         ARRAY_AGG(p_name) AS n_namen
FROM     eltern
GROUP BY e_id, e_name
ORDER BY e_name
;

SELECT   v_id, v_name,
         ARRAY_AGG(p_id)   AS n_ids,
         ARRAY_AGG(p_name) AS n_namen
FROM     vorfahren_2
GROUP BY v_id, v_name
ORDER BY v_name
;

SELECT   v_id, v_name,
         ARRAY_AGG(DISTINCT p_id)   AS n_ids,
         ARRAY_AGG(DISTINCT p_name) AS n_namen
FROM     vorfahren_2
GROUP BY v_id, v_name
ORDER BY v_name
;

-- Mit einem Right Join werden auch die Personen
-- berücksichtigt, die noch keine Kinder/Nachfahren haben.
SELECT   p.p_id, p.p_name,
         ARRAY_AGG(e.p_id)   AS n_ids,
         ARRAY_AGG(e.p_name) AS n_namen
FROM     eltern e RIGHT JOIN person p ON e.e_id = p.p_id
GROUP BY p.p_id, p.p_name
ORDER BY p_name
;

SELECT   p.p_id, p.p_name,
         ARRAY_AGG(v.p_id)   AS n_ids,
         ARRAY_AGG(v.p_name) AS n_namen
FROM     vorfahren_2 v RIGHT JOIN person p ON v.v_id = p.p_id
GROUP BY p.p_id, p.p_name
ORDER BY p_name
;

SELECT   p.p_id, p.p_name,
         ARRAY_AGG(DISTINCT v.p_id)   FILTER (WHERE v.p_id   IS NOT NULL) AS n_ids,
         ARRAY_AGG(DISTINCT v.p_name) FILTER (WHERE v.p_name IS NOT NULL) AS n_namen
FROM     vorfahren_2 v RIGHT JOIN person p ON v.v_id = p.p_id
GROUP BY p.p_id, p.p_name
ORDER BY p_name
;

-- Selbst-definierte Aggregatsfunktion: ARRAY_AGGREGATE_DISTINCT
SELECT   p.p_id, p.p_name,
         ARRAY_AGGREGATE_DISTINCT(v.p_id)   AS n_ids,
         ARRAY_AGGREGATE_DISTINCT(v.p_name) AS n_namen
FROM     vorfahren_2 v RIGHT JOIN person p ON v.v_id = p.p_id
GROUP BY p.p_id, p.p_name
ORDER BY p_name
;

-- Anstelle von echten Listen, kann man auch String-Arrays erzeugen.
SELECT   p.p_id, p.p_name,
         STRING_AGG(CAST(e.p_id AS VARCHAR), ' , ') AS n_ids,
         STRING_AGG(e.p_name,                ', ')  AS n_namen
FROM     eltern e RIGHT JOIN person p ON e.e_id = p.p_id
GROUP BY p.p_id, p.p_name
ORDER BY p_name
;

SELECT   p.p_id, p.p_name,
         STRING_AGG(CAST(v.p_id AS VARCHAR), ' , ') AS n_ids,
         STRING_AGG(v.p_name,                ', ')  AS n_namen
FROM     vorfahren_2 v RIGHT JOIN person p ON v.v_id = p.p_id
GROUP BY p.p_id, p.p_name
ORDER BY p_name
;

SELECT   p.p_id, p.p_name,
         STRING_AGGREGATE_DISTINCT(v.p_id)    AS n_ids,
         STRING_AGGREGATE_DISTINCT(v.p_name)  AS n_namen
FROM     vorfahren_2 v RIGHT JOIN person p ON v.v_id = p.p_id
GROUP BY p.p_id, p.p_name
ORDER BY p_name
;