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

canju · 14. Oktober 2022

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

Zvoni · 14. Oktober 2022

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_id	contract_id	status	created
05159679-3d96-11ed-9bcc-80e82cdc1447	358e9fa1-3d96-11ed-9bcc-80e82cdc1447	COMPLETED	2011-04-07 20:32:51

Falls du dir das Ergebnis näher anschauen willst, entkommentier das Feld "c.RN" im SELECT (Zeile 5+6), und kommentier in der letzten Zeile das "AND c.RN=1" aus (Zeile 17).

EDIT: Ich geh mal davon aus, dass du weisst, wie man aus einem SELECT in ein UPDATE abbiegt.
btw: Wieso als eigene(s) Feld(er) in "user"? Dir ist klar, dass das nicht der Normalform entspricht?
ich persönlich würde das nicht als (redundantes) eigenes Feld in User haben wollen

EDIT2: LOL... sehs gerade...
Wie kann ein User, der am 08.09.2022 angelegt wurde, Verträge am 07.04.2011 abschliessen?
Oder ist das im Rahmen einer "Digitalisierung" von Akten-Ordnern?

canju · 14. Oktober 2022

Hey Zvoni,

vielen vielen Dank. Die Abfrage funzt super.

btw: Wieso als eigene(s) Feld(er) in "user"? Dir ist klar, dass das nicht der Normalform entspricht?
ich persönlich würde das nicht als (redundantes) eigenes Feld in User haben wollen

Ja, den Update Befehl bekomme ich hin.
Die separaten Felder hauptsächlich, weil ich versuchen will die darauf folgenden Abfragen so kurz wie möglich zu halten. Du hast schon recht, dass das zwecks der Redundanz nicht die optimalste Lösung ist. Aber so kann ich mich bei weiteren JOINS direkt auf die INDEX(last_positive_contract_id) beziehen. Der SubQuery wird dann über das Update regelmäßig gesetzt. Es handelt sich hier um eine Reporting DB auf die ich eine Visualisierung gesetzt habe und mit Daten aus sehr vielen unterschiedlichen externen APIs gespeist wird. Somit wird alle X Stunden einmal eine komplette Aktualisierung angestoßen (nicht "On the fly").

EDIT2: LOL... sehs gerade...
Wie kann ein User, der am 08.09.2022 angelegt wurde, Verträge am 07.04.2011 abschliessen?
Oder ist das im Rahmen einer "Digitalisierung" von Akten-Ordnern?

Adlerauge

Da habe ich beim Ersetzen der Produktivdaten einfach nur nicht aufgepasst.

Danke nochmal vielmals.

Zvoni · 14. Oktober 2022

Nachtrag: Vorsicht. Das c.RN=1 MUSS im ON des LEFT JOINS bleiben.
Wenn du es nämlich nach „aussen“ in ein WHERE schiebst, wird nämlich aus dem LEFT JOIN ein INNER JOIN, und dann bekommst du ein anderes Ergebnis

canju · 14. Oktober 2022

Alles klar. Danke dir für den Nachtrag.

Zvoni · 14. Oktober 2022

canju hat gesagt.:
Die separaten Felder hauptsächlich, weil ich versuchen will die darauf folgenden Abfragen so kurz wie möglich zu halten. Du hast schon recht, dass das zwecks der Redundanz nicht die optimalste Lösung ist. Aber so kann ich mich bei weiteren JOINS direkt auf die INDEX(last_positive_contract_id) beziehen. Der SubQuery wird dann über das Update regelmäßig gesetzt. Es handelt sich hier um eine Reporting DB auf die ich eine Visualisierung gesetzt habe und mit Daten aus sehr vielen unterschiedlichen externen APIs gespeist wird. Somit wird alle X Stunden einmal eine komplette Aktualisierung angestoßen (nicht "On the fly").

da würd ich mir vielleicht überlegen, das SELECT von mir oben in eine View zu packen. Da musst du dann nix regelmäßig updaten.
ist dann wie ne dynamische Tabelle.
andererseits weiss ich nicht, inwieweit du „vorfilterst“, da man bekanntlich bei Views keine Parameter einsetzen kann.
müsste man sich den Anwendungsfall genauer anschauen

