经过几个小时的努力,模拟滞后(我在MySQL 5.6上没有升级选项),发现了两个功能问题,我来这里寻求帮助,现在我完全迷失了方向。
经过长时间的处理,我在存储过程中获得了一个临时表,该表按userid和purchaseDate(示例)的数据顺序如下:
样品获取方式:
SELECT
purchaseId
,userId
,purchaseDate
FROM tempPurchases
WHERE userId > 69
ORDER BY userId, purchaseDate;
+------------+--------+--------------+
| purchaseId | userId | purchaseDate |
+------------+--------+--------------+
| 2074 | 70 | 2018-11-12 |
| 2072 | 70 | 2018-11-30 |
| 2108 | 71 | 2018-01-23 |
| 2114 | 71 | 2018-02-27 |
| 2130 | 71 | 2018-03-07 |
| 2115 | 71 | 2018-04-17 |
| 2105 | 71 | 2018-11-12 |
| 2112 | 71 | 2018-11-30 |
| 2145 | 72 | 2018-01-21 |
| 2141 | 72 | 2018-01-23 |
| 2136 | 72 | 2018-02-01 |
| 2148 | 72 | 2018-03-02 |
| 2158 | 72 | 2018-06-06 |
| 2137 | 72 | 2018-07-04 |
| 2139 | 72 | 2018-07-11 |
| 2153 | 72 | 2018-10-18 |
| 2135 | 72 | 2018-10-25 |
| 2157 | 72 | 2018-11-12 |
| 2147 | 72 | 2018-12-30 |
| 2184 | 73 | 2018-02-21 |
| 2166 | 73 | 2018-03-14 |
| 2176 | 73 | 2018-05-12 |
| 2186 | 73 | 2018-07-09 |
| 2173 | 73 | 2018-08-25 |
| 2162 | 73 | 2018-11-12 |
| 2165 | 73 | 2018-11-30 |
+------------+--------+--------------+
规范是在30天内通过购买获得"奖金"(我使用TIMESTAMPDIFF(DAYS)
和滞后来计算一个日期和前一个日期之间的天数差。这里的问题是,奖金还与前多行进行比较,而不仅仅是上面的行。(a列的逻辑)
例如,在下一个示例中,对于userid 72,purchaseId 2136与2141匹配,但也与2145匹配,当然这是可变的,就像一个人今天购买10次,昨天购买20次一样。
因此,期望的结果是:
+------------+--------+--------------+-----------+------------+--------------------------+
| purchaseId | userId | purchaseDate | days diff | A (streak) | B (streak without reset) |
+------------+--------+--------------+-----------+------------+--------------------------+
| 2074 | 70 | 2018-11-12 | 0 | 0 | 0 |
| 2072 | 70 | 2018-11-30 | 18 | 1 | 1 |
| 2108 | 71 | 2018-01-23 | 0 | 0 | 0 |
| 2114 | 71 | 2018-02-27 | 35 | 0 | 0 |
| 2130 | 71 | 2018-03-07 | 8 | 1 | 1 |
| 2115 | 71 | 2018-04-17 | 41 | 0 | 0 |
| 2105 | 71 | 2018-11-12 | 209 | 0 | 0 |
| 2112 | 71 | 2018-11-30 | 18 | 1 | 1 |
| 2145 | 72 | 2018-01-21 | 0 | 0 | 0 |
| 2141 | 72 | 2018-01-23 | 2 | 1 | 1 |
| 2136 | 72 | 2018-02-01 | 9 | 2 | 2 |
| 2148 | 72 | 2018-03-02 | 29 | 1 | 3 |
| 2158 | 72 | 2018-06-06 | 96 | 0 | 0 |
| 2137 | 72 | 2018-07-04 | 28 | 1 | 1 |
| 2139 | 72 | 2018-07-11 | 7 | 1 | 2 |
| 2153 | 72 | 2018-10-18 | 99 | 0 | 0 |
| 2135 | 72 | 2018-10-25 | 7 | 1 | 1 |
| 2157 | 72 | 2018-11-12 | 18 | 2 | 2 |
| 2147 | 72 | 2018-12-30 | 48 | 0 | 0 |
| 2184 | 73 | 2018-02-21 | 0 | 0 | 0 |
| 2166 | 73 | 2018-03-14 | 21 | 1 | 1 |
| 2176 | 73 | 2018-05-12 | 59 | 0 | 0 |
| 2186 | 73 | 2018-07-09 | 58 | 0 | 0 |
| 2173 | 73 | 2018-08-25 | 47 | 0 | 0 |
| 2162 | 73 | 2018-11-12 | 79 | 0 | 0 |
| 2165 | 73 | 2018-11-30 | 18 | 1 | 1 |
+------------+--------+--------------+-----------+------------+--------------------------+
完全失去了多重联接/左联接和滞后模拟,任何提示、评论或答案都将被珍惜。请不要在评论中怀疑或问任何问题。
问题:考虑到上面几行的"滞后",我如何计算A连胜?我实际的方法是使用游标,但目前两者都不起作用。
更新:
预期的结果是获得"奖金连胜",为相同的用户ID处理以上行。例如:
userId 72在1月1日进行购买(购买1)(第一行值为0)。然后再次(购买2)在10/jan,所以是少于30天:1分(10天)。同样在15/01(购买3),因此购买2(5天)之间的差异为1分,购买1(15天)为1分。总奖金:3(0+1+(1+1))
我相信就这么简单,select中的子查询统计过去30天内的购买次数:
SELECT t.*, (
SELECT COUNT(*)
FROM t AS x
WHERE userId = t.userId
AND purchaseDate < t.purchaseDate
AND purchaseDate >= t.purchaseDate - INTERVAL 30 DAY
) AS purchases_within_30days
FROM t