56,673
社区成员
发帖
与我相关
我的任务
分享
select uid,max(b) as b,LOCATE(0,REVERSE(REPLACE(GROUP_CONCAT(c order by b),',','')))-1 as s from a group by uid ORDER BY b
思路,把c字段按b排序拼接,翻转后找见0第一次出现的位置-1,就是最新的长度
#EXPLAIN
SELECT
uid,
MAX(b) AS b,
POSITION(0 IN GROUP_CONCAT(c ORDER BY b DESC SEPARATOR ""))-1 AS num
FROM t
GROUP BY t.uid
+------+------+------+
| uid | b | num |
+------+------+------+
| 1 | 7 | 4 |
| 2 | 7 | 2 |
| 3 | 7 | 4 |
+------+------+------+
3 rows in set (0.00 sec)
========================================
256内存,单CPU,VBox虚拟机实验 -> 120W条记录查询示例如下:
MariaDB [test]> select count(*) from t;
+----------+
| count(*) |
+----------+
| 1200018 |
+----------+
1 row in set (0.34 sec)
MariaDB [test]> SELECT uid, MAX(b) AS b, POSITION(0 IN GROUP_CONCAT(c ORDER BY b DESC SEPARATOR ""))-1 AS num FROM t GROUP BY t.uid;
+------+--------+------+
| uid | b | num |
+------+--------+------+
| 1 | 400006 | 0 |
| 2 | 400006 | 0 |
| 3 | 400006 | 0 |
+------+--------+------+
3 rows in set (0.87 sec)
模拟数据生成方式
#创建存储过程
CREATE PROCEDURE test_insert ()
#开始
BEGIN
#定义变量
DECLARE i INT DEFAULT 1;
DECLARE b INT DEFAULT 8;
#条件判断
WHILE i<400000 #400000*3=1200000
#执行
DO
#SQL
INSERT INTO t(uid, b, c) VALUES
(1, b, FLOOR(RAND()*10%2)),
(2, b, FLOOR(RAND()*10%2)),
(3, b, FLOOR(RAND()*10%2));
#变量增加
SET i=i+1;
SET b=b+1;
#结束循环
END WHILE ;
#提交
commit;
#结束
END;
#执行
CALL test_insert();
#删除存储过程
drop procedure test_insert ;
SELECT
t.uid,
MAX(t.b) AS b,
MAX(t.b) - MAX(r.max) AS last_b
FROM
`t`
JOIN
(SELECT
uid,
MAX(b) AS `max`
FROM
`t`
WHERE c = 0
GROUP BY uid) AS r ON r.uid = t.uid
WHERE t.c = 1
GROUP BY t.uid