canju · 17. Oktober 2022

da würd ich mir vielleicht überlegen, das SELECT von mir oben in eine View zu packen. Da musst du dann nix regelmäßig updaten.

Der riesen Nachteil an Views ist, dass ich u.a. kein Index drauf setzen kann, was die Abfragezeit echt extrem verlängern kann. Views nutze ich hauptsächlich um ähnliche Daten in eine View zusammenzufassen ("normalisieren"), erstelle daraus dann gelegenlich indexierte Tabellen.

Hast du ne Idee wie ich die Anzahl an nicht positiven Verträgen nach dem letzten positiven Vertrag counten kann? In dem Beispiel oben müssten dann die Anahl 2 als Ergebnis rauskommen.

Ich brauche irgendwie die ROW_POSITION des letzten positiven vertrags, aus der ich dann die Differenz von Gesamtanzahl der Verträge zu einem User zu dem letzten positiven Vertrag ermitteln kann.
Mein gern genutztes Cheet Sheet zu den Widow-Functions sieht schon mal keine direkt Funktion vor, wenn ich nichts übersehen habe.

Zvoni · 17. Oktober 2022

canju hat gesagt.:
Hast du ne Idee wie ich die Anzahl an nicht positiven Verträgen nach dem letzten positiven Vertrag counten kann? In dem Beispiel oben müssten dann die Anahl 2 als Ergebnis rauskommen.

in SQLite getestet:
Da es nur eine user_id in deinen Beispieldaten gibt, müsstest du mal nen test machen, obs plausibel ist

SQL:

SELECT
    u.user_id As UserID,
    c.contract_id As LetztPosContractId,
    c.status As LetztPosContractStatus,
    c.created As LetztPosContractDatum,
    /*d.created,d.rn2,*/
    d.rn 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
        X.user_id,
        X.created,f.rn2,
        ROW_NUMBER() OVER(PARTITION BY X.user_id ORDER BY X.created ASC) as RN       
        FROM contracts AS X
        LEFT JOIN
            (SELECT
                user_id,
                contract_id,
                ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created DESC) as RN2
                FROM contracts WHERE status<>'COMPLETED') AS F
        ON F.user_id=x.user_id AND F.rn2=1 AND f.contract_id=x.contract_id
        WHERE status<>'COMPLETED') AS D
ON d.user_id=u.user_id AND d.created>c.created and Not d.rn2 is null

Ergibt:

UserID	LetztPosContractId	LetztPosContractStatus	LetztPosContractDatum	AnzahlNegContractDanach
05159679-3d96-11ed-9bcc-80e82cdc1447	358e9fa1-3d96-11ed-9bcc-80e82cdc1447	COMPLETED	2011-04-07 20:32:51	2

Der riesen Nachteil an Views ist, dass ich u.a. kein Index drauf setzen kann, was die Abfragezeit echt extrem verlängern kann. Views nutze ich hauptsächlich um ähnliche Daten in eine View zusammenzufassen ("normalisieren"), erstelle daraus dann gelegenlich indexierte Tabellen.

Normalerweise gebe ich dir da recht, aber es gibt anscheinend nen Trick der sich "covering index" nennt:
Im Prinzip erzeugst du einen Index, welcher alle Felder der View beinhaltet.
Wichtig: Die Reihenfolge der Felder im Index. Die erste Spalte sollte das Feld sein, für welche du einen "Gleichheits"-Vergleich machst (in deinem Fall also user_id bzw. contract_id denke ich mal)

How do I get MySQL to use an INDEX for view query?

Zvoni · 17. Oktober 2022

Bin ein Depp.
Hier nochmal vereinfacht.

SQL:

SELECT
    u.user_id As UserID,
    c.contract_id As LetztPosContractId,
    c.status As LetztPosContractStatus,
    c.created As LetztPosContractDatum,
    /*d.created,d.rn,*/
    d.Anzahl 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,
        created,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created ASC) as Anzahl,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created DESC) as RN
        FROM contracts     
        WHERE status<>'COMPLETED') AS D
ON d.user_id=u.user_id AND d.created>c.created and d.rn=1

Ergibt:

