Filling the gaps in currency extractor output

by martin.matejka@keboola.com


Description: We’ve realized that ECB is not providing exchange rates during weekends and holidays.

Here you can find UPDATE statement which fills the the gaps with the nearest previous NOT NULL value.


Tables:

  • <dates> - list of dates


  • <exchange_rates> - table with currency exchange rates (i.e. CZK to USD)


  • `exchange_rates_updated` - output table with not null values


Columns:

  • <date> - date from <dates> table

  • <exchnage_date> - date of the exchange rate

  • <currency_rate> - exchange rate (czk_usd in the picture above)


For code in Redshift click Filling the gaps in currency extractor output in RS



As there is just conversion from EUR to other currencies in our extractor, here is guide how to create your own exchange rate table: Creating exchange rates from the extractor output



#generates all dates from selected date till now, so there will appear rows in new exchange rates table with NULL values

CREATE TABLE `exchange_rates_updated`

SELECT

<date>,

<currency_rate>

FROM <dates>

LEFT JOIN <exchange_rates>

ON <dates>.<date>=<exchange_rates>.<exchnage_date>

WHERE <dates>.<date> BETWEEN '2010-01-04' AND CURDATE()

GROUP BY 1;


#adds auto increment column to the table

ALTER TABLE `exchange_rates_updated`

ADD `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT;


#finds closest not null value and update the current table with it

UPDATE `exchange_rates_updated` JOIN (

   SELECT curr.`id`, (

       SELECT prior.<currency_rate>

         FROM `exchange_rates_updated` prior

        WHERE prior.<currency_rate> > ''

          AND prior.`id` < curr.`id`

     ORDER BY prior.`id` DESC

      LIMIT 1) `currency_rate`

   FROM `exchange_rates_updated` curr

   WHERE IFNULL(curr.`currency_rate`,'') = ''

 ) g ON g.id = `exchange_rates_updated`.id

SET `exchange_rates_updated`.`currency_rate` = g.`currency_rate`;


MULTI CURRENCIES LISTS:


The afore mentioned transformation applies to the situation when only one currency pair is in the list. This transformation will handle the situation when you have more currencies in the list like in the one below:


#generates all dates from selected date till now, so there will appear rows in new exchange rates table with NULL values 
CREATE TABLE `exchange_rates_eur` 
SELECT `dates`.`date`, 
`exchange_rates`.`rate`, 
`exchange_rates`.`fromCurrency`, 
`exchange_rates`.`toCurrency` 
FROM `dates` 
LEFT JOIN `exchange_rates` ON `dates`.`date`=`exchange_rates`.`date` WHERE `dates`.`date` BETWEEN '2010-01-04' AND CURDATE() ; 

#generate entries for all currencies 
INSERT INTO `exchange_rates_eur` 
SELECT e.date,e.rate,'EUR' as fromCurrency,u.toCurrency from exchange_rates_eur e 
CROSS JOIN 
(SELECT r.toCurrency FROM exchange_rates_eur r) u 
WHERE e.rate IS NULL AND u.toCurrency IS NOT NULL GROUP BY 4,1; 

#Delete the empty values 
DELETE FROM `exchange_rates_eur` WHERE toCurrency IS NULL; 

#adds auto increment column to the table 

ALTER TABLE `exchange_rates_eur` ADD `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT; 

#finds closest not null value and update the current table with it 

UPDATE `exchange_rates_eur` JOIN ( SELECT curr.`id`, ( SELECT p.`rate` FROM `exchange_rates_eur` p WHERE p.`rate` > '' AND p.`id` < curr.`id` AND p.toCurrency = curr.toCurrency ORDER BY p.`id` DESC LIMIT 1) `rate` FROM `exchange_rates_eur` curr WHERE IFNULL(curr.`rate`,'') = '' ) g ON g.id = `exchange_rates_eur`.id SET `exchange_rates_eur`.`rate` = g.`rate`;
Comments