详解SQL Server 2008滑动窗口的管理

最新推荐文章于 2024-06-29 01:09:20 发布
最新推荐文章于 2024-06-29 01:09:20 发布
阅读量932 收藏
点赞数
分类专栏： SQL Server 文章标签： sql server null table go date scheme

每个月都会有新数据添加到庞大的Orders数据表中，此时如果只对这一个表进行操作，则更新索引的过程将严重制约性能并产生大量碎片。为了进行优化，我们将通过独立的两个表进行配合，即当有新数据产生时，将旧数据从分区表中切换出来，然后将新数据添加到分区表中。

　　整个过程包含如下几个步骤：

　　·准备好即将用于存放旧数据的表

　　·准备好存有新数据的表

　　·更改分区方案，以便使用新的文件组

　　·对分区函数进行更改，分裂出新的边界点

　　注意：前4步均为影响到实际的分区表

　　·切换进来新数据

　　·将旧数据切换出去

　　注意：第5步和第6步的顺序可以进行更改，且更改后执行速度更快

　　·合并边界点

　　·备份/删除旧数据

　　我们实验所用到的分区数据表中包含的数据，因此我们将把数据作为一个分区直接切换进来。当进行分区切换的时候，只有元数据会发生更改，真正的数据并不会产生移动，因此速度非常快。

　　1.在不影响当前OrdersRange 分区表的前提下，为新数据做好准备

　　(1)在Solution Explorer 窗口，双击打开Script4 – RollingRangeScenario.sql.

　　(2)新建一个文件组，用于存放新的数据


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　ALTER DATABASE AdventureWorks2008Test
　　ADD FILEGROUP [2004Q3]
　　GO
 　　(3)为文件组添加一个文件：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　ALTER DATABASE AdventureWorks2008Test
　　ADD FILE
　　(NAME = N'2004Q3',
　　FILENAME = N'C:/AdventureWorks2008Test/2004Q3.ndf',
　　SIZE = 5MB,
　　MAXSIZE = 100MB,
　　FILEGROWTH = 5MB)
　　TO FILEGROUP [2004Q3]
　　GO
　　(4)接下来，我们要新建一个未分区的数据表，用于存放分区数据表中新建分区中的数据。该未分区数据表必须与分区数据表具有完全一致的结构和聚集索引。此外，为了能够确保快速进行分区切换，还需要通过约束来确保此未分区数据表中的数据与分区数据表中新建分区的范围相吻合。接下来我们将创建该数据表，插入数据，并创建聚集索引


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　CREATE TABLE AdventureWorks2008Test.[dbo].[Orders2004Q3]

　　(

　　[OrderID] [int] NOT NULL,

　　[EmployeeID] [int] NULL,

　　[VendorID] [int] NULL,

　　[TaxAmt] [money] NULL,

　　[Freight] [money] NULL,

　　[SubTotal] [money] NULL,

　　[Status] [tinyint] NOT NULL ,

　　[RevisionNumber] [tinyint] NULL,

　　[ModifiedDate] [datetime] NULL,

　　[ShipMethodID] tinyint NULL,

　　[ShipDate] [datetime] NOT NULL,

　　[OrderDate] [datetime] NOT NULL

　　CONSTRAINT Orders2004Q3MinDate

　　CHECK (OrderDate >= '20040701'),

　　[TotalDue] [money] NULL

　　) ON [2004Q3]

　　GO

　　ALTER TABLE AdventureWorks2008Test.[dbo].[Orders2004Q3]

　　ADD CONSTRAINT Orders2004Q3MaxDate

　　CHECK (OrderDate < '20041001')

　　go

　　---------------------------------------------

　　-- Populate new table with Q3 2004 data.

　　---------------------------------------------

　　INSERT INTO AdventureWorks2008Test.[dbo].Orders2004Q3

　　SELECT o.[PurchaseOrderID]

　　, o.[EmployeeID]

　　, o.[VendorID]

　　, o.[TaxAmt]

　　, o.[Freight]

　　, o.[SubTotal]

　　, o.[Status]

　　, o.[RevisionNumber]

　　, o.[ModifiedDate]

　　, o.[ShipMethodID]

　　, o.[ShipDate]

　　, o.[OrderDate]

　　, o.[TotalDue]

　　FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader AS o

　　WHERE o.OrderDate >= '20040701'

　　AND o.OrderDate < '20041001'

　　GO

　　---------------------------------------------

　　-- The table *must* have the same clustered

　　-- index definition!

　　---------------------------------------------

