La jointure SQL par rapport aux plages de dates?

Vous pouvez d'abord faire une auto-participation sur les taux de change qui sont classés par date afin que vous ayez la date de début et la date de fin de chaque taux de change, sans chevauchement ou intervalle dans les dates (peut-être append cela à votre database dans mon cas, je suis en train d'utiliser une expression de table commune).

Maintenant, joindre ces taux "préparés" avec les transactions est simple et efficace.

Quelque chose comme:

 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) 

Remarques:

• Vous pourriez replace GETDATE() par une date dans un avenir lointain, je suppose ici qu'aucun taux pour le futur n'est connu.

• La règle (B) est implémentée en définissant la date du premier taux de change connu à la date minimale prise en charge par le datetime SQL Server, qui doit (par définition si c'est le type que vous utilisez pour la colonne Date ) être la plus petite valeur possible.

Supposons que vous ayez une table de taux de change étendue qui contient:

  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 

Nous pouvons discuter des détails pour savoir si les deux premières lignes doivent être combinées, mais l'idée générale est qu'il est sortingvial de find le taux de change pour une date donnée. Cette structure fonctionne avec l'opérateur SQL 'BETWEEN' qui inclut les extrémités des plages. Souvent, un meilleur format pour les plages est «ouvert-fermé»; la première date indiquée est incluse et la seconde est exclue. Notez qu'il y a une contrainte sur les lignes de données – il n'y a pas (a) de lacunes dans la couverture de la plage de dates et (b) pas de chevauchements dans la couverture. L'application de ces contraintes n'est pas complètement sortingviale (euphémisme poli – méiose).

Maintenant, la requête de base est sortingviale, et le cas B n'est plus un cas particulier:

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

La partie délicate consiste à créer la table ExtendedExchangeRate à partir de la table ExchangeRate donnée à la volée. Si c'est une option, la révision de la structure de la table ExchangeRate de base pour la faire correspondre à la table ExtendedExchangeRate serait une bonne idée; vous résolvez les problèmes lorsque datatables sont saisies (une fois par mois) au lieu de chaque fois qu'un taux de change doit être déterminé (plusieurs fois par jour).

Comment créer la table de taux de change étendue? Si votre système prend en charge l'ajout ou la soustraction de 1 à une valeur de date pour get le jour suivant ou précédent (et possède une seule table de lignes appelée 'Dual'), une variante fonctionnera (sans utiliser de fonctions OLAP):

 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); 

Première rangée:

 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; 

Résultat:

 0001-01-01 2009-01-31 40.10000 

Dernière rangée:

 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; 

Résultat:

 2009-05-01 9999-12-31 42.70000 

Rangées du milieu:

 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 ); 

Résultat:

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

Notez que la sous-requête NOT EXISTS est plutôt cruciale. Sans cela, le résultat des «rangées du milieu» est:

 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 

Le nombre de lignes non désirées augmente considérablement à mesure que la taille du tableau augmente (pour N> 2 lignes, il y a (N-2) * (N – 3) / 2 lignes indésirables, je crois).

Le résultat pour ExtendedExchangeRate est l'UNION (disjointe) des trois requêtes:

 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; 

Sur le SGBD de test (IBM Informix Dynamic Server 11.50.FC6 sur MacOS X 10.6.2), j'étais capable de convertir la requête en vue, mais j'ai dû arrêter de sortingcher avec les types de données – en forçant les strings en dates:

 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; 

Je ne peux pas tester cela, mais je pense que cela fonctionnerait. Il utilise coalesce avec deux sous-requêtes pour choisir le taux par la règle A ou la règle B.

 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 

 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 

La différence entre la date de transfert et la date de tarification est calculée, puis les valeurs négatives (condition b) sont multipliées par -10000 afin qu'elles puissent être classées mais les valeurs positives (la condition a est toujours prioritaire) nous sélectionnons ensuite la différence de date minimum pour chaque date en utilisant la clause rank over.

De nombreuses solutions vont fonctionner. Vous devriez vraiment find celui qui fonctionne le mieux (le plus rapide) pour votre charge de travail: searchz-vous habituellement une Transaction, une list d'entre eux, tous?

La solution tie-breaker étant donné votre schéma est:

 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) 

Vous devez avoir les bons index pour que cette requête soit rapide. Idéalement, vous ne devriez pas avoir un JOIN sur "Date" , mais sur un champ "ID" ( INTEGER ). Donnez-moi plus d'informations sur le schéma, je vais créer un exemple pour vous.

Il n'y a rien à propos d'une jointure qui sera plus élégante que la sous-requête corrélée TOP 1 dans votre message d'origine. Cependant, comme vous le dites, cela ne satisfait pas à l'exigence B.

Ces requêtes fonctionnent (SQL Server 2005 ou version ultérieure requirejs). Voir le SqlFiddle pour ceux-ci .

 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; 

Notez que le CROSS APPLY avec une seule valeur de colonne est fonctionnellement équivalent à la sous-requête corrélée dans la clause SELECT comme vous l'avez montré. Je préfère juste utiliser CROSS APPLY car il est beaucoup plus flexible et vous permet de réutiliser la valeur à plusieurs endroits, d'avoir plusieurs lignes dedans (pour un routing personnalisé) et vous permet d'avoir plusieurs colonnes.

 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; 

Je ne sais pas lequel pourrait mieux fonctionner, ou si l'un ou l'autre fonctionnera mieux que d'autres réponses sur la page. Avec un index approprié sur les colonnes Date, ils devraient plutôt bien se Row_Number() – certainement mieux que n'importe Row_Number() solution Row_Number() .