BigQuery SQL：如何计算Year_Birth的最值、均值及分段查询

一、获取Year_Birth的最小值、最大值和平均值 #

有两种实现方式，可根据实际需求选择：

方式1：单独获取统计结果 #

如果只需要Year_Birth的统计数值，直接在清理后的数据集上聚合即可：

WITH clean_profils AS (
  SELECT *, 
         REPLACE(Marital_Status,"Alone","Single") AS Clean_Marital_Status
  FROM `neoma-bdd.data.profils`
  WHERE Year_Birth > 1940 
    AND Marital_Status NOT IN ("YOLO", "Absurd")
    AND Income IS NOT NULL 
)
SELECT
  MIN(Year_Birth) AS min_year_birth,
  MAX(Year_Birth) AS max_year_birth,
  AVG(Year_Birth) AS avg_year_birth
FROM clean_profils;

方式2：在明细数据中附带统计值 #

如果需要在每条明细数据旁同时显示Year_Birth的统计结果，使用窗口聚合函数：

WITH clean_profils AS (
  SELECT *, 
         REPLACE(Marital_Status,"Alone","Single") AS Clean_Marital_Status
  FROM `neoma-bdd.data.profils`
  WHERE Year_Birth > 1940 
    AND Marital_Status NOT IN ("YOLO", "Absurd")
    AND Income IS NOT NULL 
),
combined_data AS (
  SELECT 
    clean_profils.id, 
    Year_Birth, 
    Income, 
    Kidhome, 
    Teenhome, 
    Dt_Customer, 
    Clean_Marital_Status,
    MntWines + MntFruits + MntMeatProducts + MntFishProducts + MntSweetProducts + MntGoldProds AS ALL_PURCHASES,
    MIN(Year_Birth) OVER() AS min_year_birth,
    MAX(Year_Birth) OVER() AS max_year_birth,
    AVG(Year_Birth) OVER() AS avg_year_birth
  FROM `neoma-bdd.data.purchases` 
  INNER JOIN clean_profils 
    ON `neoma-bdd.data.purchases`.id = clean_profils.id
)
SELECT * FROM combined_data ORDER BY ALL_PURCHASES;

二、筛选ALL_PURCHASES排序后的第560至1119行 #

无需自连接，使用ROW_NUMBER()窗口函数生成唯一行号，再通过行号范围筛选即可：

WITH clean_profils AS (
  SELECT *, 
         REPLACE(Marital_Status,"Alone","Single") AS Clean_Marital_Status
  FROM `neoma-bdd.data.profils`
  WHERE Year_Birth > 1940 
    AND Marital_Status NOT IN ("YOLO", "Absurd")
    AND Income IS NOT NULL 
),
ranked_data AS (
  SELECT 
    clean_profils.id, 
    Year_Birth, 
    Income, 
    Kidhome, 
    Teenhome, 
    Dt_Customer, 
    Clean_Marital_Status,
    MntWines + MntFruits + MntMeatProducts + MntFishProducts + MntSweetProducts + MntGoldProds AS ALL_PURCHASES,
    ROW_NUMBER() OVER(ORDER BY ALL_PURCHASES) AS row_num
  FROM `neoma-bdd.data.purchases` 
  INNER JOIN clean_profils 
    ON `neoma-bdd.data.purchases`.id = clean_profils.id
)
SELECT * 
FROM ranked_data 
WHERE row_num BETWEEN 560 AND 1119
ORDER BY ALL_PURCHASES;

说明 #

• ROW_NUMBER()会按ALL_PURCHASES排序顺序为每一行生成唯一序号，避免因重复值导致的分页不稳定问题
• 如果需要保留ALL_PURCHASES相同值的所有行（不跳过），可以改用RANK()或DENSE_RANK()，但根据需求，ROW_NUMBER()更适合精确筛选行号范围

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