基于MySQL和PHP的每周自动加权抽奖数据库设计方案
本文提供一个数据库设计方案，适用于每周自动进行一次加权抽奖的活动，无需用户参与，仅根据用户一周内的下单记录进行加权计算分配奖金。具体规则如下：
- 每周一轮抽奖： 每周固定时间进行一次抽奖。
- 基于下单记录： 抽奖依据为用户在一周内的下单次数和金额。
- 加权分配奖金： 奖池金额的50%根据下单金额和次数加权分配给用户，剩余50%滚动到下一轮。
- 自动开奖： 系统自动在每周特定时间点进行开奖计算。
数据库设计:
我们将使用以下几个表来存储必要的数据：
立即学习“PHP免费学习笔记（深入）”；
-
用户订单表 (user_orders): 存储用户的订单信息。
-
order_id(INT, 主键): 订单ID -
user_id(INT): 用户ID -
order_date(DATETIME): 订单日期和时间 -
order_amount(DECIMAL): 订单金额 -
order_count(INT): 该用户在这一天内的订单数量 (用于处理同一天内多次下单的情况)
-
-
奖池表 (prize_pool): 存储每一轮抽奖的奖池信息。
-
pool_id(INT, 主键): 奖池ID (自增) -
start_date(DATE): 本轮奖池开始日期 (一周的起始日期) -
end_date(DATE): 本轮奖池结束日期 (一周的结束日期) -
prize_amount(DECIMAL): 本轮奖池总金额 -
previous_pool_id(INT): 上一轮奖池ID (用于滚动奖金计算)
-
-
获奖记录表 (winners): 存储每一轮抽奖的获奖结果。
-
winner_id(INT, 主键): 获奖记录ID (自增) -
pool_id(INT): 对应的奖池ID -
user_id(INT): 获奖用户ID -
win_amount(DECIMAL): 获奖金额
-
加权抽奖算法 (SQL):
开奖时，使用以下SQL语句计算用户的加权分数并分配奖金：
<code class="sql">-- 计算用户的加权分数
WITH WeightedScores AS (
SELECT
user_id,
SUM(order_amount * order_count) AS weighted_score
FROM user_orders
WHERE order_date BETWEEN (SELECT start_date FROM prize_pool WHERE pool_id = LAST_INSERT_ID()) AND (SELECT end_date FROM prize_pool WHERE pool_id = LAST_INSERT_ID())
GROUP BY user_id
),
-- 计算总加权分数
TotalWeightedScore AS (
SELECT SUM(weighted_score) AS total_score FROM WeightedScores
)
-- 分配奖金
INSERT INTO winners (pool_id, user_id, win_amount)
SELECT
LAST_INSERT_ID(), -- 获取当前奖池ID
user_id,
ROUND((weighted_score / (SELECT total_score FROM TotalWeightedScore)) * (SELECT prize_amount * 0.5 FROM prize_pool WHERE pool_id = LAST_INSERT_ID()), 2) -- 计算并分配奖金 (保留两位小数)
FROM WeightedScores
WHERE weighted_score > 0;
-- 更新奖池，将剩余50%滚动到下一轮
UPDATE prize_pool
SET prize_amount = prize_amount * 0.5
WHERE pool_id = LAST_INSERT_ID();
-- 创建下一轮奖池
INSERT INTO prize_pool (start_date, end_date, prize_amount, previous_pool_id)
VALUES (DATE_ADD(CURDATE(), INTERVAL 1 DAY), DATE_ADD(CURDATE(), INTERVAL 7 DAY), (SELECT prize_amount * 0.5 FROM prize_pool WHERE pool_id = LAST_INSERT_ID()), LAST_INSERT_ID());</code>
PHP代码 (示例):
PHP代码将负责定时任务的调度和与数据库的交互，这里仅提供一个简单的示例：
<code class="php">// ... 数据库连接代码 ... // 获取上一轮奖池ID $lastPoolId = // ... 从数据库获取 ... // 执行SQL语句进行开奖和奖池更新 $sql = // ... 上面的SQL语句 ... $result = $conn->query($sql); // ... 错误处理和结果输出 ... // 关闭数据库连接 $conn->close();</code>
这个方案需要结合PHP的定时任务调度机制 (例如使用crontab) 来实现每周自动开奖。 记得根据实际情况调整SQL语句和PHP代码。 此外，需要考虑异常处理和数据完整性等方面的问题。
