Oracle SQL向上舍入时间间隔到第二天

总和的结果以秒为单位，因此您根本不需要将其转换为间隔。只需除以60 * 60 * 24，就可以在几天内得到答案，然后将其舍入为ceil()：

select apppackage
       , ceil(sum( trunc(extract (day from (periods)) * 86400
            + extract (hour from (periods)) *3600
            + extract (minute from (periods))*60
            + extract (second from (periods)))
            ) / 86400) as retention_period
      , count(apppackage) as users
from retentions 
where apppackage = 'com.Freesoul.Rotter' 
group by apppackage;

在两种情况下，在CTE中使用人工数据进行演示只是为了模仿您的预期结果：

-- CTE for sample data
with retentions (apppackage, periods) as (
  select 'com.Freesoul.Rotter', interval '+2967 04:32:47.000000' day(9) to second(6) from dual
  union all
  select 'com.Freesoul.Rotter', interval '1' day from dual
  union all
  select 'com.Freesoul.Rotter', interval '1' day from dual
  union all
  select 'com.Freesoul.XYZ', interval '+2967 00:00:00.000000' day(9) to second(6) from dual
  union all
  select 'com.Freesoul.XYZ', interval '1' day from dual
  union all
  select 'com.Freesoul.XYZ', interval '1' day from dual
)
-- actual query
select apppackage
       , ceil(sum( trunc(extract (day from (periods)) * 86400
            + extract (hour from (periods)) *3600
            + extract (minute from (periods))*60
            + extract (second from (periods)))
            ) / 86400) as retention_period
      , count(apppackage) as users
from retentions 
where apppackage = 'com.Freesoul.Rotter' 
-- extra clause for dummy data
or apppackage = 'com.Freesoul.XYZ'
group by apppackage;

APPPACKAGE          RETENTION_PERIOD      USERS
------------------- ---------------- ----------
com.Freesoul.XYZ                2969          3
com.Freesoul.Rotter             2970          3

您的预期输出将显示一个纯数字。如果您实际上希望将其作为一个间隔，但作为整个天数，只需将ceil'd数传递给numtodsinterval或更简单（通常出于某种原因通常更快）乘以interval '1' day。

使用相同的伪数据：

select apppackage
       , ceil(sum( trunc(extract (day from (periods)) * 86400
            + extract (hour from (periods)) *3600
            + extract (minute from (periods))*60
            + extract (second from (periods)))
            ) / 86400) * interval '1' day as retention_period
      , count(apppackage) as users
...

APPPACKAGE          RETENTION_PERIOD           USERS
------------------- --------------------- ----------
com.Freesoul.XYZ    +2969 00:00:00.000000          3
com.Freesoul.Rotter +2970 00:00:00.000000          3

正如@mathguy指出的那样，您可能不需要或不需要trunc()那里的电话。即从每个周期中减去小数秒，然后再将它们相加，这听起来微不足道，但很容易影响您获得的结果。