窗口函数

从SQL Server 2005起,SQL Server伊始扶助窗口函数 (Window
Function),以及到SQL Server
2012,窗口函数效用增强,近年来甘休协助以下三种窗口函数:

[The Swift Programming Language 中文版]
本页包蕴内容:

函数–>作为javascript的中坚模块单元,用于代码复用、音信隐藏和组成调用

函数的定义:

函数也叫做艺术,是出名字的闭包。

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

  3. 剖析函数 (Analytic Function) ;

  4. NEXT VALUE FOR Function, 那是给sequence专用的一个函数;

函数定义与调用(Defining and Calling Functions)
函数参数与返回值(Function Parameters and Return Values)
函数参数名称(Function Parameter Names)
函数类型(Function Types)
嵌套函数(Nested Functions)

函数对象、Function objects

函数的参数:

 

函数是用来形成一定职责的单独的代码块。你给一个函数起一个适宜的名字,用来标识函数做如何,并且当函数须要实施的时候,那个名字会被用来“调用”函数。

–> 1.javascript中函数就是目的,对象字面量爆发的对象连接受
 Object.prototype;函数对象连接受Function.prototype(该原型对象自我连到Object.prototype)。

无参数
func sayHelloWorld() -> String {
        return "hello, world"
}
print(sayHelloWorld())```
#####有多个参数且未定义外部参数

func sayHello(personName: String, alreadyGreeted: Bool) -> String {
if alreadyGreeted {
return sayHelloAgain(personName)
} else {
return sayHello(personName)
}
窗口函数。}
print(sayHello(“Tim”, alreadyGreeted: true))
//七个参数调用时,要是没有注解外部参数第四个参数不用写外部参数。
// Prints “Hello again, Tim!” “`

一. 排序函数(Ranking
Function)

斯威夫特统一的函数语法充裕灵活,可以用来代表其他函数,包罗从最简易的没有参数名字的
C 风格函数,到复杂的带一些和表面参数名的 Objective-C
风格函数。参数可以提供默许值,以简化函数调用。参数也可以既当做传入参数,也作为传出参数,也就是说,一旦函数执行完成,传入的参数值能够被修改。

–> 2.每个函数创建有三个隐藏属性:函数的上下文和“调用”属性。

有三个参数并且首个参数定义了表面参数那时第三个参数的外部参数无法大致
func sayHello(personName personName: String,alreadyGreeted alreadyGreeted: Bool) -> String {
      if alreadyGreeted {
               return sayHelloAgain(personName)
        } else {
                return sayHello(personName)
 }
 }
 print(sayHello(personName: "Tim", alreadyGreeted: true))
 //多个参数调用时,如果声明了外部参数第一个参数需要写外部参数。
 // Prints "Hello again, Tim!"```
#####有多个参数且在参数名前面加“_”,调用时不需要写外部参数名

func sayHello(personName: String, _ alreadyGreeted: Bool) -> String
{
if alreadyGreeted {
return sayHelloAgain(personName)
} else {
return sayHello(personName)
}
}
print(sayHello(“Tim”, true))
//多少个参数调用时,申明参数名前面加“_”,调用时不须要写外部参数名。
// Prints “Hello again, Tim!”“`

扶植文档里的代码示例很全。

在 斯威夫特中,每个函数都有一种档次,包蕴函数的参数值类型和重返值类型。你可以把函数类型当做任何其它一般变量类型一样处理,那样就可以更不难地把函数当做其他函数的参数,也得以从任何函数中回到函数。函数的定义可以写在其余函数定义中,这样可以在嵌套函数范围内达成效益封装。

–>
3.每个函数对象在开创时会有一个prototype属性,它的值拥有一个construction属性且值即为该函数对象,那和隐藏连接到Function.prototype完全分歧

涵盖默许参数调用的时候可以不用传参则使用默许参数
func someFunction(parameterWithDefault: Int = 12) {
 // function body goes here
 // if no arguments are passed to the function call,
 // value of parameterWithDefault is 12
 }
 someFunction(6) // parameterWithDefault is 6
 someFunction() // parameterWithDefault is 12```
#####可变参数,在参数类型后面加“...”代表参数个数可变但是每个函数最多只有一个可变参数

