sql - 旧版本 5.5.60 MariaDB 的“over(partition by..)”替换/等效项
问题描述
请在下面的脚本中为旧版本的 MariaDB 5.5.60 重写子句“<strong>over(partition by..)”是否有任何选项?此脚本适用于较新版本,但该子句对于 MariaDB-5.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 的脚本。
解决方案
由于在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