本文介绍了在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.
推荐答案
您可以组合 SUM
(用作运行总计)和:
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中字段值更改时增加行号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!