　　CREATE CLUSTERED INDEX Orders2004Q3CLInd

　　ON Orders2004Q3(OrderDate, OrderID)

　　ON [2004Q3]

　　GO
　　2.为将要进行归档的数据做好准备

　　存放归档数据的数据表必须与分区数据表具有完全一致的结构和聚集索引。一会儿我们将把文件组2003Q3中的数据进行归档

　　(1)创建用于归档的数据表：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　CREATE TABLE AdventureWorks2008Test.[dbo].[Orders2003Q3]

　　(

　　[OrderID] [int] NOT NULL,

　　[EmployeeID] [int] NULL,

　　[VendorID] [int] NULL,

　　[TaxAmt] [money] NULL,

　　[Freight] [money] NULL,

　　[SubTotal] [money] NULL,

　　[Status] [tinyint] NOT NULL ,

　　[RevisionNumber] [tinyint] NULL,

　　[ModifiedDate] [datetime] NULL,

　　[ShipMethodID] tinyint NULL,

　　[ShipDate] [datetime] NOT NULL,

　　[OrderDate] [datetime] NOT NULL,

　　[TotalDue] [money] NULL

　　) ON [2003Q3]

　　GO

　　---------------------------------------------

　　-- The table must have the same clustered

　　-- index definition!

　　---------------------------------------------

　　CREATE CLUSTERED INDEX Orders2003Q3CLInd

　　ON Orders2003Q3(OrderDate, OrderID)

　　ON [2003Q3]

　　GO
 　　(2)执行如下代码，进行分区切换。注意，此时只是将分区从分区表中移除，数据实际并没有删除，但在OrdersRange 数据表中将无法再看到这些数据，因为这些数据已经被“切换”到了Orders2003Q3 表中


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　ALTER TABLE OrdersRange

　　SWITCH PARTITION 2

　　TO Orders2003Q3

　　GO
 　　3.验证分区数据表中已经不再包含切换出去的数据

　　(1)为了确保分区 2中的数据已经被删除，执行如下语句进行查看：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　SELECT $partition.OrderDateRangePFN(OrderDate)

　　AS 'Parition Number'

　　, min(OrderDate) AS 'Min Order Date'

　　, max(OrderDate) AS 'Max Order Date'

　　, count(*) AS 'Rows In Partition'

　　FROM OrdersRange

　　GROUP BY $partition.OrderDateRangePFN(OrderDate)

　　ORDER BY 1

　　GO
 　　(2)此时可以看到只有分区3，4，5中有数据
4.删除已经废弃的分区边界

　　通过刚才所执行的查询我们可以看到，只有分区3，4，5当中有数据，如何才能将所有分区向左移动一位?

　　此时我们只需要删除最左侧的分区边界

　　(1)执行如下语句，从OrdersRange 分区表中删除2003年第3季度的分区边界：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　ALTER PARTITION FUNCTION OrderDateRangePFN()

　　MERGE RANGE ('20030701')

　　GO
 　　(2)此时我们的分区表中将只包含3个有数据的分区和1个空分区。执行如下语句进行核实，注意现在分区2，3，4当中有数据


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　SELECT $partition.OrderDateRangePFN(OrderDate)

　　AS 'Parition Number'

　　, min(OrderDate) AS 'Min Order Date'

　　, max(OrderDate) AS 'Max Order Date'

　　, count(*) AS 'Rows In Partition'

　　FROM OrdersRange

　　GROUP BY $partition.OrderDateRangePFN(OrderDate)

　　ORDER BY 1

　　GO
　　(3)Merge操作删除了分区1，因此SQL Server对现有分区进行了重新编号，但并没有移动任何数据，只是改变了分区号。正因如此，数据库所使用的文件组并没有发生改变。执行如下语句进行查询：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　SELECT * FROM sys.filegroups
　　(4)之前分区2存放在2003Q3 文件组，但现在该文件组已经不再与该分区表相关联了。执行如下语句，可以查看各个分区及其相关联的文件组：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　SELECT ps.name AS PSName,

　　dds.destination_id AS PartitionNumber,

　　dds.data_space_id AS FileGroup,

　　fg.name AS FileGroupName

　　FROM (((sys.tables AS t

　　INNER JOIN sys.indexes AS i

　　ON (t.object_id = i.object_id))

　　INNER JOIN sys.partition_schemes AS ps

　　ON (i.data_space_id = ps.data_space_id))

　　INNER JOIN sys.destination_data_spaces AS dds

　　ON (ps.data_space_id =

　　dds.partition_scheme_id))

　　INNER JOIN sys.filegroups AS fg

