oracle 中增加行,在Oracle中字段值更改时增加行号

我需要帮助在Oracle中编写查询以获取以下数据.数据按人员和日期字段排序.

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值发生变化就会增加.我的结果应如下所示

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等获得上述结果.

您可以将

analytic functions SUM(用作运行总计)和

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,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

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。