增加生产数量SQL语法对窗口函数和CTE的支持,S

 

MySQL 8.0 新增SQL语法对窗口函数和CTE的支持,sqlcte

 尝试了一下MySQL 8.0的部分新特性。

 

  如果用过MSSQL或者是Oracle中的窗口函数(Oracle中叫分析函数),
  然后再使用MySQL 8.0之前的时候,就知道需要在使用窗口函数处理逻辑的痛苦了,虽然纯SQL也能实现类似于窗口函数的功能,但是这种SQL在可读性和以及使用方式上大打折扣,看起来写起了都比较难受。

  在MSSQL和Oracle以及PostgreSQL都已经完整支持窗口函数的情况下,MySQL 8.0中也加入了窗口函数的功能,这一点实实在在方便了sql的编码,可以说是MySQL8.0的亮点之一。

  对于窗口函数,比如row_number(),rank(),dense_rank(),NTILE(),PERCENT_RANK()等等,在MSSQL和Oracle以及PostgreSQL,使用的语法和表达的逻辑,基本上完全一致。
  这一点,几个数据库厂商做的还是比较统一的,如果熟悉任何一种关系数据中的窗口函数(分析函数),在MySQL 8.0之后就放心的用吧。

  通过一个case来体验一下窗口函数的方便性,熟悉MSSQL或者Oracle或者PostgreSQL的老司机就不用看了。
  测试case,简单模拟一个订单表,字段分别是订单号,用户编号,金额,创建时间

drop table  if exists order_info

create table order_info
(
    order_id int primary key,
    user_no varchar(10),
    amount int,
    create_date datetime
);

insert into order_info values (1,'u0001',100,'2018-1-1');
insert into order_info values (2,'u0001',300,'2018-1-2');
insert into order_info values (3,'u0001',300,'2018-1-2');
insert into order_info values (4,'u0001',800,'2018-1-10');
insert into order_info values (5,'u0001',900,'2018-1-20');

insert into order_info values (6,'u0002',500,'2018-1-5');
insert into order_info values (7,'u0002',600,'2018-1-6');
insert into order_info values (8,'u0002',300,'2018-1-10');
insert into order_info values (9,'u0002',800,'2018-1-16');
insert into order_info values (10,'u0002',800,'2018-1-22');

要求sql查询求每个用户的最新的一个订单。

传统的方式,尽量格式化的好读一点的情况下,说实话,这句sql咋一看有点莫名其妙,不知所以。

SELECT * FROM 
(
    SELECT 
   IF(@y=a.user_no, @x:=@x 1, @x:=1) X ,
    IF(@y=a.user_no, @y, @y:=a.user_no) Y,
    a.*
    FROM order_info a, (SELECT @x:=0, @y:=NULL) b
    ORDER BY a.user_no, a.create_date desc
) a
WHERE X <= 1;

 如下是执行结果,当然执行结果是可以满足需求的。

图片 1

  如果采用新的窗口函数的方法,
  就是使用row_number()over(partition by user_no order by create_date desc) as row_num 给原始记录编一个号,
  然后取第一个编号的数据,自然就是“用户的最新的一条订单”,实现逻辑上清晰了很多,代码也简洁,可读了很多。

select * from 
(
    select row_number()over(partition by user_no order by create_date desc) as row_num,
    order_id,user_no,amount,create_date
    from order_info
)t where row_num=1;

图片 2

  需要注意的是,MySQL中的使用窗口函数的时候,是不允许使用*的,必须显式指定每一个字段。

 

 row_number()

  (分组)排序编号,正如上面的例子, row_number()over(partition by user_no order by create_date desc) as row_num,按照用户分组,按照create_date排序,对已有数据生成一个编号。
  当然也可以不分组,对整体进行排序。任何一个窗口函数,都可以分组统计或者不分组统计(也即可以不要partition by ***都可以,看你的需求了)

  图片 3

rank()

  类似于 row_number(),也是排序功能,但是rank()有什么不一样?新的事物的出现必然是为了解决潜在的问题。
  如果再往测试表中写入一条数据:insert into order_info values (11,'u0002',800,'2018-1-22');
  对于测试表中的U002用户来说,有两条create_date完全一样的数据(假设有这样的数据),那么在row_number()编号的时候,这两条数据却被编了两个不同的号
  理论上讲,这两条的数据的排名是并列最新的。因此rank()就是为了解决这个问题的,也即:排序条件一样的情况下,其编号也一样。

  图片 4

