PostgreSql根据给的时间范围统计15分钟粒度、小时粒度、天粒度、周粒度、月粒度工单

在PostgreSQL中，你可以使用以下SQL查询来统计给定时间范围内的15分钟粒度、小时粒度、天粒度、周粒度和月粒度的数据。这些查询假设你有一个表events，它有一个timestamp类型的字段event_time。

-- 设置时间范围
SET @start_time = '2023-01-01 00:00:00';
SET @end_time = '2023-01-31 23:59:59';
 
-- 15分钟粒度统计
SELECT
  date_trunc('hour', event_time) as hour,
  date_trunc('hour', event_time) + INTERVAL '15 minutes' as quarter_hour,
  COUNT(*) as event_count
FROM
  events
WHERE
  event_time >= @start_time
  AND event_time < @end_time
GROUP BY
  hour,
  quarter_hour
ORDER BY
  hour,
  quarter_hour;
 
-- 小时粒度统计
SELECT
  date_trunc('day', event_time) as day,
  date_trunc('hour', event_time) as hour,
  COUNT(*) as event_count
FROM
  events
WHERE
  event_time >= @start_time
  AND event_time < @end_time
GROUP BY
  day,
  hour
ORDER BY
  day,
  hour;
 
-- 天粒度统计
SELECT
  date_trunc('week', event_time) as week,
  date_trunc('day', event_time) as day,
  COUNT(*) as event_count
FROM
  events
WHERE
  event_time >= @start_time
  AND event_time < @end_time
GROUP BY
  week,
  day
ORDER BY
  week,
  day;
 
-- 周粒度统计
SELECT
  date_trunc('month', event_time) as month,
  date_trunc('week', event_time) as week,
  COUNT(*) as event_count
FROM
  events
WHERE
  event_time >= @start_time
  AND event_time < @end_time
GROUP BY
  month,
  week
ORDER BY
  month,
  week;
 
-- 月粒度统计
SELECT
  date_trunc('year', event_time) as year,
  date_trunc('month', event_time) as month,
  COUNT(*) as event_count
FROM
  events
WHERE
  event_time >= @start_time
  AND event_time < @end_time
GROUP BY
  year,
  month
ORDER BY
  year,
  month;

请确保将@start_time和@end_time设置为你想要分析的时间范围，并将events替换为你的实际表名以及event_time替换为你的时间戳字段。这些查询使用了date_trunc函数来获取时间的年、月、日、小时、周的起始时间，并使用COUNT聚合函数来统计每个时间段内的事件数量。