SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TemporalData
(
  ID INT IDENTITY PRIMARY KEY CLUSTERED,
  Value INT,
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
  SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
  PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalData_History));
INSERT INTO TemporalData (Value)
VALUES (100)
1 rows affected

WAITFOR DELAY '00:00:01'
UPDATE TemporalData SET Value = 225 WHERE ID = 1
WAITFOR DELAY '00:00:01'
UPDATE TemporalData SET Value = 117 WHERE ID = 1
WAITFOR DELAY '00:00:01'
UPDATE TemporalData SET Value = 301 WHERE ID = 1
3 rows affected

SELECT ID, Second, AVG(Value) AS AvgValue
FROM
(
  SELECT ID, Value, DATEPART(SS, SysStartTime) AS Second
  FROM TemporalData
  FOR SYSTEM_TIME ALL
) s
GROUP BY ID, Second


ID
Second
AvgValue


1
11
100

1
12
225

1
13
117

1
14
301