如何基于指定日期将15分钟粒度数据转为小时级产量统计

需求说明 #

现有15分钟粒度的生产数据，需针对指定日期统计当日每小时的总生产零件数，计算规则为：

每小时生产零件数 = 该时段内最后一条数据的TotalPartsProduced - 该时段内第一条数据的TotalPartsProduced

示例：10AM-11AM时段计算为 44086 - 42793 = 1293

原始数据示例 #

DateTime                     TotalPartsProduced
--------------------------------------------------
2023-04-28 10:11:48.860        42793
2023-04-28 10:26:48.737        43259
2023-04-28 10:41:48.667        43674
2023-04-28 10:56:48.427        44086

2023-04-28 11:11:49.043        44501
2023-04-28 11:26:48.787        44501
2023-04-28 11:41:49.020        44548

2023-04-28 17:02:23.363        50376
2023-04-28 17:17:23.557        50867
2023-04-28 17:32:23.690        50995
2023-04-28 17:47:23.613        50995

预期结果 #

TIME              TotalPartsProduced/Hour
-----------------------------------------------
10AM-11AM                 1293
11AM-12PM                 47

SQL实现方案 #

核心思路 #

1. 筛选指定日期数据：通过WHERE条件过滤出目标日期的所有记录
2. 按小时分组：将DateTime截断到小时级别，作为分组依据
3. 获取时段首尾值：对每个小时组，提取该时段内第一条和最后一条数据的TotalPartsProduced
4. 计算小时生产数：用时段结束值减去起始值得到结果

不同数据库的SQL代码 #

MySQL

SELECT
  CONCAT(
    DATE_FORMAT(hour_start, '%I%p'),
    '-',
    DATE_FORMAT(DATE_ADD(hour_start, INTERVAL 1 HOUR), '%I%p')
  ) AS `TIME`,
  (max_parts - min_parts) AS `TotalPartsProduced/Hour`
FROM (
  SELECT
    DATE_FORMAT(DateTime, '%Y-%m-%d %H:00:00') AS hour_start,
    FIRST_VALUE(TotalPartsProduced) OVER (PARTITION BY DATE_FORMAT(DateTime, '%Y-%m-%d %H:00:00') ORDER BY DateTime) AS min_parts,
    LAST_VALUE(TotalPartsProduced) OVER (PARTITION BY DATE_FORMAT(DateTime, '%Y-%m-%d %H:00:00') ORDER BY DateTime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS max_parts
  FROM production_data
  WHERE DATE(DateTime) = '2023-04-28' -- 指定日期
) AS hourly_data
GROUP BY hour_start
HAVING (max_parts - min_parts) > 0 -- 过滤无生产的时段
ORDER BY hour_start;

SQL Server

SELECT
  CONCAT(
    FORMAT(hour_start, 'hhtt'),
    '-',
    FORMAT(DATEADD(HOUR, 1, hour_start), 'hhtt')
  ) AS [TIME],
  (max_parts - min_parts) AS [TotalPartsProduced/Hour]
FROM (
  SELECT
    DATEADD(HOUR, DATEDIFF(HOUR, 0, DateTime), 0) AS hour_start,
    FIRST_VALUE(TotalPartsProduced) OVER (PARTITION BY DATEADD(HOUR, DATEDIFF(HOUR, 0, DateTime), 0) ORDER BY DateTime) AS min_parts,
    LAST_VALUE(TotalPartsProduced) OVER (PARTITION BY DATEADD(HOUR, DATEDIFF(HOUR, 0, DateTime), 0) ORDER BY DateTime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS max_parts
  FROM production_data
  WHERE CONVERT(DATE, DateTime) = '2023-04-28' -- 指定日期
) AS hourly_data
GROUP BY hour_start, max_parts, min_parts
HAVING (max_parts - min_parts) > 0 -- 过滤无生产的时段
ORDER BY hour_start;

PostgreSQL

SELECT
  TO_CHAR(hour_start, 'HH12AM') || '-' || TO_CHAR(hour_start + INTERVAL '1 hour', 'HH12AM') AS "TIME",
  (max_parts - min_parts) AS "TotalPartsProduced/Hour"
FROM (
  SELECT
    DATE_TRUNC('hour', "DateTime") AS hour_start,
    FIRST_VALUE("TotalPartsProduced") OVER (PARTITION BY DATE_TRUNC('hour', "DateTime") ORDER BY "DateTime") AS min_parts,
    LAST_VALUE("TotalPartsProduced") OVER (PARTITION BY DATE_TRUNC('hour', "DateTime") ORDER BY "DateTime" ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS max_parts
  FROM production_data
  WHERE DATE("DateTime") = '2023-04-28' -- 指定日期
) AS hourly_data
GROUP BY hour_start, max_parts, min_parts
HAVING (max_parts - min_parts) > 0 -- 过滤无生产的时段
ORDER BY hour_start;

关键说明 #

• 指定日期的WHERE条件：根据不同数据库的日期函数，用DATE(DateTime)、CONVERT(DATE, DateTime)或DATE("DateTime")匹配目标日期，确保只处理当天的数据
• 时段格式化：通过日期函数将小时区间格式化为10AM-11AM这类可读性格式
• 过滤无生产时段：用HAVING子句排除生产数为0的时段，避免无效记录

内容的提问来源于stack exchange，提问作者kavya shree