核心SQL设计者的复杂选择查询问题

Question

非常复杂的查询一直试图构建它几天，并取得了更多的成功。

我使用的是SQL-SERVER 2005标准

我需要的是：从广告活动 5 CampaignVariants而2有设置最大PPU号和3是随机的。

接下来条件是CampaignDailyBudget和CampaignTotalBudget低于什么是在运动设定（计算是通过CampaignVariants连接到广告活动在访问者表的点击次数对哪些用户点击）

下一页条件 CampaignLanguage，CampaignCategory CampaignRegion和CampaignCountry必须是我通过（languageID，categoryID，regionID和countryID）发送给此选择的人。

接下来的条件是我发送到这个select语句的IP地址不会在当前Campaign的IP列表中（我删除了24小时IP的非活动状态）。

换句话说，它得到5个CampaignVariants为进入该网站，当我从用户PublisherRegionUID，IP，语言，国家及地区采取

view diagram

详情

用户i从Visitor获取countryID，regionID，ipID，PublisherRegionUID和languageID。这是过滤器参数。虽然我首先需要得到Publisher要在其网站上显示的内容，然后使用除浏览器区域ID以外的所有参数，通过访问者的参数筛选剩余的所有广告系列。

所以它有两个实际的价格。一个发布商想要发布的内容以及另一个访问者可以查看的内容...

campaignDailyBudget和campaignTotalBudget是创建Campaign的用户设置的值。这两个比较（每个广告系列的点击次数）*（campaignPPU），而日期过滤器明显用于过滤从今天中午12:00到晚上11:59的campaignDailyBudget。 campaignTotalBudget不受日期的原因很明显的

演示存储过滤过程

ALTER PROCEDURE dbo.CampaignsGetCampaignVariants4Visitor 
    @publisherSiteRegionUID uniqueidentifier, 
    @visitorIP varchar(15), 
    @browserID tinyint, 
    @countryID tinyint, 
    @osID tinyint, 
    @languageID tinyint, 
    @acceptsCookies bit 
AS 
BEGIN 
    SET NOCOUNT ON; 

    -- check if such @publisherRegionUID exists 
    if exists(select publisherSiteRegionID from PublisherSiteRegions where [email protected]) 
     begin 

      declare @publisherSiteRegionID int 
      select @publisherSiteRegionID = publisherSiteRegionID from PublisherSiteRegions where [email protected] 

      -- get CampaignVariants 
      -- ** choose 2 highest PPU and 3 random CampaignVariants from Campaigns list 
      -- where regionID,countryID,categoryID,languageID meets Publisher and Visitor requirements 
      -- and Campaign.campaignDailyBudget<(sum of Clicks in Visitors per this Campaign)*Campaign.PPU during this day 
      -- and Campaign.campaignTotalBudget<(sum of Clicks in Visitors per this Campaign)*Campaign.PPU 
      -- and @visitorID does not appear in Campaigns2IPs with this Campaign 

      -- insert visitor 
      insert into Visitors (ipAddress,browserID,countryID,languageID,OSID,acceptsCookies) 
      values (@visitorIP,@browserID,@countryID,@languageID,@OSID,@acceptsCookies) 

      declare @visitorID int 
      select @visitorID = IDENT_CURRENT('Visitors') 

      -- add IP to pool Campaigns ** adding ip to all Campaigns whose CampaignVariants were chosen 

      -- add PublisherRegion2Visitor relationship 
      insert into PublisherSiteRegions2Visitors values (@visitorID,@publisherSiteRegionID) 

      -- add CampaignVariant2Visitor relationship 


     end 



END 
GO 

Answer 1

我也做了一些关于您的斜向需求的假设。我会一边说一边解释代码。请注意，我当然没有合理的方式来测试这个代码是否存在拼写错误或小的逻辑错误。

也许可以将它作为一个单独的ginormous查询来编写，但由于SQL优化器可能会在构建过大查询计划时遇到问题，所以这会非常尴尬，容易出现性能问题。一个选项是将它写成一系列查询，填充临时表以供后续查询使用（这样可以更简单地进行调试）。我选择使用一系列CTE表格将其作为一个大的公用表表达式语句来编写，这很大程度上是因为它的“流动性”更好，它可能比许多临时表版本更好。

第一个假设：那里有几个ciruclar参考。 Campaign有链接到国家和地区的链接，所以必须检查这两个参数值 - 即使基于从Countries到Region的表链接，这个过滤器可能简化为只检查Country（假设国家参数值始终“在”区域参数中）。这同样适用于语言和类别，也可能适用于IP和访问者。这似乎是马虎的设计;如果可以清除它，或者如果可以对数据的有效性进行假设，则可以简化查询。

第二个假设：参数以@Region，@Country等形式作为变量传递。此外，只有一个IP地址被传入;如果没有，那么你需要传入多个值，设置一个包含这些值的临时表，并将其添加为使用@IP参数的过滤器。

