一次执行多个聚合(SQL、LINQ)。。还是有更好的表现?

WITH CTE AS
    (SELECT COUNT(*) OVER () AS TotalCount,
        CAST(CONVERT(VARCHAR, CreationDate, 101) as DATETIME) as DateValue, *
    FROM MyTable
    WHERE CreationDate >= DATEADD(DD, -90, GETDATE())
    )

SELECT DateValue, TotalCount, COUNT(*) as RowCount
FROM CTE
group by DateValue, TotalCount
order by DateValue
;

拉取记录(或者仅仅是id和创建日期,如果这是您所需要的),然后在代码中执行逻辑。一个 SELECT 反对DB。

编辑

针对评论:

您可以通过以下查询获得每天的项目数:

SELECT CreationDate, COUNT(CreationDate) FROM MyTable GROUP BY CreationDate

CreationDate . 如果你有不同的时间,分组就不起作用了——你必须把时间平分。

您还可以添加 WHERE

把90天内的每日总数带回来,然后在应用程序中进行聚合可能是最好的主意。在SQLServer中,目前还没有特别令人满意的方法来计算运行总数。你如何 能够 sys.objects 作为演示表)

    IF OBJECT_ID('tempdb..#totals') IS NOT NULL
       DROP TABLE #totals

DECLARE @EndDate DATE = CURRENT_TIMESTAMP;
DECLARE @StartDate DATE = DATEADD(DAY,-89,@EndDate);

    WITH DateRange AS
    (
        SELECT
            @StartDate [DATE]
        UNION ALL
        SELECT
            DATEADD(DAY, 1, DATE) [DATE]
        FROM
            DateRange
        WHERE
            DATE < @EndDate
    )

    SELECT DATE,COUNT(t.modify_date) AS DailyTotal
    INTO #totals
    FROM DateRange LEFT JOIN sys.objects t
    ON  modify_date BETWEEN @StartDate AND @EndDate 
           AND CAST(t.modify_date AS DATE) = DateRange.Date
    GROUP BY DATE 
    ORDER BY DATE


DECLARE @BaseNumber INT = (SELECT COUNT(*) FROM sys.objects WHERE 
         modify_date < @StartDate);


    SELECT t1.Date,
           t1.DailyTotal, 
           @BaseNumber + SUM(t2.DailyTotal) AS RunningTotal
    FROM #totals t1 
     JOIN #totals t2 ON t2.date <= t1.date 
              /*Triangular join will yield 91x45 rows that are then grouped*/
    GROUP BY  t1.Date,t1.DailyTotal
    ORDER BY  t1.Date