dense_rank()

  dense_rank()的出现是为了解决rank()编号存在的问题的,
  rank()编号的时候存在跳号的问题,如果有两个并列第1,那么下一个名次的编号就是3,结果就是没有编号为2的数据。
  如果不想跳号,可以使用dense_rank()替代。

  图片 5

avg,sum等聚合函数在窗口函数中的的增强

  可以在聚合函数中使用窗口功能,比如sum(amount)over(partition by user_no order by create_date) as sum_amont,达到一个累积计算sum的功能
  这种需求在没有窗口函数的情况下,用纯sql写起来,也够蛋疼的了,就不举例了。

  图片 6

NTILE(N) 将数据按照某些排序分成N组

  举个简单的例子,按照分数线的倒序排列,将学生成绩分成上中下3组,可以得到哪个程序数据上中下三个组中哪一部分,就可以使用NTILE(3) 来实现。这种需求倒是用的不是非常多。
  如下还是使用上面的表,按照时间将user_no = 'u0002'的订单按照时间的纬度,划分为3组,看每一行数据数据哪一组。

  图片 7

 

first_value(column_name) and last_value(column_name)

  first_value和last_value基本上见名知意了,就是取某一组数据,按照某种方式排序的,最早的和最新的某一个字段的值。
  看结果体会一下。

  图片 8

nth_value(column_name,n)

  从排序的第n行还是返回nth_value字段中的值,这个函数用的不多,要表达的这种逻辑,说实话,很难用语言表达出来,看个例子体会一下就行。

  n = 3

  图片 9

  n = 4

图片 10

 

cume_dist

  在某种排序条件下,小于等于当前行值的行数/总行数,得到的是数据在某一个纬度的分布百分比情况。
  比如如下示例
  第1行数据的日期(create_date)是2018-01-05 00:00:00,小于等于2018-01-05 00:00:00的数据是1行,计算方式是:1/6 = 0.166666666
  第2行数据的日期(create_date)是2018-01-06 00:00:00,小于等于2018-01-06 00:00:00的数据是2行,计算方式是:2/6 = 0.333333333
  依次类推
  第4行数据的日期(create_date)是2018-01-16 00:00:00,小于等于2018-01-16 00:00:00的数据是4行,计算方式是:4/6 = 0.6666666666
  第一行数据的0.6666666666 意味着,小于第四行日期(create_date)的数据占了符合条件数据的66.66666666666%

  图片 11

 

percent_rank()

  同样是数据分布的计算方式,只不过算法变成了:当前RANK值-1/总行数-1 。
  具体算法不细说,这个实际中用的也不多。

  图片 12

 

lag以及lead

  lag(column,n)获取当前数据行按照某种排序规则的上n行数据的某个字段,lead(column,n)获取当前数据行按照某种排序规则的下n行数据的某个字段,
  确实很拗口。
  举个实际例子,按照时间排序,获取当前订单的上一笔订单发生时间和下一笔订单发生时间,(可以计算订单的时间上的间隔度或者说买买买的频繁程度)

select order_id,
         user_no,
         amount,
         create_date,
       lag(create_date,1) over (partition by user_no order by create_date asc) 'last_transaction_time',
       lead(create_date,1) over (partition by user_no order by create_date asc) 'next_transaction_time'
from order_info ;

  图片 13

 

CTE 公用表表达式

  CTE有两种用法,非递归的CTE和递归的CTE。
  非递归的CTE可以用来增加代码的可读性,增加逻辑的结构化表达。
  平时我们比较痛恨一句sql几十行甚至上上百行,根本不知道其要表达什么,难以理解,对于这种SQL,可以使用CTE分段解决,
  比如逻辑块A做成一个CTE,逻辑块B做成一个CTE,然后在逻辑块A和逻辑块B的基础上继续进行查询,这样与直接一句代码实现整个查询,逻辑上就变得相对清晰直观。
  举个简单的例子,当然这里也不足以说明问题,比如还是第一个需求,查询每个用户的最新一条订单
  第一步是对用户的订单按照时间排序编号,做成一个CTE,第二步对上面的CTE查询,取行号等于1的数据。

