如何在分组数据中选择某种顺序的数据？

Question

-----------------
tbl_test
-----------------
索引有 toid_gid_dateline 顺序的联合索引toid(int)  gid(int) dateline(int) other(vchar)
1          1         111           ...
2          1         111           ...
1          1         222           ...
1          2         111           ...
比如有这种类型的数据。
我想找 toid为1， gid为1的， dateline升序排行100的数据很容易
select * from tbl_test where toid=.. gid=.. order by dateline  limit 100, 1
想找touid为1， gid为2， 依次类推。。
现在想找这种数据的集合， 需要指定的TOID在某个GID里面的记录数量超过100的GID, 找出这种分组里面按时间排序的
第100个数字。 得到 (gid, gid_count, order_100_dateline) 的数据集。
想过下面的语句， 但是group后的数据没法排序， 一些max等聚合函数获取的又是极大或极小值， 不知道有没什么好办法。
表数据千万级， 执行的比较频繁， 需要性能快， 可以之后建索引。
目前的SQL语句
select gid, dateline from tb_test where toid=1 group by gid having count(gid)>100 
这个dateline没有参考价值。。
于是程序循环
for each __gid in gids do
   select gid, dateline from tb_test where toid=1 and gid=__gid order by dateline limit 100, 1
end
然后把这些数据整理起来， 感觉很恶心
--------------------
补充下问题， 这么做是因为有个需求，需要保证在表中，对于toid和gid的组合，保证不超过N条记录（100), 否则移除dateline最小的， 原来是插入的时候做， 后来是GET操作的时候批量做。 想尽可能快。
刚刚的想法是想得到这个用户哪些gid记录数超了100，拿到第100那条的时间点的记录，然后在批量
for each__gid in gids do
  delete from tb_test where toid=1 and gid=__gid where dateline < __dateline
还有一个问题，如果我想按照时间顺序排序，删除除最近100条记录外的数据如何处理？
最近100条貌似可以
delete from tb_test where ... roder by dateline limit 100;
但是排除这100条的记录删除不知道咋写， where not in的子查询太慢~~！

Answer 1

还有一个问题，如果我想按照时间顺序排序，删除除最近100条记录外的数据如何处理？
最近100条貌似可以
delete from tb_test where ... roder by dateline limit 100;在dateline上建立索引
delete a from tt a inner join (select dateline from tt order by dateline desc limit 100) b
on a.dateline=b.dateline

Answer 2

1楼删除最近100条记录;以外
在dateline上建立索引delete a from tt a left join (select dateline from tt order by dateline desc limit 100) b
on a.dateline=b.dateline where b.dateline is null

Answer 3

一个表里面有user_id, group_id, dateline还有其他信息
对于特定的user_id, 按照group_id分组后， 每组的数据如果超过100条， 那么则答应出每组里面按照dateline降序后的第100条的dateline数据出来。  简化一点描述（大于等于2条，则输出第2条）
uid group_id dateline 
1  1  3    
1  1  1      
1  1  2
1  1  4    
1  2  1
1  3  2
1  3  5
1  4  0
其中group_id为1,3的条数满足超过两条， 对于groupid 1而言，dateline倒叙第2个为3，对于groupid 3而言，dateline倒叙第2个为2 则输出
group_id  dateline
1        3
3        2

Answer 4

其中group_id为1,3的条数满足超过两条， 对于groupid 1而言，dateline倒叙第2个为3，：
以什么顺序，物理顺序不是dateline？对于groupid 1而言，dateline倒叙第2个为3：说明一下

Answer 5

1：表中没有唯一ID字段
2：dateline不是物理顺序， 这个dateline是一个整数，是unix_timestamp()的时间戳。 需要按照这个时间戳倒序

Answer 6

用你的数据：
SELECT * FROM 
(SELECT group_id FROM tt1 GROUP BY group_id HAVING COUNT(*)>=2) a
INNER JOIN (
SELECT a.`group_id`,a.`dateline` FROM tt1 a LEFT JOIN tt1 b 
 ON a.`group_id`=b.`group_id` AND a.`dateline`<=b.`dateline` 
 GROUP BY a.`group_id`,a.`dateline` HAVING COUNT(b.dateline)=2) b ON a.`group_id`=b.`group_id`

Answer 7

上述代码没有考虑dateline重复的情况

Answer 8

精简：
SELECT a.`group_id`,a.`dateline` FROM tt1 a WHERE 2=(SELECT COUNT(*) FROM tt1 b WHERE a.`group_id`=b.`group_id` AND a.`dateline`<=b.`dateline`);

Answer 9

