SQL Server存储圈速最优方案咨询：time(3)无法使用Avg()函数

Hey there, I totally get the frustration—using time(3) for lap times feels like the right fit for that 01:52.525 format, but hitting a wall with AVG() is such a buzzkill. Let’s walk through a few practical solutions that let you keep the display format you need and calculate averages without headaches.

1. Store as Numeric Value + Computed Column (Recommended) #

This is the most straightforward and performant approach. The idea is to separate the storage (for calculations) from the display (for your desired format):

• Storage: Use a decimal(9,3) or float column to store the lap time as total seconds. For 01:52.525, that’s 112.525 seconds.
• Display: Add a persisted computed column that converts the total seconds back to the HH:mm:ss.fff format automatically:

ALTER TABLE LapTimes
ADD LapTimeFormatted AS 
    FORMAT(
        DATEADD(SECOND, TotalSeconds, CAST('00:00:00' AS time)),
        'HH:mm:ss.fff'
    ) PERSISTED;

Now you can run AVG(TotalSeconds) to get the average in seconds, and just select LapTimeFormatted whenever you need the human-readable format. It’s the best of both worlds—fast calculations and clean display.

2. Custom CLR Aggregate Function (If You Can’t Change Schema) #

If you’re stuck with the existing time(3) column and can’t alter the table, a custom CLR aggregate function can solve the problem. Here’s the gist:

1. Write a C# function that converts each time value to total seconds, accumulates the sum and count, then converts the average back to a time value.
2. Compile it to a DLL, deploy it to your SQL Server (you’ll need to enable CLR integration first).
3. Create the aggregate function in SQL, e.g.:

CREATE AGGREGATE dbo.AvgTime (@input time(3))
RETURNS time(3)
EXTERNAL NAME YourAssemblyName.AverageTime;

Then you can use it like:

SELECT dbo.AvgTime(LapTime) AS AverageLapTime FROM LapTimes;

Note: This requires server-level permissions to enable CLR, and it’s a bit more maintenance-heavy than the first option.

3. On-the-Fly Conversion (Quick Temporary Fix) #

For one-off queries or small datasets, you can convert the time(3) values to seconds on the fly, compute the average, then convert back:

SELECT 
    FORMAT(
        DATEADD(
            MILLISECOND,
            AVG(DATEDIFF(MILLISECOND, 0, LapTime)),
            0
        ),
        'HH:mm:ss.fff'
    ) AS AverageLapTime
FROM LapTimes;

This works without changing your schema, but it’s not ideal for large tables since the conversion happens every time you run the query, which can hurt performance.

Final Recommendation #

Go with option 1 if you can—it’s the most efficient, maintainable, and aligns with SQL Server’s strengths for aggregation. The other options are great workarounds if you’re locked into your current schema.

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