✔ MySQL: Ermittlung des letzten positiven Eintrags einer "Partition" (1-n)

[Zvoni]

Hey @Zvoni,

lieben Dank für deine Zeit. Ich habs jetzt druchgetestet und muss feststellen, dass AnzahlNegContractDanach die Anzahl der Verträge ausgibt die sich nicht in dem Status 'COMPLETED' befinden. Ich bräuchte die Anzahl der Verträge die nach dem letzten positiven Vertrag ersellt wurden.

In dem Beispiel haut es zufäig hin, weil nur insgesamt 2 Verträge verfürgbar sind die nicht den Status "COMPLETED".

Schau genauer hin: In Zeile 27 von meiner letzten SQL

ON d.user_id=u.user_id AND d.created>c.created and d.rn=1

d.created muss grösser als c.created sein.
SubQuery "c" liefert die letzte contract_id und Datum mit status Completed
Subquery "d" geht auf status<>"Completed", im ON-Filter verlange ich aber dass das Datum der offenen Verträge grösser ist als dem letzten Datum mit Completed
Theoretisch könnte ich sogar die WHERE-Klausel in Zeile 26 weglassen.

Aber ich werd mir dennoch deine neuen Beispieldaten bei mir rein laden und testen
Wird aber morgen.

EDIT: OK, ich sehs. Muss ich schauen

 

[Zvoni]

OK, ich glaub ich habs.
Gefällt mir aber gar nicht. Kaskadierende SQL's hasse ich wie die Pest

SELECT 
    A.UserID, 
    A.LetztPosContractId, 
    A.LetztPosContractStatus, 
    A.LetztPosContractDatum, 
    (SELECT COUNT(Contract_id) FROM contracts     
        WHERE status<>'COMPLETED' AND user_id=A.UserID AND created>A.LetztPosContractDatum) As AnzahlNegContractDanach
FROM 
(SELECT
    u.user_id As UserID,
    c.contract_id As LetztPosContractId,
    c.status As LetztPosContractStatus,
    c.created As LetztPosContractDatum     
FROM user as u
LEFT JOIN
    (SELECT
        contract_id,
        user_id,
        status,
        created,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created DESC) AS RN 
    FROM contracts
    WHERE status='COMPLETED') As C
ON c.user_id=u.user_id AND c.RN=1) AS A

Ergibt:

UserID	LetztPosContractId	LetztPosContractStatus	LetztPosContractDatum	AnzahlNegContractDanach
05159679-3d96-11ed-9bcc-80e82cdc1447	358e9fa1-3d96-11ed-9bcc-80e82cdc1447	COMPLETED	2011-04-07 20:32:51	2
08052059-4e18-11ed-86f3-5c3a456597bc	856223c0-4e18-11ed-86f3-5c3a456597bc	COMPLETED	2011-10-27 18:26:45	1
a3339c76-4e18-11ed-86f3-5c3a456597bc	ba7c2f57-4e18-11ed-86f3-5c3a456597bc	COMPLETED	2011-04-04 00:31:32	3

 

[canju]

Top, dIe Anzahl nach dem letzten positiven Vertrag wird jetzt korrekt ausgegeben.
Bei den überschaubaren Beispieldatensätzen gehts auch flott. Muss noch schauen wie ich das bei größeren Datenmenge performanter hinbekomme. Habe die Abfrage bei > 150k Datensätze nach 10 Minuten erstmal abgebrochen und warte bis heute Abend wenn weniger workload auf der DB herrscht.

Kaskadierende SQL's hasse ich wie die Pest

Geht mit ähnlich

Vielen lieben Dank nochmal Zvoni.

 

[Zvoni]

Ok, diesmal ohne Kaskade (wusst ichs doch, dass es geht.)

SELECT
    u.user_id As UserID,
    c.contract_id As LetztPosContractId,
    c.status As LetztPosContractStatus,
    c.created As LetztPosContractDatum,
    count(b.contract_id) AS AnzahlNegContractDanach
FROM user as u
LEFT JOIN
    (SELECT
        contract_id,
        user_id,
        status,
        created,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created DESC) AS RN
    FROM contracts
    WHERE status='COMPLETED') As C
ON c.user_id=u.user_id AND c.RN=1
LEFT JOIN
    (SELECT
        user_id,
        contract_id,
        created
    FROM contracts
    WHERE status<>'COMPLETED') As B
ON B.user_id=u.user_id AND B.created>c.created
GROUP BY u.user_id, c.contract_id,c.status,c.created

Ergibt:

UserID	LetztPosContractId	LetztPosContractStatus	LetztPosContractDatum	AnzahlNegContractDanach
05159679-3d96-11ed-9bcc-80e82cdc1447	358e9fa1-3d96-11ed-9bcc-80e82cdc1447	COMPLETED	2011-04-07 20:32:51	2
08052059-4e18-11ed-86f3-5c3a456597bc	856223c0-4e18-11ed-86f3-5c3a456597bc	COMPLETED	2011-10-27 18:26:45	1
a3339c76-4e18-11ed-86f3-5c3a456597bc	ba7c2f57-4e18-11ed-86f3-5c3a456597bc	COMPLETED	2011-04-04 00:31:32	3

 

[Yaslaw]

Kann man davon ausgeehen, dass jede user_id aus contracts in der Tabelle user vorhanden ist? Wenn ja, lasst den Join weg. Ausser die USerID nehmt ihr nix aus der Usertabelle.
Um Verschachtellungen zu vereinfachen gibt es bei MySQL den WIHT-Befehl. Mit dem kann man brav Selects vorbereiten und später die Resultate zusammensetzen. Hilft auch beim debuggen.

