如何使用ROW_NUMBER函数排序日期并筛选最早日期？

你的SQL代码存在几个关键问题，导致结果不符合预期：

• 排序依据是原字符串类型的date字段，而非解析后的日期类型，字符串排序逻辑和实际日期顺序可能不匹配（比如特殊格式的日期字符串排序会出错）
• 无聚合操作却使用GROUP BY，会打乱原始数据结构，甚至丢失有效记录
• 筛选最早日期的实现逻辑冗余，没有利用更高效的方式

以下是针对性的解决方案：

方案1：仅获取最早的日期 #

直接通过聚合函数MIN解析并提取最小日期，这是最高效的方式：

SELECT MIN(PARSE_DATE('%A, %B %d, %Y', date)) AS earliest_scraping_date
FROM `kramplab-analytics-rnd.kramplab_shared_dwh.LAB_FR_2023-07_google_shopping_full_detail`

方案2：所有记录按日期升序排列，并标记最早日期 #

如果需要保留所有记录，同时按解析后的日期排序并标识最早的条目：

SELECT
  PARSE_DATE('%A, %B %d, %Y', date) AS Scraping_Date,
  -- 用CASE标记是否为最早日期
  CASE 
    WHEN PARSE_DATE('%A, %B %d, %Y', date) = (
      SELECT MIN(PARSE_DATE('%A, %B %d, %Y', date)) 
      FROM `kramplab-analytics-rnd.kramplab_shared_dwh.LAB_FR_2023-07_google_shopping_full_detail`
    ) THEN 1 
    ELSE 0 
  END AS is_earliest
FROM `kramplab-analytics-rnd.kramplab_shared_dwh.LAB_FR_2023-07_google_shopping_full_detail`
ORDER BY Scraping_Date ASC

方案3：用窗口函数排序并编号（修正你的原写法） #

如果需要保留行号，必须基于解析后的日期排序，而非原字符串字段：

SELECT
  PARSE_DATE('%A, %B %d, %Y', date) AS Scraping_Date,
  ROW_NUMBER() OVER (ORDER BY PARSE_DATE('%A, %B %d, %Y', date) ASC) AS row_num
FROM `kramplab-analytics-rnd.kramplab_shared_dwh.LAB_FR_2023-07_google_shopping_full_detail`
-- 若需去重日期，可添加DISTINCT或GROUP BY Scraping_Date

额外注意事项 #

请确认原date字段的格式与%A, %B %d, %Y完全匹配（比如是否是"Monday, July 03, 2023"这类格式），如果解析后出现NULL值，说明格式不匹配，需要调整PARSE_DATE的格式参数。

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