所以，第1步是第一遍确定“合格”活动，通过拉掉所有那些共享所需的国家，地区，语言，cateogory，那不有与之相关的一个IP地址：

WITH cteEligibleCampaigns (CampaignId) 
as (select CampaignId 
     from Campaigns2Regions 
     where RegionId = @RegionId 
    intersect select CampaignId 
     from Campaign2Countries 
     where CountryId = @CountryId 
    intersect select CampaignId 
     from Campaign2Languages 
     where LanguageId = @LanguageId 
    intersect select CampaignId 
     from Campaign2Categories 
     where CategoryId = @CategoryId 
    except select CampaignId 
     from Campaigns2IPs 
     where IPID = @IPId) 

下一步，从这些筛选出那些其中“CampaignDailyBudget和CampaignTotalBudget低于什么是在运动设定（计算是通过在其上用户点击CampaignVariants连接到广告活动在访问者表的点击的数目）”的项目。这个要求对我来说并不完全清楚。我选择将其解释为“仅包含这些广告系列，如果您计算这些广告系列的CampaignVariants的访问者人数，则总数将少于CampaignDailyBudget和CampaignTotalBudget”。请注意，这里我介绍一个随机值，稍后用于选择随机行。

,cteTargetCampaigns (CampaignId, RandomNumber) 
    as (select CampaignId, checksum(newid() RandomNumber) 
     from cteEligibleCampaigns ec 
     inner join Campaigns ca 
     on ca.CampgainId = ec.CampaignId 
     inner join CampaignVariants cv 
     on cv.CampgainId = ec.CampaignId 
     inner join CampaignVariants2Visitors cvv 
     on cvv.CampaignVariantId = cv. CampaignVariantId 
     group by ec.CampaignId 
     having count(*) < ca.CampaignDailyBudget 
     and count(*) < CampaignTotalBudget) 

接下来，确定两个“最好”的项目。

,cteTopTwo (CampaignId, Ranking) 
    as (select CampaignId, row_number() over (order by CampgainPPU desc) 
     from cteTargetCampaigns tc 
     inner join Campaigns ca 
     on ca.CampaignId = tc.CampaignId) 

接着，由随机分配数排队所有其他广告活动：

,cteRandom (CampaignId, Ranking) 
    as (select CampaignId, row_number() over (order by RandomNumber) 
     from cteTargetCampaigns 
     where CampaignId not in (select CampaignId 
           from cteTopTwo 
           where Ranking < 3)) 

而且，在最后，拉数据集一起：

select CampaignId 
    from cteTopTwo 
    where Ranking <= 2 
union all select CampaignId 
    from cteRandom 
    where Ranking <= 3 

整上述代码段，调试错误，无效假设以及错过的要求（例如，从随机条目中标识前两项的顺序或标志），并且您应该好。

Answer 2

我不知道我理解您的文章的这一部分：

它得到5个CampaignVariants为用户 进入网站，当我从 用户 PublisherRegionUID，IP，语言，国家 和地区

我假设“它”是查询。给予你第二个“下一个条件”的用户是IP？ “什么时候从用户拿走”是什么意思？这是否意味着您执行查询时的信息或者是您从查询中返回的信息？如果是后者，那么有很多问题需要回答，因为许多专栏是“多：多”关系的一部分。

无论如何，下面是获取5个广告系列的方法，根据您的第二个“下一个条件”，您有一个要过滤掉的IP地址。我还假设你要共计五个活动，这意味着三个随机的不能包含两个“最高PPU”。

With 
    ValidCampaigns As 
    (
    Select C.campaignId 
    From Campaigns As C 
     Left Join (Campaigns2IPs As CIP 
      Join IPs 
       On IPs.ipID = CIP.ipID 
        And IPs.ipAddress = @IPAddress) 
      On CIP.campaignId = C.campaignId 
    Where CIP.campaignID Is Null 
    ) 
    CampaignPPURanks As 
    (
    Select C.campaignId 
     , Row_Number() Over (Order By C.campaignPPU desc) As ItemRank 
    From ValidCampaigns As C 
    ) 
    , RandomRanks As 
    (
    Select campaignId 
     , Row_Number() Over (Order By newid() desc) As ItemRank 
    From ValidCampaigns As C 
     Left Join CampaignPPURanks As CR 
      On CR.campaignId = C.campaignId 
       And CR.ItemRank <= 2 
    Where CR.campaignId Is Null 
    ) 
Select ... 
From CampaignPPURanks As CPR 
    Join CampaignVariants As CV 
     On CV.campaignId = CPR.campaignId 
      And CPR.ItemRank <= 2 
Union All   
Select ... 
From RandomRanks As RR 
    Join CampaignVariants As CV 
     On CV.campaignId = RR.campaignId 
      And RR.ItemRank <= 3