✔ Kundenbestellungen ausgeben

[Sasser]

Hallo

Ich habe in MySQL eine Kundentabelle und 2 Tabellen mit Bestellungen.

Nun möchte ich gerne jeden Kunden ausgeben und in den beiden anderen Tabellen die Summe, Anzahl der Bestellungen und das Datum der letzten Bestellung ausgeben lassen.

SELECT customer.customer_id, customer.gender, customer.firstname, customer.lastname,
     COUNT(sales1.customer_id) + COUNT(sales2.customer_id) AS sales_count, SUM(sales1.sale_amount) + SUM(sales2.sale_amount) AS sales_sum, GREATEST(sales1.sale_date, sales2.sale_date) AS sales_date
   FROM customer
   LEFT JOIN sales1 ON customer.customer_id = sales1.customer_id
   LEFT JOIN sales2 ON customer.customer_id = sales2.customer_id
     GROUP BY customer.customer_id

Leider verrechnet er sich komplett. Was mache ich falsch?

 

[Biber3]

Moin Sasser,

lass mich raten - du hast ein Vielfaches der tatsächlichen Umsätze und Bestellungen durch die beiden LEFT JOINs?

Du musst zuerst Sales1 und Sales2 nach customer_id aggregieren (damit du jeweils maximal einen/minimal keinen Datensatz je customer_id hast und dann joinen.

Sinngemäß und ungetestet etwa so:

SELECT c.customer_id, c.gender, c.firstname, c.lastname
  , sales1.cnt + sales2.cnt AS sales_count
  , sales1.amount + sales2.amount  AS sales_sum
  , GREATEST(sales1.saledate, sales2.saledate) AS sales_date
  FROM customer c
  LEFT JOIN  ( select COUNT(sales1.customer_id) as Cnt
  , sum(sales1.sale_amount) as amount
  , max(sales1.sale_date) as saledate
  from sales1 group by customer_id
  ) sales1 ON c.customer_id = sales1.customer_id
  LEFT JOIN  ( select COUNT(sales2.customer_id) as cnt
  , sum(sales2.sale_amount) as amount
  , max(sales2.sale_date) as saledate
  from sales2 group by customer_id
  ) sales2 ON c.customer_id = sales2.customer_id

Grüße
Biber

 

[BaseBallBatBoy]

Beachte auch folgendes:
Before MySQL 5.0.13, GREATEST() returns NULL only if all arguments are NULL. As of 5.0.13, it returns NULL if any argument is NULL.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_greatest

 

[BaseBallBatBoy]

Nachtrag: du musst wahrscheinlich noch eventuelle NULLs maskieren wenn z.B. ein Kunde nur in sales1 Werte hat, aber nicht in sales2

SELECT
  c.customer_id,
  c.gender,
  c.firstname,
  c.lastname,
  COALESCE(s1.cnt,0) + COALESCE(s2.cnt,0) AS sales_count,
  COALESCE(s1.amount,0) + COALESCE(s2.amount,0) AS sales_sum, 
  GREATEST(COALESCE(s1.saledate,0), COALESCE(s2.saledate,0)) AS sales_date
FROM customer c
LEFT JOIN 
  ( SELECT
        customer_id,
        COUNT(customer_id) AS cnt,
        SUM(sale_amount) AS amount,
        MAX(sale_date) AS saledate
    FROM sales1
    GROUP BY customer_id
  ) s1
ON c.customer_id = s1.customer_id
LEFT JOIN 
  ( SELECT
        customer_id,
        COUNT(customer_id) AS cnt,
        SUM(sale_amount) AS amount,
        MAX(sale_date) AS saledate
    FROM sales2
    GROUP BY customer_id
  ) s2
ON c.customer_id = s2.customer_id
;

 

[Sasser]

Wow, dass das jetzt eine so umfangreiche Abfrage wird, dachte ich nicht...

Aber es funktioniert jetzt wunderbar

Habe nur noch LEFT JOIN durch RIGHT JOIN ersetzt, da ich nur Kunden ausgegeben haben möchte, die auch Umsätze generiert haben.

Vielen Dank

 

[Sasser]

Ich habe nun die Abfrage um einen Zeitpunkt erweitert:

SELECT
      c.customer_id,
      c.gender,
      c.firstname,
      c.lastname,
      COALESCE(s1.cnt,0) + COALESCE(s2.cnt,0) AS sales_count,
      COALESCE(s1.amount,0) + COALESCE(s2.amount,0) AS sales_sum,
      GREATEST(COALESCE(s1.saledate,0), COALESCE(s2.saledate,0)) AS sales_date
    FROM customer c
    LEFT JOIN
      ( SELECT
            customer_id,
            COUNT(customer_id) AS cnt,
            SUM(sale_amount) AS amount,
            MAX(sale_date) AS saledate
        FROM sales1
        WHERE UNIX_TIMESTAMP(sale_date) >= '" . $from . "' AND UNIX_TIMESTAMP(sale_date) <= '" . $to . "'
        GROUP BY customer_id
      ) s1
    ON c.customer_id = s1.customer_id
    LEFT JOIN
      ( SELECT
            customer_id,
            COUNT(customer_id) AS cnt,
            SUM(sale_amount) AS amount,
            MAX(sale_date) AS saledate
        FROM sales2
        WHERE UNIX_TIMESTAMP(sale_date) >= '" . $from . "' AND UNIX_TIMESTAMP(sale_date) <= '" . $to . "'
        GROUP BY customer_id
      ) s2
    ON c.customer_id = s2.customer_id

Nutze ich hier besser nur JOIN anstatt LEFT JOIN? Also ich möchte nur Kunden angezeigt haben, die in dem Zeitraum auch Umsätze generiert haben. Andernfalls nicht.

 

[Biber3]

Moin Sasser,

dann wäre es sinnvoller, die beiden aggregierten Sales-Tabellen per UNION ALL zusammenzuschrapeln und von dort aus die customer-Tabeller zu joinen (INNER JOIN)

Skizze:

SELECT c.customer_id, c.gender, c.firstname, c.lastname
  , s.sales_count
  , s.sales_sum
  , s.sales_date
  FROM (select customer_id
  , sum(cnt) as sales_count
  , sum(amount) as sales_sum
  , max(saledate) as sales_date FROM (
  select 'sales1' as tab, customer_id
  , COUNT(customer_id) as Cnt
  , sum(sale_amount) as amount
  , max(sale_date) as saledate
  from sales1 group by customer_id
  -- where zeitpunkt ...
  UNION ALL
  select 'sales2' as tab, customer_id
  , COUNT(customer_id) as Cnt
  , sum(sale_amount) as amount
  , max(sale_date) as saledate
  from sales2 group by customer_id
  -- where zeitpunkt....
  ) xx
  group by customer_id
  ) s , customer c
  where s.customer_id=c.customer_id
  ;

Frage @BaseBallBatBoy:
Fallt folgendes nicht auf die Schnauze, wenn tatsächlich eines der beiden Datumme NULL ist und von COALESCE auf den Wert "numerisch 0" gepresst wird?
Dann würde doch GREATEST( 0-Wert, Datumswert) gemacht werden, oder?

... GREATEST(COALESCE(s1.saledate,0), COALESCE(s2.saledate,0)) AS sales_date...

Grüße
Biber

 

[BaseBallBatBoy]

@Biber
Ok, anstelle 0 würde man wohl besser ein dummy datum verwenden. Das Ding ist, wenn s1 Werte hat aber s2 nicht, wird es GREATEST automatisch zu NULL, was wohl auch nicht gewünscht ist.
Evtl. ist hier sowieso ein CASE besser geeignet...

 

[Sasser]

Funktioniert, aber sobald ich die WHERE-Bedingung einbaue, bekomme ich keine Resultate mehr

SELECT c.customer_id, c.gender, c.firstname, c.lastname, s.sales_count, s.sales_sum, s.sales_date
        FROM (SELECT customer_id, SUM(cnt) AS sales_count, SUM(amount) AS sales_sum, MAX(saledate) AS sales_date FROM (
    SELECT 'sales1' AS tab, customer_id, COUNT(customer_id) AS Cnt, SUM(sale_amount) AS amount, MAX(sale_date) AS saledate
        FROM sales1 GROUP BY customer_id
        WHERE UNIX_TIMESTAMP(sale_date) >= '" . $from . "' AND UNIX_TIMESTAMP(sale_date) <= '" . $to . "'
    UNION ALL
    SELECT 'sales2' AS tab, customer_id, COUNT(customer_id) AS Cnt, SUM(sale_amount) AS amount, MAX(sale_date) AS saledate
        FROM sales2 GROUP BY customer_id
        WHERE UNIX_TIMESTAMP(sale_date) >= '" . $from . "' AND UNIX_TIMESTAMP(sale_date) <= '" . $to . "'
        ) xx
        GROUP BY customer_id
        ) s , customer c
        WHERE s.customer_id=c.customer_id

 

[Sasser]

Habe es hinbekommen... hatte nicht genau geschaut.

SELECT c.customer_id, c.gender, c.firstname, c.lastname, s.sales_count, s.sales_sum, s.sales_date
        FROM (SELECT customer_id, SUM(cnt) AS sales_count, SUM(amount) AS sales_sum, MAX(saledate) AS sales_date FROM (
    SELECT 'sales1' AS tab, customer_id, COUNT(customer_id) AS Cnt, SUM(sale_amount) AS amount, MAX(sale_date) AS saledate
        FROM sales1
        WHERE UNIX_TIMESTAMP(sale_date) >= '" . $from . "' AND UNIX_TIMESTAMP(sale_date) <= '" . $to . "'
        GROUP BY customer_id
    UNION ALL
    SELECT 'sales2' AS tab, customer_id, COUNT(customer_id) AS Cnt, SUM(sale_amount) AS amount, MAX(sale_date) AS saledate
        FROM sales2
        WHERE UNIX_TIMESTAMP(sale_date) >= '" . $from . "' AND UNIX_TIMESTAMP(sale_date) <= '" . $to . "'
        GROUP BY customer_id
        ) xx
        GROUP BY customer_id
        ) s , customer c
        WHERE s.customer_id=c.customer_id

Vielen Dank für eure tolle Hilfe!