1:oracle中sum(num) over(partition by order by col)
分组组内累计求和
select v1,v2,sum(v2) over(partition by v1 order by v2) as sum --先分组,组内在进行 1 中的操作
其中over(partition by v1 order by v2) as sum中partition 能分组,还具备累计的功能,
2:mysql实现相同效果,分组组内累计求和
将相同的表在做一次关联,让里面的值作为比较参数
各个月的统计值;
select DATE_FORMAT(gmt_create,'%Y%m') months, count(DISTINCT case_name, case_type, run_product_name) count from case_result WHERE case_type = #{caseType} group by months;
目标:针对于累计求和,在统计出各个月份的count后要是当前月份的count数等于前几个月count之和,我们在之前的基础上加上月份关联即可。
方式一:on关联,目标查询关联一次,每次都能取最新月的累计值。
如:5月数据10,六月数据20,此查询结果5月10,六月查询结果30,(6月之前所有数据之和累计值)
SELECT
s1.months, s1.case_type,
sum( s2.num ) as total
FROM
( SELECT DATE_FORMAT( gmt_create, '%Y-%m' ) months, count( DISTINCT case_name, case_type, run_product_name ) num, case_type
FROM case_result WHERE case_type =#{caseType}
GROUP BY
months ) s1
JOIN ( SELECT DATE_FORMAT( gmt_create, '%Y-%m' ) months, count( DISTINCT case_name, case_type, run_product_name ) num, case_type
FROM case_result WHERE case_type = #{caseType}
GROUP BY
months ) s2
ON s1.months >= s2.months
GROUP BY
s1.months;
方式二:where关联
结果同上
SELECT
s1.months, s1.case_type,
sum( s2.num ) as total
FROM
( SELECT DATE_FORMAT( gmt_create, '%Y-%m' ) months, count( DISTINCT case_name, case_type, run_product_name ) num, case_type
FROM case_result WHERE case_type =#{caseType}
GROUP BY
months ) s1
JOIN ( SELECT DATE_FORMAT( gmt_create, '%Y-%m' ) months, count( DISTINCT case_name, case_type, run_product_name ) num, case_type
FROM case_result WHERE case_type = #{caseType}
GROUP BY
months ) s2
ON 1=1
WHERE s1.months >= s2.months
GROUP BY
s1.months;
区别:oracle一行函数over(partition by v1 order by v2) as sum实现,mysql嵌套查询,查询结果作为参数再次查询,日期作为参数,取最大日期,值取最大日期对应的累计值。
总结:
mysql实现核心就是将相同的表在做一次关联,让里面的值作为比较参数