sql 收藏

• sql查询带行号

SELECT * FROM data1 LIMIT 5000;

SELECT @no:=@no+1 NO, d.* FROM data1 d, (SELECT @no:=0) b LIMIT 5000;

• sql查询前10%

SELECT * FROM (
	SELECT @no:=@no+1 NO, d.* FROM data1 d, (SELECT @no:=0) b LIMIT 5000
) tmp 
WHERE tmp.no=@no*0.1;

• sql带行号分组

SELECT @no:=
CASE 
WHEN @flag=d.url THEN @no+1
ELSE 1
END 'no', 
@flag:=d.url flag,
d.* 
FROM data1 d,
(SELECT @no:=0, @flag:='') b
ORDER BY url,ts DESC
;

统计以及统计95%line, 90%line, 如: 压力测试ab的结果

场景
url, ts(耗时, 毫秒), date

需求1:
统计url的次数和耗时
思路:
简单, 没思路, 直接group by

SELECT url, COUNT(1) cnt, MAX(ts) ts_max AVG(ts) ts_avg FROM data1 d GROUP BY url DESC;

需求2:
统计url的次数和耗时, 并给出ts 95%line, 90%line,
*95%line -> 95%的url请求耗时都等于少于的这个耗时
90%line -> 同理
思路:
1,通过group_concat, 获取分组的所有ts, 并从大到小排序,
2.处理95Line, 取前面5%通过cnt0.05+1, 获取前面5%的所有ts+5%之外的每一个
3.取ts的最后一个, 用substring_index把获取
4.同理90%Line

注意group_concat生成的字符串是有长度限制, 默认1024个字节, 按需扩展, 设置后要重新连接才生效
这里420w个日志, 最大的有9w行, 即9w个ts, 每个ts一般是3位数(如: 530毫秒), 加上逗号, 即90k4=360k, 所以我这边设了group_concat_max_len=1024000

#修改group_concat_max_len长度
SET GLOBAL group_concat_max_len=1024000;
SET SESSION group_concat_max_len=1024000;
#查看group_concat_max_len
SHOW VARIABLES LIKE '%group_concat%';

#调试, 分组和生成ts字符串, 按ts降序
SELECT url, COUNT(1) cnt, MAX(ts) ts_max AVG(ts) ts_avg GROUP_CONCAT(ts ORDER BY ts DESC ) txt FROM data1 d GROUP BY url DESC;

#调试, 分组和生成ts字符串数组, 按ts降序
#再截取前面5%, 10%
SELECT t.url, cnt, ts_max, TRUNCATE(ts_avg,2), 
SUBSTRING_INDEX(txt,",",cnt*0.05) ts_95l, 
SUBSTRING_INDEX(txt,",",cnt*0.1) ts_90l 
FROM (
SELECT url, COUNT(1) cnt, MAX(ts) ts_max, AVG(ts) ts_avg, GROUP_CONCAT(ts ORDER BY ts DESC ) txt FROM data1 d GROUP BY url DESC
) t
;

#结果, 分组并对ts定位95%Line, 90%Line
#步骤
#调试, 分组和生成ts字符串数组, 按ts降序
#再截取前面5%, 10%
#再对截取的字符串数组取最后一个
SELECT t.url, cnt, ts_max, TRUNCATE(ts_avg,2), 
SUBSTRING_INDEX(
SUBSTRING_INDEX(txt,",",cnt*0.05),",",-1) ts_95l,
SUBSTRING_INDEX(
SUBSTRING_INDEX(txt,",",cnt*0.1),",",-1) ts_90l
FROM (
SELECT url, COUNT(1) cnt, MAX(ts) ts_max, AVG(ts) ts_avg, GROUP_CONCAT(ts ORDER BY ts DESC ) txt FROM data1 d GROUP BY url DESC
) t
;

需求3:
加上sent_body_size
思路: 同理

SELECT t.url, cnt, 
size_max,
SUBSTRING_INDEX(SUBSTRING_INDEX(txt_size,",",cnt*0.05+1),",",-1) size_95l,
SUBSTRING_INDEX(SUBSTRING_INDEX(txt_size,",",cnt*0.1+1),",",-1) size_90l,
TRUNCATE(size_avg,2) size_avg, 
ts_max, 
SUBSTRING_INDEX(SUBSTRING_INDEX(txt_ts,",",cnt*0.05+1),",",-1) ts_95l,
SUBSTRING_INDEX(SUBSTRING_INDEX(txt_ts,",",cnt*0.1+1),",",-1) ts_90l,
TRUNCATE(ts_avg,2) ts_avg
FROM (
SELECT url, COUNT(1) cnt, MAX(size) size_max, AVG(size) size_avg, MAX(ts) ts_max, AVG(ts) ts_avg, 
GROUP_CONCAT(size ORDER BY size DESC ) txt_size,
GROUP_CONCAT(ts ORDER BY ts DESC ) txt_ts
FROM data1 d GROUP BY url DESC
) t ORDER BY cnt DESC
;