询报价相似度

-- 品牌，货号，英文名，中文名，规格
--

with target as (
SELECT
inq_m.id,
inq_m.create_time,
count(distinct inq_d.id) c
FROM t_inq_order inq_m
LEFT JOIN t_inq_order_detail inq_d
ON inq_m.id = inq_d.inq_order_id

where inq_m.id='4463064887204708352'   -- 输入
GROUP BY inq_m.id
)

SELECT
inq_d.inq_order_id,
-- avg(similarity(inq_d.brand_name, target_d.brand_name)) brand_sim,
-- sum(similarity(inq_d.brand_no, target_d.brand_no)) / (max(target.c)+abs(max(target.c)-count(distinct inq_d.id))) cat_no_sim,
-- sum(similarity(inq_d.en_name, target_d.en_name)) / (max(target.c)+abs(max(target.c)-count(distinct inq_d.id))) en_name_sim,
-- sum(similarity(inq_d.cn_name, target_d.cn_name)) / (max(target.c)+abs(max(target.c)-count(distinct inq_d.id))) cn_name_sim,
-- sum(similarity(inq_d.cas, target_d.cas)) / (max(target.c)+abs(max(target.c)-count(distinct inq_d.id))) cas_sim,
max(target.c),
count(distinct inq_d.id),
sum(similarity(inq_d.brand_no, target_d.brand_no)) / (max(target.c)+abs(max(target.c)-count(distinct inq_d.id))) +
GREATEST(
    sum(similarity(inq_d.en_name, target_d.en_name)) / (max(target.c)+abs(max(target.c)-count(distinct inq_d.id))),
    sum(similarity(inq_d.cn_name, target_d.cn_name)) / (max(target.c)+abs(max(target.c)-count(distinct inq_d.id)))
)+
sum(similarity(inq_d.cas, target_d.cas)) / (max(target.c)+abs(max(target.c)-count(distinct inq_d.id))) sim

FROM t_inq_order_detail inq_d, target

LEFT JOIN t_inq_order_detail target_d
ON target.id = target_d.inq_order_id

where inq_d.create_time > target.create_time - interval '1 month'
and inq_d.inq_order_id != target.id  -- 剔除源记录

GROUP BY inq_d.inq_order_id
-- having sim > 10

-- ORDER BY cat_no_sim desc nulls last
ORDER BY sim desc nulls last