BigQuery如何筛选表中最大Month对应的全部列数据

你可以通过以下3种常用方式实现需求，适配你现有的表结构： #

方法1：子查询匹配最大值（最容易理解，适配所有SQL方言） #

原理是先单独查询出符合条件的最大Month值，再用这个值过滤原表数据，就算最大Month对应多条数据，也可以把所有符合条件的行都查出来：

SELECT 
    CAST(MonthNum as INT64) as Month,
    CAST(COP as FLOAT64) as COP,
    CAST(USD as FLOAT64) as USD,
    CAST(EUR as FLOAT64) as EUR,
    CAST(ARS as FLOAT64) as ARS,
    CAST(CLP as FLOAT64) as CLP,
    CAST(PEN as FLOAT64) as PEN, 
    CAST(MXN as FLOAT64) as MXN
FROM 
    `xxx.regional.xr`
WHERE 
    Type = 'INTRA'
    AND CAST(MonthNum as INT64) = (
        SELECT MAX(CAST(MonthNum as INT64)) 
        FROM `xxx.regional.xr` 
        WHERE Type = 'INTRA'
    )

方法2：排序后取第一条（写法最简，仅适用于最大Month仅对应1行的场景） #

如果你能确认最大的Month只有一行数据，直接在你原有查询基础上加LIMIT 1即可：

SELECT 
    CAST(MonthNum as INT64) as Month,
    CAST(COP as FLOAT64) as COP,
    CAST(USD as FLOAT64) as USD,
    CAST(EUR as FLOAT64) as EUR,
    CAST(ARS as FLOAT64) as ARS,
    CAST(CLP as FLOAT64) as CLP,
    CAST(PEN as FLOAT64) as PEN, 
    CAST(MXN as FLOAT64) as MXN
FROM 
    `xxx.regional.xr`
WHERE 
    Type = 'INTRA'
ORDER BY 
    Month DESC
LIMIT 1

方法3：窗口函数（扩展性强，适合复杂排名场景） #

用RANK()函数给数据按Month降序排名，筛选排名为1的所有行，就算最大Month对应多条数据也能全部返回，后续如果要取前N个月的数据也只要改筛选条件即可：

WITH xr_ranked AS (
    SELECT 
        CAST(MonthNum as INT64) as Month,
        CAST(COP as FLOAT64) as COP,
        CAST(USD as FLOAT64) as USD,
        CAST(EUR as FLOAT64) as EUR,
        CAST(ARS as FLOAT64) as ARS,
        CAST(CLP as FLOAT64) as CLP,
        CAST(PEN as FLOAT64) as PEN, 
        CAST(MXN as FLOAT64) as MXN,
        RANK() OVER(ORDER BY CAST(MonthNum as INT64) DESC) AS month_rank
    FROM 
        `xxx.regional.xr`
    WHERE 
        Type = 'INTRA'
)
SELECT Month, COP, USD, EUR, ARS, CLP, PEN, MXN
FROM xr_ranked
WHERE month_rank = 1

内容的提问来源于stack exchange，提问作者Hctor Alonso Hormazbal Vildsol