魔数Plus

给魔数添加一些便捷操作
当前为 2022-05-25 提交的版本，查看最新版本。
换行
// ==UserScript==
// @name         魔数Plus
// @namespace    http://tampermonkey.net/
// @version      0.12.2
// @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
// @require      https://cdn.jsdelivr.net/gh/vkiryukhin/vkBeautify@1645229b04dfa47a2bd7def5562754cb7bae52bd/vkbeautify.min.js
// ==/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")
       console.log(matchingElement.innerText)
       var beforeEle = matchingElement
       for (var i in btnList){
           var curBtn=btnList[i]
           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};
}



// 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";
         var beautifulSql=vkbeautify.sql(finalSql);
         cm.setValue(beautifulSql);
     }
     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";
         var beautifulSql=vkbeautify.sql(finalSql);
         cm.setValue(beautifulSql);
     }
     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 beautifulSql=vkbeautify.sql(online) + " union all "+vkbeautify.sql(test);
         cm.setValue(beautifulSql);
     }
     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);
     }
     return selectAllBtn;
}
欢迎访问Greasy Fork镜像