图片 14

  另外一种是递归的CTE,递归的话,应用的场景也比较多,比如查询大部门下的子部门,每一个子部门下面的子部门等等,就需要使用递归的方式。
  这里不做细节演示,仅演示一种递归的用法,用递归的方式生成连续日期。

  图片 15

  当然递归不会无限下去,不同的数据库有不同的递归限制,MySQL 8.0中默认限制的最大递归次数是1000。
  超过最大低估次数会报错:Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
  由参数@@cte_max_recursion_depth决定。

  图片 16

  关于CTE的限制,跟其他数据库并无太大差异,比如CTE内部的查询结果都要有字段名称,不允许连续对一个CTE多次查询等等,相信熟悉CTE的老司机都很清楚。

 

窗口函数和CTE的增加,简化了SQL代码的编写和逻辑的实现,并不是说没有这些心的特性,这些功能都无法实现,只是新特性的增加,可以用更优雅和可读性的方式来写SQL。
不过这都是在MySQL 8.0中实现的新功能,在8.0之前,还是老老实实按照较为复杂的方式实现吧。

 

8.0 新增SQL语法对窗口函数和CTE的支持,sqlcte 尝试了一下MySQL 8.0的部分新特性。 如果用过MSSQL或者是Oracle中的窗口函数(Oracle中叫分析...

本文出处:

 

我们在做开发的时候,有时候会需要一些帮助数据,必须需要连续的数字,连续间隔的时间点,连续的季度日期等等
常见很多人利用master库的spt_values系统表,这个当然没有问题

比如下面这个(没截完,结果是0-2047)

图片 17

这样也可以使用,但是感觉不够灵活,一是不是随便一个账号都可以访问master数据库的,而是他这里面也只有这么一个连续的数字了,
想要别的结果集就不太弄了,
类似数据可以用公用表表达式CTE的递归来生成
比如上述的0-2047的结果集

;with GenerateHelpData
as
(
    select 0 as id
    union all
    select id 1 from GenerateHelpData where id<2047
)
select id from GenerateHelpData option (maxrecursion 2047);

可以直接让CTE参数逻辑运算,也可以生成临时表,达到多次重用的目的,这样感觉是不是也很清爽?

 

1,生成连续数字(当然数字的起始值,间隔值都可以自定义)

--生成连续数字
;with GenerateHelpData
as
(
    select 0 as id
    union all
    select id 1 from GenerateHelpData where id<2047
)
select id from GenerateHelpData option (maxrecursion 2047);

图片 18

 

2,CTE递归生成连续日期

--生成连续日期
;with GenerateHelpData
as
(
    select cast('2016-10-01' as date) as [Date]
    union all
    select DATEADD(D,1,[Date]) from GenerateHelpData where [Date]<'2017-01-01'
)
select [Date] from GenerateHelpData;

图片 19

 

3,生成连续间隔的时间点

  有时候一些统计需要按照一个小时或者半个小时之类的时间间隔做组合,比如统计某天内没半个小时的小时数据等等

--生成连续间隔的时间点
;with GenerateHelpData
as
(
    select 1 as id, cast('00:00:00' as time(0)) as timeSection
    union all
    select id 1 as id,  cast(dateadd(mi,30,timeSection) as time(0)) as timeSection 
     from GenerateHelpData  where id<49
)
select * from GenerateHelpData

图片 20

 

当然这里就可以非常灵活了,更骚一点的变形

--更骚一点的变形
;with GenerateHelpData
as
(
    select 1 as id, cast('00:00:00' as time(0)) as timeSection
    union all
    select id 1 as id,  cast(dateadd(mi,30,timeSection) as time(0)) as timeSection 
     from GenerateHelpData  where id<49
)
select 
A.timeSection as timeSectionFrom,
B.timeSection as timeSectionTo,
cast(A.timeSection as varchar(10)) '~' cast(B.timeSection as varchar(10)) as timeSection
from GenerateHelpData  A inner join GenerateHelpData B on A.id= B.id-1

图片 21

 

  4,生成连续季度的最后一天

DECLARE 
@begin_date date = '2014-12-31',
@end_date date = '2016-12-31'
;with GenerateHelpData as
(
    select 
        CAST(    CASE 
                    WHEN RIGHT(@begin_date,5)='12-30' 
                THEN DATEADD(DAY,1,@begin_date) 
                    ELSE @begin_date 
                END AS    DATE)
        AS EndingDate
    UNION ALL
    SELECT     
        CASE WHEN RIGHT(DATEADD(QQ,1,EndingDate),5)='12-30' 
        THEN  DATEADD(DAY,1,DATEADD(QQ,1,EndingDate)) 
        ELSE DATEADD(QQ,1,EndingDate)
        END AS EndingDate
    from GenerateHelpData where EndingDate< @end_date
)
select * from GenerateHelpData

图片 22

 

通过变形可以生成两个日期间隔之间的的数据

DECLARE 
@begin_date date = '2014-12-31',
@end_date date = '2016-12-31'
;with GenerateHelpData as
(
    select 1 as id ,
        CAST(    CASE 
                    WHEN RIGHT(@begin_date,5)='12-30' 
                THEN DATEADD(DAY,1,@begin_date) 
                    ELSE @begin_date 
                END AS    DATE)
        AS EndingDate
    UNION ALL
    SELECT     id 1 as id,
        CASE WHEN RIGHT(DATEADD(QQ,1,EndingDate),5)='12-30' 
        THEN  DATEADD(DAY,1,DATEADD(QQ,1,EndingDate)) 
        ELSE DATEADD(QQ,1,EndingDate)
        END AS EndingDate
    from GenerateHelpData where EndingDate< @end_date
)
select 
A.EndingDate as DateFrom,
B.EndingDate as DateTo,
cast(A.EndingDate as varchar(10)) '~' cast(B.EndingDate as varchar(10)) as timeSection
from GenerateHelpData  A inner join GenerateHelpData B on A.id= B.id-1

图片 23

 

需要注意的是,CTE递归的默认次数是100,如果不指定递归次数(option (maxrecursion N);),超出默认最大递归次数之后会报错。

图片 24

 

 

 

 

 

 

 

 ——————————————递归原本很容易使用,本文原本是说用递归生成帮助数据的,有朋友问到递归本身的使用,那就再补充一个DEMO吧———————————————————

 

补充园友的一个实际应用-20161119

测试数据:

 

create table ProuctInfo
(
    Id INT,
    ParentId INT,
    ProuctName VARCHAR(50)
)

INSERT INTO ProuctInfo VALUES (1,0,'镜片')
INSERT INTO ProuctInfo VALUES (2,0,'镜架')
INSERT INTO ProuctInfo VALUES (101,1,'高级镜片')
INSERT INTO ProuctInfo VALUES (102,1,'普通镜片')
INSERT INTO ProuctInfo VALUES (201,2,'高级镜架')
INSERT INTO ProuctInfo VALUES (202,2,'普通镜架')

INSERT INTO ProuctInfo VALUES (1001,101,'高级镜片1')
INSERT INTO ProuctInfo VALUES (1002,102,'普通镜片2')
INSERT INTO ProuctInfo VALUES (2001,201,'高级镜架1')
INSERT INTO ProuctInfo VALUES (2002,202,'普通镜架2')

原始数据的样子,很普通

图片 25

 

创建一个函数,获取当前节点的父节点信息

CREATE FUNCTION dbo.FnGetParentInfo(@id int)
returns varchar(max)
as
begin

declare @name varchar(max)

--查询某一个节点的所有父节点
;with SubTab
as
(
    select [ID],[ParentID], cast(ProuctName as varchar(200)) as ProuctName
    from ProuctInfo WHERE Id = @id
    union all
    select a.[ID],a.[ParentID],cast(a.ProuctName '--->' b.ProuctName as varchar(200)) as ProuctName
    from ProuctInfo a,SubTab b
    where a.[ID]=b.[ParentID] 
)
select @name = ProuctName from SubTab  where ParentId = 0 

return @name
end

 

实际效果:

  图片 26

 

 

 

  

  总结:本文演示了几种常用的根据CTE递归生成帮助数据的情况,如果需要帮助数据,可以根据CTE的递归特性做灵活处理。

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:增加生产数量SQL语法对窗口函数和CTE的支持,S

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。