func arithmeticMean(numbers: Double…) -> Double {
var total: Double = 0
for number in numbers {
total += number
}
return total / Double(numbers.count)
}
arithmeticMean(1, 2, 3, 4, 5)
// returns 3.0, which is the arithmetic mean of these five numbers
arithmeticMean(3, 8.25, 18.75)
// returns 10.0, which is the arithmetic mean of these three
numbers“`

排序函数中,ROW_NUMBER()较为常用,可用于去重、分页、分组中甄选数据,生成数字辅助表等等;

函数的概念与调用(Defining and Calling Functions)
当您定义一个函数时,你可以定义一个或多个闻名字和花色的值,作为函数的输入(称为参数,parameters),也得以定义某体系型的值作为函数执行完成的出口(称为重返类型,return
type)。

–>
4.此前说过函数是目的,由此它可以像任何任何值一样被接纳,函数可以保存在变量、对象和数组中;函数可以视作参数传递给任何函数,也得以再回到函数。

函数通过传引用,通过在参数名前面加上inout关键字
func swapTwoInts(inout a: Int, inout _ b: Int) {
          let temporaryA = a
          a = b
          b = temporaryA
 }
 var someInt = 3
 var anotherInt = 107
 swapTwoInts(&someInt, &anotherInt)
 print("someInt is now \(someInt), and anotherInt is now \(anotherInt)")
 // Prints "someInt is now 107, and anotherInt is now 3"```
####函数的返回值:
#####因为函数没有返回值所以这里不需要写返回值得箭头"->"

func sayGoodbye(personName: String) {
print(“Goodbye, (personName)!”)
}
sayGoodbye(“Dave”)
// Prints “Goodbye, Dave!”“`

排序函数在语法上要求OVER子句里必须含ORDER
BY,否则语法不通过,对于不想排序的情况可以那样变化;

每个函数有个函数名,用来描述函数执行的任务。要运用一个函数时,你用函数名“调用”,并传给它卓殊的输入值(称作实参,arguments)。一个函数的实参必须与函数参数表里参数的逐条一致。

函数唯有一个重回值
func printAndCount(stringToPrint: String) -> Int {
       print(stringToPrint)
return stringToPrint.characters.count
}
let count = printAndCount("hello, world")
// prints "hello, world" and returns a value of 12```
#####函数还可以有多个返回值

func minMax(array: [Int]) -> (min: Int, max: Int) {
var currentMin = array[0]
var currentMax = array[0]
for value in array[1..<array.count] {
if value < currentMin {
currentMin = value
} else if value > currentMax {
currentMax = value
}
}
return (currentMin, currentMax)
}
let bounds = minMax([8, -6, 2, 109, 3, 71])
print(“min is (bounds.min) and max is (bounds.max)”)
// 通过如此的方式收受七个重临值“`

drop table if exists test_ranking

create table test_ranking
( 
id int not null,
name varchar(20) not null,
value int not null
) 

insert test_ranking 
select 1,'name1',1 union all 
select 1,'name2',2 union all 
select 2,'name3',2 union all 
select 3,'name4',2

select id , name, ROW_NUMBER() over (PARTITION by id ORDER BY name) as num
from test_ranking

select id , name, ROW_NUMBER() over (PARTITION by id) as num
from test_ranking
/*
Msg 4112, Level 15, State 1, Line 1
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.
*/

--ORDERY BY后面给一个和原表无关的派生列
select id , name, ROW_NUMBER() over (PARTITION by id ORDER BY GETDATE()) as num
from test_ranking

select id , name, ROW_NUMBER() over (PARTITION by id ORDER BY (select 0)) as num
from test_ranking

在底下例子中的函数叫做”sayHello(_:)”,之所以叫那些名字,是因为那几个函数用一个人的名字当做输入,并回到给这个人的问候语。为了已毕这几个义务,你定义一个输入参数-一个名叫
personName 的 String 值,和一个涵盖给此人问候语的 String
类型的重回值:

函数有可选重临值时在回来值括号外加“?”可以回去nil
func minMax(array: [Int]) -> (min: Int, max: Int)? {
    if array.isEmpty { return nil }
         var currentMin = array[0]
         var currentMax = array[0]
    for value in array[1..<array.count] {
          if value < currentMin {
              currentMin = value
           } else if value > currentMax {
               currentMax = value
        }
   }
     return (currentMin, currentMax)
 }```
####函数类型:
#####函数类型和其他类型一样使用这就意味着函数可以先声明再赋值并且让函数可以像参数一样传递,像参数一样返回
定义一个函数类型为(Int, Int) -> Int的函数他表示有两个Int型参数和一个Int型返回值的类型,addTwoInts对mathFunction这个函数赋值

func addTwoInts(a: Int, _ b: Int) -> Int {
return a + b
}
var mathFunction: (Int, Int) -> Int = addTwoInts
//函数类型做为参数类型应用定义一个名为mathFunction的参数的printMathResult函数并把上文的addTwoInts函数传入其中
func printMathResult(mathFunction: (Int, Int) -> Int, _ a: Int, _
b: Int) {
print(“Result: (mathFunction(a, b))”)
}
printMathResult(addTwoInts, 3, 5)
// Prints “Result: 8″“`

 

