SQL子查询 - 有更好的方法

This is an SQL efficiency question.

这是一个SQL效率问题。

A while back I had to write a collection of queries to pull data from an ERP system. Most of these were simple enough but one of them resulted in a rather ineficient query and its been bugging me ever since as there's got to be a better way.

不久前,我不得不编写一系列查询来从ERP系统中提取数据。其中大部分都很简单,但其中一个导致了一个相当低效的查询,并且一直困扰着我,因为必须有更好的方法。

The problem is not complex. You have rows of sales data. In each row you have quantity, sales price and the salesman code, among other information.

问题并不复杂。您有一系列销售数据。在每一行中,您都有数量,销售价格和销售员代码以及其他信息。

Commission is paid based on a stepped sliding scale. The more they sell, the better the commission. Steps might be 1000, 10000, 10000$ and so forth. The real world problem is more complex but thats it essentially it.

佣金是基于阶梯式滑动比例支付的。他们卖的越多,佣金越多。步数可能是1000,10000,10000 $等等。现实世界的问题更复杂,但就其本质而言。

The only way I found of doing this was to do something like this (obviously not the real query)

我发现这样做的唯一方法是做这样的事情(显然不是真正的查询)

select qty, price, salesman,
  (select top 1 percentage from comissions 
    where comisiones.salesman = saleslines.salesman 
    and saleslines.qty > comisiones.qty
    order by comissiones.qty desc
  ) percentage
from saleslines

this results in the correct commission but is horrendously heavy.

这导致了正确的佣金,但是非常沉重。

Is there a better way of doing this? I'm not looking for someone to rewrite my sql, more 'take a look as foobar queries' and I can take it from there.

有更好的方法吗?我不是在找人重写我的sql,更多的是“看看foobar查询”,我可以从那里拿走它。

The real life commission structure can be specified for different salesmen, articles and clients and even sales dates. It also changes from time to time, so everything has to be driven by the data in the tables... i.e I can't put fixed ranges in the sql. The current query returns some 3-400000 rows and takes around 20-30 secs. Luckily its only used monthly but the slowness is kinda bugging me.

可以为不同的销售人员,物品和客户甚至销售日期指定现实生活佣金结构。它也会不时变化,所以一切都必须由表中的数据驱动...即我不能在sql中放置固定范围。当前查询返回大约3-400000行,大约需要20-30秒。幸运的是它只用了一个月,但缓慢有点烦我。

This is on mssql.

这是在mssql上。

Ian

edit:

I should have given a more complex example from the beginning. I realize now that my initial example is missing a few essential elements of the complexity, apologies to all.

我应该从一开始就给出一个更复杂的例子。我现在意识到,我最初的例子是遗漏了复杂性的一些基本要素,向所有人道歉。

This may better capture it

这可能更好地捕获它

select client-code, product, product-family, qty, price, discount, salesman,
    (select top 1 percentage from comissions 
        where comisiones.salesman = saleslines.salesman 
        and saleslines.qty > comisiones.qty
        and [
           a collection of conditions which may or may not apply:
           Exclude rows if the salesman has offered discounts above max discounts
                which appear in each row in the commissions table
           There may be a special scale for the product family
           There may be a special scale for the product
           There may be a special scale for the client

           A few more cases
            ]
        order by [
            The user can control the order though a table 
            which can prioritize by client, family or product
            It normally goes from most to least specific.
            ]
      ) percentage
    from saleslines

needless to say the real query is not easy to follow. Just to make life more interesting, its naming is multi language.

不用说真正的查询并不容易理解。为了让生活更有趣,它的命名是多语言。

Thus for every row of salesline the commission can be different.

因此,对于每一行销售线,佣金可以是不同的。

It may sound overly complex but if you think of how you would pay commission it makes sense. You don't want to pay someone for selling stuff at high discounts, you also want to be able to offer a particular client a discount on a particular product if they buy X units. The salesman should earn more if they sell more.

这可能听起来过于复杂,但如果你想到如何支付佣金就有意义了。您不希望以高折扣价格向某人付款,如果他们购买X单位,您也希望能够为特定客户提供特定产品的折扣。如果他们卖得更多,推销员应该赚更多钱。

In all the above I'm excluding date limited special offers.

在上述所有内容中,我不包括日期限制特别优惠。

I think partitions may be the solution but I need to explore this more indepth as I know nothing about partitions. Its given me a few ideas.

我认为分区可能是解决方案,但我需要更深入地探讨这个问题,因为我对分区一无所知。它给了我一些想法。

2 个解决方案

#1

If you are using a version of SQL Server that supports common-table expressions such as SQL Server 2005 and later, a more efficient solution might be:

如果您使用的是支持公用表表达式(如SQL Server 2005及更高版本)的SQL Server版本,则更有效的解决方案可能是:

With RankedCommissions As
    (
    Select SL.qty, SL.price, SL.salesman, C.percentage
        , Row_Number() Over ( Partition By SL.salesman Order By C.Qty Desc ) As CommissionRank
    From SalesLines As SL
        Join Commissions As C
            On SL.salesman = C.salesman
                 And SL.qty > C.qty
    )
Select qtr, price, salesman, percentage
From RankedCommissions
Where CommissionRank = 1

If you needed to account for the possibility that there are no Commissions values for a given salesman where the SalesLine.Qty > Commission.Qty, then you could do something like:

如果您需要考虑SalesLine.Qty> Commission.Qty中给定销售员没有佣金值的可能性,那么您可以执行以下操作:

With RankedCommissions As
    (
    Select SL.qty, SL.price, SL.salesman, C.percentage
        , Row_Number() Over ( Partition By SL.salesman Order By C.Qty Desc ) As CommissionRank
    From SalesLines As SL
        Join Commissions As C
            On SL.salesman = C.salesman
                And SL.qty > C.qty
    )
Select SL.qtr, SL.price, SL.salesman, RC.percentage
From SalesLines As SL
    Left Join RankedCommissions As RC
        On RC.salesman = SL.salesman
            And RC.CommissionRank = 1

#2

select 
     qty, price, salesman, 
     max(percentage)
from saleslines 
     inner join comissions on commisions.salesman = saleslines.salesman and 
          saleslines.qty > comissions.qty
group by 
     qty, price, salesman

#1