mysql窗口函数-累计求和sum over[通俗易懂]

mysql窗口函数-累计求和sum over[通俗易懂]先上一个简单的demo,按时间周期累计求和:SELECT date, sales, sum(sales)over(ORDERBYdate)AScum_salesFROM salesORDERBY dateASC;再来一个demo,按字段class(自定义)进行分类并累计求和:SELECT date, sales, sum(sales)over(PARTITIONBYclassORDERBYdate)AScum_sale

大家好,欢迎来到IT知识分享网。

先上一个简单的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

免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://yundeesoft.com/24225.html

(0)
上一篇 2023-08-26 14:33
下一篇 2023-08-27 19:00

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

关注微信