SQL查询报403 Forbidden错误求助：含CASE与SUM聚合语句异常

执行以下SQL查询时触发403 Forbidden错误，移除CASE语句或移除所有SUM聚合函数及对应的GROUP BY子句后，查询可正常执行，但业务需求必须保留所有这些列，求解决方案。

SELECT
    campaign_id,
    campaign_name,
    campaign_price,
    campaign_external_id,
    campaign_goal_adrequest,
    campaign_goal_banner_event,
    campaign_goal_banner_event_key,
    banner_id,
    banner_name,
    website_id,
    website_name,
    time_day,
    time_month,
    CASE
        WHEN contentunit_name LIKE 'web_%' THEN 'web'
        WHEN contentunit_name LIKE 'mob_%' THEN 'mob'
        WHEN contentunit_name LIKE 'app_%' THEN 'app'
        WHEN contentunit_name LIKE 'smart_%' THEN 'smart'
        ELSE 'others'
    END AS platform,
    SUM(measures_adrequests)    as adrequests,
    SUM(measures_views)         as views,
    SUM(measures_truecount)     as truecounts,
    SUM(events_4970_count)      as count_events,
    SUM(events_4453_visibility) as visibility_events,
    SUM(vast_click)             as click_events,
    SUM(vast_impression)        as vast_impressions,
    SUM(vast_start)             as vast_start,
    SUM(vast_firstquartile)     as vast_firstquartile,
    SUM(vast_midpoint)          as vast_midpoint,
    SUM(vast_thirdquartile)     as vast_thirdquartile,
    SUM(vast_complete)          as vast_complete,
    SUM(vast_loaded)            as vast_loaded
FROM
    MV_ADI_SUPERVIEW
WHERE
    campaign_id = :campaign_id
GROUP BY
    campaign_id,
    campaign_name,
    campaign_price,
    campaign_external_id,
    campaign_goal_adrequest,
    campaign_goal_banner_event,
    campaign_goal_banner_event_key,
    banner_id,
    banner_name,
    website_id,
    website_name,
    time_day,
    time_month,
    platform

可能的排查方向与解决方案 #

• 检查资源权限阈值：403错误虽常关联权限，但仅在复杂查询时触发，大概率是聚合+CASE分组的计算量触发了平台的资源限制（如并发配额、内存/CPU使用上限），联系运维确认该查询是否超出允许的资源额度。
• 重构GROUP BY子句：部分SQL方言不支持在GROUP BY中直接引用SELECT的别名（如platform），尝试将CASE语句直接替换GROUP BY中的别名：

  GROUP BY
      campaign_id,
      campaign_name,
      campaign_price,
      campaign_external_id,
      campaign_goal_adrequest,
      campaign_goal_banner_event,
      campaign_goal_banner_event_key,
      banner_id,
      banner_name,
      website_id,
      website_name,
      time_day,
      time_month,
      CASE
          WHEN contentunit_name LIKE 'web_%' THEN 'web'
          WHEN contentunit_name LIKE 'mob_%' THEN 'mob'
          WHEN contentunit_name LIKE 'app_%' THEN 'app'
          WHEN contentunit_name LIKE 'smart_%' THEN 'smart'
          ELSE 'others'
      END
  

• 拆分查询逻辑：将CASE分组逻辑提前到子查询中，再在外层做聚合，避免数据库解析时的潜在冲突：

  SELECT
      campaign_id,
      campaign_name,
      campaign_price,
      campaign_external_id,
      campaign_goal_adrequest,
      campaign_goal_banner_event,
      campaign_goal_banner_event_key,
      banner_id,
      banner_name,
      website_id,
      website_name,
      time_day,
      time_month,
      platform,
      SUM(measures_adrequests)    as adrequests,
      SUM(measures_views)         as views,
      SUM(measures_truecount)     as truecounts,
      SUM(events_4970_count)      as count_events,
      SUM(events_4453_visibility) as visibility_events,
      SUM(vast_click)             as click_events,
      SUM(vast_impression)        as vast_impressions,
      SUM(vast_start)             as vast_start,
      SUM(vast_firstquartile)     as vast_firstquartile,
      SUM(vast_midpoint)          as vast_midpoint,
      SUM(vast_thirdquartile)     as vast_thirdquartile,
      SUM(vast_complete)          as vast_complete,
      SUM(vast_loaded)            as vast_loaded
  FROM (
      SELECT
          campaign_id,
          campaign_name,
          campaign_price,
          campaign_external_id,
          campaign_goal_adrequest,
          campaign_goal_banner_event,
          campaign_goal_banner_event_key,
          banner_id,
          banner_name,
          website_id,
          website_name,
          time_day,
          time_month,
          measures_adrequests,
          measures_views,
          measures_truecount,
          events_4970_count,
          events_4453_visibility,
          vast_click,
          vast_impression,
          vast_start,
          vast_firstquartile,
          vast_midpoint,
          vast_thirdquartile,
          vast_complete,
          vast_loaded,
          CASE
              WHEN contentunit_name LIKE 'web_%' THEN 'web'
              WHEN contentunit_name LIKE 'mob_%' THEN 'mob'
              WHEN contentunit_name LIKE 'app_%' THEN 'app'
              WHEN contentunit_name LIKE 'smart_%' THEN 'smart'
              ELSE 'others'
          END AS platform
      FROM MV_ADI_SUPERVIEW
      WHERE campaign_id = :campaign_id
  ) t
  GROUP BY
      campaign_id,
      campaign_name,
      campaign_price,
      campaign_external_id,
      campaign_goal_adrequest,
      campaign_goal_banner_event,
      campaign_goal_banner_event_key,
      banner_id,
      banner_name,
      website_id,
      website_name,
      time_day,
      time_month,
      platform
  

• 验证数据库方言兼容性：确认当前使用的数据库是否支持GROUP BY引用SELECT别名，若不支持则必须用原始CASE表达式替换别名。

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