新方法比较巧妙，数据结果和老方法一样， 但是效率貌似很慢。
相当于对msg_touid=1034532的每条记录都进行COUNT查询。 虽然子查询也用上了索引，但是总效果相当于O(n).
老方法需要做N+1次查询，取决于第一次查询的记录数量。 对于我这里而言新的方法要慢很多。 0.04秒满足不了需求，
要在毫秒级内。 有更好的方法吗？附：----------- 老的方法
SELECT SQL_NO_CACHE msg_touid , msg_dateline, msg_fromgroupid, COUNT(msg_fromgroupid), MAX(msg_dateline)
FROM tb_group_message 
 WHERE msg_touid=1034532  
GROUP BY msg_fromgroupid  HAVING COUNT(msg_fromgroupid) > 100
拿到1642,1654,1671. 耗时：0.00076
-----
SELECT * FROM (SELECT SQL_NO_CACHE msg_touid , msg_dateline, msg_fromgroupid FROM tb_group_message WHERE
msg_touid = 1034532 AND msg_fromgroupid = 1642 ORDER BY msg_dateline DESC LIMIT 100,1) a
UNION
SELECT * FROM (SELECT SQL_NO_CACHE msg_touid , msg_dateline, msg_fromgroupid FROM tb_group_message WHERE
msg_touid = 1034532 AND msg_fromgroupid = 1654 ORDER BY msg_dateline DESC LIMIT 100,1 ) b
UNION
SELECT * FROM (SELECT SQL_NO_CACHE msg_touid , msg_dateline, msg_fromgroupid FROM tb_group_message WHERE
msg_touid = 1034532 AND msg_fromgroupid = 1671 ORDER BY msg_dateline DESC LIMIT 100,1) c
耗时：0.00235
总计耗时：0.00311---------- 新方法
SELECT SQL_NO_CACHE a.msg_touid, a.msg_dateline, a.msg_fromgroupid
FROM tb_group_message a 
WHERE
msg_touid=1034532
AND
101=(SELECT COUNT(*) FROM tb_group_message b WHERE b.msg_touid=1034532 AND a.msg_fromgroupid=b.msg_fromgroupid AND a.msg_dateline<=b.msg_dateline)
耗时：0.04677

Answer 10

tb_group_message:msg_touid、msg_fromgroupid、msg_dateline上建立复合索引

Answer 11

如何强制用这个索引啊？
确实还有另外一个索引， 是idx_touid_dateline 的， 中间少了groupid的。explain结果如下：id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,a,ref,touid_dateline_idx,touid_gid_dateline_idx,touid_gid_dateline_idx,4,const,444,Using where; Using index
2,DEPENDENT SUBQUERY,b,ref,touid_dateline_idx,touid_gid_dateline_idx,touid_gid_dateline_idx,8,const,memetel.a.msg_fromgroupid,154,Using where; Using index大概耗时这两步上
state duration (summed) in sec percentage
Sending data 0.03799 79.39394
executing 0.00937 19.58203

Answer 12

执行结果数据量并不大， 不太理解这个senddata是什么过程？ 
执行结果一共3条记录如下
msg_touid,msg_dateline,msg_fromgroupid
1034532,1328809832,1642
1034532,1328932223,1654
1034532,1329109894,1671SQL如下
---------- 新方法
 SELECT SQL_NO_CACHE a.msg_touid, a.msg_dateline, a.msg_fromgroupid
FROM tb_group_message a 
WHERE
msg_touid=1034532
AND
101=(SELECT COUNT(*) FROM tb_group_message b WHERE b.msg_touid=1034532 AND a.msg_fromgroupid=b.msg_fromgroupid AND a.msg_dateline<=b.msg_dateline)

Answer 13

SELECT SQL_NO_CACHE a.msg_touid, a.msg_dateline, a.msg_fromgroupid
FROM tb_group_message a  FORCE INDEX(索引名)
WHERE
msg_touid=1034532
AND
101=(SELECT COUNT(*) FROM tb_group_message b WHERE b.msg_touid=1034532 AND a.msg_fromgroupid=b.msg_fromgroupid AND a.msg_dateline<=b.msg_dateline)

Answer 14

问题用建临时变量解决了， 多谢wwwb。 
ref：http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/SET @num := 0, @TYPE := '';
SELECT SQL_NO_CACHE msg_fromgroupid, msg_dateline
FROM (
   SELECT SQL_NO_CACHE  msg_fromgroupid,msg_dateline,
      @num := IF(@TYPE = msg_fromgroupid, @num + 1, 1) AS row_number,
      @TYPE := msg_fromgroupid AS dummy
  FROM tb_group_message  WHERE msg_touid = 1034532
  ORDER BY msg_fromgroupid DESC, msg_dateline DESC 
) AS X WHERE x.row_number =101;经过对比， 上述SQL语句大概在0.003秒属于o(lgn+lgn)， 比AND 101=...的o(lgn*lgn) 0.04秒快很多
另，如果msg_formgroupid和msg_dateline具有唯一性，则可以吧order by替换成group by + having的形式
行程简单的单条SQL语句查询， 速度大概为0.001秒。 GROUP BY算不标准小技巧用法，容易出问题，原文作者不建议使用