# 窗口函数大揭秘！轻松计算数据累计占比，玩转数据分析的绝佳利器

08/22 09:11

1.Mysql (>=8.0)

2. PostgreSQL(>=11)
3. SQL Server(>=2012)
4. Oracle(>=8i)
5. SQLite(>=3.28.0)

# 需求背景

1. 各个耗材的每日累计损耗量。

2. 各个耗材的当月每日余量。

3. 各个耗材的每月累计消耗占比。

# 查询各个耗材的每日累计损耗量

select cate,record_date,init_value,SUM(cost) over(partition by cate,MONTH(record_date) order by record_date ) as cm_cost

from material_data md;



SUM(cost) over(partition by cate,MONTH(record_date) order by record_date )；



select cate,record_date,init_value,SUM(cost) over(partition by cate,MONTH(record_date) order by record_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as cm_cost

from material_data md;



ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW



# 查询各个耗材的当月每日余量

select

cate,

record_date,

init_value,

init_value - SUM(cost) over(partition by cate,MONTH(record_date) order by record_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as material_num

from material_data md;



select

cate,

record_date,

init_value,

init_value - SUM(cost) over(partition by cate,MONTH(record_date) order by record_date ) as material_num

from material_data md;



# 查询各个耗材的每月累计消耗占比

select

md.cate,

record_date,

init_value,

cost/ sum(cost) over(partition by cate,MONTH(record_date) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cm_cost

from material_data md



select

md.cate,

record_date,

init_value,

cost/ sum(cost) over(partition by cate,MONTH(record_date)) as cm_cost

from material_data md



Excel中自定义手写签名

0 评论
0 收藏
0