wake-up-neo.net

SQL Join gegen Datumsbereiche?

Betrachten Sie zwei Tabellen:

Transaktionen , mit Beträgen in einer Fremdwährung:

     Date  Amount
========= =======
 1/2/2009    1500
 2/4/2009    2300
3/15/2009     300
4/17/2009    2200
etc.

Wechselkurse , mit dem Wert der Primärwährung (sagen wir Dollar) in der Fremdwährung:

     Date    Rate
========= =======
 2/1/2009    40.1
 3/1/2009    41.0
 4/1/2009    38.5
 5/1/2009    42.7
etc.

Wechselkurse können für beliebige Daten eingegeben werden - der Benutzer kann sie täglich, wöchentlich, monatlich oder in unregelmäßigen Abständen eingeben.

Um die ausländischen Beträge in Dollar umzurechnen, muss ich folgende Regeln beachten:

A. Verwenden Sie nach Möglichkeit den letzten vorherigen Kurs. Daher wird für die Transaktion am 04.02.2009 der Kurs vom 01.02.2009 und für die Transaktion am 15.03.2009 der Kurs vom 01.03.2009 verwendet.

B. Wenn für ein vorheriges Datum kein Tarif definiert ist, verwenden Sie den frühesten verfügbaren Tarif. Für die Transaktion vom 1/2/2009 wird der Kurs vom 2/1/2009 verwendet, da kein früherer Kurs definiert ist.

Das funktioniert...

Select 
    t.Date, 
    t.Amount,
    ConvertedAmount=(   
        Select Top 1 
            t.Amount/ex.Rate
        From ExchangeRates ex
        Where t.Date > ex.Date
        Order by ex.Date desc
    )
From Transactions t

... aber (1) es scheint, als wäre ein Join effizienter und eleganter, und (2) es geht nicht um Regel B oben.

Gibt es eine Alternative zur Verwendung der Unterabfrage, um den geeigneten Tarif zu finden? Und gibt es eine elegante Möglichkeit, mit Regel B umzugehen, ohne mich in Knoten zu binden?

15
Herb Caudill

Sie können zunächst einen Selbst-Join zu den nach Datum geordneten Wechselkursen durchführen, so dass Sie das Anfangs- und Enddatum jedes Wechselkurses haben, ohne dass sich die Daten überschneiden oder lücken. in meinem Fall verwende ich nur einen allgemeinen Tabellenausdruck).

Nun ist es einfach und effizient, diese "vorbereiteten" Tarife mit den Transaktionen zu verbinden.

So etwas wie:

WITH IndexedExchangeRates AS (           
            SELECT  Row_Number() OVER (ORDER BY Date) ix,
                    Date,
                    Rate 
            FROM    ExchangeRates 
        ),
        RangedExchangeRates AS (             
            SELECT  CASE WHEN IER.ix=1 THEN CAST('1753-01-01' AS datetime) 
                    ELSE IER.Date 
                    END DateFrom,
                    COALESCE(IER2.Date, GETDATE()) DateTo,
                    IER.Rate 
            FROM    IndexedExchangeRates IER 
            LEFT JOIN IndexedExchangeRates IER2 
            ON IER.ix = IER2.ix-1 
        )
SELECT  T.Date,
        T.Amount,
        RER.Rate,
        T.Amount/RER.Rate ConvertedAmount 
FROM    Transactions T 
LEFT JOIN RangedExchangeRates RER 
ON (T.Date > RER.DateFrom) AND (T.Date <= RER.DateTo)

Anmerkungen:

  • Sie könnten GETDATE() durch ein Datum in ferner Zukunft ersetzen, ich gehe hier davon aus, dass keine Zinssätze für die Zukunft bekannt sind.

  • Die Regel (B) wird implementiert, indem das Datum des ersten bekannten Wechselkurses auf das Mindestdatum gesetzt wird, das von SQL Server datetime unterstützt wird. Dies sollte der kleinste Wert sein (per Definition, wenn es der Typ ist, den Sie für die Spalte Date verwenden) möglich.

20
Lucero

