/*
 * Verwendung der Aggregations-Operationen am Beispiel der Haendler-DB
 */

-- Alle Ergebnisse (ohne Gruppierung und Aggregation)

SELECT hnr, name, adresse, wnr, typ, bezeichnung, preis, lieferzeit
FROM             haendler h  
       LEFT JOIN liefert  l USING (hnr)
       LEFT JOIN ware     w USING (wnr)
;


-- Array-Aggregation

SELECT   hnr, name, adresse, 
         ARRAY_SORT_ASC(ARRAY_AGGREGATE(wnr)) AS warennummern,
         ARRAY_AGGREGATE(typ || ': ' || bezeichnung) AS artikel
FROM               haendler h  
         LEFT JOIN liefert  l USING (hnr)
         LEFT JOIN ware     w USING (wnr)
GROUP BY hnr, name, adresse
;


SELECT   hnr, name, adresse,
         ARRAY_SORT_ASC(ARRAY_AGGREGATE_DISTINCT(wnr)) AS warennummern,
         ARRAY_AGGREGATE_DISTINCT(typ || ': ' || bezeichnung) AS artikel
FROM               haendler h
         LEFT JOIN liefert  l USING (hnr)
         LEFT JOIN ware     w USING (wnr)
GROUP BY hnr, name, adresse
;


-- Postgres-Funktionen: ARRAY_AGG

SELECT   hnr, name, adresse,
         ARRAY_SORT_ASC(ARRAY_AGG(wnr)) AS warennummern,
         ARRAY_AGG(typ || ': ' || bezeichnung) AS artikel
FROM               haendler h
         LEFT JOIN liefert  l USING (hnr)
         LEFT JOIN ware     w USING (wnr)
GROUP BY hnr, name, adresse
;


-- Text-Aggregation (TEXT ist die Postgres-Version von CLOB)

SELECT   hnr, name, adresse,
         TEXT_AGGREGATE_ASC(wnr) AS warennummern,
         TEXT_AGGREGATE(typ || ': ' || bezeichnung) AS artikel
FROM               haendler h
         LEFT JOIN liefert  l USING (hnr)
         LEFT JOIN ware     w USING (wnr)
GROUP BY hnr, name, adresse
;

-- Oder auch mit ARRAY-AGGREGATE, Sortierung und anschließender String-Transformation
SELECT   hnr, name, adresse,
         ARRAY_TO_STRING(ARRAY_SORT_ASC(ARRAY_AGGREGATE(wnr)), ', ') AS warennummern,
         ARRAY_TO_STRING(ARRAY_SORT_ASC(ARRAY_AGGREGATE(typ || ': ' || bezeichnung)), ', ') AS artikel
FROM               haendler h
         LEFT JOIN liefert  l USING (hnr)
         LEFT JOIN ware     w USING (wnr)
GROUP BY hnr, name, adresse
;


SELECT   hnr, name, adresse,
         TEXT_AGGREGATE_DISTINCT_DESC(wnr) AS warennummern,
         TEXT_AGGREGATE_DISTINCT(typ || ': ' || bezeichnung) AS artikel
FROM               haendler h
         LEFT JOIN liefert  l USING (hnr)
         LEFT JOIN ware     w USING (wnr)
GROUP BY hnr, name, adresse
;


/*
 * Alternative ohne Aggregation
 */


-- mit Subquery (mit Ergebnissen fuer Haendler, die nichts liefern)

SELECT hnr, ARRAY(SELECT wnr FROM liefert l WHERE l.hnr = h.hnr) AS wnrs
FROM   haendler h
;

SELECT hnr, ARRAY(SELECT DISTINCT wnr FROM liefert l WHERE l.hnr = h.hnr) AS wnrs
FROM   haendler h
;

SELECT hnr, ARRAY(SELECT DISTINCT wnr 
                  FROM liefert l 
                  WHERE l.hnr = h.hnr
                  ORDER BY wnr DESC
                 ) AS wnrs
FROM   haendler h
;

SELECT hnr, ARRAY_TO_STRING
            (ARRAY(SELECT wnr FROM liefert l WHERE l.hnr = h.hnr),
             ', '
            ) AS wnrs
FROM   haendler h
;

SELECT hnr, ARRAY_TO_STRING
            (ARRAY(SELECT DISTINCT wnr FROM liefert l WHERE l.hnr = h.hnr),
             ', '
            ) AS wnrs
FROM   haendler h
;


-- mit Aggregation (mit Ergebnissen fuer Haendler, die nichts liefern)

SELECT   hnr, ARRAY_AGGREGATE(wnr) AS wnrs
FROM     haendler LEFT JOIN liefert USING (hnr)
GROUP BY hnr 
;

SELECT   hnr, ARRAY_AGGREGATE_DISTINCT(wnr) AS wnrs
FROM     haendler LEFT JOIN liefert USING (hnr)
GROUP BY hnr  
;

SELECT   hnr, ARRAY_AGGREGATE_DISTINCT_DESC(wnr) AS wnrs
FROM     haendler LEFT JOIN liefert USING (hnr)
GROUP BY hnr
;

SELECT   hnr, TEXT_AGGREGATE(wnr) AS wnrs
FROM     haendler LEFT JOIN liefert USING (hnr)
GROUP BY hnr  
;

SELECT   hnr, TEXT_AGGREGATE_DISTINCT(wnr) AS wnrs
FROM     haendler LEFT JOIN liefert USING (hnr)
GROUP BY hnr
;


-- mit Aggregation (ohne Ergebnissen fuer Haendler, die nichts liefern)

SELECT   hnr, ARRAY_AGGREGATE(wnr) AS wnrs
FROM     liefert
GROUP BY hnr 
;

SELECT   hnr, ARRAY_AGGREGATE_DISTINCT(wnr) AS wnrs
FROM     liefert
GROUP BY hnr  
;

SELECT   hnr, ARRAY_AGGREGATE_DISTINCT_DESC(wnr) AS wnrs
FROM     liefert
GROUP BY hnr  
;

SELECT   hnr, TEXT_AGGREGATE(wnr) AS wnrs
FROM     liefert
GROUP BY hnr  
;

SELECT   hnr, TEXT_AGGREGATE_DISTINCT(wnr) AS wnrs
FROM     liefert
GROUP BY hnr  
;