如何用 php 和 mysql 计算用户在 Group 中的计费天数？
支付阿杰
结论
这类计费不能只判断用户是否在前 15 天加入。应先根据事件日志还原用户处于成员状态的各个时间段，再累计这些时间段与计费周期的重叠时长。
计费调整也不能只看最后一次操作是添加还是移除，而要比较两个条件：
期初是否已收费本周期累计成员时间是否达到 15 天
处理规则如下：
| 期初已收费 | 累计达到 15 天 | 处理 |
|---|---|---|
| 是 | 是 | 不处理 |
| 是 | 否 | 退款 |
| 否 | 是 | 补收费 |
| 否 | 否 | 不处理 |
最终成员状态可以用于报表或校验，但不能单独作为收费依据。比如，某个用户期初在组内，期间多次进出，期末仍在组内，但累计成员时间不足 15 天。由于期初已经收费，此时仍应退款。
以下是基于 MySQL 8.0 的完整计算方案。
明确计费周期边界
时间区间建议统一采用左闭右开形式：
[period_start, period_end)
例如：
[2026-01-15 00:00:00, 2026-02-15 00:00:00)
其中：
2026-01-15 00:00:00属于本周期。2026-02-15 00:00:00属于下一个周期。- 相邻周期不会重复计算边界时刻。
以下示例假定，系统先处理 period_start 时刻的事件，再生成计费月开始时的成员快照。因此，期初状态取 ActionDate <= period_start 的最后一条记录，周期内的状态变化取：
ActionDate > period_start
AND ActionDate < period_end如果实际业务生成快照的顺序不同，需要相应调整边界条件。
表结构需要稳定的事件顺序
如果同一用户在同一秒内发生多次操作，仅靠 ActionDate 无法判断这些操作的先后顺序。建议增加自增主键：
CREATE TABLE groupuserlog (
LogID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
UserID BIGINT UNSIGNED NOT NULL,
GroupID BIGINT UNSIGNED NOT NULL,
Action TINYINT NOT NULL,
ActionDate DATETIME NOT NULL,
PRIMARY KEY (LogID),
INDEX idx_group_user_date (
GroupID,
UserID,
ActionDate,
LogID
),
INDEX idx_group_date_user (
GroupID,
ActionDate,
UserID
)
);字段含义如下：
Action = 1表示从该事件开始，用户处于组内。Action = -1表示从该事件开始，用户不在组内。
如果现有数据没有 LogID，就必须保证同一 UserID、GroupID 下的 ActionDate 唯一，否则无法可靠还原事件顺序。
MySQL 8.0 查询
下面的查询只返回计费周期内发生过状态变化的用户。整个周期内没有日志的用户无须额外处理，这与题目中的基础计费方式一致。
WITH
params AS (
SELECT
CAST(:period_start AS DATETIME) AS period_start,
CAST(:period_end AS DATETIME) AS period_end,
CAST(:group_id AS UNSIGNED) AS group_id
),
/* 本周期内发生过成员变化的用户 */
changed_users AS (
SELECT DISTINCT l.UserID
FROM groupuserlog AS l
CROSS JOIN params AS p
WHERE l.GroupID = p.group_id
AND l.ActionDate > p.period_start
AND l.ActionDate < p.period_end
),
/* 计费周期开始时的成员状态 */
start_state AS (
SELECT
u.UserID,
COALESCE(
(
SELECT l2.Action
FROM groupuserlog AS l2
CROSS JOIN params AS p2
WHERE l2.GroupID = p2.group_id
AND l2.UserID = u.UserID
AND l2.ActionDate <= p2.period_start
ORDER BY l2.ActionDate DESC, l2.LogID DESC
LIMIT 1
),
-1
) AS initial_state
FROM changed_users AS u
),
/* 加入一个人工生成的期初事件 */
timeline AS (
SELECT
s.UserID,
p.period_start AS event_time,
0 AS sort_id,
s.initial_state AS member_state
FROM start_state AS s
CROSS JOIN params AS p
UNION ALL
SELECT
l.UserID,
l.ActionDate AS event_time,
l.LogID AS sort_id,
l.Action AS member_state
FROM groupuserlog AS l
INNER JOIN changed_users AS u
ON u.UserID = l.UserID
CROSS JOIN params AS p
WHERE l.GroupID = p.group_id
AND l.ActionDate > p.period_start
AND l.ActionDate < p.period_end
),
/* 每个事件的状态持续到下一事件或计费周期结束 */
segments AS (
SELECT
t.UserID,
t.member_state,
t.event_time AS segment_start,
COALESCE(
LEAD(t.event_time) OVER (
PARTITION BY t.UserID
ORDER BY t.event_time, t.sort_id
),
p.period_end
) AS segment_end
FROM timeline AS t
CROSS JOIN params AS p
),
/* 只累计处于组内的区间 */
usage_by_user AS (
SELECT
s.UserID,
SUM(
CASE
WHEN s.member_state = 1
THEN TIMESTAMPDIFF(
SECOND,
s.segment_start,
s.segment_end
)
ELSE 0
END
) AS member_seconds
FROM segments AS s
GROUP BY s.UserID
),
/* 期末状态，用于展示和校验 */
end_state AS (
SELECT
u.UserID,
COALESCE(
(
SELECT l3.Action
FROM groupuserlog AS l3
CROSS JOIN params AS p3
WHERE l3.GroupID = p3.group_id
AND l3.UserID = u.UserID
AND l3.ActionDate < p3.period_end
ORDER BY l3.ActionDate DESC, l3.LogID DESC
LIMIT 1
),
-1
) AS final_state
FROM changed_users AS u
)
SELECT
u.UserID,
ss.initial_state,
es.final_state,
u.member_seconds,
ROUND(u.member_seconds / 86400, 4) AS member_days,
CASE
WHEN ss.initial_state = 1
AND u.member_seconds < 15 * 86400
THEN 'REFUND'
WHEN ss.initial_state <> 1
AND u.member_seconds >= 15 * 86400
THEN 'CHARGE'
ELSE 'NONE'
END AS billing_action
FROM usage_by_user AS u
INNER JOIN start_state AS ss
ON ss.UserID = u.UserID
INNER JOIN end_state AS es
ON es.UserID = u.UserID
ORDER BY u.UserID;PDO 参数可以这样传入：
$stmt->execute([
'period_start' => '2026-01-15 00:00:00',
'period_end' => '2026-02-15 00:00:00',
'group_id' => 123,
]);计算示例
假设计费周期为：
2026-01-15 00:00:00
到
2026-02-15 00:00:00
某用户期初在组内，之后发生了以下状态变化：
01-20 移除
01-25 添加
02-02 移除
有效成员区间为：
01-15 到 01-20
01-25 到 02-02
查询会分别计算这两个区间的秒数并求和，而不是仅根据最后一次移除操作判断是否收费。
如果累计时间不足 15 天：
initial_state = 1，表示期初已经收费。- 处理结果为
REFUND。
如果另一个用户期初不在组内，加入后在本周期累计达到 15 天：
initial_state = -1，表示期初没有收费。- 处理结果为
CHARGE。
在 PHP 中处理计费
不要在遍历查询结果时直接修改余额。可以先生成计费调整记录，并为“Group + User + 计费周期 + 调整类型”添加唯一约束，避免任务重跑时重复收费或退款。
foreach ($rows as $row) {
switch ($row['billing_action']) {
case 'CHARGE':
createBillingAdjustment(
groupId: $groupId,
userId: (int) $row['UserID'],
periodStart: $periodStart,
type: 'CHARGE'
);
break;
case 'REFUND':
createBillingAdjustment(
groupId: $groupId,
userId: (int) $row['UserID'],
periodStart: $periodStart,
type: 'REFUND'
);
break;
case 'NONE':
break;
}
}计费任务和调整记录的写入应放在同一个事务中。如果结算期间仍可能写入成员日志，还需要设置固定的结算截止时间，或者在一致性快照中完成计算。
明确“15 天”的业务定义
上述查询将 15 天定义为：
15 × 24 × 60 × 60 秒
也就是累计满 360 小时。通常，这比统计涉及的日期数量更准确。
如果业务规则是“跨过一个自然日就算一天”，或者“一天内只要属于过 Group 就算一天”，计算方式会完全不同，需要先按日期拆分并去重。这两种规则不能混用。
还要处理时区和夏令时问题。如果业务覆盖多个时区，建议使用 UTC 统一存储日志，展示数据或按自然日结算时，再转换到 Group 所属时区。
其他注意事项
- 不要使用
SUM(Action)判断成员状态。重复添加或重复移除会使结果失真。 - 如果日志中连续出现两个
Action = 1，或者连续出现两个Action = -1，查询仍可运行，但这种情况通常说明上游产生了重复事件，需要另行检查。 - 如果没有任何历史记录，示例默认用户期初不在 Group。若系统另有当前成员表，应使用该表或可靠的期初快照确定初始状态。
- 随着历史日志增加，反复向前查找期初状态会越来越慢。可以为每个计费周期保存不可变的期初成员快照，之后只扫描本周期事件。
- 最终结算应比较“本周期应该收费”与“本周期已经收费”的差异。这样，即使用户在周期内多次加入和移除，也不会因为最后一次事件的类型而生成错误账单。
备注：内容仅供参考。