MySQL高级查询智能摘要

假设您有一个名为 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,它们产生了预期的结果)。

亚当的回答比我想的要彻底得多,但我认为把它写成一个查询可能不是正确的方法。

是否考虑过创建一个存储过程,该存储过程将逐步创建并填充一个临时表。

临时表将具有您要查找的结果集的形状。初始插入将创建基本数据集(基本上是要用键标识符返回的行数,然后是要返回的任何其他行数,这些行数可以很容易地作为同一查询的一部分进行组装)。

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

最后,将其全部选回并删除临时表。

这样做的好处是,它可以让你在脑海中把它分解,然后一次组装一点,这样你就可以更容易地发现哪里出了问题。这也意味着更复杂的钻头可以分几个阶段组装。

此外,如果出现了一些可怜的SOD,而且事后必须调试整个系统,那么他就更容易追踪到底发生了什么。

编辑:这个答案没有考虑到下面的要求,但似乎有一堆新的解释,所以我想我会把它留在这里。

跟踪转介的数量需要考虑所有转介,而不是给定日期范围内的转介。

好的,假设报告周期是每月一次,并且使用一个实际的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”这样的名字在上面选择得不太好。)