-- 计算每个公司每个月的累计值(这种情况下,如果某个公司某个月没有销售额,这个公司在这个月的记录就没有)
select t.year,
t.month,
companyid,
companyname,
sum(amount) over(partition by companyid, companyname,year order by t.month) amount
from (
select t.year,
t.month,
companyid,
companyname,
sum(amount) amount
from property_finance_final
group by t.year, t.month, companyid, companyname
select t.year,
t.month,
companyid,
companyname,
sum(amount) over(partition by companyid, companyname,year order by t.month) amount
from (
select t.year,
t.month,
companyid,
companyname,
sum(amount) amount
from property_finance_final
group by t.year, t.month, companyid, companyname
) t
--时间补全的截至每个月月底累计值
select substr(t1.the_month,1,4),substr(t1.the_month,6,2),
t2.companyid,t2.companyname,sum(t2.amount) amount
from(
select the_month
from dim.dim_date
where the_date<'2018-07-02'
group by the_month
)t1
left join
(
select t.year,
t.month,
companyid,
companyname,
sum(amount) over(partition by company