运维开发网

记一次phoenix在不加索引的情况调优,由6s以上时间变成不到1s

运维开发网 https://www.qedev.com 2020-05-09 16:40 出处:网络 作者:运维开发网整理
背景: 网约车预约单查询: 这里面恶心的地方是: 1个时间窗口要查询6种时间:推送订单时间(来自mongodb)、有效抢单时间(来自mongodb)、抢单成功时间(实时kafka)、取消订单时间(实时kafka)、到达目的地时间(实时kafka)、支付时间(实时kafka) 根据现实的业务情况,乘客即便打车了,也不是立马支付,很可能过些天才支付。所以不能做表关联,否则结果会不对(开心不) 因此需要

记一次phoenix在不加索引的情况调优,由6s以上时间变成不到1s

背景:

网约车预约单查询:

记一次phoenix在不加索引的情况调优,由6s以上时间变成不到1s

这里面恶心的地方是:

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‘) &lt;= 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‘) &lt;= 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‘) &lt;= 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‘) &lt;= 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‘) &lt;= 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
优化前的phoenix查询操作

问题:

随着时间推移,不到两个月,数据涨到大概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
View Code

 

然后数据写入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‘) &lt;= 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‘) &lt;= 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‘) &lt;= 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‘) &lt;= 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‘) &lt;= 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‘) &lt;= 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
瘦表后的查询
0

精彩评论

暂无评论...
验证码 换一张
取 消