关于Oracle的Group by于Date列控制精度的方法

场景：

需要通过一个分组的列来对一张/多张表的查询结果进行分组，其中一个Date列可以用于组合分组，且理论上和单个分组列分组没有差别。为了硬凑一个列的结果，所以将该Date列也放入Group by 的列中。然后在测试环境中没有问题…

问题：

一天后问题出现，测试环境的查询结果中本该是一组的结果被显示为不同的两组结果（单组的ID却一样）

派擦之后发现问题出现在SQL里的Group by 中。

出现原因:

那条SQL：

select    t.SIGN_BATCH_ID,t.CREATE_EMP,t1.CREATE_TIME, --问题在于这里，理论上一个组ID对应的副表记录的create_time是一致的，但由于这里的主副表是一对多的关系，副表批量插入时，创建时间是默认精确到秒		   --的，但由于处理所用时间的问题，导致有一条副表的记录的create_time在本该一致的create_time的下一秒,直接造成了同一组因为不同的秒数被分为两组COUNT(t.SIGN_BATCH_ID) 
from TB_CM_STANDARDCOSTS t left join R_APPLY_ORDER t1 on t.SIGN_ROUTE = t1.APPLY_NO where t.SIGN_BATCH_ID is not null group by t.CREATE_EMP,t1.CREATE_TIME,t.SIGN_BATCH_ID   

出问题的查询结果：

SBS202303011030080 G4441337 2023-03-01 10:30:09 6
SBS202303011030080 G4441337 2023-03-01 10:30:08 1

从结果看很明显，副表的记录create-time的一秒间隔造成了分组错误（程序执行的间隔）

正确的结果应该是

SBS202303011030080 G4441337 2023-03-01 10:30:00 7

解决方法：

亡羊补牢式🐛:降低Group by于Date类型的精度

使用TRUNC函数来指定Date类型的精度

​ Trunc(date,‘精度’)

精度的可选参数如下表

• ‘YYYY’：按年份截断
• ‘YYY’：按年份截断，不考虑世纪
• ‘YY’：按年份截断，只保留后两位
• ‘Y’：按年份截断，只保留后一位
• ‘Q’：按季度截断
• ‘MM’：按月份截断
• ‘W’：按周截断
• ‘D’：按天截断
• ‘HH’：按小时截断
• ‘MI’：按分钟截断
• ‘SS’：按秒截断

使用Trunc后的效果：

select t.SIGN_BATCH_ID,t.CREATE_EMP,TRUNC(t1.CREATE_TIME,'MI'),COUNT(t.SIGN_BATCH_ID) 
from TB_CM_STANDARDCOSTS t left join R_APPLY_ORDER t1 on t.SIGN_ROUTE = t1.APPLY_NO where t.SIGN_BATCH_ID is not null group by t.CREATE_EMP,
TRUNC(t1.CREATE_TIME,'MI'),t.SIGN_BATCH_ID;

SBS202303011030080 G4441337 2023-03-01 10:30:00 7

PS：这样并不能彻底的解决问题，因为解决了跨秒造成的时间分组错误，但是无法解决跨分钟的，如此还可以向下推（虽然可能性极小）

彻底解决式🌀:不要用Date来分组Group by

不要用Date来Group by,如果实在需要拿一个date列的话直接用MAX或者其他聚合函数/开窗函数随便捞一个出来就可以了

例：

select t.SIGN_BATCH_ID,t.CREATE_EMP,max(t1.CREATE_TIME), --直接不用Date分组，然后用聚合函数选一个Date就行了COUNT(t.SIGN_BATCH_ID) 
from TB_CM_STANDARDCOSTS t left join R_APPLY_ORDER t1 on t.SIGN_ROUTE = t1.APPLY_NO where t.SIGN_BATCH_ID is not null group by t.CREATE_EMP,t.SIGN_BATCH_ID

结果：

SBS202303011030080 G4441337 2023-03-01 10:30:09 7