Angenommen, Sie hatten eine erweiterte Wechselkurstabelle, die Folgendes enthielt:

 Start Date   End Date    Rate
 ========== ========== =======
 0001-01-01 2009-01-31    40.1
 2009-02-01 2009-02-28    40.1
 2009-03-01 2009-03-31    41.0
 2009-04-01 2009-04-30    38.5
 2009-05-01 9999-12-31    42.7

Wir können die Details darüber diskutieren, ob die ersten beiden Zeilen kombiniert werden sollen, aber die allgemeine Idee ist, dass es einfach ist, den Wechselkurs für ein bestimmtes Datum zu finden. Diese Struktur arbeitet mit dem SQL-Operator 'BETWEEN', der die Enden der Bereiche enthält. Oft ist ein besseres Format für Bereiche "offen-geschlossen". Das erste Datum ist angegeben und das zweite Datum ist ausgeschlossen. Beachten Sie, dass die Datenzeilen eingeschränkt sind. Es gibt (a) keine Lücken im Datumsbereich und (b) keine Überlappungen im Datenbereich. Die Durchsetzung dieser Zwänge ist nicht völlig trivial (höfliche Untertreibung - Meiose).

Jetzt ist die Basisfrage trivial und Fall B ist kein Sonderfall mehr:

SELECT T.Date, T.Amount, X.Rate
  FROM Transactions AS T JOIN ExtendedExchangeRates AS X
       ON T.Date BETWEEN X.StartDate AND X.EndDate;

Der schwierige Teil ist das Erstellen der ExtendedExchangeRate-Tabelle aus der angegebenen ExchangeRate-Tabelle im laufenden Betrieb. Wenn dies eine Option ist, wäre eine Überarbeitung der Struktur der Basistabelle von ExchangeRate mit der ExtendedExchangeRate-Tabelle eine gute Idee. Sie lösen das chaotische Zeug, wenn die Daten eingegeben werden (einmal im Monat), und nicht jedes Mal, wenn ein Wechselkurs festgelegt werden muss (mehrmals am Tag).

Wie erstelle ich die erweiterte Kurstabelle? Wenn Ihr System das Addieren oder Subtrahieren von 1 von einem Datumswert unterstützt, um den nächsten oder vorherigen Tag zu erhalten (und eine einzelne Zeilentabelle mit der Bezeichnung 'Dual' hat), funktioniert eine Variation Davon (ohne Verwendung von OLAP Funktionen):

CREATE TABLE ExchangeRate
(
    Date    DATE NOT NULL,
    Rate    DECIMAL(10,5) NOT NULL
);
INSERT INTO ExchangeRate VALUES('2009-02-01', 40.1);
INSERT INTO ExchangeRate VALUES('2009-03-01', 41.0);
INSERT INTO ExchangeRate VALUES('2009-04-01', 38.5);
INSERT INTO ExchangeRate VALUES('2009-05-01', 42.7);

Erste Reihe:

SELECT '0001-01-01' AS StartDate,
       (SELECT MIN(Date) - 1 FROM ExchangeRate) AS EndDate,
       (SELECT Rate FROM ExchangeRate
         WHERE Date = (SELECT MIN(Date) FROM ExchangeRate)) AS Rate
FROM Dual;

Ergebnis:

0001-01-01  2009-01-31      40.10000

Letzte Reihe:

SELECT (SELECT MAX(Date) FROM ExchangeRate) AS StartDate,
       '9999-12-31' AS EndDate,
       (SELECT Rate FROM ExchangeRate
         WHERE Date = (SELECT MAX(Date) FROM ExchangeRate)) AS Rate
FROM Dual;

Ergebnis:

2009-05-01  9999-12-31      42.70000

Mittlere Reihen:

SELECT X1.Date     AS StartDate,
       X2.Date - 1 AS EndDate,
       X1.Rate     AS Rate
  FROM ExchangeRate AS X1 JOIN ExchangeRate AS X2
       ON X1.Date < X2.Date
 WHERE NOT EXISTS
       (SELECT *
          FROM ExchangeRate AS X3
         WHERE X3.Date > X1.Date AND X3.Date < X2.Date
        );

Ergebnis:

2009-02-01  2009-02-28      40.10000
2009-03-01  2009-03-31      41.00000
2009-04-01  2009-04-30      38.50000

