지정된 기간 내 사용자별 일별, 주별 또는 전체 통계

커뮤니티 구축 데이터 및 보고
1 
-- coverage: 'week', 'all', 또는 'date'
-- [params]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END

이 쿼리를 사용하면 지정된 기간 동안 사용자별 일별, 주별 또는 총통계 데이터를 가져올 수 있습니다.
입력 매개변수:

  • start_date (문자열) 예: ‘2019-12-31’
  • end_date (문자열) 예: ‘2019-12-31’
  • coverage (열거형) 1) 일별인 경우 ‘date’, 2) 주별인 경우 ‘week’, 또는 3) 총계인 경우 ‘all’ 중 하나

출력 필드:

  • id (user_id)
  • username
  • created_at (사용자 생성일)
  • period (‘date’ 커버리지의 경우 날짜 문자열, ‘week’ 커버리지의 경우 주 번호 (예: 12월 26~31일 = 52), ‘all’ 커버리지의 경우 -1)
  • replies (토픽의 첫 번째 게시물이 아닌 게시물의 수)
  • topics
  • likes_received
  • likes_given
  • posts_read
  • time_read
  • visits

이 쿼리의 전체 결과를 액세스하려면(특히 긴 기간에 걸친 일별 데이터의 경우) API를 사용해야 할 가능성이 높습니다. API를 통해 쿼리를 트리거하는 방법에 대한 자세한 내용은 이 게시물을 참조하세요.

이 쿼리를 사용하여 일별 사용자 참여 데이터 세트를 생성할 수 있습니다.

7개의 좋아요