开窗积累之学习更新版

1. 开窗使用1之 count range between current row and current row

将相同排序字段的值进行函数计算

selectsku_id,substr(create_date,1,7) date_month,order_id,create_date,sku_num*price,sum(sku_num*price) over (partition by sku_id order by substr(create_date,1,7) range between current row and current row ) month_num_n--dense_rank() over (partition by sku_id order by substr(create_date,1,7) )
from order_detail

2.开窗使用之直接将到某天的累计sum

直接使用sum进行值排列
注意开窗函数是每行都有值,如果要去重,distinct

selectdistinctuser_id,create_date,sum(total_amount) over (partition by user_id order by create_date range between unbounded preceding and current row) sum_xfrom order_info

3.分组优于开窗

左边是分组后,下边是分组前
所以如果想要去重,感觉还是group by 好点,