在Oracle中字段值更改时增加行号 [英] increment row number when value of field changes in Oracle

问题描述

在Oracle中为以下数据编写查询时，我需要帮助.数据按人"和天"字段排序.

I need help in writing a query in Oracle for the following data. The data is sorted by Person and Day fields.

    Person     Day   Flag
    ------     ---   ---- 
    person1    day1   Y
    person1    day2   Y
    person1    day3   Y
    person1    day4   N
    person1    day5   N
    person1    day6   Y
    person1    day7   Y
    person1    day8   Y

我需要有一个Group_Number列，每当Flag值更改时，该列都会增加.我的结果应如下图所示

I need to have a Group_Number column that gets incremented whenever the Flag value changes. My result should look as below

    Person     Day   Flag  Group_Number
    ------     ---   ----  ------------
    person1    day1   Y    1
    person1    day2   Y    1
    person1    day3   Y    1
    person1    day4   N    2
    person1    day5   N    2
    person1    day6   Y    3
    person1    day7   Y    3
    person1    day8   Y    3

我认为可以使用ROW_NUMBER，LEAD等分析函数来获得上述结果.

I think there is way to get above result using analytic functions such as ROW_NUMBER, LEAD etc.

推荐答案

您可以组合 LAG :

You can combine the analytic functions SUM (used as a running total) and LAG:

SQL> WITH data AS (
  2            SELECT 'person1' person, 'day1' day, 'Y' flag FROM dual
  3  UNION ALL SELECT 'person1' person, 'day2' day, 'Y' flag FROM dual
  4  UNION ALL SELECT 'person1' person, 'day3' day, 'Y' flag FROM dual
  5  UNION ALL SELECT 'person1' person, 'day4' day, 'N' flag FROM dual
  6  UNION ALL SELECT 'person1' person, 'day5' day, 'N' flag FROM dual
  7  UNION ALL SELECT 'person1' person, 'day6' day, 'Y' flag FROM dual
  8  UNION ALL SELECT 'person1' person, 'day7' day, 'Y' flag FROM dual
  9  UNION ALL SELECT 'person1' person, 'day8' day, 'Y' flag FROM dual
 10  )
 11  SELECT person, DAY, flag, SUM(gap) over (PARTITION BY person
 12                                           ORDER BY DAY) grp
 13    FROM (SELECT person, DAY, flag,
 14                 CASE WHEN flag = lag(flag) over (PARTITION BY person
 15                                                  ORDER BY DAY)
 16                      THEN 0
 17                      ELSE 1
 18                 END gap
 19            FROM DATA);

PERSON  DAY  FLAG        GRP
------- ---- ---- ----------
person1 day1 Y             1
person1 day2 Y             1
person1 day3 Y             1
person1 day4 N             2
person1 day5 N             2
person1 day6 Y             3
person1 day7 Y             3
person1 day8 Y             3

这篇关于在Oracle中字段值更改时增加行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！