Wie kann ich effizient das MAX einer Spalte, geordnet nach einer anderen Spalte, berechnen?

Question

Ich habe ein Tabellenschema ähnlich dem folgenden (vereinfacht):

CREATE TABLE Transactions
(
    TransactionID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    CustomerID int NOT NULL,  -- Foreign key, not shown
    TransactionDate datetime NOT NULL,
    ...
)

CREATE INDEX IX_Transactions_Customer_Date
ON Transactions (CustomerID, TransactionDate)

Zur Erläuterung: Diese Transaktionstabelle fasst verschiedene Arten von Transaktionen aus der Datenbank eines anderen Anbieters zusammen (wir nennen das einen ETL-Prozess), und ich habe daher keine große Kontrolle über die Reihenfolge, in der sie eingefügt werden. Selbst wenn ich sie hätte, könnten die Transaktionen zurückdatiert werden, daher ist es wichtig zu wissen, dass das Maximum TransactionID für jede gegebene customer ist nicht unbedingt die jüngste Transaktion.

Tatsächlich ist die jüngste Transaktion eine Kombination aus dem Datum et die ID. Die Daten sind nicht eindeutig - der Anbieter schneidet die Tageszeit oft ab. Um die jüngste Transaktion zu finden, muss ich also zunächst das jüngste Datum und dann die jüngste ID für dieses Datum ermitteln.

Ich weiß, dass ich dies mit einer Fensterabfrage tun kann ( ROW_NUMBER() OVER (PARTITION BY TransactionDate DESC, TransactionID DESC) ), aber dies erfordert einen vollständigen Index-Scan und eine sehr teure Sortierung, und scheitert daher kläglich in Bezug auf die Effizienz. Außerdem ist es ziemlich umständlich, die ganze Zeit zu schreiben.

Etwas effizienter ist die Verwendung von zwei CTEs oder verschachtelten Unterabfragen, eine zum Auffinden der MAX(TransactionDate) per CustomerID und eine weitere, um die MAX(TransactionID) . Auch hier funktioniert es, erfordert aber ein zweites Aggregat und eine Verknüpfung, was etwas besser ist als die ROW_NUMBER() Abfrage, aber immer noch ziemlich mühsam in Bezug auf die Leistung.

Ich habe auch in Betracht gezogen, ein CLR User-Defined Aggregate zu verwenden und werde darauf zurückgreifen, wenn nötig, aber ich würde es vorziehen, eine reine SQL-Lösung zu finden, wenn möglich, um die Bereitstellung zu vereinfachen (es gibt keine Notwendigkeit für SQL-CLR irgendwo sonst in diesem Projekt).

Die Frage lautet also konkret:

Ist es möglich, eine Abfrage zu schreiben, die die neueste TransactionID per CustomerID definiert als das Maximum TransactionID für die letzte TransactionDate und einen Plan zu erreichen, der in seiner Leistung einem gewöhnlichen MAX / GROUP BY abfragen?

(Mit anderen Worten: Die einzigen wichtigen Schritte im Plan sollten ein Index-Scan und ein Stream-Aggregat sein. Mehrere Scans, Sortierungen, Joins usw. werden wahrscheinlich zu langsam sein).

Answer 1

Der nützlichste Index könnte sein:

CustomerID, TransactionDate desc, TransactionId desc

Dann könnten Sie eine Abfrage wie diese versuchen:

select  a.CustomerID
,       b.TransactionID
from    (
        select  distinct
                CustomerID
        from    YourTable
        ) a
cross apply   
        (
        select  top 1
                TransactionID
        from    YourTable
        where   CustomerID = a.CustomerID
        order by
                TransactionDate desc,
                TransactionId desc
        ) b

Answer 2

Wie wäre es, wenn Sie den Optimierer zwingen, zuerst die abgeleitete Tabelle zu berechnen? In meinen Tests war dies weniger kostspielig als die beiden Max-Vergleiche.

Select T.CustomerId, T.TransactionDate, Max(TransactionId)
From Transactions As T
    Join    (
            Select T1.CustomerID, Max(T1.TransactionDate) As MaxDate
            From Transactions As T1
            Group By T1.CustomerId
            ) As Z
        On Z.CustomerId = T.CustomerId
            And Z.MaxDate = T.TransactionDate
Group By T.CustomerId, T.TransactionDate

Answer 3

Haftungsausschluss: Ich denke laut :)

Könnten Sie eine indizierte, berechnete Spalte haben, die die Spalten TransactionDate und TransactionID in einer Form kombiniert, die bedeutet, dass die Suche nach der letzten Transaktion nur ein Fall der Suche nach dem MAX-Wert dieses einzelnen Feldes ist?

Answer 4

Dieser schien gute Leistungsstatistiken zu haben:

SELECT
    T1.customer_id,
    MAX(T1.transaction_id) AS transaction_id
FROM
    dbo.Transactions T1
INNER JOIN
(
    SELECT
        T2.customer_id,
        MAX(T2.transaction_date) AS max_dt
    FROM
        dbo.Transactions T2
    GROUP BY
        T2.customer_id
) SQ1 ON
    SQ1.customer_id = T1.customer_id AND
    T1.transaction_date = SQ1.max_dt
GROUP BY
    T1.customer_id

Answer 5

Ich glaube, ich habe es tatsächlich herausgefunden. @Ada hatte die richtige Idee, und ich selbst hatte die gleiche Idee, wusste aber nicht, wie man eine einzige zusammengesetzte ID bildet und die zusätzliche Verknüpfung vermeidet.

Da sowohl Datumsangaben als auch (positive) ganze Zahlen byte-geordnet sind, können sie nicht nur für die Aggregation zu einem BLOB verkettet, sondern auch nach der Aggregation getrennt werden.

Das fühlt sich ein wenig unheilig an, aber es scheint zu funktionieren:

SELECT
    CustomerID,
    CAST(SUBSTRING(MAX(
        CAST(TransactionDate AS binary(8)) + 
        CAST(TransactionID AS binary(4))),
      9, 4) AS int) AS TransactionID
FROM Transactions
GROUP BY CustomerID

Dadurch erhalte ich einen einzelnen Index-Scan und ein Stream-Aggregat. Keine Notwendigkeit für zusätzliche Indizes entweder, es führt die gleiche wie nur tun MAX(TransactionID) - was natürlich Sinn macht, da die gesamte Verkettung innerhalb des Aggregats selbst stattfindet.