with int_data as (
        select 
         stock_move.product_id  as product_id,
        stock_picking.create_uid as seller_id,
        stock_picking.state as int_state,
        stock_picking_internal.lcl_state as lcl_state,
        stock_move.product_qty as int_quantity,
        stock_picking_internal_box_line.compensate_qty as compensate_qty,
        stock_picking_internal_box_line.receiving_qty as receiving_qty,
        CASE
           when (si.type = 'shelf' 
                AND si.usable_qty > 0 AND (procurement_order_info.order_type != 7 or procurement_order_info.order_type is null)
                OR procurement_order_info.order_type = 7)
                AND procurement_order.remain_count > 0
                AND procurement_order.stock_state in ('partial', 'done')
                AND procurement_order.state in ('running', 'done', 'move_exported','move_confirmed')
            THEN 1
            else 0
         end as is_pending_allocation, 
         CASE
            when procurement_order_transfer_details.state in ('to_be_reviewed', 'move_confirmed', 'partial_exported','partial_cancel')  and (procurement_order_transfer_details.product_qty-procurement_order_transfer_details.transfer_qty-procurement_order_transfer_details.cancel_qty) >0
            THEN 1
            else 0
        end as is_pending_stock_picking,
        stock_warehouse_configuration.warehouse_location as warehouse_location,
        stock_move.procurement_order_id   as child_erp_id, 
        procurement_order_transfer_details.transfer_qty as transfer_qty,
        procurement_order_transfer_details.cancel_qty as cancel_qty,
        procurement_order.remain_count                              as remain_count, 
        ROW_NUMBER() OVER (PARTITION BY stock_move.procurement_order_id) as row_num
        from stock_picking
                left join stock_move on stock_move.picking_id = stock_picking.id
                left join stock_picking_internal on stock_move.picking_id = stock_picking_internal.picking_id
                left join stock_picking_internal_box_line on stock_picking_internal_box_line.move_id = stock_move.id
                left join procurement_order_transfer_details
                   on procurement_order_transfer_details.name = stock_move.apply_batch_no
                left join procurement_order_info on procurement_order_info.procurement_order_id = stock_move.procurement_order_id
                left join procurement_order
                   on procurement_order.id = procurement_order_transfer_details.procurement_order_id

         left join stock_warehouse on stock_warehouse.lot_stock_id = stock_picking.location_dest_id
         left join stock_warehouse_configuration on stock_warehouse.id = stock_warehouse_configuration.id
         LEFT JOIN starmerx_inventory si ON
         procurement_order.product_id = si.product_id
         AND procurement_order.location_id = si.location_id
         where stock_picking_internal.state in ('draft','2bprint','2bmake','pushed','2bdelivery','delivery','received','cancel','packing_canceling','2bweigh_canceling','2bconfirm_canceling','2bmake_canceling','pushed_canceling','2bdelivery_canceling','exception_state') and stock_picking_internal.lcl_state in ('1','2','3','4') )  ,    
         {where_sql}
         group_data as(
         select product_id,
         seller_id,
         warehouse_location,
         sum(case when is_pending_allocation =1 and row_num = 1 then remain_count end) as to_apply,
         sum(case when is_pending_stock_picking = 1 and row_num = 1 then int_quantity-transfer_qty-cancel_qty end) as to_make,
         sum(case when lcl_state = 1 then int_quantity end) as to_lcl,
         sum(case when lcl_state = 4 then int_quantity end) as to_send,
         sum(case when int_state in ('pushed', '2bdelivery') then int_quantity-receiving_qty-compensate_qty end) as on_the_way
         from int_data
         group by product_id, seller_id,warehouse_location
         )
         select product_id,
         seller_id,
         warehouse_location,
         to_apply,
         to_lcl,
         to_make,
         to_send,
         on_the_way
         from group_data limit {page_size} offset {offset}

这段sql该如何优化