T-SQL 语句 OVER 子句排名函数

Question

我有一个表格，看起来像这样：

Field1  Field2  ValidFrom
200     a       01.01.1999
200     b       01.01.2015
210     c       01.01.2015
210     c       01.01.2010

现在，我尝试生成一个带有附加列的选择语句，当 Field1 保持不变但 Field2 发生变化时，该列会递增。extraColumn 中值的顺序应取决于 ValidFrom，这意味着例如 1 表示 01.01.1999,2 表示 01.01.2015(而不是相反！ 当 Field1 更改时，extraColumn 中的值应再次以 1 开头。当字段 1 和字段 2 的组合不更改时，它应保持相同的值。 所以我想要的结果看起来像这样：

extraColumn Field1  Field2  ValidFrom
1           200     a       01.01.1999
2           200     b       01.01.2015
1           210     c       01.01.2015
1           210     c       01.01.2010

我试图通过将此查询与 RANK(( 函数一起使用来获得此结果：

select RANK() OVER (
PARTITION BY [Field1], [Field2] 
ORDER BY [ValidFrom] DESC
) as 'extraColumn'
,Field1 ,Field2 ,ValidFrom
FROM table1

不幸的是，这并没有像我预期的那样工作，它确实与我想要的相反，所以我的结果看起来像：

extraColumn Field1  Field2  ValidFrom
1           200     a       01.01.1999
1           200     b       01.01.2015
1           210     c       01.01.2015
2           210     c       01.01.2010

知道我做错了什么吗？

Answer 1

在这里，我回答了一个类似的问题。以下是根据您的条件进行调整：

-- Preparation
declare @t table (
Field1 int,
Field2 char,
ValidFrom date
);
insert into @t (Field1, Field2, ValidFrom)
values
(200, 'a', '19990101'),
(200, 'b', '20150101'),
(210, 'c', '20150101'),
(210, 'c', '20100101');
-- The query
with cte as (
select t.*,
lag(t.Field2) over(partition by t.Field1 order by t.ValidFrom) as [Prev2]
from @t t
)
select c.Field1, c.Field2, c.ValidFrom,
sum(case when c.Prev2 = c.Field2 then 0 else 1 end)
over(partition by c.Field1 order by c.ValidFrom) as [ExtraColumn]
from cte c;

我只希望您不要对数百万或记录运行它，因为 2 个分区不会使 CPU 和内存变得容易。哦，是的，您需要SQL Server 2012或更高版本才能正常工作。

Answer 2

使用DENSE_RANK，您需要在order by中使用Field2才能获得所需的结果而不是ValidFrom

DENSE_RANK() OVER (PARTITION BY [Field1] ORDER BY [Field2])

Answer 3

试试这样...

WITH 
cte_MaxDate AS (
SELECT 
*,
MaxDate = MAX(td.ValidFrom) OVER (PARTITION BY td.Feild1, td.Field2)
FROM
#TestData td
)
SELECT 
DENSE_RANK() OVER (PARTITION BY md.Feild1 ORDER BY md.MaxDate, md.Field2),
md.Feild1, md.Field2, md.ValidFrom--, md.MaxDate
FROM
cte_MaxDate md;

Answer 4

我认为您将不得不在此处使用递归来逐行计算字段 2 是否更改。

WITH CTE_RN AS 
(
SELECT  * 
, ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY ValidFrom) RN
FROM Table1
)
, RCTE AS 
(
SELECT *, 1 AS ExtraColumn 
FROM CTE_RN WHERE RN = 1
UNION ALL
SELECT c.*
, CASE WHEN r.Field2 = c.Field2 THEN r.ExtraColumn ELSE r.ExtraColumn + 1 END
FROM RCTE r 
INNER JOIN CTE_RN c ON r.Field1 = c.Field1 AND r.RN + 1 = c.RN
)
SELECT * 
FROM RCTE
ORDER BY Field1, ValidFrom
OPTION (MAXRECURSION 0)

SQLFiddle DEMO (我为更复杂的样本添加了更多行(