package com.alimama.quanjingmonitor.kmeans;
import java.util.HashMap;
import java.util.HashSet;
public class PrintSql {
/**
* 子落 (2014-04-15 10:38:37):
就是强制 指标必须在一定范围内 才能分到一起的那个
张壮 (2014-04-15 10:48:54):
维度：主营类目、B|C、星级
指标：笔单价、财务消耗
子落 (2014-04-15 10:49:41):
就这几个啊
张壮 (2014-04-15 10:49:46):
时间周期：2014-4-2~2014-4-15
张壮 (2014-04-15 10:51:03):
样本量
1000-15000
1000-1500
* @param args
*/
public static void main(String[] args) {
String[] colls_important={"main_cat_name"};
String[] number_important={"(avg(case when (alipay_direct_num+alipay_indirect_num)=0 then 0.00001 else ((alipay_direct_amt+alipay_indirect_amt)/(alipay_direct_num+alipay_indirect_num)) end))"};
String[] colls={"bc_sellers","seller_star_name"};
String[] numbers={"finprice"};
String[] thedates={"20140415","20140402","20140403","20140404","20140405","20140406","20140407","20140408","20140409","20140410","20140412","20140412","20140414"};//
makeSql("cust_table", "1=1", "custid", thedates, colls_important, number_important, colls, numbers);
// System.out.println(ajustName("c(ust_1d@#$64fd"));
}
public static String ajustName(String cols)
{
String rtn=cols.replaceAll("[^A-Za-z0-9_]*", "");
if(rtn.length()>20)
{
return rtn.substring(0, 20);
}
return rtn;
}
public static String[] makeSql(String tablename,String sqlwhere,String idcols,String[] thedates_a,String[] colls_important,String[] number_important,String[] colls,String[] numbers)
{
String[] thedates=thedates_a;
if(thedates.length==1)
{
thedates=new String[]{thedates_a[0],thedates_a[0]};
}
if(sqlwhere.trim().isEmpty())
{
sqlwhere="1=1";
}
sqlwhere=sqlwhere.replaceAll("^[ |\t]+where", " ");
HashMap<String, String> alias_name =new HashMap<String, String>();
HashSet<String> groupbyrepeat=new HashSet<String>();
int index=0;
StringBuffer sqlselectbuff=new StringBuffer();
StringBuffer groupBybuf=new StringBuffer();
StringBuffer sqlBaseFilterbuff=new StringBuffer();
alias_name.put(idcols, "k_"+ajustName(idcols)+"_"+index++);
sqlselectbuff.append(" ").append(idcols).append(" as ").append(alias_name.get(idcols));
groupBybuf.append(" ").append(idcols);
sqlBaseFilterbuff.append(""+idcols+"<>'' and "+idcols+" is not null");
for(String s:colls_important)
{
if(s.equals(idcols))
{
continue;
}
alias_name.put(s, "hs_"+ajustName(s)+"_"+index++);
sqlselectbuff.append(" ,").append(s).append(" as ").append(alias_name.get(s));
if(!groupbyrepeat.contains(s))
{
groupbyrepeat.add(s);
groupBybuf.append(",").append(s);
}
sqlBaseFilterbuff.append(" and "+s+" is not null");
}
for(String s:number_important)
{
alias_name.put(s, "hn_"+ajustName(s)+"_"+index++);
boolean isaddavg=true;
if(s.trim().toLowerCase().startsWith("sum")||s.trim().toLowerCase().startsWith("max")||s.trim().toLowerCase().startsWith("min")||s.trim().toLowerCase().startsWith("avg")||s.trim().toLowerCase().startsWith("average")||s.trim().toLowerCase().startsWith("skip"))
{
isaddavg=false;
}
if(isaddavg)
{
sqlselectbuff.append(" ,").append("avg("+s+")").append(" as ").append(alias_name.get(s));
}else{
sqlselectbuff.append(" ,").append(s.replaceAll("average\\(", "avg(").replaceAll("skip\\(", "(")).append(" as ").append(alias_name.get(s));
}
}
for(String s:colls)
{
if(s.equals(idcols))
{
continue;
}
alias_name.put(s, "ls_"+ajustName(s)+"_"+index++);
sqlselectbuff.append(" ,").append(s).append(" as ").append(alias_name.get(s));
if(!groupbyrepeat.contains(s))
{
groupbyrepeat.add(s);
groupBybuf.append(",").append(s);
}
sqlBaseFilterbuff.append(" and "+s+" is not null");
}
for(String s:numbers)
{
alias_name.put(s, "ln_"+ajustName(s)+"_"+index++);
boolean isaddavg=true;
if(s.trim().toLowerCase().startsWith("sum")||s.trim().toLowerCase().startsWith("max")||s.trim().toLowerCase().startsWith("min")||s.trim().toLowerCase().startsWith("avg")||s.trim().toLowerCase().startsWith("average")||s.trim().toLowerCase().startsWith("skip"))
{
isaddavg=false;
}
if(isaddavg)
{
sqlselectbuff.append(" ,").append("avg("+s+")").append(" as ").append(alias_name.get(s));
}else{
sqlselectbuff.append(" ,").append(s.replaceAll("average\\(", "avg(").replaceAll("skip\\(", "(")).append(" as ").append(alias_name.get(s));
}
}
String sqlselect=sqlselectbuff.toString();
String groupBy=groupBybuf.toString();
String sqlBaseFilter="("+sqlBaseFilterbuff.toString()+") ";
StringBuffer buff=new StringBuffer();
index=0;
buff.append("select tbl_0."+alias_name.get(idcols)+" as col_"+index+" ");
index++;
StringBuffer[] bufferIndex={new StringBuffer(),new StringBuffer(),new StringBuffer(),new StringBuffer()};
for(int i=0;i<1;i++)
{
for(String s:colls_important)
{
if(s.equals(idcols))
{
continue;
}
buff.append(",tbl_"+i+"."+alias_name.get(s)+" as col_"+index);
bufferIndex[0].append(index).append(",");
index++;
}
}
for(int i=0;i<1;i++)
{
for(String s:number_important)
{
buff.append(",tbl_"+i+"."+alias_name.get(s)+" as col_"+index);
bufferIndex[1].append(index).append(",");
index++;
}
}
for(int i=0;i<1;i++)
{
for(String s:colls)
{
if(s.equals(idcols))
{
continue;
}
buff.append(",tbl_"+i+"."+alias_name.get(s)+" as col_"+index);
bufferIndex[2].append(index).append(",");
index++;
}
}
for(int i=1;i<thedates.length;i++)
{
for(String s:number_important)
{
buff.append(",tbl_"+i+"."+alias_name.get(s)+" as col_"+index);
bufferIndex[3].append(index).append(",");
index++;
}
}
for(int i=0;i<thedates.length;i++)
{
for(String s:numbers)
{
buff.append(",tbl_"+i+"."+alias_name.get(s)+" as col_"+index);
bufferIndex[3].append(index).append(",");
index++;
}
}
buff.append(" from (select "+sqlselect+" from "+tablename+" where dt='"+thedates[0]+"' and "+sqlBaseFilter+" and ("+sqlwhere+") group by "+groupBy+") tbl_0 ");
for(int i=1;i<thedates.length;i++)
{
buff.append(" left outer join (select "+sqlselect+" from "+tablename+" where dt='"+thedates[i]+"' and "+sqlBaseFilter+" and ("+sqlwhere+") group by "+groupBy+") tbl_"+i+" on (tbl_"+i+"."+alias_name.get(idcols)+"=tbl_0."+alias_name.get(idcols)+")");
}
System.out.println(buff.toString());
StringBuffer kmeansarams=new StringBuffer();
kmeansarams.append(bufferIndex[0].toString().replaceAll(",$", "")).append(";");
kmeansarams.append(bufferIndex[1].toString().replaceAll(",$", "")).append(";");
kmeansarams.append(bufferIndex[2].toString().replaceAll(",$", "")).append(";");
kmeansarams.append(bufferIndex[3].toString().replaceAll(",$", ""));
System.out.println(kmeansarams.toString());
return new String[]{buff.toString(),kmeansarams.toString()};
}
}