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