Sequencing/grouping broken sequnce numbers in sql server

ifmq2ha2 于 2023-03-07 发布在 SQL Server

关注(0)|答案(1)|浏览(166)

I have the data like,

Term_order, name
1,Summer
2,Fall
3,Spring
4,Fall
5,Fall
6,Spring
7,Summer
8,Spring

I am trying to calculate the Year column. A year can start with summer and can end with spring. Su, Fa, Sp is the order. That said, the result should be,

Term_order, name, year
1, Summer, 1
2, Fall, 1
3, Spring, 1
4, Fall, 2 (note that year 2 does not have Su, but it has ordered it and fall is the first term.so it marked it as year 2)
5, Fall, 3
6, Spring, 3
7, Summer, 4
8, Spring, 4

I tried converting the summer, fall, spring to number like 1,2,3 and tried creating rank based on term_num and term_nm. I know it does not work as there is no grouping here.

sql-server

来源：https://stackoverflow.com/questions/75642578/sequencing-grouping-broken-sequnce-numbers-in-sql-server

1条答案

按热度按时间

sgtfey8w1#

This is a classic Gaps-and-Islands problem .

We can start by assigning the seasons a number in order: 1 for Summer, 2 for Fall, etc.

Then we can use LAG to check if the previous row is the same or later season, in which case we must have gone over to a new year, and mark those rows.

Then we simply do a running count of those marked rows, adding 1 to begin the count at 1.

WITH NumberedSeason AS (
    SELECT *,
      Season = CASE name
        WHEN 'Summer' THEN 1
        WHEN 'Fall' THEN 2
        WHEN 'Winter' THEN 3
        ELSE 4
        END
    FROM Term
),
Gaps AS (
    SELECT *,
      IsNewYear = CASE WHEN LAG(Season) OVER (ORDER BY Term_order) >= Season THEN 1 END
    FROM NumberedSeason
)
SELECT
  Term_order,
  name,
  Year = COUNT(IsNewYear) OVER (ORDER BY Term_order ROWS UNBOUNDED PRECEDING) + 1
FROM Gaps;

db<>fiddle

赞(0）回复(0）举报 2023-03-07

我来回答

Sequencing/grouping broken sequnce numbers in sql server

1条答案

相关问题

热门标签

最新问答