问题陈述
如果您使用 Discourse 游戏化插件 为贡献社区的用户颁发积分，您可能会发现需要根据新出现的趋势调整积分值。在 SailPoint 开发者社区 中，我们使用游戏化插件来支持我们的 大使计划。对社区做出有价值贡献的用户会获得积分，这些积分随后用于确定他们能享受的福利等级。随着社区的增长和贡献数量的大幅增加，我们确定某些类型贡献的原始积分值需要调整。如果您从不重新计算积分，调整积分值的效果很好；但当我们进行用户合并或使用外部游戏化 API 为过去的贡献颁发积分时，经常需要重新计算积分。如果您修改了游戏化积分值并执行重新计算，那么之前使用旧积分值评分的贡献现在将使用新积分值进行评分。如果您希望新积分值仅应用于新的贡献，而保留旧贡献的原有值，这就是一个问题。在本教程中，我将讨论我创建的一种解决方案，确保当您更改积分值并执行重新计算时，用户之前的积分不会被更改。
使用数据探索器计算当前和拟议的积分
导入 SQL 查询
此过程的第一步是将以下 SQL 查询导入到您的数据探索器插件中。该 SQL 查询基于 原始 SQL 查询 进行了修改，增加了分页功能，添加了 user_name 和 name 列，并按 user_id 排序。此查询将生成给定时间范围内使用给定积分值的用户得分表。
-- [params]
-- date :start_date
-- date :end_date
-- int :day_visited_score_value = 0
-- int :time_read_score_value = 0
-- int :posts_read_score_value = 0
-- int :posts_created_score_value = 6
-- int :topics_created_score_value = 0
-- int :likes_received_score_value = 3
-- int :likes_given_score_value = 0
-- int :solutions_score_value = 60
-- int :flag_created_score_value = 6
-- int :user_invited_score_value = 0
-- int :limit = 1000
-- int :page = 0
WITH visits AS (
SELECT
uv.user_id,
COUNT(*) AS user_visits,
COUNT(*) * :day_visited_score_value AS visits_score
FROM user_visits uv
WHERE uv.visited_at BETWEEN :start_date AND :end_date
GROUP BY uv.user_id
),
time_read AS (
SELECT
uv.user_id,
SUM(uv.time_read) /3600 AS time_read,
SUM(uv.time_read) /3600 * :time_read_score_value AS time_read_score
FROM user_visits uv
WHERE uv.visited_at BETWEEN :start_date AND :end_date
AND uv.time_read >= 60
GROUP BY uv.user_id
),
posts_read AS (
SELECT
uv.user_id,
SUM(uv.posts_read) AS posts_read,
SUM(uv.posts_read) /100 * :posts_read_score_value AS posts_read_score
FROM user_visits uv
WHERE uv.visited_at BETWEEN :start_date AND :end_date
AND uv.posts_read >= 5
GROUP BY uv.user_id
),
posts_created AS (
SELECT
p.user_id,
COUNT(*) AS posts_created,
COUNT(*) * :posts_created_score_value AS posts_created_score
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.wiki IS FALSE
AND p.created_at::date BETWEEN :start_date AND :end_date
GROUP BY p.user_id
),
topics_created AS (
SELECT
t.user_id,
COUNT(*) AS topics_created,
COUNT(*) * :topics_created_score_value AS topics_created_score
FROM topics t
WHERE t.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND t.created_at::date BETWEEN :start_date AND :end_date
GROUP BY t.user_id
),
likes_received AS (
SELECT
p.user_id,
COUNT(*) AS likes_received,
COUNT(*) * :likes_received_score_value AS likes_received_score
FROM post_actions pa
INNER JOIN posts p ON p.id = pa.post_id
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.wiki IS FALSE
AND post_action_type_id = 2
AND pa.created_at::date BETWEEN :start_date AND :end_date
GROUP BY p.user_id
),
likes_given AS (
SELECT
pa.user_id AS user_id,
COUNT(*) AS likes_given,
COUNT(*) * :likes_given_score_value AS likes_given_score
FROM post_actions pa
INNER JOIN posts p ON p.id = pa.post_id
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.wiki IS FALSE
AND post_action_type_id = 2
AND pa.created_at::date BETWEEN :start_date AND :end_date
GROUP BY pa.user_id
),
solutions AS (
SELECT
p.user_id,
COUNT(st.topic_id) AS solutions,
COUNT(st.topic_id) * :solutions_score_value AS solutions_score
FROM discourse_solved_solved_topics st
INNER JOIN topics t ON st.topic_id = t.id
INNER JOIN posts p ON p.id = st.answer_post_id
WHERE p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.user_id <> t.user_id
AND st.updated_at::date BETWEEN :start_date AND :end_date
GROUP BY p.user_id
),
flags AS (
SELECT
r.created_by_id AS user_id,
COUNT(*) AS flags,
COUNT(*) * :flag_created_score_value AS flags_score
FROM reviewables r
WHERE created_at::date BETWEEN :start_date AND :end_date
AND status = 1
GROUP BY user_id
),
invites AS (
SELECT
inv.invited_by_id AS user_id,
SUM(inv.redemption_count) AS invites,
(SUM(inv.redemption_count) * :user_invited_score_value)::int AS invites_score
FROM invites inv
WHERE inv.created_at::date BETWEEN :start_date AND :end_date
AND inv.redemption_count > 0
GROUP BY inv.invited_by_id
)
SELECT
u.id AS user_id,
u.username AS username,
u.name AS name,
(
COALESCE(v.visits_score,0) +
COALESCE(tr.time_read_score,0) +
COALESCE(pr.posts_read_score,0) +
COALESCE(pc.posts_created_score,0) +
COALESCE(tc.topics_created_score,0) +
COALESCE(lr.likes_received_score,0) +
COALESCE(lg.likes_given_score,0) +
COALESCE(s.solutions_score,0) +
COALESCE(f.flags_score,0) +
COALESCE(i.invites_score,0)
) AS "Total Cheers",
COALESCE(v.user_visits,0) || ' (' || COALESCE(v.visits_score,0) || ')' AS "Visits (cheers)",
COALESCE(tr.time_read,0) || 'hrs' || ' (' || COALESCE(tr.time_read_score,0) || ')' AS "Time Read (cheers)",
COALESCE(pr.posts_read,0) || ' (' || COALESCE(pr.posts_read_score,0) || ')' AS "Posts Read (cheers)",
COALESCE(pc.posts_created,0) || ' (' || COALESCE(pc.posts_created_score,0) || ')' AS "Posts Created (cheers)",
COALESCE(tc.topics_created,0) || ' (' || COALESCE(tc.topics_created_score,0) || ')'AS "Topics Created (cheers)",
COALESCE(lr.likes_received,0) || ' (' || COALESCE(lr.likes_received_score,0) || ')' AS "Likes Received (cheers)",
COALESCE(lg.likes_given,0) || ' (' || COALESCE(lg.likes_given_score,0) || ')' AS "Likes Given (cheers)",
COALESCE(s.solutions,0) || ' (' || COALESCE(s.solutions_score,0) || ')'AS "Solutions (cheers)",
COALESCE(f.flags,0) || ' (' || COALESCE(f.flags_score,0) || ')' AS "Agreed Flags (cheers)",
COALESCE(i.invites,0) || ' (' || COALESCE(i.invites_score,0) || ')' AS "Invites Redeemed (cheers)"
FROM users u
LEFT JOIN visits v ON v.user_id = u.id
LEFT JOIN posts_read pr USING (user_id)
LEFT JOIN time_read tr USING (user_id)
LEFT JOIN flags f USING (user_id)
LEFT JOIN posts_created pc USING (user_id)
LEFT JOIN topics_created tc USING (user_id)
LEFT JOIN likes_given lg USING (user_id)
LEFT JOIN likes_received lr USING (user_id)
LEFT JOIN solutions s USING (user_id)
LEFT JOIN invites i USING (user_id)
WHERE u.id > 0
AND u.id NOT IN (SELECT user_id FROM group_users WHERE group_id = 3)
ORDER BY user_id ASC
OFFSET :page * :limit
LIMIT :limit
使用当前和拟议的积分值测试查询
导入 SQL 查询后，您应使用反映您不希望更改积分值的时期的时间段进行测试。对我们来说，这意味着从论坛开始（2020-01-01）到我们希望更新积分值的前一天（2024-05-15）。更新积分值以反映您当前的积分值，并测试查询以确保数值看起来正确。
接下来，保持相同的时间段，但将积分值更改为您希望的值。注意同一用户的数值差异。在此示例中，Neil 使用当前积分值共有 1104 积分（即 cheers），而使用新积分值后，他的积分增加到 1245。我们需要一种方法来计算调整前后的积分差异，并调整用户的积分，以便在实施新积分值后，他们不会看到积分的增加或减少。
使用外部游戏化 API 调整积分
以 Neil 为例，他当前的积分是 1104，拟议更改后的积分将是 1245。为了确保他看不到积分的增加或减少，我们需要计算这两个值的差异，然后将该差异分配给他的得分。差异计算公式为 currentValue - newValue，在 Neil 的案例中为 1104 - 1245 = -141。这意味着需要给 Neil 分配 -141 积分。我们可以使用 外部游戏化 API 将这些积分分配给他的用户账户，以便反映在他的排行榜得分中。我们需要进行的 API 调用如下：
curl --location 'https://my.discourse.com/admin/plugins/gamification/score_events' \
--header 'Api-Key: <your key>' \
--header 'Api-Username: <your username>' \
--header 'Content-Type: application/json' \
--data '{
"user_id": "101",
"date": "2024-05-15",
"points": "-141",
"description": "Gamification point adjustment"
}'
执行此 API 调用将调整 Neil 的总得分，这样拟议的积分值将仅影响新的可计分事件，而旧可计分事件的任何差异将通过调整抵消。现在，我们只需将此过程应用于社区中的每个用户。
自动化积分调整
为了自动化此过程，我创建了一个 Python 脚本，利用 Discourse API 运行 SQL 查询，计算差异，并将调整分配给每个用户。该脚本还会输出一个 CSV 文件，记录所有收到调整的用户。脚本中的注释很好地描述了您需要更改的值以及其工作原理。如果有关于如何使其运行的任何问题，请在下方留言。
要求
您需要较新版本的 Python 3。我使用的是 Python 3.9.6。您还需要从 PyPi 安装 requests 包。
python3 -m pip install requests
脚本
import requests
import secrets
from datetime import date
from datetime import timedelta
import time
import csv
import json
# API 凭据存储在秘密文件中。您如何提供秘密取决于您。
api_key = secrets.api_key
api_username = secrets.api_username
host = 'https://developer.sailpoint.com/discuss' # 您的 Discourse 实例的主机名
# 指向论坛中描述当前和拟议积分更改的主题的链接。这是一个很好的做法，
# 这样您就可以回顾它来了解发生了什么变化，同时让您的用户了解这些变化是透明的。
point_adjustment_link = 'https://developer.sailpoint.com/discuss/t/update-to-ambassador-point-values-may-15th-2024/54178'
# 您希望赋予 CSV 文件的名称。创建时将附加 start_date 和 end_date。
csv_name = 'sailpoint_developer_community_point_adjustment'
max_requests_per_minute = 200 # 每分钟的最大请求数。请将其调整为您 Discourse API 速率限制以下或相等的值。
start_date = '2020-01-01' # 论坛在此日期之后开始，这将确保考虑所有积分。
# 此日期不能是今天，否则查询会出错。此公式将使用昨天的日期。如果需要，
# 您可以将其修改为更早的日期。
end_date = (date.today() - timedelta(days = 1)).strftime('%Y-%m-%d')
# 您的 SQL 查询的 ID。您可以通过点击数据探索器插件中的查询，然后在 URL 中查找
# "id" 参数来找到它。例如，我的 ID 是 66，如下面的 URL 所示：
# https://developer.sailpoint.com/discuss/admin/plugins/explorer?id=66
query_id = '66'
# 这些是您游戏化得分设置的当前积分值。这四个适用于我们的论坛，
# 但如果您需要，可以添加其他可计分的积分类型。
current_likes_received_score_value = '3'
current_solutions_score_value = '60'
current_posts_created_score_value = '6'
current_flag_created_score_value = '6'
# 这些是拟议的积分值，将应用于所有新的积分。
new_likes_received_score_value = '6'
new_solutions_score_value = '60'
new_posts_created_score_value = '3'
new_flag_created_score_value = '6'
# 此函数运行 SQL 查询以获取所有用户的积分值。它将自动分页直到没有更多记录。
def getPointValues(query_id, start_date, end_date, limit, likes_received_score_value, solutions_score_value, posts_created_score_value, flag_created_score_value):
rows = []
page = 0
headers = {
'Api-Key': api_key,
'Api-Username': api_username,
'Content-Type': 'application/x-www-form-urlencoded'
}
payload = f'params={{"start_date":"{start_date}","end_date":"{end_date}","limit":"{str(limit)}","page":"{str(page)}","likes_received_score_value":"{likes_received_score_value}","solutions_score_value":"{solutions_score_value}","posts_created_score_value":"{posts_created_score_value}","flag_created_score_value":"{flag_created_score_value}"}}'
r = requests.post(f'{host}/admin/plugins/explorer/queries/{query_id}/run', headers=headers, data=payload).json()
rows += r['rows']
# 分页直到到达最后一页
while len(r['rows']) == limit:
page += 1
payload = f'params={{"start_date":"{start_date}","end_date":"{end_date}","limit":"{str(limit)}","page":"{str(page)}","likes_received_score_value":"{likes_received_score_value}","solutions_score_value":"{solutions_score_value}","posts_created_score_value":"{posts_created_score_value}","flag_created_score_value":"{flag_created_score_value}"}}'
r = requests.post(f'{host}/admin/plugins/explorer/queries/{query_id}/run', headers=headers, data=payload).json()
rows += r['rows']
return rows
# 创建需要进行的积分调整数组。如果用户积分为 0，或者积分没有差异，
# 它们将被过滤掉，这样我们就不需要进行不必要的 API 调用。该数组将包含进行
# 外部游戏化 API 调用以及填充 CSV 文件所需的所有信息。
def calculatePointAdjustments(old_values, new_values):
point_adjustments = []
for row in old_values:
user_id = row[0]
username = row[1]
name = row[2]
current_cheers = row[3]
for i in range(len(new_values)):
new_cheers = new_values[i][3]
if new_values[i][0] == user_id:
# 仅当积分有差异时才计算差值。
if current_cheers != new_cheers:
point_adjustments.append({
'user_id': user_id,
'username': username,
'name': name,
'current_points': current_cheers,
'new_points': new_cheers,
'difference': current_cheers - new_cheers,
'external_gamification_point_id': -1
})
new_values.pop(i) # 从列表中移除元素以加快处理速度
break
else:
new_values.pop(i) # 从列表中移除元素以加快处理速度
break
return point_adjustments
# 将积分调整数组保存为 CSV 文件以供记录
def exportCSV(differences):
fields = ['User ID', 'Username', 'Name', 'Current Points', 'New Points', 'Difference', 'External Gamification Point ID']
rows = []
for diff in differences:
rows.append([diff['user_id'], diff['username'], diff['name'], diff['current_points'], diff['new_points'], diff['difference'], diff['external_gamification_point_id']])
with open(f'{csv_name}_{start_date}_to_{end_date}.csv', 'w') as f:
csv_writer = csv.writer(f)
csv_writer.writerow(fields)
csv_writer.writerows(rows)
# 执行游戏化 API 以分配每个用户的积分差异。此函数将故意减速
# 以保持在您的速率限制内。如果达到速率限制，此函数将继续等待直到
# 速率限制过期并可以继续。如果在任何时候 API 调用出现错误，则成功完成的
# 积分调整将保存到 CSV 文件，这样您就知道哪些已完成以及需要从哪里恢复。
def assignPointAdjustments(point_adjustments):
assigned_point_adjustments = []
# 自我速率限制以避免影响其他集成
sleep_time = 60 / max_requests_per_minute # 每次调用之间等待的秒数，以保持在最大请求限制内
endpoint = f'{host}/admin/plugins/gamification/score_events'
headers = {
'Api-Key': api_key,
'Api-Username': api_username,
'Content-Type': 'application/json'
}
for adjustment in point_adjustments:
payload = json.dumps({
"user_id": str(adjustment['user_id']),
"date": end_date,
"points": str(adjustment['difference']),
"description": f'Adjusting points based on new gamification point values. Please see {point_adjustment_link}.'
})
start = time.time() # 跟踪执行时间。由于 API 调用需要几毫秒，不要将此时间计入最大请求等待时间。
try:
r = requests.post(endpoint, headers=headers, data=payload)
except Exception as e:
# 如果失败，保存已分配的调整到 CSV 文件，这样我们就知道哪些已经完成
exportCSV(assigned_point_adjustments)
raise
# 如果最大请求数低于 300，我们不应该遇到速率限制。如果我们恰好遇到速率限制，请适当处理。
while r.status_code == 429:
wait_time = r.json()["extras"]["wait_seconds"] + 1
print(f'Hit rate limit. Sleeping for {wait_time} seconds')
time.sleep(wait_time)
try:
r = requests.post(endpoint, headers=headers, data=payload)
adjustment['external_gamification_point_id'] = r.json()["id"]
except Exception as e:
# 如果失败，保存已分配的调整到 CSV 文件，这样我们就知道哪些已经完成
exportCSV(assigned_point_adjustments)
raise
if r.status_code != 200:
exportCSV(assigned_point_adjustments)
print(f'The last request returned a {r.status_code} error with the following error message\n{r.text}')
print('Aborting adjustments and writing the successful adjustments to file')
raise Exception("HTTP Error")
end = time.time()
if end - start < sleep_time:
# 仅当请求时间小于最大请求等待时间时才等待
time.sleep(sleep_time - (end - start))
adjustment['external_gamification_point_id'] = r.json()["id"]
print(f'Assigned {adjustment["username"]} {adjustment["difference"]} points')
assigned_point_adjustments.append(adjustment)
return assigned_point_adjustments
limit = 1000 # 这是 SQL 查询的最大限制。
current_values = getPointValues(query_id, start_date, end_date, limit, current_likes_received_score_value, current_solutions_score_value, current_posts_created_score_value, current_flag_created_score_value)
new_values = getPointValues(query_id, start_date, end_date, limit, new_likes_received_score_value, new_solutions_score_value, new_posts_created_score_value, new_flag_created_score_value)
point_adjustments = calculatePointAdjustments(current_values, new_values)
assigned_point_adjustments = assignPointAdjustments(point_adjustments)
exportCSV(assigned_point_adjustments)
运行积分调整的步骤
现在我们已经准备好了所有部分，您需要按照以下步骤执行积分调整过程。建议您在 Discourse 环境的测试实例中首先测试此过程，以便解决任何问题。
- 更新 Python 脚本中的变量为您所需的值。
- 将游戏化设置中的积分值更改为新值。
- 重新计算“所有时间”的得分。
- 运行 Python 脚本并等待其完成。根据您的系统中有多少用户以及您的速率限制，这可能需要几分钟才能完成。我们有 2,200 个用户需要调整。在每分钟 200 次请求的速率限制下，大约需要 15 分钟才能完成。
- 完成后，再次重新计算“所有时间”的得分，以确保安全。
- 将 CSV 文件保存在安全的地方以备记录，以防您需要回滚任何更改。
积分重新计算可能需要几个小时才能完成。您需要耐心等待调整生效。一旦重新计算完成，您现在已成功调整了用户的积分，未来的积分将根据新的积分值进行计算。
未来进行更多调整
如果您在任何时候需要调整积分值，可以遵循相同的流程。唯一的区别是，您需要将 start_date 设置为上次运行积分调整时的 end_date。例如，我第一次运行此积分调整时，将 end_date 设置为 2024-05-15。这确保了从我的论坛开始到 2024-05-15 之间获得的所有积分都被调整以保留旧值。假设一年后，我想再次更改积分值。现在我需要将 start_date 设置为 2024-05-15，将 end_date 设置为 2025-05-15。这将确保积分调整仅适用于该时间段，而不会覆盖第一次运行积分调整时的任何内容。