SQL:有效地为每一列在单独的子范围内找到最小数据点

Question

我有一个SQL查询(遗憾的是以Server为目标)，它看起来如下所示：

SELECT
    DATETIMEFROMPARTS(YEAR(ts), MONTH(ts), DAY(ts), 0, 0, 0, 0) AS day,
    AVG(CASE WHEN DATEPART(HOUR, ts) = 0 THEN val ELSE NULL END) AS hr0,
    AVG(CASE WHEN DATEPART(HOUR, ts) = 1 THEN val ELSE NULL END) AS hr1,
    -- ...etc for other hours...
FROM 
    sample_data
GROUP BY 
    DATETIMEFROMPARTS(YEAR(ts), MONTH(ts), DAY(ts), 0, 0, 0, 0)

这非常好，计算(每一天)每小时的平均值。

然而，我的需求正在改变:现在只要求我每小时提供第一个样本。因此，hr0应该只反映满足DATEPART(HOUR, ts) = 0的最小ts的单个DATEPART(HOUR, ts) = 0(当然，还在同一天内)，如果不存在，则反映为NULL。

显而易见的方法是每小时使用一个子查询，但我认为这大大降低了运行时效率(而且我的实现尝试不仅很慢，而且很难看)。有没有更好的选择我没有考虑？

Answer 1

要从datetime中截断时间组件，只需将其转换为date即可。

查一下top-n-per-group或greatest-n-per-group。有关Server，请参见每组检索n行。

下面是使用ROW_NUMBER()的一个可能的变体。

WHERE rn=1过滤器的结果最多为每小时一行。每一行将包含这个小时的第一个val。

GROUP BY dt与24 MIN(CASE WHEN DATEPART(HOUR, ts) = ...一起枢轴将结果集转换为每天生成一行，每小时有24列。在这里，您可以不使用MIN，而可以放置任何其他聚合函数(MAX、SUM、AVG)。结果不会改变，因为在第一个过滤器之后，每小时最多可以有一行。

WITH
CTE
AS
(
    SELECT
        ts
        ,CAST(ts as date) AS dt
        ,val
        ,ROW_NUMBER() 
            OVER(PARTITION BY CAST(ts as date), DATEPART(HOUR, ts) ORDER BY ts) AS rn
    FROM sample_data
)
SELECT
    dt
    ,MIN(CASE WHEN DATEPART(HOUR, ts) = 0 THEN val ELSE NULL END) AS hr0
    ,MIN(CASE WHEN DATEPART(HOUR, ts) = 1 THEN val ELSE NULL END) AS hr1
    ,MIN(CASE WHEN DATEPART(HOUR, ts) = 2 THEN val ELSE NULL END) AS hr2
    -- ...etc for other hours...
FROM CTE
WHERE rn=1
GROUP BY dt
ORDER BY dt;

下面是带有示例数据的SQL Fiddle。