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
;



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

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


*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

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
;



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
;



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