How to arrange rows by name of day (NOT ALPHABETICALLY), getting weird results using PARSE

--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

I've tried to use the PARSE_DATE function but it returns the value 1 for all. 'name_of_day' field name is STRING where the values are: Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday

This is how my query looks like

I find it weird because I was able to arrange my data by month (Jan-Dec) by

EXTRACT(MONTH FROM PARSE_DATE('%B', month)) AS month_number

where 'month' is also a string with values: January,February and so on... or did it just worked coincidentally?

I was able to arrange my data by month

CodePudding user response：

PARSE_DATE does not find a date for days of week alone, returning '1970-01-01', which results 1 if we extract the day.

Would, thus, define the correspondence as a 2nd CTE:

, b as (
  select 'Monday'    name_of_day, 1 day_number union all
  select 'Tuesday'   name_of_day, 2 day_number union all
  select 'Wednesday' name_of_day, 3 day_number union all
  select 'Thursday'  name_of_day, 4 day_number union all
  select 'Friday'    name_of_day, 5 day_number union all
  select 'Saturday'  name_of_day, 6 day_number union all
  select 'Sunday'    name_of_day, 7 day_number

)

and then order by day_number after joining a and b:

select
  a.user_type
  , a.name_of_day
  , a.user_count
  , a.percentage
  , a.avg_trip_duration
  , b.day_number
from a
join b 
  on b.name_of_day = a.name_of_day
order by user_type, day_number

https://console.cloud.google.com/bigquery?sq=1013309549723:5a56ae1b50f948b4830e58b2a449118a

CodePudding user response：

Extract must have a date to extract something from, and not the name of the day of the week (parse_date is not working as well - it wont create a date from day of the week). What you might do is create a inline table with day name and day value, and then join those two tables.