查找顺序值的有效方法

Question

每个“产品”最多可以有10000个“细分”行。这些细分受众群具有一个针对每种产品（1、2、3、4、5，…）从1开始的排序列，以及一个值列，该列可以包含诸如（323.113、5423.231、873.42、422.64、763.1，…）。

我想确定给定细分子集的产品的潜在匹配。例如，如果我按正确的顺序有5个细分值，那么如何在细分表中某处有效地找到所有具有相同顺序的5个细分的所有产品？

Answer 1

假设这样的表：

CREATE TABLE Products
 (
   ProductId  int           not null
    constraint PK_Products
     primary key
  ,Name       varchar(100)  not null
 )

CREATE TABLE Segments
 (
   ProductId   int    not null
    constraint FK_Segments__Products
     foreign key references Products (ProductId)
  ,OrderBy     int    not null
  ,Value       float  not null
  ,constraint PK_Segments
    primary key (ProductId, OrderBy)
 )

接下来，在临时表中设置搜索数据：

CREATE TABLE #MatchThis
 (
   Position  int    not null
  ,Value     float  not null
 )

对于N个搜索对象，必须像这样填充

First item   0    <value 1>
Second item  1    <value 2>
Third item   2    <value 3>
...
Nth item     N-1  <value N>

现在设置一些重要的值。（这可以塞入最终查询中，但是这种方式使它更易于阅读，并且可能会稍微提高性能。）

DECLARE
  @ItemCount   int
 ,@FirstValue  float

--  How many items to be matched ("N", above)
SELECT @ItemCount = count(*)
 from #MatchThis

--  The value of the first item in the search set
SELECT @FirstValue = Value
 from #MatchThis
 where Position = 0

然后，这只是一个查询：

SELECT
   pr.Name
  ,fv.OrderBy  --  Required by the Group By, but otherwise can be ignored
 from #MatchThis mt
  cross join (--  All Segments that match the first value in the set
              select ProductId, OrderBy
               from Segment
               where Value = @FirstValue) fv
  inner join Product pr  --  Just to get the Product name
   on pr.ProductId = fv.ProductId
  inner join Segment se
   on se.ProductId = fv.ProductId
    and se.OrderBy = fv.OrderBy + mt.Position  --  Lines them up based on the first value
    and se.Value = mt.Value                    --  No join if the values don't match
 group by
   pr.Name
  ,fv.OrderBy
 having count(*) = @ItemCount  --  Only include if as many segments pulled for this product/segment.OrderBy as are required

我坚信这是可行的，但是我现在没有时间详细测试它。为了优化性能，除了指示的主键之外，您还可以在Segment.Value上添加常规索引