代码之家 › 专栏 › 技术社区 › regex

优化查询以生成平均值

tsql sql-server sql

regex · 技术社区 · 16 年前

该表由[CreateDate]列设置,该列是付款过账到帐户时的日期时间值。我用这个来确定他们在哪一个小时付款。

CREATE TABLE #TempTimes
(
    [Time] DATETIME,
)

DECLARE @numDays INT
SET @numDays = 10
DECLARE @time DATETIME
SET @time = DATEADD(dd, DATEDIFF(dd, 0, GETDATE() - @numDays), 0)

WHILE @time < GETDATE()
BEGIN
    INSERT #TempTimes
    VALUES (@time)

    SET @time = DATEADD(hour, 1, @time)
END
GO
/*
I have to join in a table with all of the hours for the time span I'm querying against, 
because otherwise, the AVG function won't calculate in the hours where no payments were made. 
*/

SELECT DATEPART(hour, [Time]) [Hour], AVG(CAST([Count] AS DECIMAL)) [Average]
FROM 
(
    SELECT [Time], CASE WHEN [Count] IS NULL THEN 0 ELSE [Count] END [Count]
    FROM #TempTimes tt
    LEFT JOIN 
    (
        SELECT DATEADD(hour, DATEDIFF(hour, 0, [CreateDate]), 0) [hour], COUNT(*) [Count]
        FROM [dbo].[PaymentLog]
        GROUP BY DATEADD(hour, DATEDIFF(hour, 0, [CreateDate]), 0)
    ) t1 ON t1.[hour] = tt.[time]
) t2
GROUP BY DATEPART(hour, tt.[Time])
GO

DROP TABLE #TempTimes
GO

希杰

2 回复 | 直到 16 年前

BCS 16 年前

IIUC使用与临时表的联接从主表进行筛选。

为什么不放弃连接,只使用where子句呢?如果你想强迫所有的时间都出现,就加入吧其他的工作或使用某种形式的工会。

Tom H zenazn 16 年前

CREATE TABLE dbo.Payments
(
    payment_id  INT IDENTITY    NOT NULL,
    create_date DATETIME        NOT NULL,
    CONSTRAINT PK_Payments PRIMARY KEY CLUSTERED (payment_id)
)
GO

INSERT INTO dbo.Payments (create_date)
SELECT '2009-02-25 12:00:00.000' UNION
SELECT '2009-02-25 12:45:00.000' UNION
SELECT '2009-02-25 12:30:00.000' UNION
SELECT '2009-02-25 13:10:00.000' UNION
SELECT '2009-02-25 13:22:00.000' UNION
SELECT '2009-02-25 14:09:00.000' UNION
SELECT '2009-02-25 14:40:00.000'
GO

SELECT
    CAST(CONVERT(VARCHAR(13), T1.create_date, 121) + ':00:00.000' AS DATETIME),
    COUNT(T3.payment_id) + 1
FROM
    dbo.Payments T1
LEFT OUTER JOIN dbo.Payments T2 ON
    T2.create_date >= CAST(CONVERT(VARCHAR(13), T1.create_date, 121) + ':00:00.000' AS DATETIME) AND
    T2.create_date <  T1.create_date
LEFT OUTER JOIN dbo.Payments T3 ON
    T3.create_date >= T1.create_date AND
    T3.create_date <  DATEADD(hh, 1, CAST(CONVERT(VARCHAR(13), T1.create_date, 121) + ':00:00.000' AS DATETIME)) AND
    T3.payment_id <> T1.payment_id
WHERE
    T2.payment_id IS NULL
GROUP BY
    CAST(CONVERT(VARCHAR(13), T1.create_date, 121) + ':00:00.000' AS DATETIME)
GO

推荐文章

K.Z · 无法使用ADO获取Insert命令的记录Id。NET 4.0中的NET

2 年前

Duvan · 将逗号(,)替换为点(.)[副本]

2 年前

Yara1994 · 如果另一列具有特定值-SQL Server,则删除具有唯一Id的所有行

2 年前

Simon Worth · 是否可以使用SqlClient自动获取查询字符串中定义的参数列表

2 年前

Mateen Bagheri · 选择表的计数并选择其自身

2 年前

SoT · SQL Server中求和函数的工作方式

2 年前

rapha123 · 如何使用REPLACE函数使用SQL更改这两个单词?

2 年前

NKAT · 将列值聚合到列表中会产生错误

2 年前

deanpillow · 返回两列中有一个匹配值的记录

2 年前

snowflakes74 · 在Dapper中异步查询多个结果

2 年前