PostgreSQL 在一个查询中获取事件发生次数的每日、每周和每月平均值

PostgreSQL 在一个查询中获取事件发生次数的每日、每周和每月平均值

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL
);

SELECT AVG(event_count) AS daily_avg
FROM (
    SELECT date_trunc('day', "timestamp") AS event_date, COUNT(*) AS event_count
    FROM events
    GROUP BY event_date
) AS subquery;

SELECT AVG(event_count) AS weekly_avg
FROM (
    SELECT date_trunc('week', "timestamp") AS event_week, COUNT(*) AS event_count
    FROM events
    GROUP BY event_week
) AS subquery;

SELECT AVG(event_count) AS monthly_avg
FROM (
    SELECT date_trunc('month', "timestamp") AS event_month, COUNT(*) AS event_count
    FROM events
    GROUP BY event_month
) AS subquery;