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%通过cnt
0.05+1, 获取前面5%的所有ts+5%之外的每一个
3.取ts的最后一个, 用substring_index把获取
4.同理90%Line

注意group_concat生成的字符串是有长度限制, 默认1024个字节, 按需扩展, 设置后要重新连接才生效
这里420w个日志, 最大的有9w行, 即9w个ts, 每个ts一般是3位数(如: 530毫秒), 加上逗号, 即90k
4=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
;

发布了3 篇原创文章 · 获赞 0 · 访问量 36
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览