func sayHello(personName: String) -> String {
    let greeting = "Hello, " + personName + "!"
    return greeting
}
函数类型做为重临值类型使用
//定义chooseStepFunction函数它的返回值是一个参数为Int返回值为Int的函数当Bool为true时返回stepBackward 当Bool为false时返回stepForward
 func stepForward(input: Int) -> Int {
         return input + 1
 }
 func stepBackward(input: Int) -> Int {
         return input - 1
 }
 func chooseStepFunction(backwards: Bool) -> (Int) -> Int {
         return backwards ? stepBackward : stepForward
 }
 var currentValue = 3
 let moveNearerToZero = chooseStepFunction(currentValue > 0)
        // moveNearerToZero now refers to the stepBackward() function```
####函数里面定义函数:
Swift在函数中还可以声明和调用函数,如在chooseStepFunction函数中声明stepForward函数和stepBackward函数。并作为返回值返回

func chooseStepFunction(backwards: Bool) -> (Int) -> Int {
func stepForward(input: Int) -> Int { return input + 1 }
func stepBackward(input: Int) -> Int { return input – 1 }
return backwards ? stepBackward : stepForward
}“`

二. 聚合函数 (Aggregate
Function)

拥有的那一个新闻汇聚起来成为函数的定义,并以 func
作为前缀。指定函数重临类型时,用重回箭头
->(一个连字符后跟一个右尖括号)后跟回来类型的名目的法门来代表。

SQL Server 2005中,窗口聚合函数仅辅助PARTITION
BY,也就是说仅能对分组的多少总体做聚合运算;

该定义描述了函数做什么样,它希望接收什么和履行完成时它回到的结果是什么样品种。那样的概念使得函数可以在其余地方以一种清晰的法门被调用:

SQL Server 2012始发,窗口聚合函数支持ORDER
BY,以及ROWS/RAGNE选项,原本须要子查询来已毕的需求,如: 移动平均
(moving averages), 总括聚合 (cumulative aggregates), 累计求和 (running
totals) 等,变得越发惠及;

print(sayHello("Anna"))
// prints "Hello, Anna!"
print(sayHello("Brian"))
// prints "Hello, Brian!"

 

调用 sayHello(🙂 函数时,在圆括号中传给它一个 String 类型的实参,例如
sayHello(“Anna”)。因为这么些函数重临一个 String 类型的值,sayHello
可以被含有在 print(
:separator:terminator:)
的调用中,用来输出这么些函数的重回值,正如下边所示。

代码示例1:总括/小计/累计求和

在 sayHello(_:) 的函数体中,先定义了一个新的名为 greeting 的 String
常量,同时,把对 personName 的致敬新闻赋值给了 greeting 。然后用 return
关键字把这几个问候再次回到出去。一旦 return greeting
被调用,该函数甘休它的施行并赶回 greeting 的此时此刻值。

drop table if exists test_aggregate;

create table test_aggregate
(
event_id      varchar(100),
rk            int,
price         int
)

insert into test_aggregate
values
('a',1,10),
('a',2,10),
('a',3,50),
('b',1,10),
('b',2,20),
('b',3,30)


--1. 没有窗口函数时,用子查询
select a.event_id, 
       a.rk,  --build ranking column if needed
       a.price, 
     (select sum(price) from test_aggregate b where b.event_id = a.event_id and b.rk <= a.rk) as totalprice 
  from test_aggregate a


--2. 从SQL Server 2012起,用窗口函数
--2.1 
--没有PARTITION BY, 没有ORDER BY,为全部总计;
--只有PARTITION BY, 没有ORDER BY,为分组小计;
--只有ORDER BY,没有PARTITION BY,为全部累计求和(RANGE选项,见2.2)
select *,
     sum(price) over() as TotalPrice,
     sum(price) over(partition by event_id) as SubTotalPrice,
       sum(price) over(order by rk) as RunningTotalPrice
  from test_aggregate a

--2.2 注意ORDER BY列的选择,可能会带来不同结果
select *,
     sum(price) over(partition by event_id order by rk) as totalprice 
  from test_aggregate a
/*
event_id    rk    price    totalprice
a    1    10    10
a    2    10    20
a    3    50    70
b    1    10    10
b    2    20    30
b    3    30    60
*/

select *,
     sum(price) over(partition by event_id order by price) as totalprice 
  from test_aggregate a
/*
event_id    rk    price    totalprice
a    1    10    20
a    2    10    20
a    3    50    70
b    1    10    10
b    2    20    30
b    3    30    60
*/

--因为ORDER BY还有个子选项ROWS/RANGE,不指定的情况下默认为RANGE UNBOUNDED PRECEDING AND CURRENT ROW 
--RANGE按照ORDER BY中的列值,将相同的值的行均视为当前同一行
select  *,sum(price) over(partition by event_id order by price) as totalprice from test_aggregate a
select  *,sum(price) over(partition by event_id order by price range between unbounded preceding and current row) as totalprice from test_aggregate a

--如果ORDER BY中的列值有重复值,手动改用ROWS选项即可实现逐行累计求和
select  *,sum(price) over(partition by event_id order by price rows between unbounded preceding and current row) as totalprice from test_aggregate a

您可以用区其他输入值多次调用
sayHello(_:)。上边的例证体现的是用”Anna”和”Brian”调用的结果,该函数分别重返了不一致的结果。

 

为了简化那几个函数的定义,可以将问候信息的创建和重临写成一句:

代码示例2:移动平均

func sayHelloAgain(personName: String) -> String {
    return "Hello again, " + personName + "!"
}
print(sayHelloAgain("Anna"))
// prints "Hello again, Anna!"
--移动平均,举个例子,就是求前N天的平均值,和股票市场的均线类似
drop table if exists test_moving_avg

create table test_moving_avg
(
ID    int, 
Value int,
DT    datetime
)

insert into test_moving_avg 
values
(1,10,GETDATE()-10),
(2,110,GETDATE()-9),
(3,100,GETDATE()-8),
(4,80,GETDATE()-7),
(5,60,GETDATE()-6),
(6,40,GETDATE()-5),
(7,30,GETDATE()-4),
(8,50,GETDATE()-3),
(9,20,GETDATE()-2),
(10,10,GETDATE()-1)

--1. 没有窗口函数时,用子查询
select *,
(select AVG(Value) from test_moving_avg a where a.DT >= DATEADD(DAY, -5, b.DT) AND a.DT < b.DT) AS avg_value_5days
from test_moving_avg b

--2. 从SQL Server 2012起,用窗口函数
--三个内置常量,第一行,最后一行,当前行:UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW 
--在行间移动,用BETWEEN m preceding AND n following (m, n > 0)
SELECT *,
       sum(value) over (ORDER BY DT ROWS BETWEEN 5 preceding AND CURRENT ROW) moving_sum,
       avg(value) over (ORDER BY DT ROWS BETWEEN 4 preceding AND CURRENT ROW) moving_avg1,
       avg(value) over (ORDER BY DT ROWS BETWEEN 5 preceding AND 1 preceding) moving_avg2,
       avg(value) over (ORDER BY DT ROWS BETWEEN 3 preceding AND 1 following) moving_avg3
FROM  test_moving_avg
ORDER BY DT

函数参数与再次回到值(Function Parameters and Return Values)
函数参数与再次来到值在 Swift中颇为灵活。你可以定义任何项目的函数,包蕴从只带一个未名参数的简便函数到复杂的涵盖表明性参数名和见仁见智参数选项的扑朔迷离函数。

 

无参函数(Functions Without Parameters)

三. 剖析函数 (Analytic
Function)

函数可以没有参数。上面那个函数就是一个无参函数,当被调用时,它回到固定的
String 音讯:

代码示例1:取当前行某列的前一个/下一个值

func sayHelloWorld() -> String {
    return "hello, world"
}
print(sayHelloWorld())
// prints "hello, world"
drop table if exists test_analytic

create table test_analytic
(
SalesYear         varchar(10),
Revenue           int,
Offset            int
)

insert into test_analytic
values
(2013,1001,1),
(2014,1002,1),
(2015,1003,1),
(2016,1004,1),
(2017,1005,1),
(2018,1006,1)

--当年及去年的销售额
select *,lag(Revenue,1,null) over(order by SalesYear asc) as PreviousYearRevenue from test_analytic
select *,lag(Revenue,Offset,null) over(order by SalesYear asc) as PreviousYearRevenue from test_analytic
select *,lead(Revenue,1,null) over(order by SalesYear desc) as PreviousYearRevenue from test_analytic

--当年及下一年的销售额
select *,lead(Revenue,1,null) over(order by SalesYear asc) as NextYearRevenue from test_analytic
select *,lead(Revenue,Offset,null) over(order by SalesYear asc) as NextYearRevenue from test_analytic
select *,lag(Revenue,1,null) over(order by SalesYear desc) as NextYearRevenue from test_analytic

--可以根据offset调整跨度

尽管那几个函数没有参数,可是定义中在函数名后或者要求一对圆括号。当被调用时,也亟需在函数名后写一对圆括号。

 

多参数函数 (Functions With Multiple Parameters)

代码示例2:分组中某列最大/最小值,对应的其余列值

函数可以有多种输入参数,这个参数被含有在函数的括号之中,以逗号分隔。

若是有个门禁系统,在员工每便进门时写入一条记下,记录了“身份号码”,“进门时间”,“衣服颜色”,查询每个员工最终三次进门时的“衣裳颜色”。

其一函数用一个人名和是或不是业已打过招呼作为输入,并赶回对此人的方便问候语:

drop table if exists test_first_last

create table test_first_last
(
EmployeeID             int,
EnterTime              datetime,
ColorOfClothes         varchar(20)
)

insert into test_first_last
values
(1001, GETDATE()-9, 'GREEN'),
(1001, GETDATE()-8, 'RED'),
(1001, GETDATE()-7, 'YELLOW'),
(1001, GETDATE()-6, 'BLUE'),
(1002, GETDATE()-5, 'BLACK'),
(1002, GETDATE()-4, 'WHITE')

--1. 用子查询
--LastColorOfColthes
select * from test_first_last a
where not exists(select 1 from test_first_last b where a.EmployeeID = b.EmployeeID and a.EnterTime < b.EnterTime)

--LastColorOfColthes
select *
from 
(select *, ROW_NUMBER() over(partition by EmployeeID order by EnterTime DESC) num
from test_first_last ) t
where t.num =1


--2. 用窗口函数
--用LAST_VALUE时,必须加上ROWS/RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING,否则结果不正确
select *, 
       FIRST_VALUE(ColorOfClothes) OVER (PARTITION BY EmployeeID ORDER BY EnterTime DESC) as LastColorOfClothes,
       FIRST_VALUE(ColorOfClothes) OVER (PARTITION BY EmployeeID ORDER BY EnterTime ASC) as FirstColorOfClothes,
       LAST_VALUE(ColorOfClothes) OVER (PARTITION BY EmployeeID ORDER BY EnterTime ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as LastColorOfClothes,
       LAST_VALUE(ColorOfClothes) OVER (PARTITION BY EmployeeID ORDER BY EnterTime DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as FirstColorOfClothes
from test_first_last

--对于显示表中所有行,并追加Last/First字段时用窗口函数方便些
--对于挑选表中某一行/多行时,用子查询更方便
func sayHello(personName: String, alreadyGreeted: Bool) -> String {
    if alreadyGreeted {
        return sayHelloAgain(personName)
    } else {
        return sayHello(personName)
    }
}
print(sayHello("Tim", alreadyGreeted: true))
// prints "Hello again, Tim!"

 

您通过在括号内传递一个String参数值和一个标识为alreadyGreeted的Bool值,使用逗号分隔来调用sayHello(_:alreadyGreeted:)函数。

四. NEXT VALUE FOR Function

当调用当先一个参数的函数时,第二个参数后的参数依据其对应的参数名称标记,函数参数命名在函数参数名称(Function
Parameter Names)有更详细的叙说。

drop sequence if exists test_seq

create sequence test_seq
start with 1
increment by 1;

GO

drop table if exists test_next_value

create table test_next_value
(
ID         int,
Name       varchar(10)
)

insert into test_next_value(Name)
values
('AAA'),
('AAA'),
('BBB'),
('CCC')

--对于多行数据获取sequence的next value,是否使用窗口函数都会逐行计数
--窗口函数中ORDER BY用于控制不同列值的计数顺序
select *, NEXT VALUE FOR test_seq from test_next_value
select *, NEXT VALUE FOR test_seq OVER(ORDER BY Name DESC) from test_next_value

无重回值函数(Functions Without Return Values)

 

函数可以没有重返值。上面是 sayHello(🙂 函数的另一个本子,叫
sayGoodbye(
:),这些函数直接出口 String 值,而不是回去它:

参考:

func sayGoodbye(personName: String) {
    print("Goodbye, \(personName)!")
}
sayGoodbye("Dave")
// prints "Goodbye, Dave!"

SELECT – OVER Clause (Transact-SQL)

因为那个函数不需要再次回到值,所以那么些函数的定义中尚无回去箭头(->)和重临类型。

admin

网站地图xml地图