如何按星期几名称非字母排序？PARSE_DATE返回异常结果

问题描述 #

我编写了如下BigQuery查询，用于展示出行量占比、各用户类型的平均出行时长及对应的星期几：

--This query will display the occupancy of trips & average duration trip per user type & by which day the trip occurred.

with a AS (
SELECT
  user_type,
  name_of_day,
  COUNT(*) AS user_count, 
  ROUND((COUNT(*)/SUM(COUNT(*))OVER(PARTITION BY user_type))*100,2) AS percentage,
  ROUND(AVG(trip_duration_h),3) AS avg_trip_duration
FROM `fresh-ocean-357202.Cyclistic.Cyclistic_clean`   
GROUP BY 
  user_type,
  name_of_day
ORDER BY user_type ASC
)
 
SELECT *,
  EXTRACT(DAY FROM PARSE_DATE('%A', name_of_day)) AS day_number
FROM a
ORDER BY user_type,day_number

其中name_of_day是字符串类型，值为Monday、Tuesday等星期名称。但使用PARSE_DATE函数提取day_number时，所有结果均返回1。此前我用类似方法EXTRACT(MONTH FROM PARSE_DATE('%B', month)) AS month_number处理值为January等的月份字符串时可正常排序，想知道为何星期处理出现异常，以及如何实现按星期几名称非字母排序。

问题原因 #

你用PARSE_DATE('%A', name_of_day)提取星期序号的思路存在逻辑问题：

• 当仅传入星期名称（如Monday）时，PARSE_DATE会默认填充年份和月份为0001-01，并尝试匹配该月中对应的星期几。但0001-01-01本身就是Monday，对于其他星期名称，BigQuery无法在0001-01这个月中找到匹配的有效日期映射，最终会默认返回0001-01-01，因此EXTRACT(DAY)的结果始终是1。
• 而月份处理有效的原因是：PARSE_DATE('%B', 'January')会直接映射到0001-01-01，EXTRACT(MONTH)返回1；PARSE_DATE('%B', 'February')映射到0001-02-01，EXTRACT(MONTH)返回2，刚好和月份序号对应，属于巧合性的有效场景。

解决方法 #

推荐以下两种可靠的实现方式：

方法1：使用CASE语句直接映射 #

这是最直观且不易出错的方式，直接将星期名称映射为对应的序号（周一=1，周日=7）：

--This query will display the occupancy of trips & average duration trip per user type & by which day the trip occurred.

with a AS (
SELECT
  user_type,
  name_of_day,
  COUNT(*) AS user_count, 
  ROUND((COUNT(*)/SUM(COUNT(*))OVER(PARTITION BY user_type))*100,2) AS percentage,
  ROUND(AVG(trip_duration_h),3) AS avg_trip_duration
FROM `fresh-ocean-357202.Cyclistic.Cyclistic_clean`   
GROUP BY 
  user_type,
  name_of_day
)
 
SELECT *,
  CASE name_of_day
    WHEN 'Monday' THEN 1
    WHEN 'Tuesday' THEN 2
    WHEN 'Wednesday' THEN 3
    WHEN 'Thursday' THEN 4
    WHEN 'Friday' THEN 5
    WHEN 'Saturday' THEN 6
    WHEN 'Sunday' THEN 7
  END AS day_number
FROM a
ORDER BY user_type, day_number

方法2：从原始日期字段提取（如果存在） #

如果你的原始表Cyclistic_clean中包含出行日期字段（如trip_date），可以直接从日期字段提取标准的星期序号，避免字符串转换的问题：

• 使用EXTRACT(ISOWEEKDAY FROM trip_date)：返回值为1（周一）到7（周日），符合常规排序需求
• 使用EXTRACT(DAYOFWEEK FROM trip_date)：返回值为1（周日）到7（周六），如果需要周一开头需调整计算

修改后的CTE部分示例：

with a AS (
SELECT
  user_type,
  FORMAT_DATE('%A', trip_date) AS name_of_day,
  COUNT(*) AS user_count, 
  ROUND((COUNT(*)/SUM(COUNT(*))OVER(PARTITION BY user_type))*100,2) AS percentage,
  ROUND(AVG(trip_duration_h),3) AS avg_trip_duration,
  EXTRACT(ISOWEEKDAY FROM trip_date) AS day_number
FROM `fresh-ocean-357202.Cyclistic.Cyclistic_clean`   
GROUP BY 
  user_type,
  name_of_day,
  day_number
)
 
SELECT *
FROM a
ORDER BY user_type, day_number

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