BigQuery查询结果未按week_starting_from排序问题求助

你指定的排序字段week_starting_from是通过FORMAT_TIMESTAMP('%Y-%-m-%d', week_start)生成的字符串类型，而非日期类型。字符串排序是按字符逐个比较的，比如"2023-5-1"和"2023-10-1"，字符串排序时会优先比较第三个字符后的"5"和"1"，因为"1"的ASCII码更小，所以"2023-10-1"会排在"2023-5-1"前面，这和你预期的日期降序逻辑完全不符，导致看起来排序不生效。

有两种简单的修复方式：

方式1：基于原始日期字段排序 #

在CTE中保留原始的week_start日期字段，最终排序时直接用这个日期字段，同时保留week_starting_from作为展示字段：

WITH weekly_data AS (
  SELECT
    FORMAT_TIMESTAMP('%Y-%-m-%-d', week_start) AS week_starting_from,
    week_start, -- 保留原始日期字段用于排序
    CASE
      WHEN event_dow = 1 THEN 'Sunday'
      WHEN event_dow = 2 THEN 'Monday'
      WHEN event_dow = 3 THEN 'Tuesday'
      WHEN event_dow = 4 THEN 'Wednesday'
      WHEN event_dow = 5 THEN 'Thursday'
      WHEN event_dow = 6 THEN 'Friday'
      WHEN event_dow = 7 THEN 'Saturday'
      ELSE 'NA'
    END AS day_of_week,
    average_event_count,
    ROW_NUMBER() OVER (PARTITION BY week_start ORDER BY average_event_count DESC) AS rank
  FROM (
    SELECT
      DATE_TRUNC(event_day, WEEK(MONDAY)) AS week_start,
      EXTRACT(DAYOFWEEK FROM event_day) AS event_dow,
      AVG(cnt) AS average_event_count
    FROM (
      SELECT
        TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), DAY) AS event_day,
        EXTRACT(DAYOFWEEK FROM TIMESTAMP_MICROS(event_timestamp)) AS event_dow,
        COUNT(*) AS cnt
      FROM `table`
      WHERE
        event_name IN ("change_font")
        AND event_timestamp >= UNIX_MICROS(TIMESTAMP '2022-05-22')
        AND event_timestamp < UNIX_MICROS(TIMESTAMP '2023-05-28')
      GROUP BY event_day, event_dow
    )
    GROUP BY week_start, event_dow
  )
)
SELECT
  week_starting_from,
  day_of_week,
  average_event_count
FROM weekly_data
#WHERE rank = 1
ORDER BY week_start DESC, average_event_count DESC#, day_of_week

方式2：修改格式化字符串为补零格式 #

把FORMAT_TIMESTAMP的格式改为'%Y-%m-%d'，让月份和日期以两位数字展示（比如"2023-05-01"而非"2023-5-1"），此时字符串的排序逻辑就和日期排序逻辑一致了：

WITH weekly_data AS (
  SELECT
    FORMAT_TIMESTAMP('%Y-%m-%d', week_start) AS week_starting_from, -- 补零格式保证字符串排序和日期一致
    CASE
      WHEN event_dow = 1 THEN 'Sunday'
      WHEN event_dow = 2 THEN 'Monday'
      WHEN event_dow = 3 THEN 'Tuesday'
      WHEN event_dow = 4 THEN 'Wednesday'
      WHEN event_dow = 5 THEN 'Thursday'
      WHEN event_dow = 6 THEN 'Friday'
      WHEN event_dow = 7 THEN 'Saturday'
      ELSE 'NA'
    END AS day_of_week,
    average_event_count,
    ROW_NUMBER() OVER (PARTITION BY week_start ORDER BY average_event_count DESC) AS rank
  FROM (
    SELECT
      DATE_TRUNC(event_day, WEEK(MONDAY)) AS week_start,
      EXTRACT(DAYOFWEEK FROM event_day) AS event_dow,
      AVG(cnt) AS average_event_count
    FROM (
      SELECT
        TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), DAY) AS event_day,
        EXTRACT(DAYOFWEEK FROM TIMESTAMP_MICROS(event_timestamp)) AS event_dow,
        COUNT(*) AS cnt
      FROM `table`
      WHERE
        event_name IN ("change_font")
        AND event_timestamp >= UNIX_MICROS(TIMESTAMP '2022-05-22')
        AND event_timestamp < UNIX_MICROS(TIMESTAMP '2023-05-28')
      GROUP BY event_day, event_dow
    )
    GROUP BY week_start, event_dow
  )
)
SELECT
  week_starting_from,
  day_of_week,
  average_event_count
FROM weekly_data
#WHERE rank = 1
ORDER BY week_starting_from DESC, average_event_count DESC#, day_of_week

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