SQL Server 2005中的分区函数

发布于 2024-07-14 07:05:35 字数 673 浏览 7 评论 0原文

在 MSDN 中有关分区函数的信息，$PARTITION(Transact-SQL)。

我对下面的示例的底层功能感到困惑。我的理解是，这个SQL语句将迭代表Production.TransactionHistory中的所有行，并且因为对于映射到同一分区的所有行，$PARTITION.TransactionRangePF1(TransactionDate)将返回相同的值，即所有行的分区号这样的行。因此，例如，分区 1 中的所有行将导致返回结果中的一行，因为它们都具有相同的 $PARTITION.TransactionRangePF1(TransactionDate) 值。我的理解正确吗？

USE AdventureWorks ;
GO
SELECT $PARTITION.TransactionRangePF1(TransactionDate) AS Partition, 
COUNT(*) AS [COUNT] FROM Production.TransactionHistory 
GROUP BY $PARTITION.TransactionRangePF1(TransactionDate)
ORDER BY Partition ;
GO

原文

In MSDN about partition function from here, $PARTITION(Transact-SQL).

I am confused about what the below sample is doing underlying. My understanding is, this SQL statement will iterate all rows in table Production.TransactionHistory, and since for all the rows which will mapping to the same partition, $PARTITION.TransactionRangePF1(TransactionDate) will return the same value, i.e. the partition number for all such rows. So, for example, all rows in partition 1 will result in one row in returning result since they all of the same value of $PARTITION.TransactionRangePF1(TransactionDate). My understanding correct?

USE AdventureWorks ;
GO
SELECT $PARTITION.TransactionRangePF1(TransactionDate) AS Partition, 
COUNT(*) AS [COUNT] FROM Production.TransactionHistory 
GROUP BY $PARTITION.TransactionRangePF1(TransactionDate)
ORDER BY Partition ;
GO

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

人│生佛魔见 2024-07-21 07:05:35

如果您的分区函数定义如下

CREATE PARTITION FUNCTION TransactionRangePF1(DATETIME)
AS RANGE RIGHT FOR VALUES ('2007-01-01', '2008-01-01', '2009-01-01')

，则此子句：

$PARTITION.TransactionRangePF1(TransactionDate)

相当于：

CASE
  WHEN TransactionDate < '2007-01-01' THEN 1
  WHEN TransactionDate < '2008-01-01' THEN 2
  WHEN TransactionDate < '2009-01-01' THEN 3
  ELSE 4
END

如果所有日期都在 '2007-01-01' 之前，则第一个 WHEN 子句将总是触发并且总是返回1。

您发布的查询将为每个分区最多返回 1 行，因为它将将该分区中的所有行（如果有）分组为一组。

如果任何分区都没有行，则结果集中不会返回该分区的任何行。

If your parition function is defined like

CREATE PARTITION FUNCTION TransactionRangePF1(DATETIME)
AS RANGE RIGHT FOR VALUES ('2007-01-01', '2008-01-01', '2009-01-01')

, then this clause:

$PARTITION.TransactionRangePF1(TransactionDate)

is equivalent to:

CASE
  WHEN TransactionDate < '2007-01-01' THEN 1
  WHEN TransactionDate < '2008-01-01' THEN 2
  WHEN TransactionDate < '2009-01-01' THEN 3
  ELSE 4
END

If all your dates fall before '2007-01-01', then the first WHEN clause will always fire and it will always return 1.

The query you posted will return at most 1 row for each partition, as it will group all the rows from the partition (if any) into one group.

If there are no rows for any partition, no rows for it will be returned in the resultset.

回复收藏 0 原文