Komplizierte SQL-Abfrage für eine laufende Gesamtspalte

Question

Ich versuche gerade, eine ziemlich komplexe Abfrage in SQL Server 2008 zu erstellen. Ich würde gerne einige Beiträge von SQL-Experten hier.

Stellen Sie sich vor, ich hätte eine Tabelle "Zahlungen" mit diesen Feldern:

PaymentID int, CustomerID int, PaymentDate datetime, Betrag dezimal

Es handelt sich also im Wesentlichen um eine Tabelle der Zahlungen, die ein Kunde zu bestimmten Daten geleistet hat. Wichtig ist, dass ein Zahlungsbetrag in einigen Fällen ein negativer Wert sein kann. Im Laufe der Zeit kann der Gesamtbetrag, der von einem bestimmten Kunden gezahlt wurde, also steigen oder fallen.

Was wir herauszufinden versuchen, ist die SQL, um den Höchststand des Gesamtbetrags, der pro Kunde gezahlt wurde, zu berechnen.

Wenn Fred also 3 Zahlungen getätigt hat: die erste für $5, die zweite für $5, die dritte für -$3, dann zeigt der Bericht, dass Freds höchster gezahlter Gesamtbetrag $10 war (bei seiner zweiten Zahlung) und sein letzter gezahlter Betrag $7 war.

Wir müssen diesen Bericht für hunderttausend Kunden erstellen (von denen jeder hundert bis tausend Zahlungen getätigt haben kann), also muss es schnell gehen.

Gibt es eine gute Möglichkeit, diese Abfrage zu strukturieren, ohne die laufenden Summen in der Datenbank zu speichern? Wir möchten die Speicherung von vorberechneten Werten nach Möglichkeit vermeiden.

Answer 1

Ihre Frage scheint diese zu sein:

SELECT CustomerID, SUM(Ammount) FROM table WHERE Amount > 0 GROUP BY CustomerID
SELECT CustomerID, SUM(Ammount) FROM table GROUP BY CustomerID

Ich denke aber, Sie wollen eine Tabelle, die wie folgt aussieht

Customer  Payment  HighPoint  RunningTotal
123       5        5          5
123       5        10         10
123       -3       10         7

In diesem Fall würde ich eine Ansicht mit den beiden oben genannten Auswahlen erstellen, so dass die Ansicht etwa so aussieht.

SELECT CusotmerID, 
  PaymentDate, 
  Ammount, 
  (SELECT SUM(Ammount) 
    FROM table as ALIAS 
    WHERE ALIAS.Amount > 0 
      AND ALIAS.PaymentDate <= PaymentDate 
      AND ALIAS.CustomerID = CustomerID), 
  (SELECT SUM(Ammount) 
    FROM table as ALIAS 
    WHERE ALIAS.CustomerID = CustomerID 
    AND ALIAS.PaymentDate <= PaymentDate)
FROM table

Sie können auch einen nicht eindeutigen Index für die Spalte "Betrag" der Tabelle in Betracht ziehen, um die Ansicht zu beschleunigen.

Answer 2

Der Vorgang ist linear in der Anzahl der Zahlungen für jeden Kunden. Sie müssen also jede Zahlung durchgehen, eine laufende Summe und eine Hochwassermarke festhalten und am Ende aller Zahlungen haben Sie Ihre Antwort. Unabhängig davon, ob Sie das in einer gespeicherten CLR-Prozedur tun (was mir sofort in den Sinn kam) oder einen Cursor oder eine temporäre Tabelle oder was auch immer verwenden, es wird wahrscheinlich nicht schnell gehen.

Wenn Sie diesen Bericht immer wieder erstellen müssen, sollten Sie ernsthaft in Erwägung ziehen, ein Feld für die Hochwassermarke zu führen und es bei jedem Zahlungseingang zu aktualisieren (oder auch nicht). Auf diese Weise wird Ihr Bericht trivial - aber dafür sind ja Data Marts da.

Answer 3

Als Alternative zu Unterabfragen können Sie eine laufende Gesamtabfrage verwenden. Hier ist, wie ich eine für diesen Fall eingerichtet habe. Erstellen Sie zunächst einige Testdaten:

