Redshift中Group By场景下max()与min()函数返回结果异常求助

你的问题出在**trndte、begdte、enddte三个字段的日期不一定一致**。WHERE条件仅过滤了trndte在目标区间内的记录，但这些记录对应的begdte可能早于activity_date当天，enddte可能晚于当天，分组后min()/max()自然会超出activity_date的范围。

根据你的业务需求，可选择以下两种方案：

方案1：只统计完全发生在当天的交易 #

如果业务逻辑要求仅计算begdte和enddte都在activity_date当天内的记录，需在过滤条件中新增日期匹配规则：

select
  cast(trndte as date) as activity_date,
  usr_id,
  user_name,
  prt_client_id,
  actcod,
  min(begdte) as start_time,
  max(enddte) as end_time,
  (max(enddte) - min(begdte)) as active_hrs,
  count(distinct(prtnum)) as SKUs,
  (count(frstol) + count(tostol)) as locations,
  sum(TRNQTY) as Quantity
from by.by_bi_daily_tran 
where cast(trndte as date) between '2024-04-01' and '2024-04-22'
  and prt_client_id = 'MUJI'
  and actcod = 'TRLR_LOAD'
  -- 新增：确保begdte和enddte都属于trndte当天
  and cast(begdte as date) = cast(trndte as date)
  and cast(enddte as date) = cast(trndte as date)
group by cast(trndte as date), usr_id, user_name, prt_client_id, actcod;

方案2：保留跨天记录，仅统计当天内的时长片段 #

如果业务允许跨天交易，但希望只计算用户在activity_date当天内的活跃时长（比如begdte早于当天则取当天0点，enddte晚于当天则取当天23:59:59），可调整min()/max()的取值逻辑：

select
  cast(trndte as date) as activity_date,
  usr_id,
  user_name,
  prt_client_id,
  actcod,
  -- 取begdte与当天0点的较大值
  greatest(min(begdte), cast(trndte as date)) as start_time,
  -- 取enddte与当天23:59:59的较小值
  least(max(enddte), cast(trndte as date) + interval '1 day' - interval '1 second') as end_time,
  -- 计算当天内的有效时长，避免出现负数
  greatest(0, least(max(enddte), cast(trndte as date) + interval '1 day' - interval '1 second') - greatest(min(begdte), cast(trndte as date))) as active_hrs,
  count(distinct(prtnum)) as SKUs,
  (count(frstol) + count(tostol)) as locations,
  sum(TRNQTY) as Quantity
from by.by_bi_daily_tran 
where cast(trndte as date) between '2024-04-01' and '2024-04-22'
  and prt_client_id = 'MUJI'
  and actcod = 'TRLR_LOAD'
group by cast(trndte as date), usr_id, user_name, prt_client_id, actcod;

注意事项 #

不同数据库的时间函数语法略有差异：

• MySQL：将cast(trndte as date) + interval '1 day' - interval '1 second'替换为date_add(date(trndte), interval 1 day) - interval 1 second
• SQL Server：替换为DATEADD(second, -1, DATEADD(day, 1, CAST(trndte AS DATE)))

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