Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Assign group value based on lag value change

325647
Member Posts: 311
I have some old data that has a structure determined by order. The groups are in ascending order, based that the amount column, and delineated by when the previous value is less than the current value. I just need to assign a unique group ID to each group. This is what I have:
Order Amount Groupand this is what I would like:
1 56
2 63
3 41
4 52
5 59
6 74
7 39
8 42
9 44
10 51
11 62
12 77
Order Amount GroupI can get the previous value with the lag function but am struggling with assigning the number. I am a sql more than pl/sql person. TIA.
1 56 1
2 63 1
3 41 2
4 52 2
5 59 2
6 74 2
7 39 3
8 42 3
9 44 3
10 51 3
11 62 3
12 77 3
Answers
-
-
with t as ( select 1 ord,56 amount from dual union all select 2,63 from dual union all select 3,41 from dual union all select 4,52 from dual union all select 5,59 from dual union all select 6,74 from dual union all select 7,39 from dual union all select 8,42 from dual union all select 9,44 from dual union all select 10,51 from dual union all select 11,62 from dual union all select 12,77 from dual ) select ord, amount, sum(start_of_group) over(order by ord) grp from ( select ord, amount, case when lag(amount,1,amount + 1) over(order by ord) > amount then 1 end start_of_group from t ) order by ord / ORD AMOUNT GRP ---------- ---------- ---------- 1 56 1 2 63 1 3 41 2 4 52 2 5 59 2 6 74 2 7 39 3 8 42 3 9 44 3 10 51 3 11 62 3 ORD AMOUNT GRP ---------- ---------- ---------- 12 77 3 12 rows selected. SQL>
SY. -
SQL> with t as ( select 1 o, 56 amount from dual union all select 2, 63 from dual union all select 3, 41 from dual union all select 4, 52 from dual union all select 5, 59 from dual union all select 6, 74 from dual union all select 7, 39 from dual union all select 8, 42 from dual union all select 9, 44 from dual union all select 10, 51 from dual union all select 11, 62 from dual union all select 12, 77 from dual ) --- --- select o "ORDER", amount, sum (grp) over (order by o) + 1 "GROUP" from (select t.*, case when lag (amount) over (order by o) > amount then 1 else 0 end grp from t) t order by o / ORDER AMOUNT GROUP ---------- ---------- ---------- 1 56 1 2 63 1 3 41 2 4 52 2 5 59 2 6 74 2 7 39 3 8 42 3 9 44 3 10 51 3 11 62 3 12 77 3 12 rows selected.
-
Thanks. All of these solutions solve the problem.
-
I stumbled upon this question and played a bit with it and I'm wondering what point I'm missing.
On first thought I'd wanted to update T using the UPDATE A JOIN technique (as a small 'weekend-exercise' ;-) ), but I keep on hitting ORA-01779 although I added a PK constraint and all columns of T are updatable/insertable/deletable. I tried several ways, below is the last effort, but all to no luck.
But using the same query with MERGE works without any problem.
What point am I missing? Perhaps it's very obvious, but as to now I don't really get it.
Could it be the analytics?
See this example:SQL> drop table t; Table dropped. SQL> create table t as 2 select 1 col_order, 56 amount from dual union all 3 select 2, 63 from dual union all 4 select 3, 41 from dual union all 5 select 4, 52 from dual union all 6 select 5, 59 from dual union all 7 select 6, 74 from dual union all 8 select 7, 39 from dual union all 9 select 8, 42 from dual union all 10 select 9, 44 from dual union all 11 select 10, 51 from dual union all 12 select 11, 62 from dual union all 13 select 12, 77 from dual; Table created. SQL> alter table t add constraint t_pk primary key (col_order); Table altered. SQL> alter table t add (grp number); Table altered. SQL> select t.* 2 from all_updatable_columns t 3 where t.table_name = 'T'; OWNER TABLE_NAME COLUMN_NAME UPD INS DEL ------------------------------ ------------------------------ ------------------------------ --- --- --- HOEK T COL_ORDER YES YES YES HOEK T AMOUNT YES YES YES HOEK T GRP YES YES YES 3 rows selected. SQL> select * from t; COL_ORDER AMOUNT GRP ---------- ---------- ---------- 1 56 2 63 3 41 4 52 5 59 6 74 7 39 8 42 9 44 10 51 11 62 12 77 12 rows selected. SQL> update( 2 select t1.grp 3 , t2.new_grp 4 from t t1 5 , ( select col_order 6 , sum(ranges) over (order by col_order) new_grp 7 from ( select col_order 8 , case 9 when lag(amount) over (order by col_order) < amount 10 then 0 11 else 1 12 end ranges 13 from t 14 ) 15 ) t2 16 where t1.col_order = t2.col_order 17 ) 18 set grp = new_grp; set grp = new_grp * ERROR at line 18: ORA-01779: cannot modify a column which maps to a non key-preserved table SQL> select * from t; COL_ORDER AMOUNT GRP ---------- ---------- ---------- 1 56 2 63 3 41 4 52 5 59 6 74 7 39 8 42 9 44 10 51 11 62 12 77 12 rows selected. SQL> merge into t 2 using ( select col_order 3 , sum(ranges) over (order by col_order) new_grp 4 from ( select col_order 5 , case 6 when lag(amount) over (order by col_order) < amount 7 then 0 8 else 1 9 end ranges 10 from t 11 ) 12 ) t2 13 on (t.col_order = t2.col_order) 14 when matched then update set grp = new_grp; 12 rows merged. SQL> select * from t; COL_ORDER AMOUNT GRP ---------- ---------- ---------- 1 56 1 2 63 1 3 41 2 4 52 2 5 59 2 6 74 2 7 39 3 8 42 3 9 44 3 10 51 3 11 62 3 12 77 3 12 rows selected. SQL>
-
Hi !
Also from curiosityi have done some testing too
I have test this stuff withcreate or replace view VV as select <anything - also analytic functions> from (SUBQUERY) ..
SUBQUERY is select statement from table with PK and .. everything is just OK with table, and for a view VV is table key preserved
then
If somewhere in SUBQUERY is an analytic function then view VV has no updatable columns from table in subquery..
if there is no analytic functions in SUBQUERY then there are updatable columns in VV
I don't think that i have seen something like this in documentation .. Interesting realy . But in Merge statement this is OK .. Hm ??
Have a nice Sunday ..
This discussion has been closed.