안녕하세요.
데이터 탐색기를 사용하여 사용자 활동을 추적하는 데 도움이 필요합니다.
입력값: 사용자 이름과 날짜
제가 원하는 쿼리 결과는 다음과 같은 형태입니다.
| Username | Date | topics_created | post_created | like_received | total_words |
|---|---|---|---|---|---|
| User A | 01-10-2019 | ||||
| 02-01-2019 | |||||
| 03-01-2019 | |||||
| … | |||||
| 31-01-2019 | |||||
| User B | 01-10-2019 | ||||
| 02-01-2019 | |||||
| 03-01-2019 | |||||
| … | |||||
| 31-01-2019 | |||||
| User B | 01-10-2019 | ||||
| 02-01-2019 | |||||
| 03-01-2019 | |||||
| … | |||||
| 31-01-2019 | |||||
| User C | 01-10-2019 | ||||
| 02-01-2019 | |||||
| 03-01-2019 | |||||
| … | |||||
| 31-01-2019 | |||||
| … | |||||
| User Z | 01-10-2019 | ||||
| 02-01-2019 | |||||
| 03-01-2019 | |||||
| … | |||||
| 31-01-2019 |
도움 주셔서 감사합니다.
열거한 입력값이 원하는 출력값과 일치하지 않는 것 같습니다. 단일 사용자의 데이터를 가져오려면 아래 쿼리를 시작점으로 사용할 수 있습니다. 이 쿼리는 공통 테이블 표현식(CTE)을 사용하여 쿼리를 읽고 작성하기 쉽게 만듭니다. 동일한 패턴을 따라 결과에 더 많은 데이터를 추가할 수도 있습니다.
확신이 서지 않는 점은 특정 날짜에 대한 데이터의 합계를 원하는지, 아니면 각 날짜에 발생한 작업의 수를 원하는지입니다. 예를 들어, 2019-10-31에 해당 사용자가 사이트 가입日以来 전체 기간 동안 총 20개의 게시글을 작성했는지 알고 싶은 건지, 아니면 2019-10-31에 해당 사용자가 정확히 3개의 게시글을 작성했는지 알고 싶은 건지입니다. 현재 쿼리의 구조상 후자의 결과를 반환합니다.
--[params]
-- string :username
-- date :start_date
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, NOW()::date, INTERVAL '1 day') AS day
),
topics_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS topics
FROM topics t
JOIN target_user tu
ON tu.id = t.user_id
JOIN days
ON t.created_at::date = day
WHERE t.archetype = 'regular'
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts,
SUM(array_length(regexp_split_to_array(raw, '\s'), 1)) AS word_count
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND p.deleted_at IS NULL
GROUP BY day, tu.id
),
likes_received AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_received_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(word_count, 0) AS word_count,
COALESCE(likes_received_count, 0) AS likes_received
FROM days d
LEFT JOIN topics_created tc
ON tc.day = d.day
LEFT JOIN posts_created pc
ON pc.day = d.day
LEFT JOIN likes_received lc
ON lc.day = d.day
ORDER BY d.day
저는 각 날짜에 발생한 작업의 수를 원합니다. 정확히 여러분이 제공해 주신 쿼리와 동일하게요.
도와주셔서 정말 감사합니다, 선생님… 정말 큰 도움이 됩니다..
사용자 활동을 높이기 위해 포럼에서 가장 활발한 사용자를 선정하여 시상하는 챌린지를 진행할 계획입니다.
따라서 챌린지를 더 흥미롭게 만들기 위해 몇 가지 추가 데이터를 확보하고자 합니다.
현재 @simon 님의 쿼리를 통해 보유하고 있는 데이터는 토픽(Topics), 게시물(Posts), 단어 수(Words Count), 받은 좋아요 수(Likes Receive)입니다.
추가로 확보하고 싶은 데이터는 다음과 같습니다:
이 추가 데이터를 확보할 수 있도록 도와주시면 감사하겠습니다.
각 항목의 가중치 설정은 엑셀을 사용하여 진행할 예정입니다. 우승자를 선정하기 위해 각 항목에 가중치를 부여하는 방법에 대해 조언을 구합니다.
이 챌린지는 진지하면서도 재미있게 진행하고자 합니다. 최고의 사용자는 가장 활발하고, 가장 도움이 되며, 가장 인기 있는 사용자의 조합이라고 생각합니다. 
다시 한번 도움을 주셔서 감사합니다.
참고: 그룹 단위로 데이터를 조회할 수 있을까요? 그룹 ID만 입력하면 되도록요. 모든 참가자를 하나의 그룹으로 묶을 계획입니다. 이번에는 참가자별 사용자 ID를 하나씩 입력해야 해서요.
안녕하세요 @manchestermania
맨체스터 출신의 동료입니다 
저희 회사도 직원들이 커뮤니티와 더 활발하게 소통할 수 있도록 비슷한 내부 경진대회를 진행해 보고 있습니다.
경진대회를 실제로 진행해 보셨나요? 데이터를 어떻게 수집하셨는지, 그리고 결국 다양한 참여 지표들을 어떻게 가중치를 두고 평가하셨는지 궁금합니다.
한번 이야기 나눌 수 있으면 좋겠습니다!
감사합니다.
Julia