Das ganze vereinfacht sieht dan etwa so aus (ps, habe gleichzeitig wie Zvoni daran gearbeited und ebenfalls den Count in ein GROUP gesetzt

-- Alle Compled Contracts mit Rownum erweitert
WITH T_COMPLETED AS (
 	SELECT contract_id, user_id, status, created,
   	ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created DESC) AS rn
	FROM contracts
	WHERE status='COMPLETED'
),
-- Completed mit rownum=1
T_DATA AS (
	SELECT user_id, contract_id, status, created
	FROM T_COMPLETED
	WHERE rn = 1
),
-- All Nicht Completed Contracts
T_OTHER AS (
	SELECT contract_id, user_id, created
	FROM contracts
	WHERE STATUS<>'COMPLETED'
)

SELECT 
   T_DATA.user_id, T_DATA.contract_id, T_DATA.status, T_DATA.created,
	COUNT(T_OTHER.contract_id)    
FROM T_DATA LEFT JOIN T_OTHER 
    ON T_OTHER.user_id=T_DATA.user_id AND T_OTHER.created > T_DATA.created
GROUP BY T_DATA.user_id, T_DATA.contract_id, T_DATA.status, T_DATA.created

PS. JOIN mit einem Fixwert finde ich nicht gut. Je früher filtern umso schneller. Ich weiss nicht, ob das einen negativen EInfluss auf die Performance hat.

 

[Zvoni]

die user_id aus user (und damit der Join) muss bleiben. Er kann ja user haben, die noch keinen Vertrag haben (oder keinen der den Kriterien entspricht). Dann muss rechts von der user_id soweiso leer ausgeworfen werden.
Ausserdem will er ja diverse Felder aus contracts (redundant) in die user-tabelle einfügen (Anfang des Threads).

Wir zwei fangen jetzt nicht mit Filter in der ON-Klausel an *gg*
aber dein T_DATA is de facto eine Kaskade auf T_COMPLETED
wobei ich jetzt auch nicht weiss, was schneller ist: Der LEFT JOIN oder die Kaskade.
Müsste man mal gegen ne Tabelle werfen, die ein paar tausende Einträge hat

 

[Yaslaw]

Bin ja ohne IT-Theorien aufgewachsen. Was bedeutet Kaskade im Zusammenhang mit SQL? Ich wurde da nicht wirklich fündig (keine Erklärungen auf Englisch, das verstehe ich nur schlecht)

 

[canju]

Hey ihr zwei,

die Version von Zvoni ohne Kaskade funktioniert auch super, aber bei vielen Datensätzen dauert diese extrem lange. Mit gesetztem Limit 100 braucht die Abfrage ~ 5 Minuten.
Kurz bevor Yaslaw geposted hat bin ich endich mal auf den trichter gekommen, dass sich auch mehrere WITH (CTE's) miteinander verwenden lassen. Die folgende Lösung liefert das gewünschte Ergebnis für ~150k Datensätze in ~15 Sekunden:

WITH contract_rank AS (
    SELECT
        contract_id,
        user_id,
        status,
        created,
        RANK() OVER(PARTITION BY user_id ORDER BY created ASC) AS 'contract_rank'
    FROM contracts
    ORDER BY created ASC
),

last_contract_rank AS (
    SELECT
        contract_id,
        user_id,
        MAX(contract_rank) AS last_positive_contract_rank
    FROM contract_rank
    WHERE 1
            AND status IN ('COMPLETED')
    GROUP BY user_id
)

SELECT
    c.contract_id,
    c.user_id,   
    (
        MAX(contract_rank.contract_rank)
         - MAX(last_contract_rank.last_positive_contract_rank)
    ) AS 'non_positive_contracts_after_last_positive'
    
FROM contracts AS c
LEFT JOIN contract_rank ON c.contract_id = contract_rank.contract_id
LEFT JOIN last_contract_rank ON c.user_id = last_contract_rank.user_id
WHERE 1

GROUP BY c.user_id

Ich setze zunächst den generellen Rank der Contracts, dann den letzten positiven Rank und ermittle im Anschlusss dann einfach die Differenz:

    (
        MAX(contract_rank.contract_rank)
         - MAX(last_contract_rank.last_positive_contract_rank)
    ) AS 'non_positive_contracts_after_last_positive'

Das IN AND status IN ('COMPLETED') setze ich, weil bereits absehbar ist, dass es in Zukunft mehrere positive Status geben wird.

Danke euch beiden nochmal sehr für eure Unterstützung . Hab wieder was dazugelernt

Grüße,
canju

 

[Zvoni]

Bin ja ohne IT-Theorien aufgewachsen. Was bedeutet Kaskade im Zusammenhang mit SQL? Ich wurde da nicht wirklich fündig (keine Erklärungen auf Englisch, das verstehe ich nur schlecht)

"Kaskade" = SQL-Abfrage, die auf ein Sub-Query zugreift -->
zuerst wird inneres SELECT ausgeführt, dann das äussere

 

[Yaslaw]

@Zvoni
Also häufig die performante Lösung da zuerst gefiltert wird und dann zusammengesetzt. Ist in vielen meiner Lösungen drin, weil es einfach die performanteste Lösung ist bei komplexen SQL.
Es kommt halt schon drauf an, ob der JOIN 2 mal mehrere Millionen Datensätze verbindet oder wenn iene Seite davon auf 100 reduztiert wurden.
Schau dir mal bei vielen Daten und komplizierten SQL den Explain Plan an....