sql server code week14

-------------------------------------------------------------------------------------------------
-- 使用数据库
use AdventureWorks
---------如何使用排名函数------- 出现在 select 后面---------
-- row_number()-- 返回有序数字
-- rank() -- 出现了并列的现象可是生成的不是有序数字
-- dense_rank() -- 不但出现了并列而且生成了有序数字
-- ntile(n) -- 给表的数据进行分组 ntile(组的个数)
--------------------  over  ------------------
-- row_number()  over(order by desc|asc)
select  * from [HumanResources].[EmployeePayHistory]
go
-- 检索 那个员工的工资最高
select  EmployeeID ,Rate 
from [HumanResources].[EmployeePayHistory]
order by Rate desc
go
-- 检索后 给这些员工进行 排名 
-- 使用排名ROW_NUMBER()函数生成了连续的数字 
select EmployeeID,rate,
ROW_NUMBER() over(order by rate desc ) as 'ROW_NUMBER_排名'
from [HumanResources].[EmployeePayHistory]
go
select EmployeeID,Rate,
rank()       over(order by rate desc ) as 'RANK_排名' -- 出现了并列的现象可是生成的不是有序数字
from [HumanResources].[EmployeePayHistory]
go
select EmployeeID,Rate,
dense_rank() over(order by rate desc ) as 'DENSE_RANK_排名'
from [HumanResources].[EmployeePayHistory]
go
--  实例：
-- 条件 ： 根据销量值    where
-- 目标：销售人员
-- 特殊点：每个销售区  partition by
select * from [Sales].[SalesPerson] -- 销售部销售人员表
go
select SalesPersonID,TerritoryID,SalesQuota,SalesYTD
from [Sales].[SalesPerson] -- 销售部销售人员表
---------------------这个函数 是表中有区域 ，然后我们在区域中进行排名
-- TerritoryID 表示的是区域号  
select distinct TerritoryID from [Sales].[SalesPerson]
/*以 青岛为例
 市南区 1
 市北区 2
 崂山区 3
 城阳区 4
 即墨区 5
 平度区 6
 黄岛区 7
 .....  ...
*/
-----SalesYTD  Sales YTD  year to date 年初到目前为止 
-- 查看 每个区域的销售排名 partition by TerritoryID
-- over(partition by TerritoryID order by SalesYTD desc )
select SalesPersonID,TerritoryID,SalesYTD ,
dense_rank() over(partition by TerritoryID order by SalesYTD desc ) as N'区域的销售排名'
from [Sales].[SalesPerson]
where TerritoryID is not null
go
----- 
select * from [HumanResources].[Employee]
select EmployeeID,BirthDate from [HumanResources].[Employee]
---这个表中没有区域，我们使用ntile(组的个数) 函数 按照特定条件 ，进行分组
select EmployeeID,BirthDate,HireDate ,
ntile(4) over(order by BirthDate) as 'ntile'
from [HumanResources].[Employee]
where datepart(mm,HireDate)>=4 and datepart(yy,HireDate) >=2001
go

select EmployeeID,BirthDate,HireDate ,
ntile(6) over(order by BirthDate) as 'ntile'
from [HumanResources].[Employee]
where datepart(mm,HireDate)>=4 and datepart(yy,HireDate) >=2001

go
select datepart(mm,HireDate) from [HumanResources].[Employee]
select datepart(yy,HireDate) from [HumanResources].[Employee]
-------------------------------------------------------------------
create table Emp_SalesData(
 [year] int ,
 [month] int,
 productId varchar(20) ,
 amount int
)
/*
 P1: 苹果手机
 P2:华为手机
 P3:8848太空手机
*/
insert into Emp_SalesData values(2012,2,'P1',20000)
,(2012,2,'P1',20000)
,(2012,2,'P2',12000)
,(2012,3,'P1',15000)
,(2012,3,'P2',8000)
,(2012,4,'P1',12000)
,(2012,4,'P2',14000)
,(2012,5,'P3',14000)
go
select * from Emp_SalesData  
go
-- 聚合函数 在数据库中 经常与分组函数 在一起 group by 
-- 同一个表中出现了 不同的月份
-- 1 我想 查每一个月份的销售总额
select [month] ,sum(amount) as N'统计的每月销售总量' 
from Emp_SalesData 
group by [month] -- 分组查询
-- 2 想要看看 哪个月份销售的最高
select productId, avg(amount) -- 聚合函数不会随意的和无关列搭配
from Emp_SalesData 
where productId like 'p%' -- where 在 group by 关键字前 作为 构成分组查询的条件出现
group by productId  -- 是按照商品编号 进行分组
having avg(amount) <40000  -- 分组查询时 注意 使用的条件查询关键字时 having
order by avg(amount) -- order by 可以和聚合函数一起使用
go
select productId,sum(amount) as N'统计的每件商品的销售总量'
from Emp_SalesData
group by productId
go
select productId,sum(amount) as N'Tatol'
from Emp_SalesData
group by productId
go

-- 分析函数和排名函数都属于逻辑函数 
-- 分析函数可以和聚合函数相配合  主要分析数据
--  分析函数 也 需要 over子句的配合
-- 3 分析函数 下月的销售量
select [month] ,sum(amount) as 'total',
lead(sum(amount),3) over (order by [month]) as N'下月的销售量'
from Emp_SalesData
group by [month]
order by [month]
-- 4 分析函数 上月的销售量
select [month] ,sum(amount) as 'total',
lag(sum(amount),2) over (order by [month]) as N'上月的销售量'
from Emp_SalesData
group by [month]
order by [month]
-- 5  目前 返回有序记录集中的第一个值 
--原表：select * from Emp_SalesData  
-- 检索 一年中的第一个月
select distinct  productId ,
first_value([month]) over(partition by productId order by [month] )
as N'一年中的第一个月'
from Emp_SalesData
go
-- 6 检索 一年中的最近一个月
select distinct  productId ,
last_value([month]) 
over(partition by productId order by [month] rows between current row
and unbounded following)
as N'一年中的最近一个月'
from Emp_SalesData
go
--原表：select * from Emp_SalesData  
-- 系统函数
select host_id()
select host_name()
select SUSER_SID('sa')
select DB_NAME(1)
select DB_ID('master')
select user_name(1) as UserName
select cast([NationalIDNumber] as char(20)) as IDNUMBER
from [HumanResources].[Employee]
go
select [NationalIDNumber] from [HumanResources].[Employee]