# 如何在不追溯更新旧得分操作的积分值的情况下调整游戏化积分

**URL:** <https://meta.discourse.org/t/how-to-adjust-gamification-points-without-retroactively-updating-point-values-for-old-score-able-actions/308447>\
**Category:** Administrators\
**Tags:** gamification\
**Created:** [2024 年5 月 17 日 15:36 UTC](https://meta.discourse.org/t/how-to-adjust-gamification-points-without-retroactively-updating-point-values-for-old-score-able-actions/308447 "2024-05-17T15:36:09Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

作者： ![colin.mckibben](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/colin.mckibben/32/307951_2.png) [@colin.mckibben](https://meta.discourse.org/u/colin.mckibben)\
发布日期： [2024 年5 月 17 日 15:36 UTC](https://meta.discourse.org/t/how-to-adjust-gamification-points-without-retroactively-updating-point-values-for-old-score-able-actions/308447/1 "2024-05-17T15:36:09Z")

</div>

# 问题陈述

如果您使用 [Discourse 游戏化插件](https://meta.discourse.org/t/discourse-gamification-plugin/225916) 为贡献社区的用户颁发积分，您可能会发现需要根据新出现的趋势调整积分值。在 [SailPoint 开发者社区](https://developer.sailpoint.com/discuss) 中，我们使用游戏化插件来支持我们的 [大使计划](https://developer.sailpoint.com/ambassadors/)。对社区做出有价值贡献的用户会获得积分，这些积分随后用于确定他们能享受的福利等级。随着社区的增长和贡献数量的大幅增加，我们确定某些类型贡献的原始积分值需要调整。如果您从不重新计算积分，调整积分值的效果很好；但当我们进行用户合并或使用外部游戏化 API 为过去的贡献颁发积分时，经常需要重新计算积分。如果您修改了游戏化积分值并执行重新计算，那么之前使用旧积分值评分的贡献现在将使用新积分值进行评分。如果您希望新积分值仅应用于新的贡献，而保留旧贡献的原有值，这就是一个问题。在本教程中，我将讨论我创建的一种解决方案，确保当您更改积分值并执行重新计算时，用户之前的积分不会被更改。

# 使用数据探索器计算当前和拟议的积分

## 导入 SQL 查询

此过程的第一步是将以下 SQL 查询导入到您的数据探索器插件中。该 SQL 查询基于 [原始 SQL 查询](https://meta.discourse.org/t/gamification-calibration/283800) 进行了修改，增加了分页功能，添加了 `user_name` 和 `name` 列，并按 `user_id` 排序。此查询将生成给定时间范围内使用给定积分值的用户得分表。

```sql
-- [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）。更新积分值以反映您当前的积分值，并测试查询以确保数值看起来正确。

 ![image](https://global.discourse-cdn.com/meta/original/4X/0/6/3/0634245e39ce7e16be2a63eaf2e4f4ff0bc05600.png)

接下来，保持相同的时间段，但将积分值更改为您希望的值。注意同一用户的数值差异。在此示例中，Neil 使用当前积分值共有 1104 积分（即 cheers），而使用新积分值后，他的积分增加到 1245。我们需要一种方法来计算调整前后的积分差异，并调整用户的积分，以便在实施新积分值后，他们不会看到积分的增加或减少。

 ![该图像显示了一个包含各种活动相关积分值（如阅读时间、发布帖子、收到点赞和提供解决方案）的表格用户界面，以及开始和结束日期的过滤器选项，还有包含查询计划和以 JSON 或 CSV 格式导出数据的功能。（由 AI 标注）](https://global.discourse-cdn.com/meta/original/4X/7/b/4/7b48d2a3deed3fdd74450f7a11d6bee75a39a424.png)

# 使用外部游戏化 API 调整积分

以 Neil 为例，他当前的积分是 1104，拟议更改后的积分将是 1245。为了确保他看不到积分的增加或减少，我们需要计算这两个值的差异，然后将该差异分配给他的得分。差异计算公式为 `currentValue - newValue`，在 Neil 的案例中为 1104 - 1245 = -141。这意味着需要给 Neil 分配 -141 积分。我们可以使用 [外部游戏化 API](https://meta.discourse.org/t/how-to-integrate-discourse-gamification-with-an-external-system-redeem-and-award-points/262968) 将这些积分分配给他的用户账户，以便反映在他的排行榜得分中。我们需要进行的 API 调用如下：

```plaintext
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` 包。

```python
python3 -m pip install requests

```

## 脚本

```python
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 环境的测试实例中首先测试此过程，以便解决任何问题。**

1. 更新 Python 脚本中的变量为您所需的值。
2. 将游戏化设置中的积分值更改为新值。
3. 重新计算“所有时间”的得分。
4. 运行 Python 脚本并等待其完成。根据您的系统中有多少用户以及您的速率限制，这可能需要几分钟才能完成。我们有 2,200 个用户需要调整。在每分钟 200 次请求的速率限制下，大约需要 15 分钟才能完成。
5. 完成后，再次重新计算“所有时间”的得分，以确保安全。
6. 将 CSV 文件保存在安全的地方以备记录，以防您需要回滚任何更改。

积分重新计算可能需要几个小时才能完成。您需要耐心等待调整生效。一旦重新计算完成，您现在已成功调整了用户的积分，未来的积分将根据新的积分值进行计算。

# 未来进行更多调整

如果您在任何时候需要调整积分值，可以遵循相同的流程。唯一的区别是，您需要将 `start_date` 设置为上次运行积分调整时的 `end_date`。例如，我第一次运行此积分调整时，将 `end_date` 设置为 **2024-05-15** 。这确保了从我的论坛开始到 **2024-05-15** 之间获得的所有积分都被调整以保留旧值。假设一年后，我想再次更改积分值。现在我需要将 `start_date` 设置为 **2024-05-15** ，将 `end_date` 设置为 **2025-05-15** 。这将确保积分调整仅适用于该时间段，而不会覆盖第一次运行积分调整时的任何内容。

---

_[View the full topic](https://meta.discourse.org/t/how-to-adjust-gamification-points-without-retroactively-updating-point-values-for-old-score-able-actions/308447)._