Postgresql时间处理技巧，每半天，每周，每月和每5分钟统计

-- 创建一个新的表来存储统计数据
CREATE TABLE IF NOT EXISTS stats_half_hourly (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP NOT NULL,
    event_count INTEGER NOT NULL
);
 
-- 创建一个新的表来存储统计数据
CREATE TABLE IF NOT EXISTS stats_daily (
    id SERIAL PRIMARY KEY,
    event_date DATE NOT NULL,
    event_count INTEGER NOT NULL
);
 
-- 创建一个新的表来存储统计数据
CREATE TABLE IF NOT EXISTS stats_monthly (
    id SERIAL PRIMARY KEY,
    event_year_month VARCHAR(7) NOT NULL,
    event_count INTEGER NOT NULL
);
 
-- 创建一个新的表来存储统计数据
CREATE TABLE IF NOT EXISTS stats_five_minutes (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP NOT NULL,
    event_count INTEGER NOT NULL
);
 
-- 创建一个新的表来存储统计数据
CREATE TABLE IF NOT EXISTS stats_weekly (
    id SERIAL PRIMARY KEY,
    event_week DATE NOT NULL,
    event_count INTEGER NOT NULL
);
 
-- 插入样本数据到事件表
INSERT INTO events (event_time) VALUES
('2021-01-01 00:00:00'),
('2021-01-01 00:30:00'),
('2021-01-01 01:00:00'),
-- ... 更多数据
('2021-01-02 00:00:00');
 
-- 每半天统计事件数量
INSERT INTO stats_half_hourly (event_time, event_count)
SELECT 
    date_trunc('hour', event_time) + INTERVAL '30 minutes' as event_time,
    COUNT(*) as event_count
FROM 
    events
GROUP BY 
    date_trunc('hour', event_time) + INTERVAL '30 minutes';
 
-- 每周统计事件数量
INSERT INTO stats_weekly (event_week, event_count)
SELECT 
    date_trunc('week', event_time) as event_week,
    COUNT(*) as event_count
FROM 
    events
GROUP BY 
    date_trunc('week', event_time);
 
-- 每月统计事件数量
INSERT INTO stats_monthly (event_year_month, event_count)
SELECT 
    TO_CHAR(date_trunc('month', event_time), 'YYYY-MM') as event_year_month,
    COUNT(*) as event_count
FROM 
    events
GROUP BY 
    TO_CHAR(date_trunc('month', event_time), 'YYYY-MM');
 
-- 每5分钟统计事件数量
INSERT INTO stats_five_minutes (event_time, event_count)
SELECT 
    date_trunc('hour', event_time) + (floor(date_part('minute', event_time) / 5) * INTERVAL '5 minutes') as event_time,
    COUNT(*) as event_count
FROM 
    events
GROUP BY 
    date_trunc('hour', event_time) + (floor(date_part('minute', event_time) / 5) * INTERVAL '5 minutes');
 
-- 每日统计事件数量
INSERT INTO stats_daily (event_date, event_count)
SELECT 
    date_trunc('day', event_time) as event_date,
    COUNT(*) as event_count
FROM 
    events
GROUP BY 
    date_trunc('day', event_time);

这段代码首先创建了几个新的表来存储各种时间粒度的统计数据。然后，它插入样本数据到一个假设的事件表中。接下来，它对这些事件进行分组并计数，以便生成每