基于DATE列按每3分钟时间间隔查询WORK_ORDER表数据的SQL需求

由于不同数据库的日期函数语法差异，以下是主流数据库的实现方案：

MySQL/MariaDB #

核心思路：将日期转为Unix时间戳（秒级），除以180（3分钟=180秒）取整后再转回日期，得到每个3分钟间隔的起始时间。

聚合统计（如每个间隔的工单数量） #

SELECT
  FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`DATE`) / 180) * 180) AS interval_start,
  COUNT(WORK_ORDER) AS order_count,
  MAX(other_field) AS max_other_field -- 替换为实际需要聚合的字段
FROM your_table -- 替换为你的表名
GROUP BY interval_start
ORDER BY interval_start;

保留单条记录并标记所属间隔 #

SELECT
  WORK_ORDER,
  `DATE`,
  FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`DATE`) / 180) * 180) AS interval_start,
  other_field
FROM your_table
ORDER BY `DATE`;

PostgreSQL #

可以通过DATE_PART计算分钟数的分组，或者用时间戳转epoch的方式实现。

聚合统计 #

SELECT
  DATE_TRUNC('hour', "DATE") + INTERVAL '3 minutes' * FLOOR(DATE_PART('minute', "DATE") / 3) AS interval_start,
  COUNT(WORK_ORDER) AS order_count,
  MAX(other_field) AS max_other_field
FROM your_table
GROUP BY interval_start
ORDER BY interval_start;

或者更简洁的epoch方式：

SELECT
  TO_TIMESTAMP(FLOOR(EXTRACT(EPOCH FROM "DATE") / 180) * 180) AS interval_start,
  COUNT(WORK_ORDER) AS order_count
FROM your_table
GROUP BY interval_start;

保留单条记录并标记间隔 #

SELECT
  WORK_ORDER,
  "DATE",
  TO_TIMESTAMP(FLOOR(EXTRACT(EPOCH FROM "DATE") / 180) * 180) AS interval_start,
  other_field
FROM your_table
ORDER BY "DATE";

SQL Server #

利用DATEDIFF计算从0时刻到目标日期的分钟数，按3分钟分组后转回日期。

聚合统计 #

SELECT
  DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [DATE]) / 3 * 3, 0) AS interval_start,
  COUNT(WORK_ORDER) AS order_count,
  MAX(other_field) AS max_other_field
FROM your_table
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [DATE]) / 3 * 3, 0)
ORDER BY interval_start;

每个间隔取单条代表记录（如最早的工单） #

如果不需要聚合，而是要每个3分钟间隔保留一条记录，可用窗口函数：

WITH ranked_data AS (
  SELECT
    WORK_ORDER,
    [DATE],
    other_field,
    ROW_NUMBER() OVER (
      PARTITION BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [DATE]) / 3 * 3, 0) 
      ORDER BY [DATE]
    ) AS rn
  FROM your_table
)
SELECT WORK_ORDER, [DATE], other_field
FROM ranked_data
WHERE rn = 1; -- 改为rn = (SELECT MAX(rn) FROM ranked_data)可获取每个间隔的最后一条记录

内容的提问来源于stack exchange，提问作者Shivanshu Verma