在PostgreSQL+EF Core中不加载全量数据按条件生成Period对象

我数据库中有如下实体：

public class DbValue
{
    public DateTimeOffset TimeStamp { get; set; }
    public double NumericValue { get; set; }
}

希望生成如下对象：

public class Period
{
    public DateTimeOffset StartDate { get; set; }
    public DateTimeOffset EndDate { get; set; }
}

请问能否在不将所有DbValue加载到内存的前提下，按数值阈值条件生成Period实例？

例如数据库中有如下DbValue数据：

当查询NumericValue小于5的数据时，希望得到两个Period实例：一个StartDate为2023-04-01 02:00:01.000、EndDate为2023-04-01 02:00:02.000；另一个StartDate为2023-04-01 02:00:04.000、EndDate为2023-04-01 02:00:05.000，且全程不加载所有DbValue到内存。

当然可以实现，核心是让数据库完成连续符合条件区间的分组与聚合计算，仅返回最终的Period结果，无需将所有DbValue数据加载到内存。以下提供两种常见场景的实现方式：

1. 直接使用SQL查询 #

利用窗口函数LAG()标记连续符合条件的记录组，再按组聚合出起止时间（以SQL Server为例，其他数据库语法略有差异，需对应调整）：

WITH filtered AS (
    SELECT 
        TimeStamp,
        -- 判断当前记录是否与上一条符合条件的记录连续，标记新组起点
        CASE 
            WHEN LAG(TimeStamp) OVER (ORDER BY TimeStamp) = DATEADD(SECOND, -1, TimeStamp) 
            THEN 0 
            ELSE 1 
        END AS is_new_group
    FROM DbValues
    WHERE NumericValue < 5
),
grouped AS (
    SELECT 
        TimeStamp,
        -- 累计分组标记，生成唯一组ID
        SUM(is_new_group) OVER (ORDER BY TimeStamp) AS group_id
    FROM filtered
)
SELECT 
    MIN(TimeStamp) AS StartDate,
    MAX(TimeStamp) AS EndDate
FROM grouped
GROUP BY group_id
ORDER BY StartDate;

这条SQL会在数据库端完成全部计算，返回的结果就是目标Period数据，客户端仅需接收最终结果即可。

2. 使用EF Core（ORM方式） #

如果使用EF Core，可以通过LINQ查询转换为上述SQL逻辑，避免手写SQL：

var query = dbContext.DbValues
    .Where(v => v.NumericValue < 5)
    .OrderBy(v => v.TimeStamp)
    // 标记当前记录是否为新组的起点
    .Select(v => new 
    {
        v.TimeStamp,
        IsNewGroup = EF.Functions.Lag(v.TimeStamp, 1) == null 
            || v.TimeStamp != EF.Functions.Lag(v.TimeStamp, 1).Value.AddSeconds(1)
    })
    // 生成组ID
    .Select((item, index) => new 
    {
        item.TimeStamp,
        GroupId = item.IsNewGroup ? index : index - 1
    })
    // 按组聚合起止时间
    .GroupBy(g => g.GroupId)
    .Select(g => new Period
    {
        StartDate = g.Min(x => x.TimeStamp),
        EndDate = g.Max(x => x.TimeStamp)
    });

// 执行查询，仅加载最终的Period数据
var periods = await query.ToListAsync();

EF Core会根据你使用的数据库提供程序，自动将LINQ查询转换为对应的SQL，确保所有计算在数据库端完成，不会加载所有DbValue到内存。

内容的提问来源于stack exchange，提问作者Patrick