select
    dw_order.USER_UUID AS                                                                                   "用户UUID"                                  --支付用户UUID
    ,CONCAT('#',CAST(dw_order.ROOT_ORDER_NO AS varchar)) AS                                                 "根单号"                                    --支付用户UUID
    ,MAX(dw_order.MALL_ORDER_COMPLETE_TIME) AS                                                              "订单完成时间"                              --商城订单完成时间
    ,MAX(dw_order.MALL_SKU_SALE_COUNT) AS                                                                   "SKU销售数量"                               --商城单SKU销售数量
    ,SUM(dw_order.MALL_SKU_FREIGHT_FEE) AS                                                                  "商城SKU运费金额"                           --商城运费金额
    ,SUM(dw_order.MALL_SKU_FREIGHT_DEDUCTION_FEE) AS                                                        "商城运费抵扣金额"                          --商城运费抵扣金额
    ,SUM(dw_order.MALL_SKU_CASH_COUPON_DEDUCTION_FEE) AS                                                    "商城现金抵扣金额"                          --商城现金抵扣金额
    ,SUM(dw_order.MALL_SKU_COUPON_DEDUCTION_FEE) AS                                                         "商城优惠券抵扣金额"                        --商城优惠券抵扣金额
    ,SUM(dw_order.MALL_SKU_ACTIVITY_DEDUCTION_FEE) AS                                                       "商城活动抵扣金额"                          --商城活动抵扣金额
    ,SUM(dw_order.MALL_SKU_AMT_NEED_PAY) AS                                                                 "商城应付金额"                              --商城应付金额
    ,SUM(dw_order.MALL_SKU_PAY_AMOUNT) AS                                                                   "商城实付金额"                              --商城实付金额
    ,'||||' AS                                                                                              "-"
    ,MAX(dw_order.PS_CREATE_TIME) AS                                                                        "支付创建时间"                              --支付创建时间
    ,MAX(dw_order.VCC_PAY_FLOW_NO) AS                                                                       "VCC支付流水号"                             --VCC支付流水号
    ,SUM(dw_order.PS_SKU_AMT_DEDUCT_PAY_COUPON) AS                                                          "支付优惠抵扣金额"                          --支付优惠抵扣金额
    ,SUM(dw_order.PS_SKU_VCC_AMT) AS                                                                        "支付VCC金额"                               --支付VCC金额
    ,SUM(dw_order.PS_SKU_WX_AMT) AS                                                                         "支付微信金额"                              --支付微信金额
    ,SUM(dw_order.PS_SKU_ZFB_AMT) AS                                                                        "支付支付宝金额"                            --支付支付宝金额
    ,SUM(dw_order.PS_SKU_VCC_AMT+dw_order.PS_SKU_WX_AMT+dw_order.PS_SKU_ZFB_AMT) AS                         "实付金额"                                  --支付实付金额
    ,CAST((SUM(dw_order.MALL_SKU_AMT_NEED_PAY)+SUM(dw_order.MALL_SKU_FREIGHT_FEE)
        -SUM(dw_order.MALL_SKU_FREIGHT_DEDUCTION_FEE)-SUM(dw_order.MALL_SKU_CASH_COUPON_DEDUCTION_FEE)
        -SUM(dw_order.MALL_SKU_COUPON_DEDUCTION_FEE)-SUM(dw_order.MALL_SKU_ACTIVITY_DEDUCTION_FEE)
        -SUM(dw_order.PS_SKU_AMT_DEDUCT_PAY_COUPON)-SUM(dw_order.PS_SKU_VCC_AMT)
        -SUM(dw_order.PS_SKU_WX_AMT)-SUM(dw_order.PS_SKU_ZFB_AMT)) AS DECIMAL(13,3)) AS                     "检测余额"

