by martin.matejka@keboola.com
We've already published this guide for MySQL and Redshift backends, so I'll skip the part where you are preparing exchange rates table for one currency. We'll use table for conversion CZK to USD (you can see the gaps marked by red lines) and a simple table with dates. Table is called czk_to_usd.
First, we'll join the rate table to dates table, so we've got nulls for missing dates in the output. CREATE TABLE "usd_temp" AS
SELECT
"d"."date" AS "date",
"c"."czk_usd_rate" AS "rate",
'USD' AS "currency"
FROM "dates" "d"
LEFT JOIN "czk_to_usd" "c"
ON "c"."date" = "d"."date"
WHERE "d"."date" BETWEEN '2014-01-02' AND CURRENT_DATE
ORDER BY 1;
Here's the output:
And now we'll create partition from every block of null values and preceding value and fill first_value to these blocks' rows. CREATE TABLE "out_exchange_rates"
AS
SELECT
"date",
"currency",
first_value("rate")
OVER (PARTITION BY "rate_partition"
ORDER BY "date") :: DECIMAL(12, 3) AS "rate"
FROM (
SELECT
"date",
"currency",
"rate",
sum(CASE WHEN "rate" IS NULL
THEN 0
ELSE 1 END)
OVER (
ORDER BY "date") AS "rate_partition"
FROM "usd_temp"
ORDER BY "date" ASC
) AS "a";
Tam-ta-da-daaa!
Wish you happy currency-converting-weekends!
| 
