有一需求,赐教各位大神

一把编程的菜刀 2018-09-20 10:46:06
加精
数据库 a
字段 uid,b, c
其值如下:
uid b c
1 1 1
2 1 1
3 1 1
1 2 1
2 2 1
3 2 1
1 3 0
2 3 1
3 3 0
1 4 1
2 4 0
3 4 1
1 5 1
2 5 0
3 5 1
1 6 1
2 6 1
3 6 1
1 7 1
2 7 1
3 7 1

需求:
求各个UID的c最新连续=1的次数
最后结果是
uid b num
1 7 4次
2 7 2次
3 7 4次
...全文
28435 68 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
68 条回复
切换为时间正序
请发表友善的回复…
发表回复
༺七喜༻ 2021-03-04
  • 打赏
  • 举报
回复
$Tb->field('cast(c as UNSIGNED INTEGER) as c,count(*)')->group('uid')->order("c desc")->select();
惊喜不断 2021-02-07
  • 打赏
  • 举报
回复
触发器技术,一般web攻城狮 ,都不知道有这东西,也是无语.........
惊喜不断 2021-02-07
  • 打赏
  • 举报
回复
这么多年了,还没结帖呢, 这种一看数据就非常庞大,换种思路试试,尝试用触发器每次增加数据即计算一下结果,非常简单。
giào! giào ! 2020-12-27
  • 打赏
  • 举报
回复
rownumber
  • 打赏
  • 举报
回复
如果你是要计算长龙,你直接去看别人源码里的实现方式就好了,数据库设计、数据层、业务层,展示层都一清二楚,比你这般在论坛上瞎子摸象要省事多了。
  • 打赏
  • 举报
回复
你这是要计算长龙吧,彩票方面的知识。
苍穹0113 2020-07-09
  • 打赏
  • 举报
回复
发言被禁了吗?
有戏说戏 2020-04-27
  • 打赏
  • 举报
回复
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,就是最新的长度
huangfuyk 2020-03-17
  • 打赏
  • 举报
回复
可以可以,学到了。
XM3990205HZ2552 2020-03-05
  • 打赏
  • 举报
回复
接分 我一点分都没有了,给点分我吧
evanweng 2020-01-19
  • 打赏
  • 举报
回复
引用 77 楼 evanweng 的回复:
[quote=引用 76 楼 evanweng 的回复:]
[quote=引用 75 楼 evanweng 的回复:]
先加个自增id
select t1.uid, count(1) from t t1 where t1.c = 1 and t1.id > (
select max(t2.id) from t t2 where t2.uid = t1.uid and t2.c = 0
) group by t1.uid
数据量大的话,给uid和c加索引[/quote]上面那个有点慢,改了一下,十万条数据,不加索引,100毫秒左右
select t1.uid, count(1) from t t1,
(select t2.uid, max(t2.id) id from t t2 where t2.c = 0 group by t2.uid) t3
where t1.c = 1 and t1.uid = t3.uid and t1.id > t3.id group by t1.uid[/quote]再考虑c=0不存在的情况,加个左连接,完美!
select t1.uid, count(1) from t t1
left join (select t2.uid, max(t2.id) id from t t2 where t2.c = 0 group by t2.uid) t3 on t1.uid = t3.uid
where t1.c = 1 and t1.id > ifnull(t3.id,0) group by t1.uid
evanweng 2020-01-19
  • 打赏
  • 举报
回复
引用 76 楼 evanweng 的回复:
[quote=引用 75 楼 evanweng 的回复:]
先加个自增id
select t1.uid, count(1) from t t1 where t1.c = 1 and t1.id > (
select max(t2.id) from t t2 where t2.uid = t1.uid and t2.c = 0
) group by t1.uid
数据量大的话,给uid和c加索引[/quote]上面那个有点慢,改了一下,十万条数据,不加索引,100毫秒左右
select t1.uid, count(1) from t t1,
(select t2.uid, max(t2.id) id from t t2 where t2.c = 0 group by t2.uid) t3
where t1.c = 1 and t1.uid = t3.uid and t1.id > t3.id group by t1.uid
evanweng 2020-01-19
  • 打赏
  • 举报
回复
引用 75 楼 evanweng 的回复:
先加个自增id
select t1.uid, count(1) from t t1 where t1.c = 1 and t1.id > (
select max(t2.id) from t t2 where t2.uid = t1.uid and t2.c = 0
) group by t1.uid
数据量大的话,给uid和c加索引
evanweng 2020-01-19
  • 打赏
  • 举报
回复
先加个自增id
select t1.uid, count(1) from t t1 where t1.c = 1 and t1.id > (
select max(t2.id) from t t2 where t2.uid = t1.uid and t2.c = 0
) group by t1.uid
青鬆下的坚躯 2020-01-18
  • 打赏
  • 举报
回复
看上这400分了

#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 ;
Mr_heber 2020-01-09
  • 打赏
  • 举报
回复
楼主是在做类似连续签到的业务需求吗
qq_38865092 2019-11-13
  • 打赏
  • 举报
回复
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
qq_38865092 2019-11-13
  • 打赏
  • 举报
回复
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
可怜的小白兔 2019-10-14
  • 打赏
  • 举报
回复
看不懂你的需求
yanglinqiang 2019-09-11
  • 打赏
  • 举报
回复
select a.uid,count(1) from t a,(select uid,max(b) as b from t where c=0 group by uid) b where a.uid=b.uid and a.b>b.b group by a.uid;
加载更多回复(48)

56,867

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