Berechnungen in SQL

[Felix_83]

Hi zusammen,

ich würde gerne eine Art Mess-Instrument / Dashboard - wie auch immer man es nennen möchte - entwickeln. In diesem soll basierend auf einer Ursprungstabelle ein entsprechendes Counting stattfinden. Hier einmal ein Beispiel wie die Ursprungstabelle aussieht:

id	Leadabgabe	Name	Reaktion	Marketing-Kanal
1	01.01.2019	Max	Zusage	Online
2	15.01.2019	Lisa	offen	Online
3	20.02.2019	Tom	Zusage	Online
4	15.03.2019	Anton	offen	Online
5	20.03.2019	Marie	Zusage	Telefonaktion
6	22.03.2019	Alexander	Absage	Telefonaktion
7	01.04.2019	Susan	Zusage	Telefonaktion
9	01.04.2019	Alfred	Absage	Online
10	03.04.2019	Saskia	offen	Online
11	10.05.2019	Axel	Zusage	Online
12	01.06.2019	Sven	offen	Online
13	15.06.2019	Steffi	Zusage	Online
14	01.07.2019	Manu	Absage	Online

So soll das gewünschtes Ergebnis aussehen:

Quartal	Anzahl Leads	Zusagen	Absagen	Reaktion offen	Lead_to_Zusage
Q1	4	2		2	50%
Q2	5	2	1	2	40%
Q3	1		1		0%

Ist so eine Darstellung möglich? (Sorry für die hässliche Darstellung hier) Bin mir hier gerade nicht sicher bzgl. der Herangehensweise...

Vielen Dank für jeden Tipp.

LG Felix

[defo2]

zunächst zur Darstellung:
die richtige Herangehensweise wäre, wenn die Betreiber die Forensoftware ordentlich einrichten.

Zu deiner Frage:
Du musst die Daten gruppieren und das Datum auf Quartal aggregieren:
Für den Anfang vielleicht erstmal auf Monat mit DATE_FORMAT(date, format)
https://www.w3schools.com/sql/func_m...ate_format.asp
ansonsten entsprechend umrechne auf Quartal und ggf das Jahr mit anzeigen (rausziehen), damit es nicht zu Aggregationsfehlern kommt.

Spalte "Reaktion" mehrfach auswerten und die ensprechende Zielspalte berechnen, Schema:
..
sum(case Reaktion when "Zusage" then 1 else 0 end case) as Leadszusage
..

Prozentwert:
Am einfachsten in einer zweiten Abfrage-Schicht die Zahlen addieren und den Lead Anteil berechnen und zusätzlich ausgeben.

P.S.
Hab grad gesehen, dass mySQL für das Quartal die Quarter Funktion anbietet. (Warum das keine Formatoption ist, muss ich nicht verstehen)
Case Statement korrigiert

[Christian Marquardt]

M.E. ist die Zietabelle falsch:

Quartal	Anzahl Leads	Zusagen	Absagen	Reaktion offen	Lead_to_Zusage
Q1	6	3	1	2	50
Q2	6	3	1	2	50
Q3	1		1		0

[Felix_83]

Hi zusammen,

zunächst einmal vielen Dank für die Inspiration. @Christian: Du hast absolut recht, ich hatte vergessen anzufügen, dass ich nur die aus dem Marketing-Kanal "Online" angezeigt haben möchte. Ich habe das Ganze bislang so gelöst:

select
  Quartal,

   /* Leads aus Online-Kanal*/

      nullif(
    sum(
      case
        when (template = 'Online') then 1
        else 0
      end
    ),
    0
  ) as "Leads aus Online",

 /* Zusagen aus Online-Kanal*/

      nullif(
    sum(
      case
        when (template = 'Zusagen_Online') then 1
        else 0
      end
    ),
    0
  ) as "Zusagen aus Online"

from
  (
    /* all sources into one */
    select
      QUARTER(created_at) Quartal,
      id tbk_id,
      'Online' template
    from
      Charity ch
     WHERE
    ch.kanal = 'Online'
      DATE(created_at) >= '2019-01-01'

UNION ALL

    select
      QUARTER(created_at) Quartal,
      id tbk_id,
      'Zusagen_Online' template
    from
      Charity ch
     WHERE
    ch.kanal = 'Online'
      DATE(created_at) >= '2019-01-01'
    WHERE
    ch.kanal = 'Online' AND ch.reaktion = 'Zusage'

  ) all_sources
  /* end of all sources */
group by
  Quartal
order by
  Quartal

Das counting funktioniert soweit und in dieser Form würde ich auch die anderen Spalten erstellten. Nur weiß ich an dieser Stelle nicht, wie ich in dieser Form die Lead_to_Zusage schreiben soll.

Jemand eine Idee?

LG Felix

[Christian Marquardt]

ich würde es probieren (als Pseudocode)

select sub.quartal, sub.quartal, sub.anzahl, sub.zulagen, sub.absagen, sub.offen, (sub.zusagen*100)/sub.anzahl from
(select quartal, anzahl,zulagen,absagen, offen from Tabelle group by quartal) sub

