SQL-窗口函数

统计2022年和2023年每月的投资额并分别统计2022和2023年度的总额

利用PARTITION by将年份数据分区，利用sum() over()统计累计值，PARTITION by放在over里面

SELECT 
year( investmentdate ) AS yea,
MONTH( investmentdate ) AS mon,
    SUM( investment ) AS mon_total_investment,
    SUM(SUM( investment )) over ( PARTITION by(YEAR ( investmentdate )) ORDER BY MONTH ( investmentdate ) ASC ) AS result 
FROM
    investmentmenttab 
WHERE
    YEAR ( investmentdate ) in( 2022 ,2023)
GROUP BY
year (
    investmentdate),
    MONTH (
    investmentdate)

执行效果如图