递归SQL中的聚合函数

大不丢儿

我一直试图在SQL中解决以下迭代方程式：
5 K; `+ F1 X2 ^# U' E5 x5 i0 G: YU^{F,D}{t,p} = (\sum U^{F,D}{t-1,p} + C )*R^{F,D}_{t-1,p}
结果是：
5 E) M5 }; p: N6 ]& a$ k% W, g在此处输入图片说明
我能想到的最接近的比喻是，U^{F,D}_{t,p}有一些汽车经销商（） 当时有一定颜色（）
+ `- `, l$ B' A7 p- Z的品牌汽车。因此，上面的等式基本上说：取 前一天（即）的汽车单位，对颜色（）求和，然后 将前一天的值（，无论 是什么）相加，然后乘以其他号之前，从一天 （，不管它是什么太）。FDptt-1U^{F,D}_{t-1,p}\sum_{D}CC_{t-1,p}RR^{F,D}_{t-1,p}
" N0 A" K2 I, U4 {简化的问题
  v1 m* E( @  f- X我设法解决了上述方程式的简化形式，即：
0 U& \; T7 }7 H1 L即，不包括汽车颜色的总和（D）。示例数据和SQL
查询位于我链接的小提琴中，但我也将其粘贴在这里以供参考：
完整数据：
9 I+ h0 R% t% B) b) [% x. qCREATE 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  
0 L1 k! X) h; T+ m2 oINSERT 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 );  
. t4 ]% D( b& v7 V& t$ tINSERT 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 );  
7 f. V" Q2 o( {$ I9 w) {-- 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 );  
8 g, n, \% p9 ~) ZINSERT 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 );  
! H4 N$ s5 f: Z1 Z# K-- 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 );  
) s$ v( T  \* `: ?/ y2 M9 h2 z-- DAY 3, P_2  
: K  ^/ }8 q* _' |1 aINSERT 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 );  
" R* D0 a# z/ o6 cINSERT 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 );
. a9 x3 z: y9 _0 I! E4 Y样本数据：
下面演示汽车经销商p=1，F=BMW颜色汽车模型的示例数据D=RED（D从数学方程式中称为DELTASQL）。初始条件（t=0）在这里2015年1月1日。对于所有日子
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 |
|---------------------------|----------------------------|---|-----|---------|--------|--------|--------|-----|-----|--------|
: T0 M' r/ z% t$ j5 ~9 ]| 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) |
& K' y. m" ]& [* }  PQUERY:
2 M( h' W7 v" u8 k2 ^) Y% cIn 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:
4 `6 Q4 M: K  W2 }--
-- SQL
-- T -> t
! Z- `+ p/ v" X5 B-- T_M1 -> t-1
) k2 Q+ R# p4 z4 W. Y" ]4 d; D& ~--
WITH RECU(  T, T_M1, P, F, DELTA_F,
% R& u& J: X' y% ^0 ]            R_T_M1, C_T_M1, U_T_M1,
            R_T, C_T, U_T ) AS (
    -- Anchor member.
+ N% N3 N  c" C# [. W1 U7 ~: M$ D9 d" Z    SELECT  T, T_M1, P, F, DELTA_F,
: C2 x% H$ N8 ~: F; G2 ]$ n            R_T_M1, C_T_M1,
            U_T_M1,
            R_T, C_T,
            U_T
    FROM DYNAMICS
