sql-拓展篇

1 存储过程：create proc

1.1 基本用法

创建：

CREATE PROCEDURE [拥有者.]存储过程名[;程序编号]
@[参数名] [类型],@[参数名] [类型]
[WITH
{RECOMPILE | ENCRYPTION , ENCRYPTION}
]
[FOR REPLICATION]
AS 程序行
BEGIN
...
END;

删除：

drop procedure 存储过程名;

1.2 应用示例

【例】创建-》执行存储过程

if (exists (select * from sys.objects where name = '月报'))
    drop proc 月报
go
create proc 月报(
@平台 varchar(100) ,
@日期 varchar(100) )
as 
begin  ---begin与end成对出现，可都不写
select * FROM [月报全览]
where  平台=@平台 and 日期>=@日期
end
;

exec 月报  'jingdong','2021-02-01';  --执行语句

2 开窗函数：over()

2.1 聚合函数vs开窗函数

1. 普通的聚合函数用group by分组，每个分组返回一个统计值；
2. 分析函数采用partition by分组，并且每组每行都可以返回一个统计值；
3. 开窗函数指定了函数所能影响的窗口范围，也就是说在这个窗口范围中都可以受到函数的影响，有些分析函数就是开窗函数。

2.2 基本用法

 function_name over(partition by 分组字段 order by 排序字段)  

其中，function_name有以下几种：

1. 聚合函数：sum、min、max、avg、count
2. 排名函数：
   ① row_number( )：返回一个唯一的值，相同排名按照记录集中记录的顺序依次递增。
   ② rank( )：返回一个唯一的值，相同排名并列，和下一条不同记录的排名间隔断。
   ③ dense_rank( )：返回一个唯一的值，相同排名并列，和下一条不同记录的排名间紧邻递增。
   

2.3 应用示例

【例】查询最新采购单价（sql server，用于排序)

select * into 最新采购价格  from
 (SELECT * from (select *,row_number() over(partition by sku,月份 order by 采购时间 desc) as rn from 最新入库清单)a
where a.rn=1)b

3 条件插入：merge into

3.1 基本用法

MERGE INTO..USING ..ON..
WHEN MATCHED THEN 【UPDATE..SET..】
WHEN NOT MATCHED THEN 【INSERT..VALUES (..)】;

【注意】merge可根据与源表联接的结果，对目标表执行插入、更新或删除操作，但merge无法多次更新同一行，也无法更新和删除同一行，故需保证数据唯一性。

3.2 应用示例

【例】综合应用：子查询-窗口函数over-联合union-条件插入merge into

MERGE INTO 国内仓销售出库成本v1 as bb  
USING (
select a.*,b.需扣除总跌价 总跌价,b.需扣除总跌价*0.032*收入/sum(收入) over(partition by a.发货月份)  as 总跌价分摊
from (select * from 国内仓销售出库成本v1  where 平台 in ('x','y','z') ) a
left join 本期总跌价v1_2020年  b on a.发货月份=b.本月
UNION ALL
select a.*,b.需扣除总跌价 总跌价,b.需扣除总跌价*(1-0.032)*收入/sum(收入) over(partition by a.发货月份)  as 总跌价分摊
from  (select * from 国内仓销售出库成本v1  where 平台 not in ('x','y','z') ) a
left join 本期总跌价v1_2020年  b on a.发货月份=b.本月
) aa
ON aa.唯一性=bb.唯一性
WHEN MATCHED THEN
update set bb.需扣除总跌价=aa.总跌价分摊;