T-SQL CTE自引用CROSS APPLY前一行按日期划分

时间：2022-10-21 11:24:15

I have an updatable table of date-value sequence (say dbo.sequence) in SQL Server 2014. Dates are unique.

我在SQL Server 2014中有一个可更新的日期值序列表(比如dbo.sequence)。日期是唯一的。

When new updates come I want to distribute that values into different columns in a separate table (say dbo.distributed_values) by certain conditions, e.g. if previous value from dbo.sequence is less/greater than current dbo.sequence value, it gets inserted into specified column of dbo.distributed_values or becomes NULL in that column.

当新的更新到来时,我希望通过某些条件将这些值分配到单独的表(例如dbo.distributed_values)中的不同列中,例如如果来自dbo.sequence的先前值小于/大于当前dbo.sequence值,则它将插入到dbo.distributed_values的指定列中或在该列中变为NULL。

Here is the main idea:

这是主要的想法:

;WITH
CTE_tbl (date, value, val_1, val_2, val_3)
AS (
SELECT ... FROM dbo.distributed_values  -- get latest values from database

UNION ALL

SELECT
   SEQ.date,
   SEQ.value,

   CASE
   WHEN ABS (SEQ.value - prev.value) >= 0.5
   THEN SEQ.value
   ELSE NULL
   END AS val_1,

   ...

   FROM dbo.sequence AS SEQ
   CROSS APPLY (SELECT * FROM CTE_tbl WHERE date = DATEADD(DAY, -1, SEQ.date)) AS prev
)

INSERT INTO dbo.distributed_values (...)
SELECT *
   FROM CTE_tbl
   ORDER BY date ASC
   OPTION (MAXRECURSION 1000)

Seems it works mostly, but the dbo.sequence contains gaps, so I can not use things like date = DATEADD(DAY, -1, SEQ.date) to bind on previous row properly.

似乎它主要起作用,但是dbo.sequence包含间隙,所以我不能使用像date = DATEADD(DAY,-1,SEQ.date)这样的东西来正确绑定前一行。

2012-01-04
2012-01-05
2012-01-06
2012-01-09
2012-01-10
2012-01-11

How to bind previous value correctly in case of date gaps?

如果日期空白,如何正确绑定以前的值?

UPD: By the way, I can not use LAG ... OVER in WHERE clause, I tried. Could it be used here somehow?

UPD:顺便说一句,我不能在WHERE子句中使用LAG ... OVER,我试过了。它可以在这里以某种方式使用吗?

1 个解决方案

#1

1  

Add another CTE and use that in your recursive CTE, something like this:

添加另一个CTE并在递归CTE中使用它,如下所示:

;WITH
SequenceWithPrevious AS(
    SELECT * 
        ,PrevValue = LAG(value,1,NULL) OVER (ORDER BY SEQ.date)
        ,Prevdate = LAG(date,1,NULL) OVER (ORDER BY SEQ.date)
    FROM dbo.sequence AS SEQ
),
CTE_tbl (date, value, val_1, val_2, val_3)
AS (
SELECT ... FROM dbo.distributed_values  -- get latest values from database

UNION ALL

SELECT  ...
   FROM SequenceWithPrevious AS SEQ
   CROSS APPLY (SELECT * FROM CTE_tbl WHERE date = SEQ.PrevDate) AS prev
)

INSERT INTO dbo.distributed_values (...)
SELECT *
   FROM CTE_tbl
   ORDER BY date ASC
   OPTION (MAXRECURSION 1000)

#1

1  

Add another CTE and use that in your recursive CTE, something like this:

添加另一个CTE并在递归CTE中使用它,如下所示:

;WITH
SequenceWithPrevious AS(
    SELECT * 
        ,PrevValue = LAG(value,1,NULL) OVER (ORDER BY SEQ.date)
        ,Prevdate = LAG(date,1,NULL) OVER (ORDER BY SEQ.date)
    FROM dbo.sequence AS SEQ
),
CTE_tbl (date, value, val_1, val_2, val_3)
AS (
SELECT ... FROM dbo.distributed_values  -- get latest values from database

UNION ALL

SELECT  ...
   FROM SequenceWithPrevious AS SEQ
   CROSS APPLY (SELECT * FROM CTE_tbl WHERE date = SEQ.PrevDate) AS prev
)

INSERT INTO dbo.distributed_values (...)
SELECT *
   FROM CTE_tbl
   ORDER BY date ASC
   OPTION (MAXRECURSION 1000)
标签：

相关文章