如何在按其他列分組時取得 MySQL 中 DATETIME 列的平均值

Question

我正在使用以下查詢建立並填入 MySQL 表：

SET time_zone = ' 00:00';

CREATE TABLE timestamp_n_datetime (
    id INT,
    time DATETIME,
    avg_time DATETIME
);

INSERT INTO timestamp_n_datetime(id,time,avg_time)
VALUES("1","2023-01-03 10:12:13", NULL),
("1", "2023-02-04 11:13:14", NULL),
("2", "2023-03-02 09:14:10", NULL),
("2", "2023-04-02 05:15:50", NULL),
("1", "2023-06-10 02:04:10", NULL);

我想用 id 分組的「時間」列的平均值填入「avg_time」列。例如，第一行的 'id' = 1，因此 'avg_time' 列應取第一、第二和第五行的平均值，因為它們的 'id' 也是 1。

我該如何回答這個問題？

編輯：為了澄清，我希望將所有 DATETIME 欄位轉換為毫秒，將它們全部相加，然後除以相加的數字。例如，對於 id=2，將它們轉換為毫秒，將它們相加然後除以 2（因為有兩行“id”= 2）給出 2023 年 3 月 17 日下午 5:45:00 的平均值。我想透過這種方式得到平均值。

Answer 1

使用此 UNIX_TIMESTAMP 轉換為秒，然後使用 FROM_UNIXTIME 將其恢復為日期時間格式，這就是獲取平均值的方法：

select id, FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(`time`)))
    from timestamp_n_datetime
    group by id;

這是填滿平均值的方法：

update timestamp_n_datetime  td
inner join (
    select id, FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(`time`))) as average
    from timestamp_n_datetime
    group by id
) as s on s.id = td.id
set avg_time = s.average;