[241204 TIL] 팀플...

새로 알게 된 합수!

 

 

날짜 요일 구하기

 

DAYOFWEEK( DATE ) -- 정수로 리턴

DAYNAME( DATE ) -- 영어요일로 리턴

*DAYOFWEEK 숫자 ( 1~7 일~토 )

 

[예시]

SELECT DAYOFWEEK( '2017-11-10' )

결과값 = 6

SELECT DAYNAME( '2017-11-13' )

결과값 = Monday

 

한글값 요일을 불러오고싶을 때

SELECT SUBSTR(_UTF8'일월화수목금토', DAYOFWEEK('2017-11-10'), 1 );

 

 

[팀플에서 활용]

SELECT

dayname(event_time) as '요일',

COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) AS '구매 수',

COUNT(DISTINCT user_id) AS '사용자 수',

ROUND(

(COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) * 100.0) /

COUNT(DISTINCT user_id),

2

) as '구매전환율%'

FROM 2019_nov n

GROUP BY 1

ORDER BY '요일';