Big Query中ORDER BY DESC失效：如何按最新时间排序查询结果？

你的timestamp字段存在类型不统一的情况：部分值是YYYY-MM-DD HH:MM:SS.FFFFFF格式的字符串，另一部分是科学计数法表示的UNIX时间戳（数值或数值字符串）。BigQuery对混合类型字段排序时，会按类型优先级和字符串字典序处理，导致实际最新的时间（对应科学计数法的数值）被排在后面。

将所有timestamp值统一转换为标准的TIMESTAMP类型后再排序，确保排序逻辑基于时间先后而非字符串或数值的原始格式。

方法1：精准匹配格式转换 #

根据值的格式分别处理，确保转换准确性：

SELECT
  CASE
    -- 匹配日期格式字符串，转换为TIMESTAMP
    WHEN REGEXP_CONTAINS(u.timestamp, r'^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d+$') THEN PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%E6S', u.timestamp)
    -- 匹配数值/科学计数法字符串，转换为TIMESTAMP（若为微秒级时间戳，替换为TIMESTAMP_MICROS）
    WHEN REGEXP_CONTAINS(u.timestamp, r'^[\d\.Ee+-]+$') THEN TIMESTAMP_SECONDS(SAFE_CAST(u.timestamp AS INT64))
    -- 无法转换的情况返回NULL，可根据需求调整处理逻辑
    ELSE NULL
  END AS normalized_timestamp
FROM
  `app-68ccf.firestore_export.EventsHandle_raw_latest` AS u
ORDER BY
  normalized_timestamp DESC
LIMIT 50;

方法2：简化转换逻辑 #

尝试先按日期格式转换，失败则按数值转换：

SELECT
  COALESCE(
    SAFE.PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%E6S', u.timestamp),
    TIMESTAMP_SECONDS(SAFE_CAST(u.timestamp AS INT64))
  ) AS normalized_timestamp
FROM
  `app-68ccf.firestore_export.EventsHandle_raw_latest` AS u
ORDER BY
  normalized_timestamp DESC
LIMIT 50;

转换后的normalized_timestamp是标准TIMESTAMP类型，排序时会严格按时间先后从新到旧排列，你之前查询max(u.timestamp)得到的科学计数法数值对应的最新时间，会排在结果的最顶部。

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