SQL Server 查询选择 min over partition by

Question

我很难通过按语句选择分区来理解这一点。我读过它，但不明白。使用的好处是什么:

SELECT MIN(Field) OVER (PARTITION BY OtherField) as Value
FROM MYTABLE

你能简要描述一下，你什么时候使用它，为什么？写这样的东西有什么好处和成就目标

Answer 1

OVER (PARTITION BY OtherField)是窗函数。另外，主要思想是按分区分组没有 减少选定表行的计数。

一般来说，窗口函数会比连接/聚合解决方案更快。这是一个相当简单的情况，因此性能可能基本相同。

让我举个例子:

CREATE TABLE dbo.Duration     (startDate datetime2, endDate datetime2);

INSERT INTO dbo.Duration (startDate, endDate)  VALUES ('2007-05-06 16:42:09', '2007-05-07 11:10:08');  
INSERT INTO dbo.Duration (startDate, endDate)  VALUES ('2007-05-07 08:30:09', '2007-05-07 12:12:43');  
INSERT INTO dbo.Duration (startDate, endDate)  VALUES ('2007-05-07 11:35:09', '2007-05-07 17:13:39');  
INSERT INTO dbo.Duration (startDate, endDate)  VALUES ('2007-05-07 08:30:10', '2007-05-07 12:12:43');  
INSERT INTO dbo.Duration (startDate, endDate)  VALUES ('2007-05-07 08:30:11', '2007-05-07 12:12:43');  
INSERT INTO dbo.Duration (startDate, endDate)  VALUES ('2007-05-07 08:30:12', '2007-05-07 12:12:43');  

现在我们可以按字段 EndDate 进行分区并获得 MIN()开始日期 in the partition of结束日期`:

SELECT 
MIN(dr.startDate)  OVER (PARTITION BY dr.endDate ) MinDateByPartitionOfEndDate
,  dr.endDate
FROM dbo.Duration dr

高度关注 2007-05-07 12:12:43和它们的值 2007-05-07 08:30:09.0000000 .所以我们没有减少查询结果，但是我们得到了 MIN每个分区的值按所需字段。

输出:

MinDateByPartitionOfEndDate    endDate
2007-05-06 16:42:09.0000000    2007-05-07 11:10:08.0000000
2007-05-07 08:30:09.0000000    2007-05-07 12:12:43.0000000
2007-05-07 08:30:09.0000000    2007-05-07 12:12:43.0000000
2007-05-07 08:30:09.0000000    2007-05-07 12:12:43.0000000
2007-05-07 08:30:09.0000000    2007-05-07 12:12:43.0000000
2007-05-07 11:35:09.0000000    2007-05-07 17:13:39.0000000
2007-05-07 11:35:09.0000000    2007-05-07 17:13:39.0000000

还有一个 GROUP BY (它减少了查询结果)查询查看 GROUP BY之间的主要区别和 PARTITION BY :

SELECT 
MIN(dr.startDate) MinDateByGroupBy
,  dr.endDate
FROM dbo.Duration dr
GROUP BY dr.endDate

输出:

MinDateByGroupBy                    endDate
2007-05-06 16:42:09.0000000     2007-05-07 11:10:08.0000000
2007-05-07 08:30:09.0000000     2007-05-07 12:12:43.0000000
2007-05-07 11:35:09.0000000     2007-05-07 17:13:39.0000000