小编典典

查找顺序值的有效方法

sql

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

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



阅读 211

收藏
2021-04-28

共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上添加常规索引

2021-04-28