데이터 익스플로러를 사용 중인 여러분 안녕하세요!
(@michebs, 조금만 기다려 주세요 
- 질문은 두 개만 더 할 겁니다. 다른 분들도 도움이 되길 바랍니다)
특정 그룹이 월별/연별로 해결한 토픽 비율을 계산하는 데이터 익스플로러 쿼리를 작성해 본 적이 있는 분이 계신가요?
결과는 다음과 같을 텐데, 마지막 열이 백분율로 표시되도록 하려 합니다.
이런 작업은 충분히 가능합니다. 필요한 데이터는 모두 존재합니다.
"Buy % solved"가 정확히 무엇을 의미하는지 궁금합니다.
- 게시물이 1월에 작성됨
- ‘awesome’ 그룹이 2월에 채택된 답변을 제공함
- 2월에 다른 그룹과 사용자들에 의해 73개의 다른 주제에 대한 해결책이 제공됨
따라서 ‘awesome’ 그룹의 2월 해결률은 1.35%인 것으로 보입니다.
한 달에 100개의 해결책이 있다고 가정해 보겠습니다. 특정 그룹에 속한 20명의 사용자가 자신의 답변을 해결책으로 표시하기 시작했고, 총 20개의 답변이 그렇게 표시되었습니다. 스크립트에서 그들의 기본 그룹 ID를 지정하고 월별로 데이터를 가져와서, 이번 달에는 20/100 - 20%를 표시하는 쿼리를 작성하고 싶습니다.
콘라드님, 늦어서 죄송합니다.
여전히 몇 가지 쿼리를 검증하고 있지만, 이 기회에 확인해 보겠습니다. 이것이 기다리고 계신 결과인가요?
| year | month | group_name | tt_groups | total | % |
|---|---|---|---|---|---|
| 2021 | 1 | team1 | 40 | 70 | 57 |
| 2021 | 1 | team2 | 30 | 70 | 43 |
쿼리를 더 자세히 만들어 향후 이해와 유지보수가 더 쉽도록 수정해 보았습니다.
WITH users_groups AS (
SELECT
user_id,
g.id,
g.name group_name
FROM users u
INNER JOIN user_actions ua ON ua.user_id = u.id
LEFT JOIN groups g ON g.id = u.primary_group_id
WHERE ua.action_type = 15
GROUP BY user_id, g.id
),
tt_solution_by_month AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS "total"
FROM user_actions ua
WHERE ua.action_type = 15
GROUP BY date_part('year', created_at), date_part('month', created_at)
ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),
tt_solution_groups_by_month AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
ug.group_name,
COUNT(*) AS "tt_groups"
FROM user_actions ua
INNER JOIN users_groups ug ON ug.user_id = ua.user_id
WHERE ua.action_type = 15
GROUP BY ug.group_name, date_part('year', created_at), date_part('month', created_at)
ORDER BY date_part('year', created_at) ASC, date_part('month', created_at), ug.group_name)
SELECT
ts.year,
ts.month,
COALESCE(tsg.group_name,'without group'),
tt_groups,
total,
TRUNC((tt_groups::decimal/total::decimal) *100,1) AS "%"
FROM tt_solution_groups_by_month tsg
INNER JOIN tt_solution_by_month ts
ON ts.year = tsg.year AND ts.month = tsg.month
이것이 기대하시는 결과인지, 아니면 조정해야 할 부분이 있는지 알려주세요.
미셸
걱정 마세요! 여기서는 정말 큰 도움이 되네요!
거의 완벽합니다! tt_groups와 total 컬럼은 필요 없고, 퍼센트 숫자만 있으면 됩니다. group_name 컬럼에 관해서도, 이건 특정 그룹 하나에 대한 쿼리이므로 이 컬럼도 필요 없습니다. 쿼리 코드 안에서 primary_group_id를 직접 지정할 것이므로, 해당 그룹의 솔루션만 검색되도록 할 겁니다.
수정했습니다. 
-- [params]
-- string :primary_group_id
WITH users_groups AS (
SELECT
user_id,
g.id,
g.name group_name
FROM users u
INNER JOIN user_actions ua ON ua.user_id = u.id
LEFT JOIN groups g ON g.id = u.primary_group_id
WHERE ua.action_type = 15
AND u.primary_group_id = :primary_group_id
GROUP BY user_id, g.id
),
tt_solution_by_month AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS "total"
FROM user_actions ua
WHERE ua.action_type = 15
GROUP BY date_part('year', created_at), date_part('month', created_at)
ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),
tt_solution_groups_by_month AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
ug.group_name,
COUNT(*) AS "tt_groups"
FROM user_actions ua
INNER JOIN users_groups ug ON ug.user_id = ua.user_id
WHERE ua.action_type = 15
GROUP BY ug.group_name, date_part('year', created_at), date_part('month', created_at)
ORDER BY date_part('year', created_at) ASC, date_part('month', created_at), ug.group_name)
SELECT
ts.year,
ts.month,
TRUNC((tt_groups::decimal/total::decimal) *100,1) AS "%"
FROM tt_solution_groups_by_month tsg
INNER JOIN tt_solution_by_month ts
ON ts.year = tsg.year AND ts.month = tsg.month
완벽해요! 바로 제가 찾던 거였어요!