带GROUP BY的临时表SQL报错：AnalysisException问题求解

报错原因 #

当使用GROUP BY分组时，SQL语法规则要求：SELECT子句中的列必须要么是GROUP BY指定的分组列，要么被聚合函数（如COUNT、SUM、MAX等）包裹。你用SELECT *会包含临时表t中所有列，而这些列大部分不在GROUP BY id_date的分组字段里，数据库无法确定如何处理这些非分组列，因此触发该错误。

解决方案 #

根据你的实际需求选择对应方案：

方案1：按id_date分组并聚合其他列 #

如果需要对每个id_date对应的其他数据做统计（比如计数、求和、取最值），需明确写出SELECT的列，非分组列用聚合函数处理：

WITH t AS (
  SELECT 
    YEAR(start_date) AS année,
    MONTH(start_date) AS mois, 
    WEEK(start_date) AS semaine, 
    start_date AS intervalle_de_date,
    channel AS type_de_recharge,
    rch_type AS étoile,
    Produit,
    desc_profil AS forfait,
    distributeur,
    segment_activation_channel AS canal,
    offer_code AS offre,
    tel_prod_fed_ref.ref_segment_b2b.segment,
    tel_test_anl_360b2b.dn_rch_kpi_d1.id_date 
  FROM tel_test_anl_360b2b.dn_rch_kpi_d1 
  INNER JOIN tel_test_anl_360b2b.dn_parc_b2b_d1 
    ON tel_test_anl_360b2b.dn_rch_kpi_d1.dn = tel_test_anl_360b2b.dn_parc_b2b_d1.mdn 
  INNER JOIN tel_prod_fed_ref.ref_segment_b2b 
    ON tel_test_anl_360b2b.dn_parc_b2b_d1.s1 = tel_prod_fed_ref.ref_segment_b2b.s1 
    AND tel_test_anl_360b2b.dn_parc_b2b_d1.s2 = tel_prod_fed_ref.ref_segment_b2b.s2 
  WHERE 
    offer_code IN ('OGSMPOSTB2B','OTPEGSMB2B','ODATAONLYB2B','ODATAB2B','OETEPOSTFMB2B','OFTEPOSTFMB2B')
    AND segment_activation_channel IN ('VI','VD','BIR','D2D')
)
SELECT 
  id_date,
  MAX(année) AS année,  -- 同一id_date下année通常唯一，用MAX/MIN均可保留值
  MAX(mois) AS mois,
  MAX(semaine) AS semaine,
  MAX(intervalle_de_date) AS intervalle_de_date,
  COUNT(DISTINCT type_de_recharge) AS nombre_types_recharge,  -- 统计不同充值类型数量
  MAX(étoile) AS étoile,
  MAX(Produit) AS Produit,
  MAX(forfait) AS forfait,
  COUNT(DISTINCT distributeur) AS nombre_distributeurs,  -- 统计不同分销商数量
  MAX(canal) AS canal,
  MAX(offre) AS offre,
  MAX(segment) AS segment
FROM t 
GROUP BY id_date;

注：聚合函数可根据实际需求调整，比如用SUM()求和、AVG()取平均等。

方案2：按id_date去重，保留单条数据 #

如果只是想对id_date去重，保留每组中的某一条数据（比如最新的一条），可以用窗口函数实现：

WITH t AS (
  SELECT 
    YEAR(start_date) AS année,
    MONTH(start_date) AS mois, 
    WEEK(start_date) AS semaine, 
    start_date AS intervalle_de_date,
    channel AS type_de_recharge,
    rch_type AS étoile,
    Produit,
    desc_profil AS forfait,
    distributeur,
    segment_activation_channel AS canal,
    offer_code AS offre,
    tel_prod_fed_ref.ref_segment_b2b.segment,
    tel_test_anl_360b2b.dn_rch_kpi_d1.id_date 
  FROM tel_test_anl_360b2b.dn_rch_kpi_d1 
  INNER JOIN tel_test_anl_360b2b.dn_parc_b2b_d1 
    ON tel_test_anl_360b2b.dn_rch_kpi_d1.dn = tel_test_anl_360b2b.dn_parc_b2b_d1.mdn 
  INNER JOIN tel_prod_fed_ref.ref_segment_b2b 
    ON tel_test_anl_360b2b.dn_parc_b2b_d1.s1 = tel_prod_fed_ref.ref_segment_b2b.s1 
    AND tel_test_anl_360b2b.dn_parc_b2b_d1.s2 = tel_prod_fed_ref.ref_segment_b2b.s2 
  WHERE 
    offer_code IN ('OGSMPOSTB2B','OTPEGSMB2B','ODATAONLYB2B','ODATAB2B','OETEPOSTFMB2B','OFTEPOSTFMB2B')
    AND segment_activation_channel IN ('VI','VD','BIR','D2D')
),
ranked_data AS (
  SELECT *,
    -- 按id_date分组，给每组数据排序，这里按start_date倒序取最新一条
    ROW_NUMBER() OVER (PARTITION BY id_date ORDER BY start_date DESC) AS row_rank
  FROM t
)
SELECT 
  année, mois, semaine, intervalle_de_date,
  type_de_recharge, étoile, Produit, forfait,
  distributeur, canal, offre, segment, id_date
FROM ranked_data 
WHERE row_rank = 1;

注：可以修改ORDER BY后的字段，选择保留你需要的那条数据。

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