복잡한 사용자 활동 보고서 요구 사항

커뮤니티 구축 데이터 및 보고
21

정말 감사합니다!

제가 사용하려고 하는 사용자 프로필 커스텀 필드는 'NZRAB number’입니다.

image
image1202×937 64 KB

아래의 스크립트로 시도해 보았지만, 당연히 실수를 해서 ‘Undefined function’ 오류가 발생했습니다.

필드 이름에 대문자나 공백이 포함되지 않아야 하는지 확실하지 않았습니다. 에디터는 필드 이름의 공백을 허용하지 않는 것 같아서, 공백 대신 밑줄(_)을 넣었습니다.

또한 필드 이름(field name)과 필드 라벨(field label)을 혼동하고 있는 것일 수도 있습니다. 하지만 그렇다면 라벨 'NZRAB number’에 해당하는 필드 이름이 무엇인지 어떻게 알 수 있는지 모르겠습니다.

-- coverage: 'week', 'all', or '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, cf.value as NZRAB_number
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)
LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and cf.name == "NZRAB number") 
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
SELECT ps.id, ps.username, ps.created_at, ps.NZRAB_number, 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

발생한 오류:

PG::UndefinedFunction: ERROR: operator does not exist: integer == integer
LINE 32: LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and c…
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

이것을 보고 제가 무엇을 하는지 전혀 모른다고 결론 내리셨다면, 그건 맞는 말입니다!

22

코드에서 실행 가능한 예제를 만들 수 있었습니다.

-- coverage: 'week', 'all', or '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, cf.value as nzrab_number
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)
LEFT JOIN user_custom_fields cf ON (u.id = cf.user_id and cf.name = CONCAT('user_field_', (select id::text from user_fields where name='NZRAB number'))) 
GROUP BY u.id, dr.date, dr.week, cf.value
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, ps.NZRAB_number, 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
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
GROUP BY ps.id, ps.username, ps.created_at, ps.nzrab_number, 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

참고로 user_custom_fields의 관계가 다소 이상합니다. 작동하지 않는 경우, 사용할 정확한 이름을 결정하기 위해 별도의 쿼리를 실행해야 할 수 있습니다:

별도의 쿼리로 다음을 실행하세요:

select * from user_custom_fields

결과 집합에서 “NZRAB number” 필드와 일치하는 레코드를 찾아 이름을 확인하세요. 제 경우엔 "user_field_2"였습니다.

그 이름을 확인한 후, 위 쿼리의 다음 줄을:

LEFT JOIN user_custom_fields cf ON (u.id = cf.user_id and cf.name = CONCAT('user_field_', (select id::text from user_fields where name='NZRAB number')))

다음과 같이 교체하세요:

LEFT JOIN user_custom_fields cf ON (u.id = cf.user_id and cf.name = <YOUR FIELD NAME>)

아마 이 과정을 거칠 필요가 없을 것이라 생각합니다.

23

훌륭네요 — 그대로 적용해서 제 환경에서도 잘 작동했습니다!

조금 더 욕심내서 여쭤볼게요. 좋아요를 받은 게시물과 그렇지 않은 게시물을 각각 따로 집계하는 것이 기술적으로 가능한가요?

이렇게 묻는 이유는, 8개의 좋아요를 받은 사용자가 그 모든 좋아요를 단일 게시물로 받았는지, 아니면 여러 게시물에 걸쳐 분산되어 받았는지를 구별할 다른 방법이 없기 때문입니다.

또 다른 질문은, NZRAB_number 사용자 정의 필드가 두 사용자가 입력한 숫자 사이에 있는 사람들로만 출력을 제한하는 방법이 있는지입니다. (이 필드에 텍스트를 입력하거나 아무것도 입력하지 않은 가입자들을 제외하기 위함입니다)

그리고 사용자 ID 옆에 사용자의 실명을 함께 나열할 수 있을까요?