SQL如何使用自定义创建的计算列作为GROUP BY分组依据

需求目标 #

开发工时统计视图，实现效果为：

• 每个日期+每位员工对应单独一行数据
• 行内展示该员工当日最早、最晚的活动记录时间

原有写法的问题 #

之前尝试用DATEFROMPARTS提取日期值，配合GROUP BY过滤重复的日期+员工组合时未达到预期，核心问题点如下：

• SQL执行顺序中GROUP BY阶段早于SELECT阶段，因此无法直接引用SELECT中定义的计算列别名，要么在GROUP BY中重复写计算逻辑，要么通过子查询/CTE提前封装计算字段
• 把精确到时分秒的原始时间字段dt放入GROUP BY条件，会导致每条原始记录单独成组，完全无法实现按「日期+员工」合并的效果
• 用Datepart(dy, dt)做分区存在逻辑bug：dy返回的是日期在当年的第几天，跨年时不同年份的相同天数值会被错误分到同一组
• 窗口函数会为每条原始记录返回计算值，本身不会自动合并重复分组

测试表结构 #

CREATE TABLE employees(
  id int not null,
  name varchar(25) not null,
  dt DateTime not null,
  action varchar(10) not null,
  primary key(id));
  
INSERT INTO employees
VALUES
('1', 'a', '2022-06-13 17:00:00', 'out'),
('2', 'a', '2022-06-13 12:30:00', 'in'),
('3', 'a', '2022-06-13 12:00:00', 'out'),
('4', 'a', '2022-06-13 9:00:00', 'in'),
('5', 'b', '2022-06-13 19:00:00', 'out'),
('6', 'b', '2022-06-13 9:00:00', 'in'),
('7', 'b', '2022-06-14 18:00:00', 'out'),
('8', 'b', '2022-06-14 8:00:00', 'in'),
('9', 'noname', '2022-06-14 0:00:00', 'in');

正确实现SQL #

不需要混用窗口函数，直接在GROUP BY中写入日期计算逻辑，配合聚合函数取值即可，代码简洁且性能更好：

SELECT
  CAST(dt AS DATE) AS work_date,
  name,
  MIN(dt) AS first_record,
  MAX(dt) AS last_record,
  CONCAT(CAST(dt AS DATE), name) AS unique_group_key
FROM employees
WHERE name <> 'noname'
GROUP BY CAST(dt AS DATE), name
ORDER BY work_date ASC, name ASC

写法说明 #

• 用CAST(dt AS DATE)直接将datetime类型转为日期类型截断时间部分，替代冗长的DATEFROMPARTS(year(dt), month(dt), day(dt))写法，效果完全一致
• 直接在GROUP BY后写入日期转换逻辑+姓名字段，从根本上保证每个「日期+员工」组合只返回一行，不需要额外用HAVING计数做去重
• 直接用MIN/MAX聚合函数取当日最早、最晚记录时间，避免窗口函数产生的重复行问题

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