给魔数添加一些便捷操作
目前為
// ==UserScript==
// @name 魔数Plus
// @namespace http://tampermonkey.net/
// @version 0.12.5
// @description 给魔数添加一些便捷操作
// @author duantianci
// @match https://bi.sankuai.com/sql/edit*
// @icon https://www.google.com/s2/favicons?domain=sankuai.com
// @license MIT
// @run-at document-end
// @grant unsafeWindow
// ==/UserScript==
(function() {
// Your code here...
var selectAllBtn = createSelectAllBtn(); // select *
var SelectAggrBtn = createSelectAggrBtn(); // 单表聚合
var SelectAggrDiffBtn = createSelectAggrDiffBtn(); // 双表聚合diff
var AllDiffBtn = createAllDiffBtn(); // 无diff测试
var btnList = [selectAllBtn,SelectAggrBtn,SelectAggrDiffBtn,AllDiffBtn];
setTimeout( function(){
console.log("i am here")
var matchingElement=document.querySelector("#app > div > div.sidebar-container > div > div.ms-sidebar-main > div > div.router-view > div > div > div.mtd-tabs-content > div > div > div.control-buttons > span > button")
var beforeEle = matchingElement
for (var i in btnList){
var curBtn=btnList[i]
curBtn.style.cssText = 'color: #0a70f5;border-color: transparent!important;background-color: #0000;font-size: 14px;font-weight: 400;cursor: pointer;';
beforeEle.parentElement.insertBefore(curBtn,beforeEle);
beforeEle=curBtn;
}
},3000);
})();
Date.prototype.format = function (fmt) {
var o = {
"M+": this.getMonth() + 1, //月份
"d+": this.getDate(), //日
"h+": this.getHours(), //小时
"m+": this.getMinutes(), //分
"s+": this.getSeconds(), //秒
"q+": Math.floor((this.getMonth() + 3) / 3), //季度
"S": this.getMilliseconds() //毫秒
};
if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
for(var k in o)
if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
return fmt;
}
var getDate=function(delta){
var day = new Date();
day.setDate(day.getDate() - delta);
var sDate = day.format("yyyyMMdd");
return sDate;
}
var getFormatBtn = function(){
return document.getElementsByClassName("sql-icon-format")[0];
}
var getCm = function(){
return document.getElementsByClassName("CodeMirror cm-s-ms-light CodeMirror-wrap")[0].CodeMirror;
}
var getDiffTableName = function(oriTableName){
return oriTableName.split(".")[0]+"_test." + oriTableName.split(".")[1]
}
var getTableName = function(){
return document.getElementsByClassName("table-line show-column")[0].childNodes[1].childNodes[0].title;
}
var getColsName = function(){
var tableCols=[];
var table = document.getElementsByClassName("column-list")[0];
var rows = table.rows;//获取所有行
console.log("lenth",rows.length) //
for(var i=1; i < rows.length; i++){
var row = rows[i];//获取每一行
var colName = row.cells[0].title;//获取具体单元格
tableCols.push(colName);
}
return tableCols;
}
var getColInfo = function(cols){
var sumEndPatterns=['num', 'cnt', 'amt','fee','1d','7d','15d']
var disEndPatterns=['uv','user','poi_num']
var sumCols=cols.filter(name => (
sumEndPatterns.some(pattern => name.endsWith(pattern)) && !disEndPatterns.some(pattern => name.endsWith(pattern))
)).map(function(col){
return 'sum('+ col +') as '+col
}).join(',');
var disCols=cols.filter(name => (
disEndPatterns.some(pattern => name.endsWith(pattern))
)).map(function(col){
return 'count(distinct '+ col +') as '+col
}).join(',');
return {'sum':sumCols,'dis':disCols};
}
var buautify = function(){
var matchingElement=document.querySelector("#app > div > div.sidebar-container > div > div.ms-sidebar-main > div > div.router-view > div > div > div.mtd-tabs-content > div > div > div.sql-item-main > div.panel-set > div.sql-draft-editor > div.editor-controls > div.format-control.control-item > button")
matchingElement.click()
}
// select *
function createSelectAllBtn(){
let selectAllBtn=document.createElement("button");
selectAllBtn.innerText="select *";
selectAllBtn.onclick=function(){
var yesterday=getDate(1);
var cm = getCm();
var tableName =getTableName();
var tableCols = getColsName();
var joinCols = tableCols.join(",")
var finalSql="select " +tableCols.join(",") +" from "+tableName +" where dt ="+yesterday +" limit 100";
cm.setValue(finalSql);
buautify();
}
return selectAllBtn;
}
// 单表聚合
function createSelectAggrBtn(){
let selectAllBtn=document.createElement("button");
selectAllBtn.innerText="单表聚合";
selectAllBtn.onclick=function(){
var yesterday=getDate(1);
var cm = getCm();
var tableName =getTableName();
var tableCols = getColsName();
var colInfo = getColInfo(tableCols);
var commaInfo = colInfo.dis=='' ? '' : ',';
var finalSql="select dt, 'online' as type, count(*) count_num, " +colInfo.sum +commaInfo+colInfo.dis +" from "+tableName +" where dt ="+yesterday+" group by dt";
cm.setValue(finalSql);
buautify();
}
return selectAllBtn;
}
//双表聚合diff
function createSelectAggrDiffBtn(){
let selectAllBtn=document.createElement("button");
selectAllBtn.innerText="双表聚合diff";
selectAllBtn.onclick=function(){
var yesterday=getDate(1);
var cm = getCm();
var tableName =getTableName();
var diffTabeName = getDiffTableName(tableName);
var tableCols = getColsName();
var colInfo = getColInfo(tableCols);
var commaInfo = colInfo.dis=='' ? '' : ',';
var online ="select dt, 'online' as type, count(*) count_num, " +colInfo.sum +commaInfo+colInfo.dis +" from "+tableName +" where dt ="+yesterday +" group by dt";
var test ="select dt, 'test' as type, count(*) count_num, " +colInfo.sum +commaInfo+colInfo.dis +" from "+diffTabeName +" where dt ="+yesterday +" group by dt";
//var finalSql="select 'online' as type, " +colInfo.sum +commaInfo+colInfo.dis +" from "+tableName +" where dt ="+yesterday + " union all select 'test' as type," +colInfo.sum +commaInfo+colInfo.dis +" from "+diffTabeName +" where dt ="+yesterday;
var finalSql= online +" union all "+test;
cm.setValue(finalSql);
buautify();
}
return selectAllBtn;
}
//双表无diff
function createAllDiffBtn(){
let selectAllBtn=document.createElement("button");
selectAllBtn.innerText="双表无diff";
selectAllBtn.onclick=function(){
var yesterday=getDate(1);
var cm = getCm();
var tableName =getTableName();
var diffTabeName = getDiffTableName(tableName);
var tableCols = getColsName();
var joinCols = tableCols.join(",")
var finalSql="select "+tableCols.join(",") +", COUNT(*) num FROM (select " +tableCols.join(",") +" from "+tableName +" where dt ="+yesterday+" union all select " +tableCols.join(",") +" from "+diffTabeName +" where dt ="+yesterday +" )tmp GROUP BY " + tableCols.join(",") + " HAVING COUNT(*) !=2";
// var beautifulSql=vkbeautify.sql(finalSql);
cm.setValue(finalSql);
buautify();
}
return selectAllBtn;
}