# m2 r  v' U6 m/ a        -- Initial condition: U_{t-1} does not exist, and U_{t=0} is given
" ]6 q5 k" F, t- ?" Z$ m  Q        WHERE  ( U_T_M1 IS NULL AND U_T IS NOT NULL )
/ J3 F, e! ]# \    UNION ALL
( L# Y- V+ w* m# t/ N    -- 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,
4 q* P' M" u/ g! a; k3 m            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
3 ]6 ?- B3 o0 n- D7 Q    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
6 m: ~& z. S& J1 D4 `7 k. X6 T        NEW.P = RECU.P AND
        NEW.F = RECU.F AND
        NEW.DELTA_F = RECU.DELTA_F
% q1 D% s1 p! o. f)
SELECT * FROM  RECU ORDER BY P, F, T;
This query, for the example data pasted above, results in:
8 _) O3 H7 _: h6 R! }|                         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,
; i; y; W& K% A( Pand different car brands, which can be checked running the query in the
( @9 y/ v# }: Q! V1 ulinked fiddle
Coming back to the full problem
The query above cannot handle correctly the sum over cars’ colors from the
original equation:
5 E0 E; S' A& E2 p- i$ K3 P' E3 E

. H8 p3 a9 c' V1 b
This was irrelevant in the simplified data, since all cars (BMW and MERCEDES)
$ {. ?9 }2 B2 c. I7 V$ Moccur 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
0 K( k- V6 u% Y- fdo 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:
  ]4 s* _) ~. F! `0 B" C|                         T |                       T_M1 | P |        F | DELTA_F | R_T_M1 | C_T_M1 | U_T_M1 | R_T | C_T |    U_T |
$ |- L. ^4 w; |: ?' q* I9 I% k|---------------------------|----------------------------|---|----------|---------|--------|--------|--------|-----|-----|--------|
| 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 |
% V' \3 [$ |, J2 N) u& n* I| 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 |
/ Y0 w) s$ M/ e/ l' |4 G9 b6 p| 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) |
" t/ {% H! |, N6 }3 p. k. ~. x) `| 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
2 b8 B3 e$ N( R1 P- G2 }$ `) S9 w% cdynamics 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
2 F. U1 ~, d, v6 h8 x/ LU^{MERCEDES,RED}_{T=2015-01-02,P=2} = ( (5.5 + 5) + 0.8 )*0.7 = 11.3*0.7 = 7.91
9 _' {; A" u9 s& i! yU^{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
* V* U! S3 m1 @7 z3 ^6 R  X) b& ~problem.
7 V) o: V- x8 O2 V3 [# s               
解决方案:
% b; t4 f5 H' f( i. L( ~9 d3 ^               

3 X$ d, d9 ]. s: M) G. Y, \
                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,
  C7 M% `2 C. I' S7 g            R_T_M1, C_T_M1, U_T_M1,
            R_T, C_T, U_T ) AS (
$ k6 H3 x2 E! d, Y* n    -- Anchor member.
, K4 ~) U$ d: b7 m& y0 [  C! ?" S    SELECT  T, T_M1, P, F, DELTA_F,
" \/ j0 S  f, ^6 r: J            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
& i0 E: l& A2 \% r1 r+ x               group by T, T_M1, P, F) as u_t
-- End SUM of u_t   
( S8 @1 ^5 I+ d    FROM DYNAMICS d1
' `' h  o- A! i) B        -- Initial condition: U_{t-1} does not exist, and U_{t=0} is given
3 R5 G& B1 q* u7 }" k- N: N8 h        WHERE  ( U_T_M1 IS NULL AND U_T IS NOT NULL )
2 a- D+ ~7 F& E/ [) E    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
3 Q9 O* q- E4 \              ,
) B, ^2 K  d/ {( d0 Y6 ]' h* L+ M            -- Here the magic happens, i.e., (U_{t-1} + C_{t-1})*R_{t-1} = U_{t}
  I: \1 Y; }0 j3 i' X; M9 e! O            (
) ]  b# F( d* D8 m. D5 `3 y& W$ {  b              RECU.U_T
              +NEW.C_T_M1)*NEW.R_T_M1 AS U_T
, }9 j: `  X1 }3 m/ m0 q    FROM DYNAMICS NEW
    INNER JOIN RECU
    ON
" O% m& y7 X# ]* P1 m) B* z2 {        -- Translates: yesterday (t-1) of the new record equals today (t) of the parent record
        NEW.T_M1 = RECU.T AND
3 B; n" [% h2 D, Z; |& E        NEW.P = RECU.P AND
        NEW.F = RECU.F AND
9 F: q. }4 r8 h  s        NEW.DELTA_F = RECU.DELTA_F
) w  ~7 @' \, f+ T& G9 ~. I# T  J4 w)
SELECT * FROM  RECU ORDER BY P, F, T;
6 a$ n1 @) _$ \( X7 ]$ ~! _' z. YWhat i’ve added is between Start SUM of u_t and End SUM of u_t comments
and here is the fiddle.