create table #payments (
    paymentid int identity,
    customerid int,
    paymentdate datetime,
    amount decimal
)

insert into #payments (customerid,paymentdate,amount) values (1,'2009-01-01',1.00)
insert into #payments (customerid,paymentdate,amount) values (1,'2009-01-02',2.00)
insert into #payments (customerid,paymentdate,amount) values (1,'2009-01-03',-1.00)
insert into #payments (customerid,paymentdate,amount) values (1,'2009-01-04',2.00)
insert into #payments (customerid,paymentdate,amount) values (1,'2009-01-05',-3.00)
insert into #payments (customerid,paymentdate,amount) values (2,'2009-01-01',10.00)
insert into #payments (customerid,paymentdate,amount) values (2,'2009-01-02',-5.00)
insert into #payments (customerid,paymentdate,amount) values (2,'2009-01-03',7.00)

Nun können Sie die Abfrage der laufenden Summe ausführen, die den Saldo für jeden Kunden nach jeder Zahlung berechnet:

select cur.customerid, cur.paymentdate, sum(prev.amount)
from #payments cur
inner join #payments prev
    on cur.customerid = prev.customerid
    and cur.paymentdate >= prev.paymentdate
group by cur.customerid, cur.paymentdate

Dadurch werden Daten erzeugt:

Customer  Paymentdate        Balance after payment
1         2009.01.01         1
1         2009.01.02         3
1         2009.01.03         2
1         2009.01.04         4
1         2009.01.05         1
2         2009.01.01         10
2         2009.01.02         5
2         2009.01.03         12

Um das Maximum zu ermitteln, können Sie die laufende Gesamtabfrage gruppieren:

select customerid, max(balance)
from (
    select cur.customerid, cur.paymentdate, balance = sum(prev.amount)
    from #payments cur
    inner join #payments prev
        on cur.customerid = prev.customerid
        and cur.paymentdate >= prev.paymentdate
    group by cur.customerid, cur.paymentdate
) runningtotal
group by customerid

Das ergibt:

Customer   Max balance
1          4
2          12

Ich hoffe, dies ist nützlich.

Answer 4

list = list of amounts ordered by date
foreach in list as amount
  running += amount
  if running >= high
    high = running

Damit es schnell geht, benötigen Sie eine laufende Gesamtsumme, die durch einen Trigger um den Betrag erhöht wird, und einen hohen Wert für jeden Kunden (der auch durch einen Trigger aktualisiert werden kann, um die erneute Abfrage noch einfacher zu machen).

Ich glaube nicht, dass man so etwas ohne Code machen kann (gespeicherte Verfahren sind Code).

Answer 5

Wie die Antwort von Andomar. Sie können die laufende Summe für jede Zahlung berechnen. Dann finden Sie die maximale Spitzenzahlung...

with
rt as (
  select
    Payments.*,
    isnull(sum(p.Amount), 0) + Payments.Amount as running
  from
    Payments
    left outer join Payments p on Payments.CustomerID = p.CustomerID
      and p.PaymentDate <= Payments.PaymentDate
      and p.PaymentID < Payments.PaymentID
),
highest as
(
  select
    CustomerID, PaymentID, running as peak_paid
  from
    rt
  where
    PaymentID = (select top 1 rt2.PaymentID 
        from rt rt2 
        where rt2.CustomerID = rt.CustomerID
        order by rt2.running desc, rt2.PaymentDate, rt2.PaymentID)
)

select
  *,
  (select sum(amount) from Payments where Payments.CustomerID = highest.CustomerID) as total_paid  
from
  highest;

Da Sie jedoch rund 1 Million Zahlungen haben, könnte dies recht langsam sein. Wie andere schon sagten, sollten Sie die CustomerID, PaymentID und peak_paid in einer separaten Tabelle speichern. Diese Tabelle könnte bei jedem Einfügen einer Zahlung oder als Sqljob aktualisiert werden.

Die Abfrage wurde aktualisiert, um Join anstelle von Unterabfragen zu verwenden. Da das PaymentDate keine Uhrzeit hat, filtere ich mehrere Zahlungen am selben Tag anhand der PaymentId heraus.