sql server 重复率

huang0817

大佬们，求助个问题
假设现在有个数据库表：orderform
有2列字段，分别是订单号：dd   商品编号：sp
然后现在要用sql server 查订单中商品对比其他订单商品的重复率

例如订单D01有3样商品，跟D22对比重复率100%
反过来订单D22有4样商品，跟D01订单比重复率75%

这样的效果能实现吗

YUYELOVE

感觉应该是笛卡积后，然后再判断，思路如下请参考：

[SQL] 纯文本查看 复制代码

SELECT t1dd, t2dd, SUM(same) / COUNT(*)  
FROM (
    SELECT t1.dd AS t1dd, t1.sp as t1sp, t2.dd AS t2dd, t2.sp as t2sp, 
    CASE WHEN t1.sp = t2.sp THEN 1 ELSE 0 END AS same
    FROM (SELECT * FROM orderform) as t1, orderform as t2
    WHERE t1.dd != t2.dd) tmp
GROUP BY t1dd, t2dd;

Allansoft

如果订单D22中有2个商品，且全是订单D01中有的，这种情况重复率应该是多少

cshadow

这样？

[SQL] 纯文本查看 复制代码

SELECT COUNT(t1.sp) AS 重复数, (SELECT COUNT(sp) FROM orderform WHERE dd = 'D01' ) AS t1合计,
COUNT(t1.sp)*1.0 / (SELECT COUNT(sp) FROM orderform WHERE dd = 'D01' ) * 100
FROM 
(
SELECT sp 
FROM orderform
WHERE dd = 'D01') AS t1 JOIN
(
SELECT sp 
FROM orderform
WHERE dd = 'D22'
)  AS t2 ON t1.sp = t2.sp;

SELECT COUNT(t2.sp) AS 重复数, (SELECT COUNT(sp) FROM orderform WHERE dd = 'D22' ) AS t2合计,
COUNT(t2.sp)*1.0 / (SELECT COUNT(sp) FROM orderform WHERE dd = 'D22' ) * 100
FROM 
(
SELECT sp 
FROM orderform
WHERE dd = 'D01') AS t1 JOIN
(
SELECT sp 
FROM orderform
WHERE dd = 'D22'
)  AS t2 ON t1.sp = t2.sp

cshadow

cshadow 发表于 2024-4-24 03:18
这样？

[SQL] 纯文本查看 复制代码

[/quote]SELECT COUNT(t1.sp) AS 重复数, (SELECT COUNT(sp) FROM orderform WHERE dd = 'D01' ) AS D01合计,
COUNT(t1.sp)*1.0 / (SELECT COUNT(sp) FROM orderform WHERE dd = 'D01' ) * 100 占D01重复率 ,
(SELECT COUNT(sp) FROM orderform WHERE dd = 'D22' ) AS D22合计,
COUNT(t2.sp)*1.0 / (SELECT COUNT(sp) FROM orderform WHERE dd = 'D22' ) * 100 占D22重复率
FROM 
(
SELECT sp 
FROM orderform
WHERE dd = 'D01') AS t1 JOIN
(
SELECT sp 
FROM orderform
WHERE dd = 'D22'
)  AS t2 ON t1.sp = t2.sp;