　　ON dds.data_space_id = fg.data_space_id

　　WHERE (t.name = 'OrdersRange')

　　AND (i.index_id IN (0,1))
　　5.将新的文件组添加到分区方案中

　　前面我们查看了分区表当前的状态，为了添加新的分区(从现有分区当中拆分出来)，我们还要为新的分区准备一个存放位置。新的分区将存放在2004Q3 文件组中，该文件组我们前面已经创建。执行如下代码将该文件组添加到分区方案中：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　ALTER PARTITION SCHEME OrderDatePScheme

　　NEXT USED [2004Q3]

　　GO
　　6.更改数据表的约束

　　分区视图非常依赖约束，而分区数据表对约束的依赖没有这么强。但出于数据完整性的考虑，此实验中我们为数据表添加了约束。现有约束只允许添加2003Q3 到2004Q2之间的数据。为了能够将新数据切换到分区表中，我们首先要更改此约束

　　(1)执行如下代码，更改分区表的约束：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　ALTER TABLE OrdersRange

　　ADD CONSTRAINT OrdersRangeMax

　　CHECK ([OrderDate] < '20041001')

　　go

　　ALTER TABLE OrdersRange

　　ADD CONSTRAINT OrdersRangeMin

　　CHECK ([OrderDate] >= '20031001')

　　go

　　ALTER TABLE OrdersRange

　　DROP CONSTRAINT OrdersRangeYear

　　go
　　(2)接下来执行下面的代码，分裂出新的分区：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　ALTER PARTITION FUNCTION OrderDateRangePFN()

　　SPLIT RANGE ('20040701')

　　GO
　　(3)将数据切换到新的分区中：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　ALTER TABLE Orders2004Q3

　　SWITCH TO OrdersRange PARTITION 5

　　GO
　　(4)最后验证数据：


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->　　SELECT $partition.OrderDateRangePFN(OrderDate)

　　AS 'Parition Number'

　　, min(OrderDate) AS 'Min Order Date'

　　, max(OrderDate) AS 'Max Order Date'

　　, count(*) AS 'Rows In Partition'

　　FROM OrdersRange

　　GROUP BY $partition.OrderDateRangePFN(OrderDate)

　　ORDER BY 1

　　GO
　　(5)关闭Script4 - RollingRangeScenario.sql

