产品成交信息

SELECT
prd.id,
prd.cat_no 货号,
prd.cas,
coalesce(so_summary.近一年成交次数::float / NULLIF(GREATEST(inq_summary.近一年询价次数, so_summary.近一年成交次数), 0), 0) 近一年成交率,
inq_summary.*,
so_summary.*,
coalesce(rd32_summary.近一年毛利率, 0) 近一年毛利率

FROM t_product prd

-- 询价汇总 inq_summary
    INNER JOIN LATERAL (
        SELECT
        COUNT(distinct inq_d.inq_order_id) FILTER(WHERE inq_m.create_time >= now() - INTERVAL '12 month') 近一年询价次数

        FROM "public".t_inq_order_detail_offer inq_quo

        INNER JOIN "public".t_inq_order_detail inq_d
        ON inq_quo.id = inq_d.id

        INNER JOIN "public".t_inq_order inq_m
        ON inq_d.inq_order_id = inq_m.id

        WHERE 1=1
        AND inq_quo.is_delete = 1
        AND inq_quo.brand_no = prd.cat_no
--      AND inq_quo.create_time >= now() - INTERVAL '1 month'

--      GROUP BY inq_quo.brand_no
    ) inq_summary
    ON TRUE

    -- 销售订单汇总
    INNER JOIN LATERAL (
        SELECT
        count(distinct so_d."ID") filter(WHERE so_m."dDate" >= now() - interval '12 month') 近一年成交次数
        FROM u8_009."SO_SODetails" so_d

        INNER JOIN u8_009."SO_SOMain" so_m
        ON so_d."ID" = so_m."ID"

        LEFT JOIN "public".t_inq_sync_crm_detail crm_so
        ON crm_so.sal_order_detail_id = so_d."iSOsID"::text

        WHERE so_m.dverifysystime is not NULL
        AND (so_d."cSCloser" is null or so_d."cSCloser" = 'asuser')
        AND so_d."cInvCode" = prd.cat_no
    ) so_summary
    ON TRUE

LEFT JOIN (
    SELECT
    rd32_d."cInvCode",
    avg(((so_d."iSum" - coalesce(crm_so_d.loss_amount, 0)) / so_d."iQuantity" * rd32_d."iQuantity" - rd32_d."iPrice") / nullif(((so_d."iSum" - coalesce(crm_so_d.loss_amount, 0)) / so_d."iQuantity" * rd32_d."iQuantity"), 0)) 近一年毛利率
    FROM u8_009.rdrecord32 rd32_m

    LEFT JOIN u8_009.rdrecords32 rd32_d
    ON rd32_d."ID" = rd32_m."ID"

    LEFT JOIN u8_009."SO_SODetails" so_d
    ON so_d."iSOsID" = rd32_d.iorderdid

    LEFT JOIN "public".t_inq_sync_crm_detail crm_so_d
    ON crm_so_d.sal_order_detail_id = so_d."iSOsID"::text
    AND crm_so_d.is_delete = 1

    LEFT JOIN u8_009."Inventory" inv
    ON inv."cInvCode"= rd32_d."cInvCode"

    WHERE 1=1
--  AND rd32_m."cCusCode" = cus.code
    AND rd32_m."dDate" >= now() - '1 year'::interval
    AND rd32_m.dnverifytime is not null
    AND rd32_d."iPrice" is not null
    AND rd32_d."iQuantity" > 0
    AND inv."cInvCCode" in (
--  '03011006',
    '03021005'  -- 药物线
--  '03031001',
--  '03051001'
    )
    GROUP BY rd32_d."cInvCode"
) rd32_summary
ON rd32_summary."cInvCode" = prd.cat_no

WHERE 1=1
AND prd.is_delete = 1
AND prd.brand_name = 'cato'
AND prd.cat_no = 'C4X-201663'   -- 改这里