mysql窗口函数-累计求和sum over

先上一个简单的demo，按时间周期累计求和：SELECTdate,sales,sum( sales ) over ( ORDER BY date ) AS cum_salesFROMsalesORDER BYdate ASC;再来一个demo，按字段class（自定义）进行分类并累计求和：SELECTdate,sales,sum( sales ) over ( PARTITION BY class

丿潇湘丶书笛

5199人浏览 · 2022-01-30 14:02:04

丿潇湘丶书笛 · 2022-01-30 14:02:04 发布

先上一个简单的demo，按时间周期累计求和：

SELECT
	date,
	sales,
	sum( sales ) over ( ORDER BY date ) AS cum_sales 
FROM
	sales 
ORDER BY
	date ASC;

再来一个demo，按字段class（自定义）进行分类并累计求和：

SELECT
	date,
	sales,
	sum( sales ) over ( PARTITION BY class ORDER BY date ) AS cum_sales 
FROM
	sales 
ORDER BY
	date ASC;

下面是一个实际例子，有兴趣可以了解了；

这里是根据预定义参数，按周进行累计求和，涉及字符串去中文、取产品名等操作，比较长

SELECT
	* 
FROM
	(
	SELECT
		se_sale.c_week_id,
		RIGHT ( se_sale.c_week_id, 2 ) week_num,
		'ALL' product_type,
		se_sale.sale_cnt ,
		se_sale.sale_add ,
		se_back.back_cnt ,
		se_back.back_add ,
		ifnull( se_back.back_add / se_sale.sale_add, 0 ) back_rate 
	FROM
		(
		SELECT
			sou.c_week_id,
			sou.sale_cnt,
			sum( sou.sale_cnt ) over ( ORDER BY sou.c_week_id ) sale_add 
		FROM
			(
			SELECT
				se.c_week_id,
				sum( sale.quantity_ordered ) sale_cnt 
			FROM
				( SELECT c_week_id, c_start_time, c_end_time FROM product_week_info WHERE YEAR ( c_start_time ) = ${p_year} ) se
				LEFT JOIN (
				SELECT
					purchase_date_by_local purchase_date,
					trim(
					REPLACE ( CONVERT ( SUBSTRING_INDEX( REPLACE ( product_name, "product", "Product" ), "Product",- 1 ) USING ASCII ), "?", "" )) product_type,
					quantity_ordered 
				FROM
					all_order 
				) sale ON sale.purchase_date BETWEEN se.c_start_time 
				AND se.c_end_time 
				AND sale.product_type IN ( SELECT product_type FROM product_quality_product_class WHERE product_class = 'phone' ) 
			GROUP BY
				se.c_week_id
			) sou

		) se_sale -- 到mssql转full join
		LEFT JOIN (
		SELECT
			sou.c_week_id,
			sou.back_cnt,
			sum( sou.back_cnt ) over (ORDER BY sou.c_week_id ) back_add 
		FROM
			(
			SELECT
				se.c_week_id,
				sum( back.quantity ) back_cnt 
			FROM
				( SELECT c_week_id, c_start_time, c_end_time FROM product_week_info WHERE YEAR ( c_start_time ) = ${p_year} ) se
				LEFT JOIN (
				SELECT
					return_at,
					trim(
					REPLACE ( CONVERT ( SUBSTRING_INDEX( REPLACE ( product_name, "product", "Product" ), "Product",- 1 ) USING ASCII ), "?", "" )) product_type,
					quantity 
				FROM
					return_order 
				) back ON back.return_at BETWEEN se.c_start_time 
				AND se.c_end_time 
				AND back.product_type IN ( SELECT product_type FROM product_quality_product_class WHERE product_class = 'phone' ) 
			GROUP BY
				se.c_week_id
			) sou 
		) se_back ON se_sale.c_week_id = se_back.c_week_id
	) res
WHERE
	sale_cnt>0 and back_cnt>0 and
	back_rate < 0.2 


UNION ALL
SELECT
	* 
FROM
	(
	SELECT
		se_sale.c_week_id,
		RIGHT ( se_sale.c_week_id, 2 ) week_num,
		se_sale.product_type,
		se_sale.sale_cnt ,
		se_sale.sale_add ,
		se_back.back_cnt ,
		se_back.back_add ,
		ifnull( se_back.back_add / se_sale.sale_add, 0 ) back_rate 
	FROM
		(
		SELECT
			sou.c_week_id,
			sou.product_type,
			sou.sale_cnt,
			sum( sou.sale_cnt ) over ( PARTITION BY sou.product_type ORDER BY sou.c_week_id ) sale_add 
		FROM
			(
			SELECT
				se.c_week_id,
				sale.product_type,
				sum( sale.quantity_ordered ) sale_cnt 
			FROM
				( SELECT c_week_id, c_start_time, c_end_time FROM product_week_info WHERE YEAR ( c_start_time ) = ${p_year} ) se
				LEFT JOIN (
				SELECT
					purchase_date_by_local purchase_date,
					trim(
					REPLACE ( CONVERT ( SUBSTRING_INDEX( REPLACE ( product_name, "product", "Product" ), "Product",- 1 ) USING ASCII ), "?", "" )) product_type,
					quantity_ordered 
				FROM
					all_order 
				) sale ON sale.purchase_date BETWEEN se.c_start_time 
				AND se.c_end_time 
				AND sale.product_type IN ( SELECT product_type FROM product_quality_product_class WHERE product_class = 'phone' ) 
			GROUP BY
				se.c_week_id,
				sale.product_type 
			) sou
		) se_sale -- 到mssql转full join
		LEFT JOIN (
		SELECT
			sou.c_week_id,
			sou.product_type,
			sou.back_cnt,
			sum( sou.back_cnt ) over ( PARTITION BY sou.product_type ORDER BY sou.c_week_id ) back_add 
		FROM
			(
			SELECT
				se.c_week_id,
				back.product_type,
				sum( back.quantity ) back_cnt 
			FROM
				( SELECT c_week_id, c_start_time, c_end_time FROM product_week_info WHERE YEAR ( c_start_time ) = ${p_year} ) se
				LEFT JOIN (
				SELECT
					return_at,
					trim(
					REPLACE ( CONVERT ( SUBSTRING_INDEX( REPLACE ( product_name, "product", "Product" ), "Product",- 1 ) USING ASCII ), "?", "" )) product_type,
					quantity 
				FROM
					return_order 
				) back ON back.return_at BETWEEN se.c_start_time 
				AND se.c_end_time 
				AND back.product_type IN ( SELECT product_type FROM product_quality_product_class WHERE product_class = 'phone' ) 
			GROUP BY
				se.c_week_id,
				back.product_type 
			) sou 
		) se_back ON se_sale.c_week_id = se_back.c_week_id
		and se_sale.product_type = se_back.product_type
	) res

WHERE
	sale_cnt>0 and back_cnt>0 and
	back_rate < 0.2 
	
ORDER BY
	c_week_id