Beachten Sie, dass die Unterabfrage NOT EXISTS sehr wichtig ist. Andernfalls lautet das Ergebnis der "mittleren Reihen":

2009-02-01  2009-02-28      40.10000
2009-02-01  2009-03-31      40.10000    # Unwanted
2009-02-01  2009-04-30      40.10000    # Unwanted
2009-03-01  2009-03-31      41.00000
2009-03-01  2009-04-30      41.00000    # Unwanted
2009-04-01  2009-04-30      38.50000

Die Anzahl der unerwünschten Zeilen nimmt dramatisch zu, je größer die Tabelle wird (für N> 2 Zeilen gibt es (N-2) * (N-3)/2 unerwünschte Zeilen, glaube ich).

Das Ergebnis für ExtendedExchangeRate ist die (getrennte) UNION der drei Abfragen:

SELECT DATE '0001-01-01' AS StartDate,
       (SELECT MIN(Date) - 1 FROM ExchangeRate) AS EndDate,
       (SELECT Rate FROM ExchangeRate
         WHERE Date = (SELECT MIN(Date) FROM ExchangeRate)) AS Rate
FROM Dual
UNION
SELECT X1.Date     AS StartDate,
       X2.Date - 1 AS EndDate,
       X1.Rate     AS Rate
  FROM ExchangeRate AS X1 JOIN ExchangeRate AS X2
       ON X1.Date < X2.Date
 WHERE NOT EXISTS
       (SELECT *
          FROM ExchangeRate AS X3
         WHERE X3.Date > X1.Date AND X3.Date < X2.Date
        )
UNION
SELECT (SELECT MAX(Date) FROM ExchangeRate) AS StartDate,
       DATE '9999-12-31' AS EndDate,
       (SELECT Rate FROM ExchangeRate
         WHERE Date = (SELECT MAX(Date) FROM ExchangeRate)) AS Rate
FROM Dual;

Beim Test-DBMS (IBM Informix Dynamic Server 11.50.FC6 unter MacOS X 10.6.2) konnte ich die Abfrage in eine Ansicht konvertieren, aber ich musste aufhören, mit den Datentypen zu schummeln - indem ich die Zeichenfolgen in Datumsangaben umwandelte:

CREATE VIEW ExtendedExchangeRate(StartDate, EndDate, Rate) AS
    SELECT DATE('0001-01-01')  AS StartDate,
           (SELECT MIN(Date) - 1 FROM ExchangeRate) AS EndDate,
           (SELECT Rate FROM ExchangeRate WHERE Date = (SELECT MIN(Date) FROM ExchangeRate)) AS Rate
    FROM Dual
    UNION
    SELECT X1.Date     AS StartDate,
           X2.Date - 1 AS EndDate,
           X1.Rate     AS Rate
      FROM ExchangeRate AS X1 JOIN ExchangeRate AS X2
           ON X1.Date < X2.Date
     WHERE NOT EXISTS
           (SELECT *
              FROM ExchangeRate AS X3
             WHERE X3.Date > X1.Date AND X3.Date < X2.Date
            )
    UNION 
    SELECT (SELECT MAX(Date) FROM ExchangeRate) AS StartDate,
           DATE('9999-12-31') AS EndDate,
           (SELECT Rate FROM ExchangeRate WHERE Date = (SELECT MAX(Date) FROM ExchangeRate)) AS Rate
    FROM Dual;
3

Ich kann das nicht testen, aber ich denke, es würde funktionieren. Es verwendet koaleszieren mit zwei Unterabfragen, um die Rate nach Regel A oder Regel B auszuwählen.

Select t.Date, t.Amount, 
  ConvertedAmount = t.Amount/coalesce(    
    (Select Top 1 ex.Rate 
        From ExchangeRates ex 
        Where t.Date > ex.Date 
        Order by ex.Date desc )
     ,
     (select top 1 ex.Rate 
        From ExchangeRates  
        Order by ex.Date asc)
    ) 
From Transactions t
1
Ray
SELECT 
    a.tranDate, 
    a.Amount,
    a.Amount/a.Rate as convertedRate
