.net - sql按周统计 - sql时间分组



LINQ to SQL-按小时分组 (1)

考虑到列类型是DateTime,如何将LINQ to SQL查询的结果按小时分组?


这是一个技术小时解决方案(无背景)。

var query = myDC.Orders
    .GroupBy(x => x.OrderDate.Hour)
    .Select(g => new {
        Hour = g.Key,
        Amount = g.Sum(x => x.OrderAmount)
    });

其中产生这个:

SELECT SUM([t1].[OrderAmount]) AS [Amount], [t1].[value] AS [Hour]
FROM (
    SELECT DATEPART(Hour, [t0].[OrderDate]) AS [value], [t0].[OrderAmount]
    FROM [dbo].[Orders] AS [t0]
    ) AS [t1]
GROUP BY [t1].[value]

这是一个营业时间的解决方案(context-ful)。

DateTime zeroDate = new DateTime(2008, 1, 1);

var query = myDC.Orders
    .GroupBy(x => System.Data.Linq.SqlClient.SqlMethods.DateDiffHour(zeroDate, x.OrderDate)
    )
    .Select(g => new { Hours = g.Key, Amount = g.Sum(x => x.OrderAmount) })
    .Select(x => new { Hour = zeroDate.AddHours(x.Hours), Amount = x.Amount});

其中产生这个:

SELECT DATEADD(ms, (CONVERT(BigInt,(CONVERT(Float,[t2].[value2])) * 3600000)) % 86400000, DATEADD(day, (CONVERT(BigInt,(CONVERT(Float,[t2].[value2])) * 3600000)) / 86400000, @p1)) AS [Hour], [t2].[value] AS [Amount]
FROM (
    SELECT SUM([t1].[OrderAmount]) AS [value], [t1].[value] AS [value2]
    FROM (
        SELECT DATEDIFF(Hour, @p0, [t0].[OrderDate]) AS [value], [t0].[OrderAmount]
        FROM [dbo].[Orders] AS [t0]
        ) AS [t1]
    GROUP BY [t1].[value]
    ) AS [t2]

呃 - 那个bigint / float /毫秒的东西是丑陋的,很难验证。 我更喜欢在客户端重新加入:

var results = myDC.Orders
    .GroupBy(x => System.Data.Linq.SqlClient.SqlMethods.DateDiffHour(zeroDate, x.OrderDate)
    )
    .Select(g => new { Hours = g.Key, Amount = g.Sum(x => x.OrderAmount) })
    .ToList()
    .Select(x => new { Hour = zeroDate.AddHours(x.Hours), Amount = x.Amount});

其中产生这个:

SELECT SUM([t1].[OrderAmount]) AS [Amount], [t1].[value] AS [Hours]
FROM (
    SELECT DATEDIFF(Hour, @p0, [t0].[OrderDate]) AS [value], [t0].[OrderAmount]
    FROM [dbo].[Orders] AS [t0]
    ) AS [t1]
GROUP BY [t1].[value]

这是进行上下文时间的第三种方法。 这个是非常友好的c#,但是在数据库中有字符串逻辑(yuck)。

var query = myDC.Orders
    .GroupBy(x => new DateTime(x.OrderDate.Year, x.OrderDate.Month, x.OrderDate.Day, x.OrderDate.Hour, 0, 0))
    .Select(g => new { Hour = g.Key, Amount = g.Sum(x => x.OrderAmount) });

其中产生

SELECT SUM([t1].[OrderAmount]) AS [Amount], [t1].[value] AS [Hour]
FROM (
    SELECT CONVERT(DATETIME, (CONVERT(NCHAR(4), DATEPART(Year, [t0].[OrderDate])) + ('-' + (CONVERT(NCHAR(2), DATEPART(Month, [t0].[OrderDate])) + ('-' + CONVERT(NCHAR(2), DATEPART(Day, [t0].[OrderDate])))))) + (' ' + (CONVERT(NCHAR(2), DATEPART(Hour, [t0].[OrderDate])) + (':' + (CONVERT(NCHAR(2), @p0) + (':' + CONVERT(NCHAR(2), @p1)))))), 120) AS [value], [t0].[OrderAmount]
    FROM [dbo].[Orders] AS [t0]
    ) AS [t1]
GROUP BY [t1].[value]




linq-to-sql