postgresql 한달동안 방문자, 최근 6일동안 방문자, 전체 가입자 수 (월간) 통계
2016. 8. 14. 19:42 - 티메르--한달동안 가입한 유져를 1주일마다 통계
SELECT date_trunc('week', "createdAt") AS "week" , count(*), 0 AS "users"
FROM "user"
WHERE "createdAt" > now() - interval '1 months'
GROUP BY 1
ORDER BY 1;
--주간 방문 수
SELECT date_trunc('week', "lastLogin") AS "week" , count(*) AS "users"
FROM "user"
WHERE "lastLogin" > now() - interval '1 months'
GROUP BY 1
ORDER BY 1;
--최근 6일동안 방문자 통계
SELECT date_trunc('day', "lastLogin") AS "day" , count(*) AS "users"
FROM "user"
WHERE "lastLogin" > now() - interval '1 weeks'
GROUP BY 1
ORDER BY 1;
--전체 가입자 수 (월간)
SELECT date_trunc('month', "createdAt") AS "month" , count(*) AS "users"
FROM "user"
WHERE "createdAt" > now() - interval '1 years'
GROUP BY 1
ORDER BY 1;
--연령별 가입자
SELECT date_trunc('decade', "birthDate") AS "decade" , count(*) AS "users"
FROM "user"
WHERE "birthDate" > now() - interval '100 years'
GROUP BY 1
ORDER BY 1;
--성별별 가입자 카운트
SELECT
COUNT(case when gender='male' then 1 end) as male,
COUNT(case when gender='female' then 1 end) as female
FROM "user";
--한달동안 가입한 유져를 1주일마다 통계
SELECT
COUNT(case when date_trunc('week' , "createdAt") = date_trunc('week', now()) then 1 end) as week1,
COUNT(case when date_trunc('week' , "createdAt") = date_trunc('week', now() - interval '1 weeks') then 1 end) as week2,
COUNT(case when date_trunc('week' , "createdAt") = date_trunc('week', now() - interval '2 weeks') then 1 end) as week3,
COUNT(case when date_trunc('week' , "createdAt") = date_trunc('week', now() - interval '3 weeks') then 1 end) as week4
FROM "user";
'DataBase > PostgreSQL' 카테고리의 다른 글
Postgresql 방문자, 가입자 통계 (개월, 주, 일) (0) | 2016.08.18 |
---|---|
Postgres 대문자 오류 (0) | 2016.08.08 |
댓글 로드 중…
트랙백을 확인할 수 있습니다
URL을 배껴둬서 트랙백을 보낼 수 있습니다