/// <summary>
/// 基础过滤，聚合，条件判断sql生成
/// </summary>
/// <returns></returns>
private static QueryFactory ODBCQueryFactory1()
{
var compiler = new GenericCompiler();
var connection = new OdbcConnection(
"Driver=Cloudera ODBC Driver for Impala;Host=10.201.82.170;Port=21050;Schema=eap_1"
);
var db = new QueryFactory(connection, compiler);
// CommonQuery传递的前端参数转换为 sql字段
var queryDto = new CommonQuery
{
Columns = new List <string> {
"ss", "ss"
},
GroupBys = new List <string> {
"s_event_name"
},
};
var query = db.Query("event_today").Where("s_event_name", "s_app_launch").OrWhere("s_event_name", "s_app_show").GroupBy("s_event_name")
.Select("s_event_name", "count(*) as CC"); // .OrWhere("s_event_name", "s_page_show").
var sql = compiler.Compile(query);
// var res = db.Statement(sql.ToString()); // 这玩意返回的是受影响的行数，select 始终返回-1，我们不会更新数据库，所以这个方法我们不用。
var result = query.Get <ResultEntity>();
foreach (var s in result)
{
var ssss = s;
}
//var dr = new DataResult
//{
// Headers = result.
//};
db.Logger = rs =>
{
Console.WriteLine(rs.ToString());
};
return(db);
}
/// <summary>
/// 支持CTE
/// </summary>
/// <returns></returns>
private static QueryFactory ODBCQueryFactory4()
{
var compiler = new GenericCompiler();
var connection = new OdbcConnection(
"Driver=Cloudera ODBC Driver for Impala;Host=10.201.82.170;Port=21050;Schema=eap_1"
);
var db = new QueryFactory(connection, compiler);
var init_event = new Query("event_today")
.Select("s_final_user_id", "s_brand", "s_day")
.Where("s_event_name", "s_app_launch").WhereBetween("s_day", 20200520, 20200526);
var second_event = new Query("event_today")
.With("init_event", init_event) // now you can consider ActivePosts as a regular table in the database
.Join("init_event", "init_event.s_final_user_id", "event_today.s_final_user_id")
.Where("s_event_name", "s_app_show")
.GroupByRaw("s_final_user_id,intervalDays,s_brand")
.SelectRaw("event_today.s_final_user_id,event_today.s_brand,(event_today.s_day-init_event.s_day) as intervalDays");
var query = db.Query("second_event")
.With("second_event", second_event)
.GroupByRaw("s_brand,intervalDays")
.SelectRaw("s_brand,intervalDays,count( DISTINCT s_final_user_id),count(*) as times,count (*) over() as totalCount ");
var sql = compiler.Compile(query);
var ss = db.Select(sql.ToString());
var result = query.Get();
db.Logger = rs =>
{
Console.WriteLine(rs.ToString());
};
return(db);
}