MySQL高级查询智能体

Question

我被要求创建一份财务报告，需要给出几个“推荐人”在两个日期之间的总佣金率。这是最简单的部分。

困难之处在于，佣金率不仅取决于推荐人，还取决于，也取决于推荐的类型，也取决于指定推荐者的推荐数量。

对转介人数的跟踪需要考虑到所有的转介，而不是在给定的日期范围内--换句话说，每一个转介者的佣金率都在一个滑动的尺度上，随着其总推荐量的增加而变化。幸运的是，每种类型的推荐最多只有3个佣金级别。

所有的引用都存储在同一个表中，每个引用1行，其中一个字段表示推荐程序和推荐类型。举例说明：

ID   Type    Referrer    Date
1    A       X           01/12/08
2    A       X           15/01/09
3    A       X           23/02/09
4    B       X           01/12/08
5    B       X           15/01/09
6    A       Y           01/12/08
7    A       Y           15/01/09
8    B       Y           15/01/09
9    B       Y           23/02/09

佣金率不存储在转介表中--实际上可能会改变--而是存储在转介者表中，如下所示：

Referrer    Comm_A1    Comm_A2    Comm_A3    Comm_B1    Comm_B2    Comm_B3
X           30         20         10         55         45         35
Y           45         35         25         60         40         30

以上述转介表为例，假设在第1和第2号转介之后(然后保持原样)佣金率有所提高，那么运行2008年12月至2009年2月的委员会报告将返回以下内容：

编辑--为了澄清上述情况，每个类型和每个推荐者的佣金比率有三个级别，第一个推荐委员会的初始费率Comm_A1，然后是第二个推荐委员会的Comm_A2，以及所有后续推荐的Comm_A3。

Referrer    Type_A_Comm    Type_A_Ref    Type_B_Comm    Type_B_Ref
X           60             3             100            2
Y           80             2             100            2

运行2009年2月的委员会报告将返回：

Referrer    Type_A_Comm    Type_A_Ref    Type_B_Comm    Type_B_Ref
X           10             1             0              0
Y           0              0             40             1

编辑上述结果已根据列/行分组从我最初的问题中调整。

我确信，任何解决方案都会涉及子查询(可能是针对每种引用类型)，也会涉及某种聚合/求和，但我很难想出一个有效的查询。

编辑--我不确定是否要编写一个需求方程，但我将在看到这些步骤时，尝试列出这些步骤：

确定每个类型和每个引用者以前引用的数量--也就是说，不考虑任何日期范围。

根据以前转介的次数，选择适当的佣金级别-0以前= 1级，1以前= 2级，2级或更高级别=3级。

(注:一名转介人若没有转介，但如有3名新转介者，则预期佣金为1 x 1级、1 x 2级、1 x 3级=总佣金)

过滤结果根据日期范围--因此，可以确定一段活动期间的佣金。

返回包含引用者的列的数据，以及每种推荐类型的总佣金的列(理想情况下，每种推荐类型的计数也是一列)。

这对澄清我的要求有帮助吗？

Answer 1

假设您有一个名为type的表，该表列出了您的特定引用类型，这应该有效(如果不能，您可以用另一个子选择来代替从引用中获取不同类型)。

