时间戳

首页 > 解决方案 > SQL Server：变量窗口函数

sql - SQL Server：变量窗口函数

问题描述

使用 SQL Server 2016，我有两个这样的表：

T1
-----------------------
User |  Y   | M | V
---- ------------------
US1  | 2011 | 1 | 87.5
US1  | 2011 | 2 | 76.4
...  | ...  | ..| ...
US1  | 2018 | 7 | 4.7
US1  | 2018 | 8 | 7.7
US1  | 2018 | 9 | 16.3
US1  | 2018 | 10| 11.1
US1  | 2018 | 11| 98.8
US1  | 2018 | 12| 65.7
US2  | 2011 | 1 | 44.5
US2  | 2011 | 2 | 34.7
...  |  ... |...| ... 
US2  | 2018 | 6 | 18.7
US2  | 2018 | 7 | 5.1
US2  | 2018 | 8 | 1.0 
US2  | 2018 | 9 | 4.4
US2  | 2018 | 10| 22.6
US2  | 2018 | 11| 66.5
US2  | 2018 | 12| 23.7

T2
----------
User | S | 
---- -----
US1  | 3 | 
US2  | 5 |

我想得到一个这样的运行总和

SELECT t1.Y
   ,t1.M
   ,t1.User
   ,SUM(t1.V) OVER (PARTITION BY t1.User ORDER BY t1.Y,t1.M ROWS BETWEEN t2.S PRECEDING AND CURRENT ROW)
FROM t1
JOIN t2 ON t1.User = t2.User

所以我过去 2 个月的预期输出是

US1  | 2018 | 11| 133,9 (98.8+11.1+16.3+7.7)
US1  | 2018 | 12| 191.9 (65.7+98.8+11.1+16.3)
US2  | 2018 | 11| 118.3 (66.5+22.6+4.4+1.0+5.1+18.7)
US2  | 2018 | 12| 123.3 (23.7+66.5+22.6+4.4+1.0+5.1)

但我不能在窗口函数中使用 t2.S 作为变量。

有没有办法使用 Window Funcitons 来做到这一点？

标签： sqlsql-serverwindow-functions

解决方案

我认为窗框规范不允许使用表达式。您可以使用以下方法执行此操作apply：

SELECT t1.Y, t1.M, t1.User, t2.sum_v
FROM t1 CROSS APPLY
     (SELECT SUM(t.v) as sum_v
      FROM (SELECT tt1.v, tt2.S
                   ROW_NUMBER() OVER (ORDER BY tt1.Y DESC, tt1.M DESC) as seqnum
            FROM t1 tt1 JOIN
                 t2 tt2
                 ON tt1.User = tt2.User
            WHERE tt1.User = t1.User
           ) t
      WHERE seqnum <= S
     ) t2

编辑：

以上不起作用，因为顺序直接错误。在任何情况下，它都相当于一个自连接，而且是相当昂贵的。

这可以用窗口函数来完成吗？

我认为答案是“是”：

with t1 as (
      select t1.*, t2.s,
             sum(v) over (partition by user order by y, m) as running_v
      from t1 join
           t2
           on t1.user = t2.user
     ) t
select t.*,
       (running_v - running_v_s)
from (select t.*,
             lag(running_v, s) over (partition by user order by y, m) as running_v_s
      from t
     ) t;

虽然您不能将表达式用于窗框，但您可以将它们用于lag(). 我认为这种方法可能是性能最好的方法。

推荐阅读