[Postgresql] truncate(), date_trunc() 함수

Mode 사이트에서 yammaer tutorial을 활용하는 분석 프로젝트 진행 중 
date_trunc 함수가 계속 보이는 것 같아서 정리해보자!!

💡 truncate() 함수

: 소수점 이하 숫자를 버릴 때 사용하는 함수

truncate(숫자, 버림할 자릿수)

 
SELECT TRUNCATE(1234.56789 ,1) FROM DUAL;
-- 1234.5
 
SELECT TRUNCATE(1234.56789 ,4) FROM DUAL;
-- 1234.5678
 
SELECT TRUNCATE(1234.56789 ,-1) FROM DUAL;
-- 1230
 
SELECT TRUNCATE(1234.56789 ,-2) FROM DUAL;
-- 1200

💡 date_trunc() 함수

date_trunc(text, timestamp)

• 첫번째 인자
  : [year,month,day,hour,minute,second,millisecond] 와 같은 날짜 키워드

• 두번째 인자
  : timestamp 데이터, 그 시점의 text 키워드까지의 값을 0(default 처리)

SELECT DATE_TRUNC('month', '2019-04-01 12:12:12::timestamp')   
--  2019-04-01 00:00:00

SELECT DATE_TRUNC('month', '2019-04-05 12:12:12::timestamp')  
--  2019-04-01 00:00:00

SELECT DATE_TRUNC('year', '2019-04-01 12:12:12::timestamp')   
--  2019-01-01 00:00:00 
--  두번째 인자로 들어온 날짜의 year까지만을 유효하게 취하고 이하는 default 값

SELECT DATE_TRUNC('minute', '2019-04-01 12:12:12::timestamp')
--  2019-04-01 12:12:00

💡 Mode : yammer Cohort Analysis Code

-- Cohort Analysis Code

SELECT DATE_TRUNC('week',z.occurred_at) AS "week",
       AVG(z.age_at_event) AS "Average age during week",
       COUNT(DISTINCT CASE WHEN z.user_age > 70 THEN z.user_id ELSE NULL END) AS "10+ weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 70 AND z.user_age >= 63 THEN z.user_id ELSE NULL END) AS "9 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 63 AND z.user_age >= 56 THEN z.user_id ELSE NULL END) AS "8 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 56 AND z.user_age >= 49 THEN z.user_id ELSE NULL END) AS "7 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 49 AND z.user_age >= 42 THEN z.user_id ELSE NULL END) AS "6 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 42 AND z.user_age >= 35 THEN z.user_id ELSE NULL END) AS "5 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 35 AND z.user_age >= 28 THEN z.user_id ELSE NULL END) AS "4 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 28 AND z.user_age >= 21 THEN z.user_id ELSE NULL END) AS "3 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 21 AND z.user_age >= 14 THEN z.user_id ELSE NULL END) AS "2 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 14 AND z.user_age >= 7 THEN z.user_id ELSE NULL END) AS "1 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 7 THEN z.user_id ELSE NULL END) AS "Less than a week"
  FROM (
        SELECT e.occurred_at,
               u.user_id,
               DATE_TRUNC('week',u.activated_at) AS activation_week,
               EXTRACT('day' FROM e.occurred_at - u.activated_at) AS age_at_event,
               EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS user_age
          FROM tutorial.yammer_users u
          JOIN tutorial.yammer_events e
            ON e.user_id = u.user_id
           AND e.event_type = 'engagement'
           AND e.event_name = 'login'
           AND e.occurred_at >= '2014-05-01'
           AND e.occurred_at < '2014-09-01'
         WHERE u.activated_at IS NOT NULL
       ) z

 GROUP BY 1
 ORDER BY 1
 LIMIT 100

Mysql에서도 똑같이 활용할 수 있는 문법인지는 모르겠담

출처