递归SQL中的聚合函数 [英] aggregate function in recursive SQL
问题描述
此问题是此问题的扩展和简化版本. /p>
我一直试图在SQL中解决以下迭代方程:
U^{F,D}_{t,p} = (\sum_{D} U^{F,D}_{t-1,p} + C_{t-1,p} )*R^{F,D}_{t-1,p}
结果为:
我能想到的最接近的类比是U^{F,D}_{t,p}是一定数量颜色(D)的某些品牌F的汽车，而汽车经销商(p)在时间t-1(即U^{F,D}_{t-1,p})前一天的汽车单位，对颜色(\sum_{D})求和，然后将前一天的C值加到总和中(C_{t-1,p}，无论是什么)，再乘前一天的其他数字R(R^{F,D}_{t-1,p}，无论是什么).
简化的问题
我设法解决了上述方程式的简化形式，即:
，即，不包括汽车颜色的总和(D).示例数据和SQL查询位于我链接的小提琴中 ，但是我也将其粘贴在这里以供参考:
完整数据:
CREATE TABLE DYNAMICS ( T DATE, T_M1 DATE, P INTEGER, F VARCHAR(255), DELTA_F VARCHAR(255), R_T_M1 NUMBER, C_T_M1 NUMBER, U_T_M1 NUMBER, R_T NUMBER, C_T NUMBER, U_T NUMBER );
-- DAY 1, P_1
INSERT INTO DYNAMICS VALUES ( TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('31.12.2014','DD.MM.YYYY HH24:MI:SS'), 1,'BMW','RED', 0.5, 0.6, NULL, 0.7,0.8,100.0 );
INSERT INTO DYNAMICS VALUES ( TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('31.12.2014','DD.MM.YYYY HH24:MI:SS'), 1,'MERCEDES','RED', 0.5, 0.6, NULL, 0.7,0.8,50.0 );
-- DAY 1, P_2
INSERT INTO DYNAMICS VALUES ( TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('31.12.2014','DD.MM.YYYY HH24:MI:SS'), 2,'BMW','RED', 0.5, 0.6, NULL, 0.7,0.8,10.0 );
INSERT INTO DYNAMICS VALUES ( TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('31.12.2014','DD.MM.YYYY HH24:MI:SS'), 2,'MERCEDES','RED', 0.5, 0.6, NULL, 0.7,0.8,5.0 );
-- DAY 2, P_1
INSERT INTO DYNAMICS VALUES ( TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), 1,'BMW','RED', 0.7, 0.8, 100, 0.9,0.9, NULL );
INSERT INTO DYNAMICS VALUES ( TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), 1,'MERCEDES','RED', 0.7, 0.8, 50, 0.6,0.5, NULL );
-- DAY 2, P_2
INSERT INTO DYNAMICS VALUES ( TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), 2,'BMW','RED', 0.7, 0.8, 10, 0.7,0.8, NULL );
INSERT INTO DYNAMICS VALUES ( TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), 2,'MERCEDES','RED', 0.7, 0.8, 5, 0.3,0.3, NULL );
-- DAY 3, P_1
INSERT INTO DYNAMICS VALUES ( TO_DATE('03.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), 1,'BMW','RED', 0.9, 0.9, NULL, 0.2,0.3, NULL );
INSERT INTO DYNAMICS VALUES ( TO_DATE('03.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), 1,'MERCEDES','RED', 0.6, 0.5, NULL, 1.7,1.8, NULL );
-- DAY 3, P_2
INSERT INTO DYNAMICS VALUES ( TO_DATE('03.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), 2,'BMW','RED', 0.7, 0.8, NULL, 0.2,0.3, NULL );
INSERT INTO DYNAMICS VALUES ( TO_DATE('03.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), 2,'MERCEDES','RED', 0.3, 0.3, NULL, 0.8,0.9, NULL );
样本数据:
以下内容演示了汽车经销商p=1的示例数据，颜色为D=RED的汽车型号F=BMW(在数学方程式中的D在SQL中称为DELTA).初始条件(t=0)在此处为2015-01-01.对于t的所有日期，都给出了t(R_T, C_T)和t-1(R_T_M1, C_T_M1)的所有参数.了解它们之后，任务就是计算t > t=0整天的汽车单位.
| T | T_M1 | P | F | DELTA_F | R_T_M1 | C_T_M1 | U_T_M1 | R_T | C_T | U_T |
|---------------------------|----------------------------|---|-----|---------|--------|--------|--------|-----|-----|--------|
| January, 01 2015 00:00:00 | December, 31 2014 00:00:00 | 1 | BMW | RED | 0.5 | 0.6 | (null) | 0.7 | 0.8 | 100 |
| January, 02 2015 00:00:00 | January, 01 2015 00:00:00 | 1 | BMW | RED | 0.7 | 0.8 | 100 | 0.9 | 0.9 | (null) |
| January, 03 2015 00:00:00 | January, 02 2015 00:00:00 | 1 | BMW | RED | 0.9 | 0.9 | (null) | 0.2 | 0.3 | (null) |
查询:
为了解决简化的问题，我在链接的小提琴中提出了查询我也粘贴在这里以供参考:
--
-- SQL
-- T -> t
-- T_M1 -> t-1
--
WITH RECU( T, T_M1, P, F, DELTA_F,
R_T_M1, C_T_M1, U_T_M1,
R_T, C_T, U_T ) AS (
-- Anchor member.
SELECT T, T_M1, P, F, DELTA_F,
R_T_M1, C_T_M1,
U_T_M1,
R_T, C_T,
U_T
FROM DYNAMICS
-- Initial condition: U_{t-1} does not exist, and U_{t=0} is given
WHERE ( U_T_M1 IS NULL AND U_T IS NOT NULL )
UNION ALL
-- Recursive member.
SELECT NEW.T, NEW.T_M1, NEW.P, NEW.F, NEW.DELTA_F,
NEW.R_T_M1, NEW.C_T_M1,
RECU.U_T AS U_T_M1,
NEW.R_T, NEW.C_T,
-- Here the magic happens, i.e., (U_{t-1} + C_{t-1})*R_{t-1} = U_{t}
(RECU.U_T+NEW.C_T_M1)*NEW.R_T_M1 AS U_T
FROM DYNAMICS NEW
INNER JOIN RECU
ON
-- Translates: yesterday (t-1) of the new record equals today (t) of the parent record
NEW.T_M1 = RECU.T AND
NEW.P = RECU.P AND
NEW.F = RECU.F AND
NEW.DELTA_F = RECU.DELTA_F
)
SELECT * FROM RECU ORDER BY P, F, T;
对于上面粘贴的示例数据，此查询的结果为:
| T | T_M1 | P | F | DELTA_F | R_T_M1 | C_T_M1 | U_T_M1 | R_T | C_T | U_T |
|---------------------------|----------------------------|---|-----|---------|--------|--------|--------|-----|-----|--------|
| January, 01 2015 00:00:00 | December, 31 2014 00:00:00 | 1 | BMW | RED | 0.5 | 0.6 | (null) | 0.7 | 0.8 | 100 |
| January, 02 2015 00:00:00 | January, 01 2015 00:00:00 | 1 | BMW | RED | 0.7 | 0.8 | 100 | 0.9 | 0.9 | 70.56 |
| January, 03 2015 00:00:00 | January, 02 2015 00:00:00 | 1 | BMW | RED | 0.9 | 0.9 | 70.56 | 0.2 | 0.3 | 64.314 |
哪个效果很好，例如:2015年1月2日，U_t = (100+0.8)*0.7 = 70.56，2015年1月3日，U_t = (70.56+0.9)*0.9 = 64.314.
查询的编写方式使其可以与不同的汽车经销商和不同的汽车品牌一起使用，可以运行链接的小提琴中的查询
回到完整的问题
上面的查询无法正确处理原始方程式中汽车颜色的总和:
这与简化数据无关，因为所有汽车(BMW和MERCEDES)仅在RED中出现，因此颜色上的总和实际上消失了.
这种完整的逻辑可能应该通过上述原始查询中内置的GROUP BY/SUM表达式来实现. 很遗憾，我不知道该怎么做.
因此，假设您拥有简化问题部分中形状的数据，但是现在每个汽车品牌都有两种颜色，例如，例如在此链接的小提琴中:
| T | T_M1 | P | F | DELTA_F | R_T_M1 | C_T_M1 | U_T_M1 | R_T | C_T | U_T |
|---------------------------|----------------------------|---|----------|---------|--------|--------|--------|-----|-----|--------|
| January, 01 2015 00:00:00 | December, 31 2014 00:00:00 | 2 | MERCEDES | BLACK | 0.2 | 0.6 | (null) | 0.5 | 0.8 | 5.5 |
| January, 01 2015 00:00:00 | December, 31 2014 00:00:00 | 2 | MERCEDES | RED | 0.5 | 0.6 | (null) | 0.7 | 0.8 | 5 |
| January, 02 2015 00:00:00 | January, 01 2015 00:00:00 | 2 | MERCEDES | BLACK | 0.5 | 0.8 | 5.5 | 1.3 | 0.5 | (null) |
| January, 02 2015 00:00:00 | January, 01 2015 00:00:00 | 2 | MERCEDES | RED | 0.7 | 0.8 | 5 | 4.3 | 0.5 | (null) |
| January, 03 2015 00:00:00 | January, 02 2015 00:00:00 | 2 | MERCEDES | BLACK | 1.3 | 0.5 | (null) | 0.3 | 0.9 | (null) |
| January, 03 2015 00:00:00 | January, 02 2015 00:00:00 | 2 | MERCEDES | RED | 4.3 | 0.5 | (null) | 0.4 | 0.9 | (null) |
鉴于此类数据，您期望经销商p=2 F=MERCEDES汽车动态如下:
U^{MERCEDES,BLACK}_{T=2015-01-02,P=2} = ( (5.5 + 5) + 0.8 )*0.5 = 11.3*0.5 = 5.65
U^{MERCEDES,RED}_{T=2015-01-02,P=2} = ( (5.5 + 5) + 0.8 )*0.7 = 11.3*0.7 = 7.91
U^{MERCEDES,BLACK}_{T=2015-01-03,P=2} = ( (5.65 + 7.91) + 0.5 )*1.3 = 14.06*1.3 = 18.278
U^{MERCEDES,RED}_{T=2015-01-03,P=2} = ( (5.65 + 7.91) + 0.5 )*4.3 = 14.06*4.3 = 60.458
问题是应如何调整上面的简化查询以解决此问题.
我认为这不是最好的答案，但我认为它会为您提供所需的结果.
WITH RECU( T, T_M1, P, F, DELTA_F,
R_T_M1, C_T_M1, U_T_M1,
R_T, C_T, U_T ) AS (
-- Anchor member.
SELECT T, T_M1, P, F, DELTA_F,
R_T_M1, C_T_M1,
U_T_M1,
R_T, C_T,
-- Start SUM of u_t
(select sum(u_t) from DYNAMICS d2
where d2.T=d1.T and d2.T_M1=d1.T_M1 and d2.P=d1.P and d2.F=d1.F
group by T, T_M1, P, F) as u_t
-- End SUM of u_t
FROM DYNAMICS d1
-- Initial condition: U_{t-1} does not exist, and U_{t=0} is given
WHERE ( U_T_M1 IS NULL AND U_T IS NOT NULL )
UNION ALL
-- Recursive member.
SELECT NEW.T, NEW.T_M1, NEW.P, NEW.F, NEW.DELTA_F,
NEW.R_T_M1, NEW.C_T_M1,
RECU.U_T AS U_T_M1,
NEW.R_T, NEW.C_T
,
-- Here the magic happens, i.e., (U_{t-1} + C_{t-1})*R_{t-1} = U_{t}
(
RECU.U_T
+NEW.C_T_M1)*NEW.R_T_M1 AS U_T
FROM DYNAMICS NEW
INNER JOIN RECU
ON
-- Translates: yesterday (t-1) of the new record equals today (t) of the parent record
NEW.T_M1 = RECU.T AND
NEW.P = RECU.P AND
NEW.F = RECU.F AND
NEW.DELTA_F = RECU.DELTA_F
)
SELECT * FROM RECU ORDER BY P, F, T;
我添加的内容在Start SUM of u_t和End SUM of u_t注释之间，这是小提琴.
This question is an extended and simplified version of this question.
I have been trying to solve in SQL the following iterative equation:
U^{F,D}_{t,p} = (\sum_{D} U^{F,D}_{t-1,p} + C_{t-1,p} )*R^{F,D}_{t-1,p}
that results in:
The closest analogy I can think of is that U^{F,D}_{t,p} is a number of cars of brand F, of certain color (D), that a car dealer (p) has available at time t. So the above equation basically says: take the cars' units from the day before t-1 (i.e., U^{F,D}_{t-1,p}), sum over colors (\sum_{D}), then add to the sum a C value from the day before (C_{t-1,p}, whatever that is), and multiply by some other number R from the day before (R^{F,D}_{t-1,p}, whatever that is too).
Simplified problem
I have managed to solve a simplified form of the above equation, namely:
i.e., without the sum over cars' colors (D). The sample data, and the SQL query are in the fiddle that I link, but I paste it here for reference as well:
FULL DATA:
CREATE TABLE DYNAMICS ( T DATE, T_M1 DATE, P INTEGER, F VARCHAR(255), DELTA_F VARCHAR(255), R_T_M1 NUMBER, C_T_M1 NUMBER, U_T_M1 NUMBER, R_T NUMBER, C_T NUMBER, U_T NUMBER );
-- DAY 1, P_1
INSERT INTO DYNAMICS VALUES ( TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('31.12.2014','DD.MM.YYYY HH24:MI:SS'), 1,'BMW','RED', 0.5, 0.6, NULL, 0.7,0.8,100.0 );
INSERT INTO DYNAMICS VALUES ( TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('31.12.2014','DD.MM.YYYY HH24:MI:SS'), 1,'MERCEDES','RED', 0.5, 0.6, NULL, 0.7,0.8,50.0 );
-- DAY 1, P_2
INSERT INTO DYNAMICS VALUES ( TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('31.12.2014','DD.MM.YYYY HH24:MI:SS'), 2,'BMW','RED', 0.5, 0.6, NULL, 0.7,0.8,10.0 );
INSERT INTO DYNAMICS VALUES ( TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('31.12.2014','DD.MM.YYYY HH24:MI:SS'), 2,'MERCEDES','RED', 0.5, 0.6, NULL, 0.7,0.8,5.0 );
-- DAY 2, P_1
INSERT INTO DYNAMICS VALUES ( TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), 1,'BMW','RED', 0.7, 0.8, 100, 0.9,0.9, NULL );
INSERT INTO DYNAMICS VALUES ( TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), 1,'MERCEDES','RED', 0.7, 0.8, 50, 0.6,0.5, NULL );
-- DAY 2, P_2
INSERT INTO DYNAMICS VALUES ( TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), 2,'BMW','RED', 0.7, 0.8, 10, 0.7,0.8, NULL );
INSERT INTO DYNAMICS VALUES ( TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('01.01.2015','DD.MM.YYYY HH24:MI:SS'), 2,'MERCEDES','RED', 0.7, 0.8, 5, 0.3,0.3, NULL );
-- DAY 3, P_1
INSERT INTO DYNAMICS VALUES ( TO_DATE('03.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), 1,'BMW','RED', 0.9, 0.9, NULL, 0.2,0.3, NULL );
INSERT INTO DYNAMICS VALUES ( TO_DATE('03.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), 1,'MERCEDES','RED', 0.6, 0.5, NULL, 1.7,1.8, NULL );
-- DAY 3, P_2
INSERT INTO DYNAMICS VALUES ( TO_DATE('03.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), 2,'BMW','RED', 0.7, 0.8, NULL, 0.2,0.3, NULL );
INSERT INTO DYNAMICS VALUES ( TO_DATE('03.01.2015','DD.MM.YYYY HH24:MI:SS'), TO_DATE('02.01.2015','DD.MM.YYYY HH24:MI:SS'), 2,'MERCEDES','RED', 0.3, 0.3, NULL, 0.8,0.9, NULL );
SAMPLE DATA:
The following demonstrates example data for car dealer p=1, car model F=BMW of color D=RED (D from the mathematical equation is called DELTA in SQL). The initial condition (t=0) is here 2015-01-01. For all the days t, all the parameters at t (R_T, C_T), and t-1 (R_T_M1, C_T_M1) are given. Knowing them, the task is to compute cars' units for all the days t > t=0.
| T | T_M1 | P | F | DELTA_F | R_T_M1 | C_T_M1 | U_T_M1 | R_T | C_T | U_T |
|---------------------------|----------------------------|---|-----|---------|--------|--------|--------|-----|-----|--------|
| January, 01 2015 00:00:00 | December, 31 2014 00:00:00 | 1 | BMW | RED | 0.5 | 0.6 | (null) | 0.7 | 0.8 | 100 |
| January, 02 2015 00:00:00 | January, 01 2015 00:00:00 | 1 | BMW | RED | 0.7 | 0.8 | 100 | 0.9 | 0.9 | (null) |
| January, 03 2015 00:00:00 | January, 02 2015 00:00:00 | 1 | BMW | RED | 0.9 | 0.9 | (null) | 0.2 | 0.3 | (null) |
QUERY:
In order to resolve the simplified problem, I have come up with the query in the linked fiddle that I paste here as well for reference:
--
-- SQL
-- T -> t
-- T_M1 -> t-1
--
WITH RECU( T, T_M1, P, F, DELTA_F,
R_T_M1, C_T_M1, U_T_M1,
R_T, C_T, U_T ) AS (
-- Anchor member.
SELECT T, T_M1, P, F, DELTA_F,
R_T_M1, C_T_M1,
U_T_M1,
R_T, C_T,
U_T
FROM DYNAMICS
-- Initial condition: U_{t-1} does not exist, and U_{t=0} is given
WHERE ( U_T_M1 IS NULL AND U_T IS NOT NULL )
UNION ALL
-- Recursive member.
SELECT NEW.T, NEW.T_M1, NEW.P, NEW.F, NEW.DELTA_F,
NEW.R_T_M1, NEW.C_T_M1,
RECU.U_T AS U_T_M1,
NEW.R_T, NEW.C_T,
-- Here the magic happens, i.e., (U_{t-1} + C_{t-1})*R_{t-1} = U_{t}
(RECU.U_T+NEW.C_T_M1)*NEW.R_T_M1 AS U_T
FROM DYNAMICS NEW
INNER JOIN RECU
ON
-- Translates: yesterday (t-1) of the new record equals today (t) of the parent record
NEW.T_M1 = RECU.T AND
NEW.P = RECU.P AND
NEW.F = RECU.F AND
NEW.DELTA_F = RECU.DELTA_F
)
SELECT * FROM RECU ORDER BY P, F, T;
This query, for the example data pasted above, results in:
| T | T_M1 | P | F | DELTA_F | R_T_M1 | C_T_M1 | U_T_M1 | R_T | C_T | U_T |
|---------------------------|----------------------------|---|-----|---------|--------|--------|--------|-----|-----|--------|
| January, 01 2015 00:00:00 | December, 31 2014 00:00:00 | 1 | BMW | RED | 0.5 | 0.6 | (null) | 0.7 | 0.8 | 100 |
| January, 02 2015 00:00:00 | January, 01 2015 00:00:00 | 1 | BMW | RED | 0.7 | 0.8 | 100 | 0.9 | 0.9 | 70.56 |
| January, 03 2015 00:00:00 | January, 02 2015 00:00:00 | 1 | BMW | RED | 0.9 | 0.9 | 70.56 | 0.2 | 0.3 | 64.314 |
Which works well, i.e., for: 2015-01-02, U_t = (100+0.8)*0.7 = 70.56, 2015-01-03, U_t = (70.56+0.9)*0.9 = 64.314.
The query is written in such a way that it works with different car dealers, and different car brands, which can be checked running the query in the linked fiddle
Coming back to the full problem
The query above cannot handle correctly the sum over cars' colors from the original equation:
This was irrelevant in the simplified data, since all cars (BMW and MERCEDES) occur there only in RED, and so the sum over colors effectively vanishes.
Such full logic should be probably implemented via a GROUP BY/SUM expression built in into the original query above. Unfortunately, I do not know how to do it.
So, imagine you have data in the shape like in the simplified problem section, but now every car brand exists in two colors, e.g., like in this linked fiddle:
| T | T_M1 | P | F | DELTA_F | R_T_M1 | C_T_M1 | U_T_M1 | R_T | C_T | U_T |
|---------------------------|----------------------------|---|----------|---------|--------|--------|--------|-----|-----|--------|
| January, 01 2015 00:00:00 | December, 31 2014 00:00:00 | 2 | MERCEDES | BLACK | 0.2 | 0.6 | (null) | 0.5 | 0.8 | 5.5 |
| January, 01 2015 00:00:00 | December, 31 2014 00:00:00 | 2 | MERCEDES | RED | 0.5 | 0.6 | (null) | 0.7 | 0.8 | 5 |
| January, 02 2015 00:00:00 | January, 01 2015 00:00:00 | 2 | MERCEDES | BLACK | 0.5 | 0.8 | 5.5 | 1.3 | 0.5 | (null) |
| January, 02 2015 00:00:00 | January, 01 2015 00:00:00 | 2 | MERCEDES | RED | 0.7 | 0.8 | 5 | 4.3 | 0.5 | (null) |
| January, 03 2015 00:00:00 | January, 02 2015 00:00:00 | 2 | MERCEDES | BLACK | 1.3 | 0.5 | (null) | 0.3 | 0.9 | (null) |
| January, 03 2015 00:00:00 | January, 02 2015 00:00:00 | 2 | MERCEDES | RED | 4.3 | 0.5 | (null) | 0.4 | 0.9 | (null) |
Given such data, you would expect for the dealer p=2 F=MERCEDES cars dynamics to look as follows:
U^{MERCEDES,BLACK}_{T=2015-01-02,P=2} = ( (5.5 + 5) + 0.8 )*0.5 = 11.3*0.5 = 5.65
U^{MERCEDES,RED}_{T=2015-01-02,P=2} = ( (5.5 + 5) + 0.8 )*0.7 = 11.3*0.7 = 7.91
U^{MERCEDES,BLACK}_{T=2015-01-03,P=2} = ( (5.65 + 7.91) + 0.5 )*1.3 = 14.06*1.3 = 18.278
U^{MERCEDES,RED}_{T=2015-01-03,P=2} = ( (5.65 + 7.91) + 0.5 )*4.3 = 14.06*4.3 = 60.458
Question is how the simplified query above should be adjusted to solve this problem.
I do not think this is the best answer but I think it gives you the result that you are looking for.
WITH RECU( T, T_M1, P, F, DELTA_F,
R_T_M1, C_T_M1, U_T_M1,
R_T, C_T, U_T ) AS (
-- Anchor member.
SELECT T, T_M1, P, F, DELTA_F,
R_T_M1, C_T_M1,
U_T_M1,
R_T, C_T,
-- Start SUM of u_t
(select sum(u_t) from DYNAMICS d2
where d2.T=d1.T and d2.T_M1=d1.T_M1 and d2.P=d1.P and d2.F=d1.F
group by T, T_M1, P, F) as u_t
-- End SUM of u_t
FROM DYNAMICS d1
-- Initial condition: U_{t-1} does not exist, and U_{t=0} is given
WHERE ( U_T_M1 IS NULL AND U_T IS NOT NULL )
UNION ALL
-- Recursive member.
SELECT NEW.T, NEW.T_M1, NEW.P, NEW.F, NEW.DELTA_F,
NEW.R_T_M1, NEW.C_T_M1,
RECU.U_T AS U_T_M1,
NEW.R_T, NEW.C_T
,
-- Here the magic happens, i.e., (U_{t-1} + C_{t-1})*R_{t-1} = U_{t}
(
RECU.U_T
+NEW.C_T_M1)*NEW.R_T_M1 AS U_T
FROM DYNAMICS NEW
INNER JOIN RECU
ON
-- Translates: yesterday (t-1) of the new record equals today (t) of the parent record
NEW.T_M1 = RECU.T AND
NEW.P = RECU.P AND
NEW.F = RECU.F AND
NEW.DELTA_F = RECU.DELTA_F
)
SELECT * FROM RECU ORDER BY P, F, T;
What i've added is between Start SUM of u_t and End SUM of u_t comments and here is the fiddle.
这篇关于递归SQL中的聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