UserID	LetztPosContractId	LetztPosContractStatus	LetztPosContractDatum	AnzahlNegContractDanach
05159679-3d96-11ed-9bcc-80e82cdc1447	358e9fa1-3d96-11ed-9bcc-80e82cdc1447	COMPLETED	2011-04-07 20:32:51	2

canju · 17. Oktober 2022

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".

Habe jetzt mehrere Datensätze hinzugefügt:

SQL:

CREATE TABLE IF NOT EXISTS `user` (
  `user_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `user` (`user_id`, `created`) VALUES
    ('05159679-3d96-11ed-9bcc-80e82cdc1447', '2022-09-08 20:10:00'),
    ('08052059-4e18-11ed-86f3-5c3a456597bc', '2022-07-08 20:10:00'),
    ('a3339c76-4e18-11ed-86f3-5c3a456597bc', '2021-04-08 20:10:00');

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'),
    ('83698af5-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'WAITING_FOR_PAYMENT', '2010-08-15 10:57:02'),
    ('843dd7af-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'COMPLETED', '2010-09-05 05:44:50'),
    ('8480fa18-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'WAITING_FOR_PAYMENT', '2010-05-03 20:37:56'),
    ('849709ca-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'COMPLETED', '2011-05-08 14:16:23'),
    ('84aa5816-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'CANCELLED', '2011-08-30 23:29:07'),
    ('84c35906-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'COMPLETED', '2010-07-02 15:45:16'),
    ('84d8a0cd-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'COMPLETED', '2011-08-05 01:57:24'),
    ('84f3a51a-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'CANCELLED', '2010-10-18 01:22:19'),
    ('856223c0-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'COMPLETED', '2011-10-27 18:26:45'),
    ('857f996b-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'WAITING_FOR_PAYMENT', '2010-07-03 12:06:36'),
    ('859a73c7-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'WAITING_FOR_PAYMENT', '2012-03-18 01:04:02'),
    ('85b7c02a-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'COMPLETED', '2010-07-16 12:16:33'),
    ('85d04550-4e18-11ed-86f3-5c3a456597bc', '08052059-4e18-11ed-86f3-5c3a456597bc', 'WAITING_FOR_PAYMENT', '2011-05-19 08:37:32'),
    ('b9eabb45-4e18-11ed-86f3-5c3a456597bc', 'a3339c76-4e18-11ed-86f3-5c3a456597bc', 'CANCELLED', '2012-01-21 06:46:20'),
    ('ba021cfb-4e18-11ed-86f3-5c3a456597bc', 'a3339c76-4e18-11ed-86f3-5c3a456597bc', 'WAITING_FOR_PAYMENT', '2010-11-01 04:30:20'),
    ('ba189b6e-4e18-11ed-86f3-5c3a456597bc', 'a3339c76-4e18-11ed-86f3-5c3a456597bc', 'COMPLETED', '2010-05-21 20:38:41'),
    ('ba35af83-4e18-11ed-86f3-5c3a456597bc', 'a3339c76-4e18-11ed-86f3-5c3a456597bc', 'CANCELLED', '2010-11-18 15:41:25'),
    ('ba5c3672-4e18-11ed-86f3-5c3a456597bc', 'a3339c76-4e18-11ed-86f3-5c3a456597bc', 'CANCELLED', '2012-04-27 21:18:10'),
    ('ba7c2f57-4e18-11ed-86f3-5c3a456597bc', 'a3339c76-4e18-11ed-86f3-5c3a456597bc', 'COMPLETED', '2011-04-04 00:31:32'),
    ('baa11a38-4e18-11ed-86f3-5c3a456597bc', 'a3339c76-4e18-11ed-86f3-5c3a456597bc', 'WAITING_FOR_PAYMENT', '2011-12-26 19:14:54');

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

canju

Erfahrenes Mitglied

Zvoni

Zvoni

Erfahrenes Mitglied

canju

Erfahrenes Mitglied

Zvoni

Erfahrenes Mitglied

canju

Erfahrenes Mitglied

Zvoni

Erfahrenes Mitglied

canju

Erfahrenes Mitglied

Zvoni

Erfahrenes Mitglied

Zvoni

Erfahrenes Mitglied

canju

Erfahrenes Mitglied

Neue Beiträge