背景：
网约车预约单查询：
这里面恶心的地方是：
1个时间窗口要查询6种时间：推送订单时间（来自mongodb）、有效抢单时间（来自mongodb）、抢单成功时间（实时kafka）、取消订单时间（实时kafka）、到达目的地时间（实时kafka）、支付时间（实时kafka）
根据现实的业务情况，乘客即便打车了，也不是立马支付，很可能过些天才支付。所以不能做表关联，否则结果会不对（开心不）
因此需要做的就是，将一张表拆成6张表，然后做union操作
最后结果存入了Hbase，用phoenix做查询；
优化前，phoenix的查询操作：
SELECT "TB1".driver_id, "TB1".driver_name, "TB1".mobile, "TB1".alliance_name, "TB1".register_city, "TB1".driver_type, SUM("TB1".pushOrderNum) AS "pushOrderNum" , SUM("TB1".effectiveSlogan) AS "effectiveSlogan", SUM("TB1".grab_sus_order) AS "grab_sus_order", SUM("TB1".cancel_order_num) AS "cancel_order_num", SUM("TB1".cancel_passenger_num) AS "cancel_passenger_num", SUM("TB1".cancel_driver_num) AS "cancel_driver_num", SUM("TB1".cancel_service_num) AS "cancel_service_num", SUM("TB1".destination_reached_num) AS "destination_reached_num", SUM("TB1".pay_num) AS "pay_num", SUM("TB1".order_cumulative_num) AS "order_cumulative_num", SUM("TB1".order_pay_cumulative_num) AS "order_pay_cumulative_num" from (SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(to_number("pushOrderNum")) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "pushOrderTime" != '0' THEN TO_DATE("pushOrderTime",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("pushOrderTime",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(to_number("effectiveSlogan")) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "_effective_glab_time" != '0' THEN TO_DATE("_effective_glab_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("_effective_glab_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(to_number("grab_sus_order")) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "grab_sus_time" != '0' THEN TO_DATE("grab_sus_time" , 'yyyy-MM-dd') >= TO_DATE(#{start_time} , 'yyyy-MM-dd') AND TO_DATE("grab_sus_time" , 'yyyy-MM-dd') <= TO_DATE(#{end_time} , 'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(to_number("cancel_order_num")) AS "cancel_order_num", sum(to_number("cancel_passenger_num")) AS "cancel_passenger_num", sum(to_number("cancel_driver_num")) AS "cancel_driver_num", sum(to_number("cancel_service_num")) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "cancel_time" != '0' THEN TO_DATE("cancel_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("cancel_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(to_number("destination_reached_num")) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "close_gps_time" != '0' THEN TO_DATE("close_gps_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("close_gps_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(to_number("pay_num")) AS "pay_num" , sum(to_number("order_cumulative_num")) AS "order_cumulative_num", sum(to_number("order_pay_cumulative_num")) AS "order_pay_cumulative_num" FROM "_crontab_reservation" WHERE CASE WHEN "pay_time" != '0' THEN TO_DATE("pay_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("pay_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" ) AS "TB1" GROUP BY "TB1".driver_id , "TB1".driver_name , "TB1".mobile , "TB1".alliance_name , "TB1".register_city , "TB1".driver_type
问题：
随着时间推移，不到两个月，数据涨到大概200万左右。每一次查询能慢到怀疑人生。
问题排查：
phoenix的查询语句大概是
select a1 , a2 , sum(a3) , sum(a4) ..... from( select * from table where 条件（推送订单） union all select * from table where 条件（有效抢单） union all select * from table where 条件（抢单成功） union all select * from table where 条件（取消订单） union all select * from table where 条件（到达目的地） union all select * from table where 条件（支付） ) group by a1 , a2
请注意，200万条数据，phoenix不加索引，那么每一个表查询大概花费1秒左右，那么union了6次，就会花费6秒的时间，在加上最后的结果聚合，因此花费的时间超过了6s
并且每个时间字段格式是：yyyy-mm-dd HH:mm:ss
那么每一个司机每天都会有很多的订单，这样就导致数据边的很大。
优化
其实所谓的优化就是瘦表。想办法让phoenix在查询的时候，数据边的少一些，基于这个宗旨开始使用spark做定时任务的优化，然后在写入hbase
//基表 val _reservation = session .read .format(GlobalConfigUtils.customHbasePath) .options( Map( GlobalConfigUtils.sparksql_table_schema -> GlobalConfigUtils._spark_reservation, GlobalConfigUtils.hbase_table_name -> "_crontab_reservation", GlobalConfigUtils.hbase_table_schema -> GlobalConfigUtils._hbase_reservation )).load() _reservation.createOrReplaceTempView("reservation") //缓存表 session.sqlContext.cacheTable("reservation")
然后迭代出6张小表
//1）：推送订单表 val _push_order_tb: DataFrame = session.sql(Optimization_resevation._push_order_tb) //2）：有效订单表 val _effective_tb: DataFrame = session.sql(Optimization_resevation._effective_tb) //3）：抢单成功表 val _success_glab_order: DataFrame = session.sql(Optimization_resevation._success_glab_order) //4）：取消订单表 val _cancel_order:DataFrame = session.sql(Optimization_resevation._cancel_order) //5）：到达目的地表 val _destination_reached_order:DataFrame = session.sql(Optimization_resevation._destination_reached_order) //6）：支付订单表 val _pay_order:DataFrame = session.sql(Optimization_resevation._pay_order)
sql语句就是对每个时间对应的表做时间瘦表，把yyyy-mm-dd HH:mm:ss变成yyyy-mm-dd
lazy val _push_order_tb =
"""
|select
|tb.driver_id ,
|tb.driver_name ,
|tb.mobile ,
|tb.alliance_name ,
|tb.driver_management_id ,
|tb.register_city ,
|tb.driver_type ,
|cast(tb.close_gps_time as string) as close_gps_time,
|cast(tb.pushOrderTime as string) as pushOrderTime ,
|cast(tb._effective_glab_time as string) as _effective_glab_time ,
|cast(tb.pay_time as string) as pay_time,
|cast(tb.cancel_time as string) as cancel_time,
|cast(tb.grab_sus_time as string) as grab_sus_time ,
|sum(tb.pushOrderNum) as pushOrderNum ,
|sum(0) AS effectiveSlogan ,
|sum(0) AS grab_sus_order ,
|sum(0) AS cancel_order_num,
|sum(0) AS cancel_passenger_num,
|sum(0) AS cancel_driver_num,
|sum(0) AS cancel_service_num ,
|sum(0) AS destination_reached_num ,
|sum(0) AS pay_num ,
|sum(0) AS order_cumulative_num,
|sum(0) AS order_pay_cumulative_num
|from
|(
|select
|driver_id ,
|driver_name ,
|mobile ,
|alliance_name ,
|driver_management_id ,
|register_city ,
|driver_type ,
|0 as close_gps_time ,
|date_format(pushOrderTime,'yyyy-MM-dd') as pushOrderTime ,
|0 as _effective_glab_time ,
|0 as pay_time ,
|0 as cancel_time ,
|0 as grab_sus_time ,
|pushOrderNum ,
|0 AS effectiveSlogan ,
|0 AS grab_sus_order ,
|0 AS cancel_order_num,
|0 AS cancel_passenger_num,
|0 AS cancel_driver_num,
|0 AS cancel_service_num ,
|0 AS destination_reached_num ,
|0 AS pay_num ,
|0 AS order_cumulative_num,
|0 AS order_pay_cumulative_num
|from
|reservation
|where
|pushOrderTime != '0'
|and
|cast(date_format(pushOrderTime , 'yyyyMMdd') as bigint) < cast(date_format(now() , 'yyyyMMdd') as bigint)
|) tb
|GROUP BY
|tb.driver_id ,
|tb.driver_name ,
|tb.mobile ,
|tb.alliance_name ,
|tb.driver_management_id ,
|tb.register_city ,
|tb.driver_type ,
|tb.close_gps_time ,
|tb.pushOrderTime ,
|tb._effective_glab_time ,
|tb.pay_time ,
|tb.cancel_time ,
|tb.grab_sus_time
""".stripMargin
然后数据写入hbase
dF.write .mode(SaveMode.Append) .options(Map(HBaseTableCatalog.tableCatalog -> catalog , HBaseTableCatalog.newTable -> "8")) .format("org.apache.spark.sql.execution.datasources.hbase") .save()
代码其实就是将一个表拆成6张表（对应每个不同的时间维度），然后将时间yyyy-mm-dd HH:mm:ss中的数据按照司机ID和yyyy-mm-dd做分组求和。这样落到hbase就是6张小表了。
然后phoenix在去分别查询6张小表，在聚合起来即可；
这样200万的数据经过优化后，在不加索引的前提下查询不到1s；
最后的phoenix查询语句就是分别查6个小表：
SELECT "TB1".driver_id, "TB1".driver_name, "TB1".mobile, "TB1".alliance_name, "TB1".register_city, "TB1".driver_type, SUM("TB1".pushOrderNum) AS "pushOrderNum" , SUM("TB1".effectiveSlogan) AS "effectiveSlogan", SUM("TB1".grab_sus_order) AS "grab_sus_order", SUM("TB1".cancel_order_num) AS "cancel_order_num", SUM("TB1".cancel_passenger_num) AS "cancel_passenger_num", SUM("TB1".cancel_driver_num) AS "cancel_driver_num", SUM("TB1".cancel_service_num) AS "cancel_service_num", SUM("TB1".destination_reached_num) AS "destination_reached_num", SUM("TB1".pay_num) AS "pay_num", SUM("TB1".order_cumulative_num) AS "order_cumulative_num", SUM("TB1".order_pay_cumulative_num) AS "order_pay_cumulative_num" from (SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(to_number("pushOrderNum")) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_push_order_tb" WHERE CASE WHEN "pushOrderTime" != '0' THEN TO_DATE("pushOrderTime",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("pushOrderTime",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(to_number("effectiveSlogan")) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_effective_tb" WHERE CASE WHEN "_effective_glab_time" != '0' THEN TO_DATE("_effective_glab_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("_effective_glab_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(to_number("grab_sus_order")) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_success_glab_order_tb" WHERE CASE WHEN "grab_sus_time" != '0' THEN TO_DATE("grab_sus_time" , 'yyyy-MM-dd') >= TO_DATE(#{start_time} , 'yyyy-MM-dd') AND TO_DATE("grab_sus_time" , 'yyyy-MM-dd') <= TO_DATE(#{end_time} , 'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(to_number("cancel_order_num")) AS "cancel_order_num", sum(to_number("cancel_passenger_num")) AS "cancel_passenger_num", sum(to_number("cancel_driver_num")) AS "cancel_driver_num", sum(to_number("cancel_service_num")) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_cancel_order_tb" WHERE CASE WHEN "cancel_time" != '0' THEN TO_DATE("cancel_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("cancel_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(to_number("destination_reached_num")) AS "destination_reached_num" , sum(0) AS "pay_num" , sum(0) AS "order_cumulative_num", sum(0) AS "order_pay_cumulative_num" FROM "_destination_reached_order_tb" WHERE CASE WHEN "close_gps_time" != '0' THEN TO_DATE("close_gps_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("close_gps_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" union all SELECT "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" , sum(0) AS "pushOrderNum" , sum(0) AS "effectiveSlogan" , sum(0) AS "grab_sus_order" , sum(0) AS "cancel_order_num", sum(0) AS "cancel_passenger_num", sum(0) AS "cancel_driver_num", sum(0) AS "cancel_service_num" , sum(0) AS "destination_reached_num" , sum(to_number("pay_num")) AS "pay_num" , sum(to_number("order_cumulative_num")) AS "order_cumulative_num", sum(to_number("order_pay_cumulative_num")) AS "order_pay_cumulative_num" FROM "_pay_order_tb" WHERE CASE WHEN "pay_time" != '0' THEN TO_DATE("pay_time",'yyyy-MM-dd') >= TO_DATE(#{start_time},'yyyy-MM-dd') AND TO_DATE("pay_time",'yyyy-MM-dd') <= TO_DATE(#{end_time},'yyyy-MM-dd') ELSE 1=1 END <if test="driver_id != null and driver_id !=''"> and "driver_id"= #{driver_id} </if> <if test="driver_name != null and driver_name !=''"> and "driver_name" = #{driver_name} </if> <if test="mobile != null and mobile !=''"> and "mobile" = #{mobile} </if> <if test="alliance_name != null and alliance_name !=''"> and "driver_management_id" = #{alliance_name} </if> <if test="register_city != null and register_city !=''"> and substr("register_city" , 0 , 4) = substr(#{register_city} , 0 , 4) </if> <if test="driver_type != null and driver_type !=''"> and "driver_type" = #{driver_type} </if> GROUP BY "driver_id" , "driver_name" , "mobile" , "alliance_name" , "register_city" , "driver_type" ) AS "TB1" GROUP BY "TB1".driver_id , "TB1".driver_name , "TB1".mobile , "TB1".alliance_name , "TB1".register_city , "TB1".driver_type