SQL编译错误：[TABLE.CRM_ID]不是有效的GROUP BY表达式

你的代码报错核心是两点：一是使用array_agg聚合函数但未指定GROUP BY分组字段；二是窗口函数的分区逻辑不符合需求——你要获取的是每个客户最早订阅日期对应的产品集，而非按crm_id+term_start_date分组后取单条记录。

修正方案一：先锁定最早订阅日期，再聚合产品 #

, products_extended as (
    select
        crm_id,
        term_start_date,
        rate_plan_name,
        product_category
    from {{ref('products_extended')}}
),
-- 第一步：获取每个客户的最早订阅日期
customer_first_sub_date as (
    select
        crm_id,
        min(term_start_date) as first_term_start_date
    from products_extended
    where rate_plan_name not like '%Free Trial%' 
      and rate_plan_name not like '%Free Access%'
    group by crm_id
),
-- 第二步：关联回原表，聚合最早日期下的所有产品
first_product_set as (
    select
        p.crm_id,
        c.first_term_start_date as term_start_date,
        array_agg(distinct p.rate_plan_name) as first_rate_plan_names,
        array_agg(distinct p.product_category) as first_rate_plan_categories
    from products_extended p
    inner join customer_first_sub_date c
        on p.crm_id = c.crm_id
        and p.term_start_date = c.first_term_start_date
    where p.rate_plan_name not like '%Free Trial%' 
      and p.rate_plan_name not like '%Free Access%'
    group by p.crm_id, c.first_term_start_date
)

修正方案二：用窗口函数标记最早日期记录后聚合 #

, products_extended as (
    select
        crm_id,
        term_start_date,
        rate_plan_name,
        product_category
    from {{ref('products_extended')}}
),
-- 第一步：标记每个客户的最早订阅日期
filtered_products as (
    select
        crm_id,
        term_start_date,
        rate_plan_name,
        product_category,
        min(term_start_date) over(partition by crm_id) as first_term_start_date
    from products_extended
    where rate_plan_name not like '%Free Trial%' 
      and rate_plan_name not like '%Free Access%'
),
-- 第二步：仅保留最早日期的记录，再聚合产品集
first_product_set as (
    select
        crm_id,
        first_term_start_date as term_start_date,
        array_agg(distinct rate_plan_name) as first_rate_plan_names,
        array_agg(distinct product_category) as first_rate_plan_categories
    from filtered_products
    where term_start_date = first_term_start_date
    group by crm_id, first_term_start_date
)

关键说明 #

• 两种方案都先锁定了每个客户的最早订阅日期，再对该日期下的所有产品进行聚合，确保得到的是首个订阅日期对应的完整产品集
• 必须添加GROUP BY子句指定crm_id和日期字段，因为聚合函数需要明确分组维度
• 原代码的qualify逻辑错误：按crm_id+term_start_date分区后，每个分组内的日期是相同的，row_number()仅保留了每个日期分组的任意一行，并没有筛选出客户的最早日期

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