from(
    select
        dw_ps_order.USER_UUID AS                                                                            "USER_UUID"                                 --用户UUID
        ,if(MAX(dw_ps_order.PS_ORDER_NO) IS NULL, MAX(kdsp_order.root_order_no)
            ,MAX(dw_ps_order.PS_ORDER_NO))                                                                  "ROOT_ORDER_NO"
        ,MAX(kdsp_order.order_complete_time) AS                                                             "MALL_ORDER_COMPLETE_TIME"                  --商城订单完成时间
        ,MAX(kdsp_order.created_at) AS                                                                      "MALL_CREATE_TIME"                          --商城创建时间
        ,MAX(kdsp_order.sale_count) AS                                                                      "MALL_SKU_SALE_COUNT"                       --商城单SKU销售数量
        ,MAX(kdsp_order.freight_fee) AS                                                                     "MALL_SKU_FREIGHT_FEE"                      --商城SKU运费金额
        ,MAX(kdsp_order.freight_deduction_fee) AS                                                           "MALL_SKU_FREIGHT_DEDUCTION_FEE"            --商城SKU运费抵扣金额
        ,MAX(kdsp_order.cash_coupon_deduction_fee) AS                                                       "MALL_SKU_CASH_COUPON_DEDUCTION_FEE"        --商城SKU现金抵扣金额
        ,MAX(kdsp_order.coupon_deduction_fee) AS                                                            "MALL_SKU_COUPON_DEDUCTION_FEE"             --商城SKU优惠券抵扣金额
        ,MAX(kdsp_order.fr_activity_deduction_fee) AS                                                       "MALL_SKU_ACTIVITY_DEDUCTION_FEE"           --商城SKU活动抵扣金额
        ,MAX(kdsp_order.amount) AS                                                                          "MALL_SKU_AMT_NEED_PAY"                     --商城SKU应付金额
        ,MAX(kdsp_order.pay_amount) AS                                                                      "MALL_SKU_PAY_AMOUNT"                       --商城SKU实付金额
        ,MAX(dw_ps_order.VCC_PAY_FLOW_NO) AS                                                                "VCC_PAY_FLOW_NO"                           --VCC支付流水号
        ,SUM(CAST(gsinf.amt_deduct_pay_coupon AS DECIMAL(13,3))/100) AS                                     "PS_SKU_AMT_DEDUCT_PAY_COUPON"              --支付SKU优惠抵扣金额
        ,SUM(dw_ps_order.PS_SKU_VCC_AMT) AS                                                                 "PS_SKU_VCC_AMT"                            --支付SKU VCC金额
        ,SUM(dw_ps_order.PS_SKU_WX_AMT) AS                                                                  "PS_SKU_WX_AMT"                             --支付SKU微信金额
        ,SUM(dw_ps_order.PS_SKU_ZFB_AMT) AS                                                                 "PS_SKU_ZFB_AMT"                            --支付SKU支付宝金额
        ,SUM(dw_ps_order.PS_SKU_VCC_AMT+dw_ps_order.PS_SKU_WX_AMT+dw_ps_order.PS_SKU_ZFB_AMT) AS            "PS_SKU_TOTAL_AMT"                          --支付SKU实付金额
        ,MAX(dw_ps_order.PS_CREATED_AT) AS                                                                  "PS_CREATE_TIME"                            --支付创建时间
    from(
    	select
    		MAX(det.user_uuid) AS                                                                     "USER_UUID"
    		,MAX(det.order_no) AS                                                                     "PS_ORDER_NO"
    		,det.order_goods_id AS                                                                    "PS_ORDER_GOODS_ID"
    		,MAX(IF(det.trade_type=1,det.consume_flow_no,NULL)) AS                                    "VCC_PAY_FLOW_NO"
    		,MAX(IF(det.trade_type<>1,det.consume_flow_no,NULL)) AS                                   "PS_PAY_FLOW_NO"
    		,MAX(det.SKU_NO) AS                                                                       "PS_SKU_NO"
    		,avg(det.trade_method) AS                                                                 "PS_TRADE_MOTHOD"                                   --1:VCC，2:微信支付 ,3:支付宝支付, 4:VCC+WX，5:VCC+AliPay
    		,SUM(case det.trade_type
    			when 1 then
    			CAST(det.trade_amount AS DECIMAL (13, 3)) / 100
    			else
    			0.0
    			end) AS                                                                               "PS_SKU_VCC_AMT"
    		,SUM(case det.trade_type
    			when 2 then
    			CAST(det.trade_amount AS DECIMAL (13, 3)) / 100
    			else
    			0.0
    			end) AS                                                                               "PS_SKU_WX_AMT"
    		,SUM(case det.trade_type
    			when 3 then
    			CAST(det.trade_amount AS DECIMAL (13, 3)) / 100
    			else
    			0.0
    			end) AS                                                                               "PS_SKU_ZFB_AMT"
    		,MAX(inf.created_at) AS                                                                   "PS_CREATED_AT"
    	from
    		ods_pay_service.order_info inf
    		left join ods_pay_service.goods_pay_detail det on det.order_no = inf.order_no
    	where
    		inf.status=1
    		and det.trade_type In (1,2,3)
            and inf.paid_time>={{start_date}}
            and inf.paid_time<{{end_date}}
            -- and inf.order_no IN ('1443758033275613184'
            -- ,'1443835979227156480','1443838616274128896'
            -- )
    	group by
    		det.order_goods_id
    ) dw_ps_order
    left join ods_pay_service.order_goods_info gsinf on gsinf.order_no=dw_ps_order.PS_ORDER_NO and gsinf.id=dw_ps_order.PS_ORDER_GOODS_ID
    left join (
    	select
    		oinf.user_id
    		,case
    		when oinf.root_order_no IS NULL then
    		oinf.order_no
    		else
    		oinf.root_order_no
    		end root_order_no                                                                   --跟单号
    		,oinf.order_no                                                                      --子单号
    		,oinf.order_status                                                                  --订单状态，11-待付款 12-待发货 13-待收货 14-发货中 21-订单完成 31-订单已付款主动取消 32-订单已付款自动取消 33-订单拒收取消 36-订单已付款充值失败取消 41-已付款主动取消退款完成订单关闭 42-已付款自动取消退款完成订单关闭 43-订单未支付主动取消订单关闭 44-订单超时未支付订单关闭 45-订单拒收退款订单关闭 46-订单已付款充值失败已退款订单关闭 51-订单拆单关闭
    		,oinf.pay_status                                                                    --支付状态，1-未支付 2-支付中 3-支付成功 4-支付失败 5-待退款 6-退款中 7-已退款
    		,oinf.order_time                                                                    --下单时间
    		,oinf.order_complete_time                                                           --订单完成时间
    		,oinf.pay_type                                                                      --支付方式 1-微信支付 2-信用支付 3-支付宝支付 4-组合支付(信用和微信) 5-组合支付(信用和支付宝)
    		,oinf.pay_time                                                                      --支付时间
    		,oinf.delivery_at                                                                   --发货时间
    		,oinf.delivery_status                                                               --运单状态 1-未出库 2-已出库 3-已妥投
    		,oinf.channel_id								                                    --销售渠道ID
        	,ka.CHANNEL_NAME AS "CHANNEL_NAME"                                                  --销售渠道
    		,oinf.created_at                                                                    --订单创建时间
    		,oinf.pay_amount AS "ORDER_PAY_AMOUNT"                                              --商品SKU支付金额
    		,gcat1.CATEGROY_NAME AS "categroy_name1"                                            --一级品类
    		,gcat2.CATEGROY_NAME AS "categroy_name2"                                            --二级品类
    		,gcat3.CATEGROY_NAME AS "categroy_name3"                                            --三级品类
    		,osku.id as "order_sku_id"                                                          --t_order_sku主键ID
    		,osku.sku_source                                                                    --商品来源 1-自营 2-京东开普勒 3-京东联盟 4-众联 5-企业购 6-企业购直连
    		,osku.sku_no                                                                        --商品SKU号
    		,osku.sku_id                                                                        --商品SKU ID
    		,osku.sku_name                                                                      --商品SKU名称
    		,sinf.BRAND_NAME                                                                    --商品品牌
    		,osku.shop_id                                                                       --商品店铺ID
    		,osku.shop_name                                                                     --商品店铺名称
    		,osku.price                                                                         --商品销售单价
    		,osku.cost_price                                                                    --商品成本价
    		,osku.amount                                                                        --商品应付金额
    		,osku.pay_amount                                                                    --商品实付金额
    		,osku.count as "sale_count"                                                         --商品单笔销售数量
    		,osku.freight_deduction_fee                                                         --商品运费抵扣金额
    		,osku.coupon_deduction_fee                                                          --商品优惠券抵扣金额
    		,osku.cash_coupon_deduction_fee                                                     --商品现金抵扣金额
    		,osku.fr_activity_deduction_fee                                                     --商品满减抵扣金额
    		,osku.freight_fee                                                                   --商品运费金额
    	from
    		ods_kdsp.t_order_info oinf
    		left join ods_kdsp.t_order_sku osku ON oinf.id = osku.order_info_id
    		left join ods_kdsp.t_sku_info sinf ON sinf.sku_no=osku.sku_no
    		left join ods_kdsp.t_goods_rear_category gcat1 ON gcat1.categroy_id=sinf.c_id1
    		left join ods_kdsp.t_goods_rear_category gcat2 ON gcat2.categroy_id=sinf.c_id2
    		left join ods_kdsp.t_goods_rear_category gcat3 ON gcat3.categroy_id=sinf.c_id3
            left join ods_kdsp.t_ka_channel_info ka ON CAST(ka.channel_no as varchar)=oinf.channel_id
    	where
    		oinf.pay_amount<>0
    		and oinf.order_status NOT IN(51)                                    --不取拆单的
    		and oinf.pay_status NOT IN(1, 2, 4, 5, 6)                   --不取未支付过的
    		and oinf.created_at>=DATE_ADD('MONTH',-1,{{start_date}})        --取向前一个月的订单信息防止跨月
    ) kdsp_order ON kdsp_order.root_order_no=dw_ps_order.PS_ORDER_NO and kdsp_order.sku_no=dw_ps_order.PS_SKU_NO
    group by
    dw_ps_order.USER_UUID
    ,dw_ps_order.PS_ORDER_NO
    ,dw_ps_order.PS_SKU_NO
    ,kdsp_order.order_no
) dw_order
group by
    dw_order.USER_UUID
    ,dw_order.ROOT_ORDER_NO
