用“over( /equivalent by.)”替换旧版本5.5.60 MariaDB

Question

下面的脚本中是否有重写子句“over(.的分区)”的选项，请为MariaDB的较旧版本5.5.60进行重写？这个脚本对于更新版本好，但是这个子句对于MariaDB5.5.60是不可用的(不是可执行的)

select
    a.year,
    a.company,
    b.dt,
    case 
        when a.year = 2018 
            then sum(b.quantity)
        when a.year = 2020
            then sum(b.quantity) 
                - sum(case when a.year = 2018 then sum(b.quantity) else 0 end) 
                  over(partition by a.company, b.dt)
    end quantity
from taba a
inner join tabb b on b.id = a.id
group by
    a.year,
    a.company,
    b.dt

有人知道这个条款的一些变通之处吗？

我将非常感谢对前面提到的MariaDB版本的脚本进行更正。

Answer 1

由于窗口函数是在MarieDB 10.2.0中引入的，请考虑加入聚合子查询，以等同于条件SUM() OVER()调用。

select
    a.year,
    a.company,
    b.dt,
    case 
        when a.year = 2018 
            then sum(b.quantity)
        when a.year = 2020
            then sum(b.quantity) - sub.calc_qty
    end quantity
from taba a
inner join tabb b on b.id = a.id
inner join 
   (select sub_a.company, sub_b.dt,
           sum(case
                    when sub_a.year = 2018
                    then sub_b.quantity
                    else 0     
               end) as calc_qty
    from taba sub_a
    inner join tabb sub_b on sub_b.id = sub_a.id
    group by sub_a.company, sub_b.dt
   ) sub
 on sub.company = a.company 
 and sub.dt = b.dt

group by
    a.year,
    a.company,
    b.dt