FROM
    (

    SELECT 
        t.date tranDate,
        e.date as rateDate,
        t.Amount,
        e.rate,
        RANK() OVER (Partition BY t.date ORDER BY
                         CASE WHEN DATEDIFF(day,e.date,t.date) < 0 THEN
                                   DATEDIFF(day,e.date,t.date) * -100000
                              ELSE DATEDIFF(day,e.date,t.date)
                         END ) AS diff
    FROM 
        ExchangeRates e
    CROSS JOIN 
        Transactions t
         ) a
WHERE a.diff = 1

Die Differenz zwischen Übertragungsdatum und Tarifdatum wird berechnet, dann werden negative Werte (Bedingung b) mit -10000 multipliziert, so dass sie immer noch mit positiven Werten bewertet werden können (Bedingung a hat immer Priorität. Wir wählen dann die minimale Datumsdifferenz für jedes Übertragungsdatum aus mit der Rangfolge-Klausel.

0
Paul Creasey

Viele Lösungen werden funktionieren. Sie sollten wirklich diejenige finden, die am besten für Ihre Workload geeignet ist: Suchen Sie normalerweise nach einer Transaktion, einer Liste von ihnen, allen?

Die Tie-Breaker-Lösung für Ihr Schema lautet:

SELECT      t.Date,
            t.Amount,
            r.Rate
            --//add your multiplication/division here

FROM        "Transactions" t

INNER JOIN  "ExchangeRates" r
        ON  r."ExchangeRateID" = (
                        SELECT TOP 1 x."ExchangeRateID"
                        FROM        "ExchangeRates" x
                        WHERE       x."SourceCurrencyISO" = t."SourceCurrencyISO" --//these are currency-related filters for your tables
                                AND x."TargetCurrencyISO" = t."TargetCurrencyISO" --//,which you should also JOIN on
                                AND x."Date" <= t."Date"
                        ORDER BY    x."Date" DESC)

Sie müssen über die richtigen Indizes für diese Abfrage verfügen, um schnell zu sein. Idealerweise sollten Sie auch keine JOIN in "Date", sondern in "ID"-artigem Feld (INTEGER) verwenden. Gib mir mehr Schema-Informationen, ich werde ein Beispiel für dich erstellen.

0
van

Es gibt nichts über einen Join, der eleganter ist als die TOP 1-korrelierte Unterabfrage in Ihrem ursprünglichen Beitrag. Wie Sie jedoch sagen, wird die Anforderung B nicht erfüllt.

Diese Abfragen funktionieren (SQL Server 2005 oder höher erforderlich). Siehe das SqlFiddle für diese .

SELECT
   T.*,
   ExchangeRate = E.Rate
FROM
  dbo.Transactions T
  CROSS APPLY (
    SELECT TOP 1 Rate
    FROM dbo.ExchangeRate E
    WHERE E.RateDate <= T.TranDate
    ORDER BY
      CASE WHEN E.RateDate <= T.TranDate THEN 0 ELSE 1 END,
      E.RateDate DESC
  ) E;

Beachten Sie, dass das CROSS APPLY mit einem einzelnen Spaltenwert der korrelierten Unterabfrage in der SELECT-Klausel funktional entspricht, wie Sie es gezeigt haben. Ich ziehe CROSS APPLY jetzt lieber vor, weil es viel flexibler ist und Sie den Wert an mehreren Stellen wiederverwenden können, mehrere Zeilen enthalten können (für benutzerdefiniertes Unpivotieren) und mehrere Spalten haben können.

SELECT
   T.*,
   ExchangeRate = Coalesce(E.Rate, E2.Rate)
FROM
  dbo.Transactions T
  OUTER APPLY (
    SELECT TOP 1 Rate
    FROM dbo.ExchangeRate E
    WHERE E.RateDate <= T.TranDate
    ORDER BY E.RateDate DESC
  ) E
  OUTER APPLY (
    SELECT TOP 1 Rate
    FROM dbo.ExchangeRate E2
    WHERE E.Rate IS NULL
    ORDER BY E2.RateDate
  ) E2;

Ich weiß nicht, welche die bessere Leistung bringt oder ob eine der Antworten besser ist als die anderen Antworten auf der Seite. Mit einem richtigen Index für die Date-Spalten sollten sie ziemlich gut passen - definitiv besser als jede Row_Number()-Lösung.

0
ErikE