BigQuery中NULL排序：能否在ORDER BY指定规则或全局生效？

嘿，这个问题问得很准！作为常年跟BigQuery打交道的人，我来给你拆解下怎么实现你要的效果——不管是内嵌在排序项里控制NULL顺序，还是尽量接近全局生效的方案：

1. 内嵌在排序项本身控制NULL顺序 #

BigQuery确实不像PostgreSQL那样支持直接在ORDER BY里写NULLS FIRST/LAST这种简洁语法，但咱们可以通过包装排序字段的表达式实现完全等效的效果，而且是内嵌在单个排序项里的，不用额外加排序字段，完全符合你的要求！

举两个常用场景的例子：

• 让NULL排在最前面：
  如果是数值型字段（比如user_id），可以用CASE表达式把NULL转成一个比所有非NULL值都小的数：

  ORDER BY CASE WHEN user_id IS NULL THEN 0 ELSE user_id END
  

  如果是字符串型字段（比如username），可以把NULL转成一个比所有非空字符串排序更靠前的字符（比如空格）：

  ORDER BY CASE WHEN username IS NULL THEN ' ' ELSE username END
  

• 让NULL排在最后面：
  数值型字段可以把NULL转成一个极大值：

  ORDER BY CASE WHEN user_id IS NULL THEN 999999999 ELSE user_id END
  

  字符串型字段可以转成一个排序极靠后的字符（比如~）：

  ORDER BY CASE WHEN username IS NULL THEN '~' ELSE username END
  

  这种方式本质是把NULL映射成排序序列里你想要位置的占位值，全程只用到一个排序项，完美替代PostgreSQL的NULLS FIRST/LAST语法。

2. 近似全局生效的方案（适用于所有查询） #

BigQuery目前没有全局配置默认NULL排序规则的参数，但有两个实用方案能帮你避免重复写逻辑：

• 创建统一处理的视图：
  如果你的查询大多基于某个基础表，可以创建一个视图，预先对所有需要排序的字段处理好NULL映射逻辑。后续查询直接基于视图排序，不用每次写表达式：

  CREATE OR REPLACE VIEW my_dataset.my_unified_view AS
  SELECT 
    -- 数值型字段：NULL排前面
    CASE WHEN user_id IS NULL THEN 0 ELSE user_id END AS user_id,
    -- 字符串型字段：NULL排最后
    CASE WHEN username IS NULL THEN '~' ELSE username END AS username,
    -- 其他字段直接保留
    email, create_time
  FROM my_dataset.my_source_table
  

  之后查询时直接ORDER BY user_id，就会自动遵循预先设置的NULL排序规则。

• 用自定义UDF封装逻辑：
  写一个通用UDF来统一处理不同类型字段的NULL排序，每次排序时调用UDF即可，不用重复写CASE：

  -- 创建让NULL排前面的UDF
  CREATE OR REPLACE FUNCTION my_dataset.nulls_first(input ANY TYPE) AS (
    CASE 
      WHEN input IS NULL THEN 
        -- 根据字段类型返回对应最小占位符
        IF(REGEXP_CONTAINS(FORMAT("%T", input), r"'"), ' ', 0)
      ELSE input 
    END
  );
  
  -- 创建让NULL排最后的UDF
  CREATE OR REPLACE FUNCTION my_dataset.nulls_last(input ANY TYPE) AS (
    CASE 
      WHEN input IS NULL THEN 
        -- 根据字段类型返回对应最大占位符
        IF(REGEXP_CONTAINS(FORMAT("%T", input), r"'"), '~', 999999999)
      ELSE input 
    END
  );
  

  使用时直接调用：

  ORDER BY my_dataset.nulls_first(user_id)
  ORDER BY my_dataset.nulls_last(username)
  

补充：BigQuery默认的NULL排序规则 #

顺带提一句，BigQuery本身有默认的NULL排序逻辑：

• 升序（ASC）时，NULL默认排在最后
• 降序（DESC）时，NULL默认排在最前
  如果你的需求刚好匹配这个，那啥都不用改；要是反过来，就用上面的方法调整就行。

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