彩世界平台-彩世界时时app-彩世界开奖app苹果下载

热门关键词: 彩世界平台,彩世界时时app,彩世界开奖app苹果下载

您的位置:彩世界平台 > 工作委员会 > 16. 窗口函数 (Window Function) 的使用

16. 窗口函数 (Window Function) 的使用

发布时间:2019-08-30 09:31编辑:工作委员会浏览(55)

    从SQL Server 2005起,SQL Server开始支持窗口函数 (Window Function),以及到SQL Server 2012,窗口函数功能增强,目前为止支持以下几种窗口函数:

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

    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)
    

     

    函数是用来完成特定任务的独立的代码块。你给一个函数起一个合适的名字,用来标识函数做什么,并且当函数需要执行的时候,这个名字会被用于“调用”函数。

    一. 排序函数(Ranking Function)

    Swift 统一的函数语法足够灵活,可以用来表示任何函数,包括从最简单的没有参数名字的 C 风格函数,到复杂的带局部和外部参数名的 Objective-C 风格函数。参数可以提供默认值,以简化函数调用。参数也可以既当做传入参数,也当做传出参数,也就是说,一旦函数执行结束,传入的参数值可以被修改。

    帮助文档里的代码示例很全。

    在 Swift 中,每个函数都有一种类型,包括函数的参数值类型和返回值类型。你可以把函数类型当做任何其他普通变量类型一样处理,这样就可以更简单地把函数当做别的函数的参数,也可以从其他函数中返回函数。函数的定义可以写在其他函数定义中,这样可以在嵌套函数范围内实现功能封装。

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

    函数的定义与调用(Defining and Calling Functions)
    当你定义一个函数时,你可以定义一个或多个有名字和类型的值,作为函数的输入(称为参数,parameters),也可以定义某种类型的值作为函数执行结束的输出(称为返回类型,return type)。

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

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

    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 类型的返回值:

     

    func sayHello(personName: String) -> String {
        let greeting = "Hello, " + personName + "!"
        return greeting
    }
    

    二. 聚合函数 (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)

    因为这个函数不需要返回值,所以这个函数的定义中没有返回箭头(->)和返回类型。

    注意
    严格上来说,虽然没有返回值被定义,sayGoodbye(_:) 函数依然返回了值。没有定义返回类型的函数会返回特殊的值,叫 Void。它其实是一个空的元组(tuple),没有任何元素,可以写成()。
    被调用时,一个函数的返回值可以被忽略:

    SQL Server Windowing Functions: ROWS vs. RANGE

    func printAndCount(stringToPrint: String) -> Int {
        print(stringToPrint)
        return stringToPrint.characters.count
    }
    func printWithoutCounting(stringToPrint: String) {
        printAndCount(stringToPrint)
    }
    printAndCount("hello, world")
    // prints "hello, world" and returns a value of 12
    printWithoutCounting("hello, world")
    // prints "hello, world" but does not return a value
    

    第一个函数 printAndCount(_:),输出一个字符串并返回 Int 类型的字符数。第二个函数 printWithoutCounting调用了第一个函数,但是忽略了它的返回值。当第二个函数被调用时,消息依然会由第一个函数输出,但是返回值不会被用到。

    注意
    返回值可以被忽略,但定义了有返回值的函数必须返回一个值,如果在函数定义底部没有返回任何值,将导致编译错误(compile-time error)。

    多重返回值函数(Functions with Multiple Return Values)

    你可以用元组(tuple)类型让多个值作为一个复合值从函数中返回。

    下面的这个例子中,定义了一个名为minMax(_:)的函数,作用是在一个Int数组中找出最小值与最大值。

    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)
    }
    

    minMax(_:)函数返回一个包含两个Int值的元组,这些值被标记为min和max,以便查询函数的返回值时可以通过名字访问它们。

    minMax(_:)的函数体中,在开始的时候设置两个工作变量currentMin和currentMax的值为数组中的第一个数。然后函数会遍历数组中剩余的值并检查该值是否比currentMin和currentMax更小或更大。最后数组中的最小值与最大值作为一个包含两个Int值的元组返回。

    因为元组的成员值已被命名,因此可以通过点语法来检索找到的最小值与最大值:

    let bounds = minMax([8, -6, 2, 109, 3, 71])
    print("min is (bounds.min) and max is (bounds.max)")
    // prints "min is -6 and max is 109"
    

    需要注意的是,元组的成员不需要在元组从函数中返回时命名,因为它们的名字已经在函数返回类型中指定了。

    可选元组返回类型(Optional Tuple Return Types)

    如果函数返回的元组类型有可能整个元组都“没有值”,你可以使用可选的(Optional) 元组返回类型反映整个元组可以是nil的事实。你可以通过在元组类型的右括号后放置一个问号来定义一个可选元组,例如(Int, Int)?或(String, Int, Bool)?

    注意
    可选元组类型如(Int, Int)?与元组包含可选类型如(Int?, Int?)是不同的.可选的元组类型,整个元组是可选的,而不只是元组中的每个元素值。
    前面的minMax(:)函数返回了一个包含两个Int值的元组。但是函数不会对传入的数组执行任何安全检查,如果array参数是一个空数组,如上定义的minMax(:)在试图访问array[0]时会触发一个运行时错误。

    为了安全地处理这个“空数组”问题,将minMax(_:)函数改写为使用可选元组返回类型,并且当数组为空时返回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)
    }
    

    你可以使用可选绑定来检查minMax(_:)函数返回的是一个实际的元组值还是nil:

    if let bounds = minMax([8, -6, 2, 109, 3, 71]) {
        print("min is (bounds.min) and max is (bounds.max)")
    }
    // prints "min is -6 and max is 109"
    

    函数参数名称(Function Parameter Names)
    函数参数都有一个外部参数名(external parameter name)和一个局部参数名(local parameter name)。外部参数名用于在函数调用时标注传递给函数的参数,局部参数名在函数的实现内部使用。

    func someFunction(firstParameterName: Int, secondParameterName: Int) {
        // function body goes here
        // firstParameterName and secondParameterName refer to
        // the argument values for the first and second parameters
    }
    someFunction(1, secondParameterName: 2)
    

    一般情况下,第一个参数省略其外部参数名,第二个以及随后的参数使用其局部参数名作为外部参数名。所有参数必须有独一无二的局部参数名。尽管多个参数可以有相同的外部参数名,但不同的外部参数名能让你的代码更有可读性。

    指定外部参数名(Specifying External Parameter Names)

    你可以在局部参数名前指定外部参数名,中间以空格分隔:

    func someFunction(externalParameterName localParameterName: Int) {
        // function body goes here, and can use localParameterName
        // to refer to the argument value for that parameter
    }
    

    注意
    如果你提供了外部参数名,那么函数在被调用时,必须使用外部参数名。
    这个版本的sayHello(_:)函数,接收两个人的名字,会同时返回对他俩的问候:

    func sayHello(to person: String, and anotherPerson: String) -> String {
        return "Hello (person) and (anotherPerson)!"
    }
    print(sayHello(to: "Bill", and: "Ted"))
    // prints "Hello Bill and Ted!"
    

    为每个参数指定外部参数名后,在你调用sayHello(to:and:)函数时两个外部参数名都必须写出来。

    使用外部函数名可以使函数以一种更富有表达性的类似句子的方式调用,并使函数体意图清晰,更具可读性。

    忽略外部参数名(Omitting External Parameter Names)

    如果你不想为第二个及后续的参数设置外部参数名,用一个下划线(_)代替一个明确的参数名。

    本文由彩世界平台发布于工作委员会,转载请注明出处:16. 窗口函数 (Window Function) 的使用

    关键词:

上一篇:没有了

下一篇:没有了