时间戳

首页 > 解决方案 > 带有日期过滤器的 SQL 窗口函数

sql - 带有日期过滤器的 SQL 窗口函数

问题描述

希望在需要包含日期过滤器的表上执行 LAG/LAST_VALUE/MAGIC 窗口函数（如它应该返回多远）。我正在尝试将 7 天前的值放到每一行上。不确定这可以通过稀疏数据集中的精美窗口函数来实现。由于数据非常大，它也需要是一个高效的查询查询。

测试查询（错误结果）：

WITH TestData AS (
    SELECT 'MyKey' AS KeyCol, CAST('2019-05-06' AS DATE) as DateCol, 20 as ValueCol
    UNION ALL
    SELECT 'MyKey' AS KeyCol, CAST('2019-05-12' AS DATE) as DateCol, 12 as ValueCol
    UNION ALL
    SELECT 'MyKey' AS KeyCol, CAST('2019-06-01' AS DATE) as DateCol, 41 as ValueCol
    UNION ALL
    SELECT 'MyKey' AS KeyCol, CAST('2019-06-14' AS DATE) as DateCol, 21 as ValueCol
    UNION ALL
    SELECT 'MyKey' AS KeyCol, CAST('2019-06-15' AS DATE) as DateCol, 2 as ValueCol
    UNION ALL
    SELECT 'MyKey' AS KeyCol, CAST('2019-06-20' AS DATE) as DateCol, 32 as ValueCol
)

SELECT KeyCol, DateCol, ValueCol
, LAG(ValueCol) OVER (ORDER BY DateCol ASC) ThisShouldShowValueAsOf7DaysAgo

from TestData

输出：

+--------+------------+----------+---------------------------------+
| KeyCol |  DateCol   | ValueCol | ThisShouldShowValueAsOf7DaysAgo |
+--------+------------+----------+---------------------------------+
| MyKey  | 2019-05-06 |       20 | NULL                            |
| MyKey  | 2019-05-12 |       12 | 20    -> should be NULL         |
| MyKey  | 2019-06-01 |       41 | 12    -> correct                |
| MyKey  | 2019-06-14 |       21 | 41    -> correct                |
| MyKey  | 2019-06-15 |        2 | 21    -> should be 41           |
| MyKey  | 2019-06-20 |       32 | 2     -> should be 41           |
+--------+------------+----------+---------------------------------+

标签： sqlsql-server

解决方案

我想你想要apply：

select t.*, t2.valuecol
from t outer apply
     (select top (1) t2.*
      from t t2
      where t2.keycol = t.keycol and
            t2.date < dateadd(day, -7, t.date)
      order by t2.date desc
     ) t2

在其他数据库中，您可以使用带有range between. 但是，SQL Server 不支持此类功能。

推荐阅读