select
    r.referrer,
    t.type,
    (case 
        when isnull(ref_prior.referrals, 0) < @max1 then 
            (case 
                when isnull(ref_prior.referrals, 0) + isnull(ref_period.referrals, 0) < @max1 then isnull(ref_period.referrals, 0) 
                else @max1 - isnull(ref_prior.referrals, 0) 
            end) 
        else 0 
    end) * (case t.type when 'A' then r.Comm_A1 when 'B' then r.Comm_B1 else null end) +
    (case when isnull(ref_prior.referrals, 0) + isnull(ref_period.referrals, 0) > @max1 then
        (case 
            when isnull(ref_prior.referrals, 0) < @max2 then 
                (case 
                    when isnull(ref_prior.referrals, 0) + isnull(ref_period.referrals, 0) < @max2 then isnull(ref_period.referrals, 0) 
                    else @max2 - isnull(ref_prior.referrals, 0) 
                end) 
            else 0 
        end) -
        (case 
            when isnull(ref_prior.referrals, 0) < @max1 then 
                (case 
                    when isnull(ref_prior.referrals, 0) + isnull(ref_period.referrals, 0) < @max1 then isnull(ref_period.referrals, 0) 
                    else @max1 - isnull(ref_prior.referrals, 0) 
                end) 
            else 0 
        end)
    else 0 end) * (case t.type when 'A' then r.Comm_A2 when 'B' then r.Comm_B2 else null end) +
    (case when isnull(ref_prior.referrals, 0) + isnull(ref_period.referrals, 0) > @max2 then
        (isnull(ref_period.referrals, 0)) -
            (
                (case when isnull(ref_prior.referrals, 0) + isnull(ref_period.referrals, 0) > @max1 then
                    (case 
                        when isnull(ref_prior.referrals, 0) < @max2 then 
                            (case 
                                when isnull(ref_prior.referrals, 0) + isnull(ref_period.referrals, 0) < @max2 then isnull(ref_period.referrals, 0) 
                                else @max2 - isnull(ref_prior.referrals, 0) 
                            end) 
                        else 0 
                    end) -
                    (case 
                        when isnull(ref_prior.referrals, 0) < @max1 then 
                            (case 
                                when isnull(ref_prior.referrals, 0) + isnull(ref_period.referrals, 0) < @max1 then isnull(ref_period.referrals, 0) 
                                else @max1 - isnull(ref_prior.referrals, 0) 
                            end) 
                        else 0 
                    end)
                else 0 end) +
                (case 
                    when isnull(ref_prior.referrals, 0) < @max1 then 
                        (case 
                            when isnull(ref_prior.referrals, 0) + isnull(ref_period.referrals, 0) < @max1 then isnull(ref_period.referrals, 0) 
                            else @max1 - isnull(ref_prior.referrals, 0) 
                        end) 
                    else 0 
                end)
            )                   
    else 0 end) * (case t.type when 'A' then r.Comm_A3 when 'B' then r.Comm_B3 else null end) as Total_Commission

from referrer r

join type t on 1 = 1 --intentional cartesian product
left join (select referrer, type, count(1) as referrals from referral where date < @start_date group by referrer, type) ref_prior on ref_prior.referrer = r.referrer and ref_prior.type = t.type
left join (select referrer, type, count(1) as referrals from referral where date between @start_date and @end_date group by referrer, type) ref_period on ref_period.referrer = r.referrer and ref_period.type = t.type

这假设您有一个@start_date和@end_date变量，显然您必须提供case语句中缺少的逻辑，以便根据来自ref_total的引用的类型和数量进行适当的费率选择。

编辑

在回顾了这个问题之后，我看到了关于滑动标度的评论。这大大增加了查询的复杂性，但仍然可行。修订后的查询现在还取决于两个变量@max1和@max2的存在，这两个变量表示可以归入类别'1‘和类别'2’的最大销售量(为了测试目的，我分别使用了1和2，这些都产生了预期的结果)。

Answer 2

Adam的答案比我要深入得多，但我认为尝试将其作为一个查询来编写可能不是正确的方法。

您是否考虑过创建一个存储过程，然后逐步创建并填充一个临时表。

临时表将具有正在寻找的结果集的形状。初始insert创建基本数据集(基本上是您希望返回的带键标识符的行数，然后是您希望返回的任何其他行数，可以轻松地作为同一查询的一部分进行组装)。

然后对临时表进行一系列更新，组装更复杂数据的每个部分。

最后，选择“全部返回”并删除临时表。

这样做的好处在于，它可以让你把它分解到你的脑海中，并一次把它组装起来，这样你就可以更容易地找到你错了的地方。这也意味着更复杂的比特可以在几个阶段组装。

此外，如果某个可怜的sod出现了，然后不得不调试整个事件，那么他将更容易跟踪在哪里发生的事情。

Answer 3

编辑:这个答案没有考虑到以下要求，但是似乎有很多新的解释，所以我想我还是把它放在这里吧。

对推荐数量的跟踪需要考虑到所有的推荐，而不是给定日期范围内的推荐。

好的，假设报告期间是每月的，并且使用一个实际的IF可以区分两个有效比率的情况(对于count =1和count > 1)，那么如何：

select
  ref.month, 
  ref.referrer, 
  ref.type,
  ( ref.count * 
      case ref.type
        when 'A' then
          case ref.count
            -- not useful: when 0 then com.Comm_A1
            when 1 then com.Comm_A2
            else com.Comm_A3
          end case
        when 'B' then
          case ref.count
            -- not useful: when 0 then com.Comm_B1
            when 1 then com.Comm_B2
            else com.Comm_B3
          end case
      end case 
  ) as total_commission
from
  ( select
      date_format(date, '%Y-%m') as month,
      referrer,
      type,
      count(*) as count
    from referrals
    group by month, referrer, type
  ) as ref,
join commissions com on com.referrer = ref.referrer

(我想像'ref‘和'count’这样的名字在上面选择得不太好。)