如果在session\u id字段上使用group by，则需要在select query中的其他字段上使用聚合函数（如sum、min、max等）。
我认为session_id和session_date对于记录是相同的，所以我在group by中使用这两个字段（或者）如果您不想在group by中使用session_date，那么您需要在这个字段上使用任何聚合函数，比如max（session_date）…等等。
对参与者\u duration使用sum aggregate函数，然后在having子句中使用partition \u duration只过滤出值大于5的记录。
select语句中只剩下email字段，它不在groupby子句中，所以我使用max aggregate函数只获取email字段的一个值。
分组依据中的会话\u日期clause:-

select 
U.session_id,
U.session_date,
sum(U.participant_duration) participant_duration,
max(U.email) email
from data.usage U
left outer join
  (select 
  distinct M.session_id
  from data.usage M
  where email like '%gmail.com%'
  and data_date >= '20180101'
  and name in
    ( 
    select 
    lower(name)
    from data.users
    where role like 'Person%' 
    and isactive = TRUE
    and data_date = '20180412'
    ))M
on U.session_id = M.session_id
group by U.session_id,U.session_date
having sum(cast(participant_duration >= 5 as int)) >= 1;

（或）
会话\u日期不在分组依据中clause:-

select 
U.session_id,
max(U.session_date) session_date,
sum(U.participant_duration) participant_duration,
max(U.email) email
from data.usage U
left outer join
  (select 
  distinct M.session_id
  from data.usage M
  where email like '%gmail.com%'
  and data_date >= '20180101'
  and name in
    ( 
    select 
    lower(name)
    from data.users
    where role like 'Person%' 
    and isactive = TRUE
    and data_date = '20180412'
    ))M
on U.session_id = M.session_id
group by U.session_id
having sum(cast(participant_duration >= 5 as int)) >= 1;

是的，你的想法是对的 group by 以及 having .

group by session_id
having sum(cast(participant_duration >= 5 as int)) >= 1

您的查询也可以简化为

select *
from (select U.session_id,U.session_date,U.participant_duration,U.email,
      SUM(cast(U.participant_duration >= 5 as int)) OVER(PARTITION BY U.session_id) as dur_gt_5
      from data.usage U
      join data.users M on U.session_id = M.session_id and U.name=lower(M.name)
      where M.role like 'Person%' and M.isactive = TRUE and M.data_date = '20180412'
      and U.email like '%gmail.com%' and U.data_date >= '20180101'
     ) t
where dur_gt_5>=1