manbet手机客户端3.0Window( 窗口)

发布时间:2018-11-15  栏目:SQL  评论:0 Comments

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

一. 加载方式
//class 加载方式
<div id=”box” class=”easyui-window” title=”My Window”
style=”width:600px;height:400px”
data-options=”iconCls:’icon-save’,modal:true”>
窗口
</div>
//JS 加载调用
$(‘#box’).window({
width : 600,
height : 400,
modal : true,
});

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

  3. 分析函数 (Analytic Function) ;

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

 

 

二. 属性列表
窗口属性扩展自 Panel(面板),窗口新增或重新定义的性质如下:

一. 排序函数(Ranking
Function)

manbet手机客户端3.0 1

支援文档里的代码示例很咸。

manbet手机客户端3.0 2

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

//属性设置
$(‘#box’).window({
width : 600,
height : 400,
modal : true,
collapsible : false,
minimizable : false,
maximizable : false,
closable : false,
closed : false,
zIndex : 9999,
draggable : false,
resizable : false,
shadow : false,
inline : false,
});

排序函数在语法上要求OVER子句里必须含ORDER
BY,否则语法不经过,对于非思排序的场景可以如此变化;

 

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

//配合 inline 属性的容器
<div style=”position:relative;
width:500px;height:300px;overflow:auto;border:1px solid #ccc;”>
<div id=”box”>窗口</div>
</div>

 

 

二. 聚合函数 (Aggregate
Function)

PS: 以上属性是Window自行扩展或代表Panel的属性, 本身 Window就是指让
Panel
的。所以,上面没有涉及到的习性,请查看 Panel 的习性即可。比如:
//这里的 fit 和 iconCls 来自 Panel 属性
$(‘#box’).window({
width : 600,
height : 400,

SQL Server 2005遭遇,窗口聚合函数仅支持PARTITION
BY,也就是说仅能针对分组的数完全做聚合运算;

modal : true,
fit : true,
iconCls : ‘icon-add’,
});

SQL Server 2012发端,窗口聚合函数支持ORDER
BY,以及ROWS/RAGNE选项,原本需要子查询来促成的需,如: 移动平均
(moving averages), 总计聚合 (cumulative aggregates), 累计求和 (running
totals) 等,变得尤其便利;

 

 

 

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

三. 事件列表
窗口的事件完整继承自 Panel(面板)。所以,直接参考 Panel
面板的波即可。
//Window 事件
$(‘#box’).window({
width : 600,
height : 400,
modal : true,
onClose : function () {
alert(‘关闭后点发!’);
},
});

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

 

 

四.方法列表

代码示例2:移动平均

窗口的方扩展自 Panel(面板),窗口新增方法如下:

--移动平均,举个例子,就是求前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

manbet手机客户端3.0 3

 

//返回外部窗口对象
console.log($(‘#box’).window(‘window’));

三. 分析函数 (Analytic
Function)

//单击时调整岗位
$(document).click(function () {
$(‘#box’).window(‘move’, {
left : 0,
top : 0,
});
});

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

 

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调整跨度

//双击时回升各种居中
$(document).dblclick(function () {
//替换成 center 或 vcenter 亦可
$(‘#box’).window(‘hcenter’);

 

});
以$.fn.window.defaults 重写默认值对象。
window 组件最强劲的地方便是得中布局和添加 linkbutton。
具体布局方法如下:
1.标用 window 组件封装转;
2.里用 layout 组件左右各个分配一个,底部分配一个;
3.底部丰富一个按钮即可。

代码示例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字段时用窗口函数方便些
--对于挑选表中某一行/多行时,用子查询更方便

 

四. NEXT VALUE FOR Function

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

 

参考:

SELECT – OVER Clause (Transact-SQL)

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

SQL Server Windowing Functions: ROWS vs. RANGE

https://www.sqlpassion.at/archive/2015/01/22/sql-server-windowing-functions-rows-vs-range/

留下评论

网站地图xml地图