修复会员沉睡天数数据.sql
3.07 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
-- 修复会员沉睡天数数据
-- 问题：LastConsumeTime 与有效耗卡 MAX(hksj) 不一致（含零金额耗卡、LastConsumeTime 偏新/偏旧）
-- 解决方案：不限制消耗金额，按有效耗卡 MAX(hksj) 纠正 LastConsumeTime，并按运行时/批量口径重算 SleepDays、SleepStartTime
-- 沉睡阈值默认 60 天（与 MemberInfoUpdateConfig.SleepDaysThresholds 最小值一致）
-- 1. 修复单个会员（陈秋竹）
UPDATE lq_khxx kh
INNER JOIN (
SELECT
hy,
MAX(hksj) as last_consume_time
FROM lq_xh_hyhk
WHERE hymc = '陈秋竹'
AND F_IsEffective = 1
GROUP BY hy
) consume ON kh.F_Id = consume.hy
SET
kh.F_LastConsumeTime = consume.last_consume_time,
kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)),
kh.F_SleepStartTime = CASE
WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) >= 60
AND kh.F_SleepStartTime IS NULL
THEN DATE(consume.last_consume_time)
WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) < 60
THEN NULL
ELSE kh.F_SleepStartTime
END
WHERE kh.khmc = '陈秋竹'
AND kh.F_IsEffective = 1;
-- 2. 批量修复所有会员（根据最新的有效耗卡记录更新 LastConsumeTime）
-- 注意：执行此SQL前请先备份数据库
UPDATE lq_khxx kh
INNER JOIN (
SELECT
hy,
MAX(hksj) as last_consume_time
FROM lq_xh_hyhk
WHERE F_IsEffective = 1
GROUP BY hy
) consume ON kh.F_Id = consume.hy
SET
kh.F_LastConsumeTime = consume.last_consume_time,
kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)),
kh.F_SleepStartTime = CASE
WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) >= 60
AND kh.F_SleepStartTime IS NULL
THEN DATE(consume.last_consume_time)
WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) < 60
THEN NULL
ELSE kh.F_SleepStartTime
END
WHERE kh.F_IsEffective = 1
AND (
-- 纠正 LastConsumeTime 为空、偏旧或偏新的脏数据
kh.F_LastConsumeTime IS NULL
OR kh.F_LastConsumeTime <> consume.last_consume_time
);
-- 3. 无有效耗卡会员的 fallback（LastConsumeTime 置空，SleepDays 按 F_CreateTime 计算）
-- 建议优先调用 API：POST /api/Extend/LqKhxx/BatchUpdateMemberInfo
-- 若需 SQL 手工修复，可参考：
-- UPDATE lq_khxx kh
-- LEFT JOIN (
-- SELECT hy, MAX(hksj) AS last_consume_time
-- FROM lq_xh_hyhk
-- WHERE F_IsEffective = 1
-- GROUP BY hy
-- ) consume ON kh.F_Id = consume.hy
-- SET
-- kh.F_LastConsumeTime = NULL,
-- kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), kh.F_CreateTime)),
-- kh.F_SleepStartTime = CASE
-- WHEN GREATEST(0, DATEDIFF(CURDATE(), kh.F_CreateTime)) >= 60 AND kh.F_SleepStartTime IS NULL
-- THEN DATE(kh.F_CreateTime)
-- WHEN GREATEST(0, DATEDIFF(CURDATE(), kh.F_CreateTime)) < 60
-- THEN NULL
-- ELSE kh.F_SleepStartTime
-- END
-- WHERE kh.F_IsEffective = 1
-- AND consume.hy IS NULL;