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

canju

Erfahrenes Mitglied
Hallo liebe Community,

ich nutze schon eine Weile die WINDOW-Functions in MySQL (MariaDB 10.3), diese lassen sich aber leider nicht in der WHERE Clause verwenden. Daher meine Hoffnung, dass sich einer von euch schon mal mit einem ähnlichen Case beschäftigt hat und einen guten Lösungsweg kennt.

Ich habe die beiden Tabellen: user und contracts

user:
Code:
user_id                             |created                |
------------------------------------+-----------------------+
05159679-3d96-11ed-9bcc-80e82cdc1447|2022-09-08 20:10:00.000|
SQL:
CREATE TABLE IF NOT EXISTS `user` (
  `user_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `last_positive_contract_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_positive_contract_created` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `user` (`user_id`, `created`, `last_positive_contract_id`, `last_positive_contract_created`) VALUES
    ('05159679-3d96-11ed-9bcc-80e82cdc1447', '2022-09-08 20:10:00', '358e9fa1-3d96-11ed-9bcc-80e82cdc1447', '2011-04-07 20:32:51');

contracts:
Code:
contract_id                         |user_id                             |status             |created                |
------------------------------------+------------------------------------+-------------------+-----------------------+
2813ab8d-3d96-11ed-9bcc-80e82cdc1447|05159679-3d96-11ed-9bcc-80e82cdc1447|COMPLETED          |2010-09-06 17:40:10.000|
358e9fa1-3d96-11ed-9bcc-80e82cdc1447|05159679-3d96-11ed-9bcc-80e82cdc1447|COMPLETED          |2011-04-07 20:32:51.000|
8088d0d8-4b98-11ed-8de2-5c3a456597bc|05159679-3d96-11ed-9bcc-80e82cdc1447|CANCELLED          |2011-12-04 14:23:14.000|
4693a45d-3d96-11ed-9bcc-80e82cdc1447|05159679-3d96-11ed-9bcc-80e82cdc1447|WAITING_FOR_PAYMENT|2022-01-15 09:50:23.000|
SQL:
CREATE TABLE IF NOT EXISTS `contracts` (
  `contract_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `contracts` (`contract_id`, `user_id`, `status`, `created`) VALUES
    ('2813ab8d-3d96-11ed-9bcc-80e82cdc1447', '05159679-3d96-11ed-9bcc-80e82cdc1447', 'COMPLETED', '2010-09-06 17:40:10'),
    ('358e9fa1-3d96-11ed-9bcc-80e82cdc1447', '05159679-3d96-11ed-9bcc-80e82cdc1447', 'COMPLETED', '2011-04-07 20:32:51'),
    ('4693a45d-3d96-11ed-9bcc-80e82cdc1447', '05159679-3d96-11ed-9bcc-80e82cdc1447', 'WAITING_FOR_PAYMENT', '2022-01-15 09:50:23'),
    ('8088d0d8-4b98-11ed-8de2-5c3a456597bc', '05159679-3d96-11ed-9bcc-80e82cdc1447', 'CANCELLED', '2011-12-04 14:23:14');

Der status "COMPLETED" eines Vertags ist hierbei der letztmögliche (positive) Status der zu einem Vertrag erreicht werden kann.

Ich möchte jetzt die contract_id und das created Datum des jeweils zuletzt erstellten positiven Vertrags mit dem Status "COMPLETED" zu jedem eintrag in der user Tabelle als eigenes Feld (last_positive_contract_id und last_positive_contract_created) ausgeben lassen.

Das gewünschte Ergebnis in diesem Beispiel wäre dann die contract_id 358e9fa1-3d96-11ed-9bcc-80e82cdc1447 und das created 2011-04-07 20:32:51.000:

Code:
user_id                             |created                |last_positive_contract_id           |last_positive_contract_created|
------------------------------------+-----------------------+------------------------------------+------------------------------+
05159679-3d96-11ed-9bcc-80e82cdc1447|2022-09-08 20:10:00.000|358e9fa1-3d96-11ed-9bcc-80e82cdc1447|       2011-04-07 20:32:51.000|

Könnt ihr mir hier weiterhelfen?

Beste Grüße,
canju
 
Lösung
Falls es tatsächlich MariaDB 10.3 ist, müsste das DBMS ROW_NUMBER verstehen.
(ROW_NUMBER wurde mWn in 10.2 eingeführt)
Hab solche Szenarien wie du dauernd.
SQL:
SELECT
    u.user_id,
    c.contract_id,
    c.status,
    c.created /*,
    c.RN */
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

Getestet in SQLite
Ergibt
user_idcontract_idstatuscreated
05159679-3d96-11ed-9bcc-80e82cdc1447358e9fa1-3d96-11ed-9bcc-80e82cdc1447COMPLETED
...
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

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
 
Zuletzt bearbeitet:
OK, ich glaub ich habs.
Gefällt mir aber gar nicht. Kaskadierende SQL's hasse ich wie die Pest
SQL:
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:
UserIDLetztPosContractIdLetztPosContractStatusLetztPosContractDatumAnzahlNegContractDanach
05159679-3d96-11ed-9bcc-80e82cdc1447358e9fa1-3d96-11ed-9bcc-80e82cdc1447COMPLETED2011-04-07 20:32:512
08052059-4e18-11ed-86f3-5c3a456597bc856223c0-4e18-11ed-86f3-5c3a456597bcCOMPLETED2011-10-27 18:26:451
a3339c76-4e18-11ed-86f3-5c3a456597bcba7c2f57-4e18-11ed-86f3-5c3a456597bcCOMPLETED2011-04-04 00:31:323
 
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.
 
Ok, diesmal ohne Kaskade (wusst ichs doch, dass es geht.)
SQL:
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:
UserIDLetztPosContractIdLetztPosContractStatusLetztPosContractDatumAnzahlNegContractDanach
05159679-3d96-11ed-9bcc-80e82cdc1447358e9fa1-3d96-11ed-9bcc-80e82cdc1447COMPLETED2011-04-07 20:32:512
08052059-4e18-11ed-86f3-5c3a456597bc856223c0-4e18-11ed-86f3-5c3a456597bcCOMPLETED2011-10-27 18:26:451
a3339c76-4e18-11ed-86f3-5c3a456597bcba7c2f57-4e18-11ed-86f3-5c3a456597bcCOMPLETED2011-04-04 00:31:323
 
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
SQL:
-- 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.
 
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
 
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)
 
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:
SQL:
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:
SQL:
    (
        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 (y). Hab wieder was dazugelernt :)

Grüße,
canju
 
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
 
@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....
 
Zurück