rYrPyeyT

1-- input "cur"
2DROP TABLE IF EXISTS "curdates";
3DROP TABLE IF EXISTS "outCur";
4
5CREATE TABLE "curdates" AS (
6	SELECT "date",
7	    TO_CHAR("date", 'YYYY-MM-DD') ||  (SELECT DISTINCT "fromCurrency" FROM "cur" LIMIT 1) || "currency" AS "id",
8	    "currency"
9	FROM 
10		(
11		    SELECT DATEADD(day, ROW_NUMBER() OVER (ORDER BY seq8()), '1999-01-03'::DATE) AS "date"
12		    FROM 
13		      	TABLE(GENERATOR(ROWCOUNT=>10000))	     
14	    ) AS "dates", 
15	    (
16	    	SELECT DISTINCT "toCurrency" AS "currency" 
17	    	FROM "cur"
18	    ) AS "currencies"
19	    WHERE "date" < CURRENT_TIMESTAMP
20	ORDER BY 1
21);
22
23CREATE TABLE "outCur" AS (
24	SELECT 
25		"cid" AS "id",
26		"allDate" AS "date",
27		(SELECT "fromCurrency" FROM "cur" LIMIT 1) AS "fromCurrency",
28		COALESCE("toCurrency", FIRST_VALUE("leadCurrency") OVER (PARTITION BY "grp", "allCurrency" ORDER BY "allDate", "allCurrency")) AS "toCurrency",
29		COALESCE("rate", FIRST_VALUE("leadRate") OVER (PARTITION BY "grp", "allCurrency" ORDER BY "allDate", "allCurrency")) AS "rate",
30		(SELECT "source" FROM "cur" LIMIT 1) AS "source"
31	FROM (
32		SELECT 
33			SUM("jump") OVER (ORDER BY "allDate") AS "grp", 
34			* 
35	  	FROM (  
36	    	SELECT 
37	    		CASE WHEN "leadRate" IS NOT NULL AND "rate" IS NULL OR
38	      			"leadRate" IS NULL AND "rate" IS NOT NULL THEN 1 ELSE 0 END AS "jump",
39	      		*
40	    	FROM (
41	    		SELECT 
42	          		LEAD("cur"."rate", -1) OVER ( PARTITION BY "curdates"."currency" ORDER BY "curdates"."date", "curdates"."currency") AS "leadRate",
43			        LEAD("cur"."toCurrency", -1) OVER ( PARTITION BY "curdates"."currency" ORDER BY "curdates"."date", "curdates"."currency") AS "leadCurrency",
44	        		"curdates"."id" AS "cid",
45	        		"curdates"."date" AS "allDate",
46	        		"curdates"."currency" AS "allCurrency",
47	        		"cur".* 
48	        	FROM "curdates" LEFT JOIN "cur"
49	          		ON "curdates"."id" = "cur"."id"
50	    	)     
51	  	)
52	)
53	ORDER BY "toCurrency", "date"
54);