关联同表两列取min、max值计算时长，按月查询结果错误如何解决

问题原因 #

• 你使用了无关联条件的自连接（隐式笛卡尔积）：两张表别名s1、s2没有加关联匹配条件（比如s1.name = s2.name），导致所有符合过滤条件的s1记录和所有符合过滤条件的s2记录全量交叉匹配。
  单日查询时符合条件的s1、s2记录都只有对应单条，所以刚好能得到正确结果；按月查询时s2符合条件的记录有多条，max(s2.end_time)会取整个月所有process='xyz'记录的最大结束时间，和当前行的s1.name没有对应关系，这也是你返回结果中所有行的最大结束时间都为27-aug-2021 06:16:30 AM的原因。
• 你的SQL存在语法疏漏：s2.process in ('xyz' and month like '%08021%')的写法错误，in子句没有闭合，应该拆分为s2.process in ('xyz') and month like '%08021%'。

解决方案 #

推荐使用条件聚合替代自连接，写法更简洁也不会出现笛卡尔积问题，无需关联两次表：

select 
  name,
  min(case when process = 'abcd' then start_time end) as min_start_time,
  max(case when process = 'xyz' then end_time end) as max_end_time,
  round((max(case when process = 'xyz' then end_time end) - min(case when process = 'abcd' then start_time end)) * 24) as diff_hours
from My_table 
where month like '%08021%'
  and process in ('abcd','xyz')
group by name;

如果一定要用自连接的写法，需要补充两个表的关联条件，确保同名称的流程互相匹配：

select 
  s1.name,
  min(s1.start_time),
  max(s2.end_time),
  round(((max(s2.end_time) - min(s1.start_time)))*24)
from My_table s1
join My_table s2 on s1.name = s2.name -- 补充同名称流程的关联条件
where s1.process = 'abcd' 
  and s1.month like '%08021%'
  and s2.process = 'xyz' 
  and s2.month like '%08021%'
group by s1.name;

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