[defo2]

select Jahr, Quartal, Zusagen, Absagen, Offen,
       100/(Zusagen+ Absagen + Offen)*Zusagen as LeadToZusage
  from (
        select year(Leadabgabe) as Jahr,
               quarter(Leadabgabe) as Quartal,
               sum(case Reaktion when "Zusage" then 1 else 0 end ) as Zusagen,
               sum(case Reaktion when "Absage" then 1 else 0 end ) as Absagen,
               sum(case Reaktion when "offen"  then 1 else 0 end ) as Offen
        from fleads
        where ´Marketing-Kanal´="Online"
        group by Jahr, quarter(Leadabgabe)
       )x ; 

P.S.: Warum union?
Union könnte man seiner Natur nach häufig einsetzten und Teilergebnisse damit zusammensetzen. Das ist aber unnötig, wenn man keine Teilergebnisse produziert.
Je größer die Basistabelle, desto langsamer macht jedes Union die Abfrage.
Union setzt man besonders dann ein, wenn die Datenquellen unterschiedlich sind (verschiedene Tabellen).

[Felix_83]

Hi, beherrsche leider nur Basic SQL und kein PHP. Ich nehme an in meine oben skizzierte Code-Struktur lässt sich der Prozentwert nicht einbauen?

Dennoch danke.

LG Felix

[Christian Marquardt]

Mal bitte hinschauen!!!
a) habe ich dir den SQL-Code genannt
b) ist das der gleiche Code wie von Defo2
c) ist das SQL bloß in das Forentag 'PHP' einschlossen

[Felix_83]

select Jahr, Quartal, Zusagen, Absagen, Offen,
100/(Zusagen+ Absagen + Offen)*Zusagen as LeadToZusage
from (
select year(Leadabgabe) as Jahr,
quarter(Leadabgabe) as Quartal,
sum(case Reaktion when "Zusage" then 1 else 0 end ) as Zusagen,
sum(case Reaktion when "Absage" then 1 else 0 end ) as Absagen,
sum(case Reaktion when "offen" then 1 else 0 end ) as Offen
from fleads
where ´Marketing-Kanal´="Online"
group by Jahr, quarter(Leadabgabe)
)x ; 

P.S.: Warum union?
Union könnte man seiner Natur nach häufig einsetzten und Teilergebnisse damit zusammensetzen. Das ist aber unnötig, wenn man keine Teilergebnisse produziert.
Je größer die Basistabelle, desto langsamer macht jedes Union die Abfrage.
Union setzt man besonders dann ein, wenn die Datenquellen unterschiedlich sind (verschiedene Tabellen).

Ah ok, perfekt. Nähere mich damit dem Ergebnis. Wie bekomme ich denn in deinen Code die Anzahl der Leads hinein? Also unabhängig vom Status der Reaktion soll in einer Spalte die Anzahl der Total Leads ausgewiesen werden. Und bei der Reaktion "offen" steht in der Tabelle ein Null-Wert. Habe versucht mit deinem Code den Bereich so zu ergänzen, aber das funktioniert nicht:

sum(case Reaktion when IS NULL then 1 else 0 end ) as Offen

Was mache ich hier falsch?

LG Felix

[Christian Marquardt]

Wie bekomme ich denn in deinen Code die Anzahl der Leads hinein?

Also unabhängig vom Status der Reaktion soll in einer Spalte die Anzahl der Total Leads ausgewiesen werden.

Da muss in den Code
quarter(Leadabgabe) as Quartal,
count(*),
sum(case Reaktion when "Zusage" then 1 else 0 end ) as Zusagen,

oder wie es im Pseudocode stand
(select quartal, anzahl,zusagen,absagen, offen from Tabelle group by quartal) sub

Da du den nullwert mit 1 bewerten willst
sum(case when Reaktion= "offen" OR Reaktion is null then 1 else 0 end ) as Offen

[Felix_83]

Da muss in den Code
quarter(Leadabgabe) as Quartal,
count(*),
sum(case Reaktion when "Zusage" then 1 else 0 end ) as Zusagen,

oder wie es im Pseudocode stand
(select quartal, anzahl,zusagen,absagen, offen from Tabelle group by quartal) sub

Der Nullwert sollte im else-Zweig landen

Perfekt, die Leadzählung funktioniert!!! Danke. Aber Nochmal zur hierzu:

  sum(case Reaktion when IS NULL then 1 else 0 end ) as Offen

In der Ursprungstabelle sind die Zellen, die als "offen" benannt und gezählt werden sollen mit NULL befüllt...

[Christian Marquardt]

Siehe EDIT oben
sum(case when Reaktion= 'offen' OR Reaktion is null then 1 else 0 end ) as Offen

[Felix_83]

siehe edit oben
sum(case when reaktion= 'offen' or reaktion is null then 1 else 0 end ) as offen

perfekt, tausend dank!!!

[Christian Marquardt]

Wenn es jetzt geht -> sofort ins Wochenende gehen, bevor wieder Ärger dazu kommt

[Felix_83]

:-) Alle klar