主页 / coding / 问题 / 79349469
Accepted
DooDoo
Asked: 2025-01-12 14:50:44 +0800 CST

使用带有年份和月份（时间段）的 SQL Server 窗口函数

  • 772

请考虑这个脚本：

Declare @tbl Table
(
    F1    int,
    F2    int,
    Year  int,
    Month tinyint
)

Insert into @tbl 
values
(10, 1, 2020, 1),
(10, 1, 2020, 2),
(10, 1, 2020, 3),
(10, 1, 2020, 4),
(10, 2, 2020, 5),
(10, 1, 2020, 6),
(10, 1, 2020, 7),
(11, 1, 2020, 1),
(11, 1, 2020, 2),
(11, 2, 2020, 3),
(11, 2, 2020, 4),
(11, 1, 2020, 5),
(12, 1, 2020, 1),
(12, 1, 2020, 2),
(12, 1, 2020, 3),
(12, 1, 2020, 4)

我写了这个查询：

Select 
    F1, F2, year, month,
    Row_number() over (partition by F1, F2 order by year, month) as rownumber,
    Rank() over (partition by F1, F2 order by year, month) as rnk
From 
    @tbl
Order by 
    f1, f2, year, month

此查询返回以下结果集：

F1 F2 年 月 行号 排名
10 1 2020 1 1 1
10 1 2020 2 2 2
10 1 2020 3 3 3
10 1 2020 4 4 4
10 1 2020 6 5 5
10 1 2020 7 6 6
10 2 2020 5 1 1
11 1 2020 1 1 1
11 1 2020 2 2 2
11 1 2020 5 3 3
11 2 2020 3 1 1
11 2 2020 4 2 2
12 1 2020 1 1 1
12 1 2020 2 2 2
12 1 2020 3 3 3

但我想要这样的结果：

F1 F2 年 月 顺序
10 1 2020 1 1
10 1 2020 2 2
10 1 2020 3 3
10 1 2020 4 4
10 2 2020 5 1
10 1 2020 6 1
10 1 2020 7 2
11 1 2020 1 1
11 1 2020 2 2
11 2 2020 3 1
11 2 2020 4 2
11 1 2020 5 1
12 1 2020 1 1
12 1 2020 2 2
12 1 2020 3 3
12 1 2020 4 4

也就是说，在每组相同的 F1 值中，
先按年份再按月份排序，
但每次 F2 改变时重置年份月份序列号。

我怎样才能实现我想要的结果？谢谢

sql

3 个回答

  1. Best Answer

    这基本上是伪装的间隙和孤岛问题。这里，每个“孤岛”是一组属于同F1一块的记录，F2这些记录的值在年份和月份之间没有按顺序发生变化。我们可以生成一个伪组来跟踪这些记录，然后利用来生成最终的序列值。请注意，当块结束或值发生变化ROW_NUMBER()时，此伪组值将重置为 1 。F1F2

    WITH cte AS (
    SELECT *, CASE WHEN LAG(F2) OVER (PARTITION BY F1 ORDER BY Year, Month) != F2 THEN 1 ELSE 0 END AS flag
    FROM @tbl
),
cte2 AS (
    SELECT *, SUM(flag) OVER (PARTITION BY F1 ORDER BY Year, Month) grp
    FROM cte
)

SELECT F1, F2, Year, Month,
       ROW_NUMBER() OVER (PARTITION BY F1, grp ORDER BY Year, Month) AS seq
FROM cte2
ORDER BY F1, Year, Month;

    演示

    • 3

  2. 确定边界（F1 分组序列中 F2 发生变化的时刻）后，可以从所有后续位置中减去最后一个边界的位置：

    With bordered as
(
    Select 
        *,
        Row_number() over win as pos,
        case when F2 = lag(F2) over win then null else Count(1) over win - 1 end as ignore_before
    From 
        @tbl
    Window win As (partition by F1 order by year, month)
)
Select F1, F2, year, month, pos - max(ignore_before) over win Sequence
from bordered
Window win As (partition by F1 order by year, month)
Order by
    f1, year, month;

    这里它在 SQLFiddle 中运行。

    因为您需要窗口位置的窗口最大值，所以
    您需要对每个窗口函数进行至少一次传递，
    因此需要使用 CTE。

    请注意，我使用了窗口定义以避免重复使用（从而避免复制错误）。

    • 1 
  3. WITH GroupedData
AS (SELECT F1,F2,YEAR,MONTH, 
           CASE
               WHEN F2 != LAG(F2, 1, F2) OVER (PARTITION BY F1 ORDER BY YEAR, MONTH) THEN
                   1
               ELSE
                   0
           END AS IsNewGroup
    FROM tbl),
     Groups
AS (SELECT F1,F2,YEAR,MONTH, 
           SUM(IsNewGroup) OVER (PARTITION BY F1 ORDER BY YEAR, MONTH) AS GroupID
    FROM GroupedData)
SELECT F1,F2,YEAR,MONTH, 
       ROW_NUMBER() OVER (PARTITION BY F1, GroupID ORDER BY YEAR, MONTH) AS SEQUENCE
FROM Groups
ORDER BY F1,YEAR,MONTH;

    我们使用该LAG()函数查看上一行的 F2 值（在同一个 F1 组中，按年份和月份排序）。如果 F2 值与上一行不同，则表示新组正在开始。在这种情况下，我们将其标记为 1（称为IsNewGroup）。如果相同，我们将其标记为 0。

    我们取IsNewGroup列并使用 计算累计总数SUM()。此累计总数（称为GroupID）可帮助我们为 F1 组中共享相同 F2 值的每组行分配一个唯一编号。对于每个组（基于 F1 和 GroupID），我们使用 生成一个序列号ROW_NUMBER()。此序列按年份和月份排序。最后，我们按 F1、年份和月份对所有内容进行排序，以便结果以正确的顺序显示。

    • 1

相关问题