Sky_666
关注
专栏目录
大数据Flink（一百一十七）：Flink SQL的窗口操作
Lansonli（蓝深李）的博客
09-12 1462
在流处理应用中，数据是连续不断的，因此我们不可能等到所有数据都到了才开始处理。当然我们可以每来一个消息就处理一次，但是有时我们需要做一些聚合类的处理，例如：在过去的1分钟内有多少用户点击了我们的网页。在这种情况下，我们必须定义一个窗口，用来收集最近一分钟内的数据，并对这个窗口内的数据进行计算。Flink 认为 Batch 是 Streaming 的一个特例，所以 Flink 底层引擎是一个流式引擎，在上面实现了流处理和批处理。而窗口（window）就是从 Streaming 到 Batch 的一个桥梁。
大数据Flink（七十四）：SQL的滑动窗口（HOP）
Lansonli（蓝深李）的博客
09-06 1550
与滚动窗口功能一样，也有窗口大小的概念。不一样的地方在于，滑动窗口有另一个参数控制窗口计算的频率（滑动窗口滑动的步长）。因此，如果滑动的步长小于窗口大小，则滑动窗口之间每个窗口是可以重叠。这样，每 5 分钟会划分一次窗口，这个窗口包含的数据是过去 10 分钟内的数据，如下图所示。：比如计算同时在线的数据，要求结果的输出频率是 1 分钟一次，每次计算的数据是过去 5 分钟的数据（有的场景下用户可能在线，但是可能会 2 分钟不活跃，但是这也要算在同时在线数据中，所以取最近 5 分钟的数据就能计算进去了）
sqlserver压缩分区滑动窗口
01-20
sqlserver压缩分区滑动窗口,该文档详述表分区，压缩，滑动窗口的使用
SQL滑动窗口
qq_45849148的博客
08-10 1532
窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。
SQL Server 2005 分区表实践——滑动窗口方案
wengene的专栏
09-29 1326
<br />[作/译者]：鹏城万里    [日期]：2008-07-15    [来源]：本站原创    [查看]: 1494 <br />【鹏城万里】 发表于 www.sqlstudy.com<br />SQL Server 2005 分区表——滑动窗口方案实践：采用滑动窗口机制，把分区表的分区依次移入到另一个分区表。值得一提的是，本文示例涉及了 SQL 分区表的方方面面：建立分区函数（partition function）、分区方案（partition scheme）；分区表创建；分区函数拆分、合并；分
SQL Server关系型数据仓库分区策略详解与最佳实践
- 开发人员和DBA，他们已经熟悉SQL Server分区视图，将从中获益于SQL Server 2005的新功能，包括滑动窗口实现和优化的分区策略。 - 对未来计划使用分区的开发人员和DBA，他们可以通过详细阅读此白皮书来规划和实施更...
窗口函数 原理与代码实例讲解
AI天才研究院
06-17 740
窗口函数 原理与代码实例讲解 1.背景介绍 1.1 什么是窗口函数 窗口函数(Window Function)，也称为OLAP函数(Online Analytical Processing)，是对一组值执行计算并为每一行返回一个结果的函数。与普通
【微服务】springboot整合kafka-stream使用详解
热门推荐
congge
12-24 1万+
kafka stream使用详解
Spark SQL深度解析：大数据库引擎的力量详解
[Spark SQL深度解析：大数据库引擎的力量详解](https://daxg39y63pxwu.cloudfront.net/images/blog/what-is-data-engineering/Data_Engineering_Example_Project.png) # 1. Spark SQL的基本概念与架构 在当今的数据...
【AI大数据计算原理与代码实例讲解】窗口函数
最新发布
AI天才研究院
06-29 455
【AI大数据计算原理与代码实例讲解】窗口函数 作者：禅与计算机程序设计艺术 / Zen and the Art of Computer Programming / TextGenWebUILLM 【AI大数据计算原理与代码实例讲解】窗口函数
SQLServer 窗口函数
ajwqb06628的专栏
11-28 125
SQLServer 窗口函数 一、窗口函数的作用 　　窗口函数是对一组值进行操作，不需要使用GROUP BY 子句对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。窗口函数，基础列和聚合列的查询都非常简单。 二、语法格式 　　窗口函数的语法格式如下：...
关于SQL2008分区表滑动窗口方案的应用策略
Billy.lee 的博客
03-18 1200
<br />接着上次的分区表来说..创建好了后..就得有一个脚本来管理这些分区表的及时管理了。。<br />呵呵。。现在直接看脚本吧。。。<br />参考了网上的一些方法，结合自己的系统应用。。<br />由于本次分区的字段并非主键，因此遇到一些小问题。。<br />下面虽然能实现滑动窗口方案，但是总觉得通过删除索引在创建索引的方式来实现有点浪费。。。希望有高人能有有更好的意见。。<br />谢谢。。。<br /> <br /> /* * 1.创建两个函数MinFileGroupsByPartitio
sql技巧-滚动窗口分组
cakecc2008的专栏
06-21 571
本文基于hive1.1.0-cdh5.12.1 将数据按N个一组进行分组 示例数据： id 0 1 2 3 4 6 7 要求：将id按一个长度为N的滚动窗口分组（N>1），并求出窗口区间(前闭后开) 参考结果：N=3 id 分组 窗口区间 0
记录一下sql滑窗的学习
m0_63513270的博客
08-26 217
链接
滑动窗口机制
langjian2012的专栏
03-12 3207
计算机网络 滑动窗口机制
SQL聚合函数实现滑动窗口计算
简单的记录一下
11-16 314
COUNT(col) - 返回所有非空值的个数。FIRST(col) - 返回第一个记录的值。LAST(col) - 返回最后一个记录的值。AVG(col) - 返回平均值。MAX(col) - 返回最大值。MIN(col) - 返回最小值。COUNT(字段) 空值不计入。SUM(col) - 返回总和。
想要精通算法和SQL的成长之路 - 滑动窗口和大小根堆
Zong_0915的博客
10-14 426
想要精通算法和SQL的成长之路 - 系列导航。
SQL学习(基础) ——窗口函数
S1406793的博客
10-25 2308
窗口函数、滑动函数、
滑动窗口算法_滑动窗口算法
weixin_39634576的博客
11-24 232
形如 LeetCode第209题：给定一个含有 n 个正整数的数组和一个正整数 s ，找出该数组中满足其和 ≥ s 的长度最小的连续子数组。如果不存在符合条件的连续子数组，返回 0。 示例: 输入: s = 7, nums = [2,3,1,2,4,3] 输出: 2 解释: 子数组 [4,3] 是该条件下的长度最小的连续子数组。或者是形如 LeetCode第3题 无重复字符的最长子串：给定一个字...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值