基于多列加权排序的结果获取

Question

我的问题非常有趣，需要这里的所有sql母版的帮助。

我有以下输入表格，以接受用户的输入：

​

​

用户将输入c1、c2等的值、方差和权重。

结果表如下所示

​

​

我需要根据以下条件从结果表中检索数据：

1. 用户c1输入的值应在表的c1列中与给定的方差百分比相匹配。输入的值是15000，%是10，所以匹配应该是10% +- in表。
2. 结果应按用户对每一栏的权重排序。最大称重是5。即使C1值精确匹配，而c2值接近方差匹配，如果c2的权重较高，则c2行应位于顶部。

我尝试了以下查询：

DECLARE @c1 int; set @c1 = 15000;
DECLARE @c2 int; set @c2 = 4;
DECLARE @c3 int; set @c3 = 570;
DECLARE @c4 int; set @c4 = 2000;
DECLARE @c5 int; set @c5 = 450;
select results.*, ((((@c1 - c1val) * 100 / @c1 ) * 6-1) + (((@c2 - c2val) * 100 / @c2) * 6 - 1) + (((@c3 - c3val) * 100 / @c3) * 6-5)+ (((@c4 - c4val) * 100 / @c4) * 6-3)+ (((@c5 - c5val) * 100 / @c5) * 6-3)) AS relevance 
from results
where (c1val <= @c1 - (@c1 * 10 /100) or c1val > = @c1 +  (@c1 * 10 /100)) or 
      (c2val <= @c2 - (@c2 * 25 /100) or c2val > = @c2 + (@c2 * 25 /100)) or
      (c3val <= @c3 - (@c3 * 20 /100) or c3val > = @c3 + (@c3 * 20 /100)) or
      (c4val <= @c4 - (@c4 * 10 /100) or c4val > = @c4 + (@c4 * 10 /100)) or
      (c5val <= @c5 - (@c5 * 15 /100) or c5val > = @c5 + (@c5 * 15 /100))

order by relevance desc

但效果不太好。

Answer 1

首先，您需要小数运算，声明@c1等小数点。否则，所有表达式都被转换为int。接下来，我想您需要abs(@c1 - c1val)等相关表达式。另外，我的猜测是，相关性与偏差成反比。

试一试

DECLARE @c1 decimal(7,2) = 15000
      , @var1 decimal(4,3) = 10./100;
DECLARE @d1 decimal(7,3) = @c1 * @var1
      , @w1 int = 1;
...
select * 
from(
    select results.*
        , (100 - abs(@c1 - c1val) * 100 / @c1)  * (6 - @w1) 
        +  ...  AS relevance 
    from results
    where (c1val between @c1 - @d1 and  @c1 + @d1)
       AND  ...
) t
order by relevance desc

关联可能需要一种调整，以获得一个适当的重量和偏差的平衡。

Answer 2

以下查询工作完美：

ALTER PROCEDURE [dbo].[GetResult] 
    @c1 as decimal(7,2), @c2 as decimal(7,2), @c3 as decimal(7,2), @c4 as decimal(7,2), @c5 as decimal(7,2), 
    @v1 as decimal, @v2 as decimal, @v3 as decimal, @v4 as decimal, @v5 as decimal, 
    @w1 as int, @w2 as int, @w3 as int, @w4 as int, @w5 as int
AS
BEGIN

DECLARE @var1 decimal(4,3) = @v1/100;
DECLARE @d1 decimal(7,3) = @c1 * @var1

DECLARE @var2 decimal(4,3) = @v2/100;
DECLARE @d2 decimal(7,3) = @c2 * @var2;

DECLARE @var3 decimal(4,3) = @v3/100;
DECLARE @d3 decimal(7,3) = @c3 * @var3

DECLARE @var4 decimal(4,3) = @v4/100;
DECLARE @d4 decimal(7,3) = @c4 * @var4

DECLARE @var5 decimal(4,3) = @v5/100;
DECLARE @d5 decimal(7,3) = @c5 * @var5

select * 
from(
    select results.*
        ,   (abs(@c1 - c1val) / @c1 * @w1 +
            abs(@c2 - c2val) / @c2 * @w2 +
            abs(@c3 - c1val) / @c3 * @w3 +
            abs(@c4 - c1val) / @c4 * @w4 +
            abs(@c5 - c1val) / @c5 * @w5 / 5)
            AS relevance 
    from results
    where (c1val between @c1 - @d1 and  @c1 + @d1) or
          (c2val between @c2 - @d2 and  @c2 + @d2) or
          (c3val between @c3 - @d3 and  @c3 + @d3) or
          (c4val between @c4 - @d4 and  @c4 + @d4) or
          (c5val between @c5 - @d5 and  @c5 + @d5)
) t
order by relevance
END