Return AVG DayName

leeloo_dallas

Hey I was wondering if there is a beastmode windows function that would allow me to return the avg dayname from the average of x?

 

I have data that includes a domain, a column for day name(made via beastmode), and an avg of impressions).

 

Any ideas? I thought the following would work if I added concat in front of it but I keep getting 0 as a returned response. 

avg(distinct DAYNAME(date))

MarkSnodgrass

Average doesn't work for words, but you could use the DAYOFWEEK function to get the numerical day, average that and then convert that to the day name. Would look something like this:

CASE ROUND(AVG(DAYOFWEEK(`date`)),0) WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END

MarkSnodgrass

Average doesn't work for words, but you could use the DAYOFWEEK function to get the numerical day, average that and then convert that to the day name. Would look something like this:

CASE ROUND(AVG(DAYOFWEEK(`date`)),0) WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END

leeloo_dallas

Oh my gosh I didn't even think to use DAYOFWEEK.

 

It works well too!

 

I was curious, do you think this returns there's a way to FLOOR or CEILING to get the lowest avg and highest avg to DAYOFWEEK?

leeloo_dallas

Kind of like this?
(CASE ROUND(AVG(FLOOR(DAYOFWEEK(`date`))),0) WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END)

MarkSnodgrass

Yes, I thought about including floor or ceiling in my reply, but wasn't sure which way you would want to go. I would do some testing and just see what works best for your situation.

leeloo_dallas

Will do! Thanks so much for your help! 

 

Also, did I apply that correctly? Like the FLOOR to the query?

MarkSnodgrass

Actually FLOOR would replace ROUND and it would be outside the average function. Like this:

 

 FLOOR(AVG(DAYOFWEEK(`date`)))

MarkSnodgrass

Here's link describing the floor and ceiling functions.

https://www.mathsisfun.com/sets/function-floor-ceiling.html

 

jaeW_at_Onyx

... oof are you trying to figure out on which day of the week people tend to visit your site?

if yes, then you should definitely bake day of week into your dataset via ETL, different implementations of dayofweek will shift Monday from 1 to 0, and that's not the sort of thing you want to guess about.

 

meanwhile, it's interesting you mention avg. number of impressions, b/c depending on the granularity of your dataset (what each row of data represents), the avg. number of impressions may differ from the avg day of week in your dataset.  

 

make sure to be sure the math you're calculating is answering the business question!