时间戳

首页 > 解决方案 > SQL 条件聚合随时间变化

sql-server - SQL 条件聚合随时间变化

问题描述

出于报告目的，我正在努力获取一些可以在报告工具（SSRS）中轻松分组和汇总的表格。

我使用 SQL Server 2014

我有以下表格：（示例中显示了日期，但这些实际上是日期时间）

tblEvents

datetimestamp       prio
----------------------------------
2018-05-20          4
2018-08-05          1 
2018-11-12          3
2018-11-13          1

需要对 tblCalendar 进行“重新采样”，这是一个等间隔的时间间隔表，具有可配置的 @start_datetime、@end_date、@number_of_intervals，我已经设法使用 CTE 制作：

tblCalendar

datefrom        dateto      
----------------------------------
1900-01-01      2018-04-12  
2018-04-12      2018-05-31  
2018-05-31      2018-07-19
2018-07-19      2018-09-06   
2018-09-06      2018-10-25
2018-10-25      2018-12-13
2018-12-13      2100-01-01

期望的结果是：

datefrom        dateto      prio
-------------------------------------------
1900-01-01      2018-04-12     -1
2018-04-12      2018-05-31     4     
2018-05-31      2018-07-19     4    
2018-07-19      2018-09-06     1 
2018-09-06      2018-10-25     1    
2018-10-25      2018-12-13     3
2018-12-13      2100-01-01     3

对于每个 tblCalendar 行，需要根据以下添加列 prio：

   IF between [datefrom] en [dateto] 1 or more datetimestamp present in tblEvents

        take MAXIMUM of prio's between [datefrom] en [dateto]

    ELSE
        IF before [date1]  1 or more datetimestamp present in tblEvents

            take LAST prio between [datefrom] en [dateto]

        ELSE

            IF after [date2] 1 or more datetimestamp present

                take FIRST prio between [datefrom] en [dateto]

            ELSE

                prio unknown = -1

            END
        END
    END

所以我将这个问题描述为条件聚合。

我对 SQL 很陌生，并试图用子查询、条件连接等来解决这个问题，但无法让它工作。下面的代码是我目前的尝试。

SELECT
    datefrom,
    dateto,
    MAX(Case 
            When ((datetimestamp >= datefrom)  AND (datetimestamp <= dateto)) Then prio
            When ((datetimestamp <= datefrom)) Then 'last-known-prio'
            When ((datetimestamp >  dateto)) Then 'first future-prio'                       
            Else    
                -1  -- no prio known ever                                                   
            End),

FROM   tblCalendar CROSS JOIN tblEvents

任何帮助和想法将不胜感激。

标签： sql-serveraggregation

解决方案

这不是条件聚合。

这是一个简单的选择tblCalendar，然后是基于 CASE 的 SUBSELECT 来获取prio值。

伪代码：

SELECT
    datefrom,
    dateto,
    CASE 
        WHEN {between [datefrom] en [dateto] 1 or more datetimestamp present in tblEvents}
          THEN (SELECT {MAXIMUM of prio's between [datefrom] en [dateto]})
        WHEN {before [date1]  1 or more datetimestamp present in tblEvents}
          THEN (SELECT {LAST prio between [datefrom] en [dateto]})
        WHEN {after [date2] 1 or more datetimestamp present}
          THEN (SELECT {FIRST prio between [datefrom] en [dateto]})
        ELSE -1
    END as prio
FROM   tblCalendar

CROSS APPLY 等可能还有其他语法选项，但这是考虑这个问题的方法。

推荐阅读