Data Explorer 查询：特定用户组解决主题的百分比

社区 数据与报告
1

大家好，正在使用数据探索器的朋友们！

（请多包涵 @michebs :smiley: - 我还有最后两个问题，希望其他人也能从中受益）

是否有人曾创建过按月份和年份分组、统计特定群组解决主题百分比的数据探索器查询？

结果将如下所示，但最后一列将为百分比：

Screenshot 2021-02-23 at 15.32.39
Screenshot 2021-02-23 at 15.32.39681×118 1.98 KB

3

我们当然可以搞定类似的事情，所有数据都存在。

“购买 % 已解决”……您具体是什么意思？

  • 帖子发布于 1 月
  • “了不起小组”在 2 月获得了一个被采纳的答案
  • 其他小组和用户在 2 月解决了 73 个其他话题

所以，我猜“了不起小组”在 2 月的解决率是 1.35%？

4

假设一个月内有 100 个解决方案。有 20 人属于某个群组，并且他们开始将各自的回复标记为解决方案，总计 20 个。我希望编写一个查询，在脚本中指定他们的主群组 ID，并按月获取数据，以显示本月为 20/100，即 20%。

11

你好，Konrad，抱歉迟复。
我仍在验证一些查询，但借此机会确认一下：这是您期待的结果吗？

year month group_name tt_groups total %
2021 1 team1 40 70 57
2021 1 team2 30 70 43
12

我尝试让查询更加详细，以便未来更易于理解和维护。

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

请告诉我这是否是您期望的结果，或者我是否需要调整某些内容。

Michelle

13

别担心！您真是我的救星！:smiley:

几乎完美！我不需要 tt_groups 和 total 列，只需要百分比数值。至于 group_name 列，由于查询只针对一个组，因此也不需要该列。在查询代码中，我只需指定 primary_group_id，这样它就会仅查找该特定组的解决方案。

14

已调整。:slightly_smiling_face:

-- [params]
-- string :primary_group_id

WITH users_groups AS (
    SELECT 
        user_id, 
        g.id,
        g.name AS 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
15

太棒了！这正是我在找的！