先上一个简单的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
) resWHERE
sale_cnt>0 and back_cnt>0 and
back_rate < 0.2
ORDER BY
c_week_id【Mysql|mysql窗口函数-累计求和sum over】
推荐阅读
- mysql|mysql 窗口函数 求和_mysql窗口函数
- 工具教程|MySQL8.0.26安装超详细教程〔超详细〕
- mySql数据库|mysql安装教程8.0.26
- database|Mysql 8.0.26安装，听我的6分钟让你安装成功！
- 安装|mysql 8.0.28版本安装配置方法图文教程
- 安装Mysql 8.0
- 数据库|Mysql-8.0.26-winx64下载和安装
- 数据库|2021MySql-8.0.26安装详细教程（保姆级）
- windows|MySQL8.0.26版本的windows安装教程