CODEWITHSUNDEEP
sqloracle-database
czasoprzestrzenny
czasoprzestrzenny

Reputation: 27

SQL ORACLE grouping by row values (values are a same, but important is ordering a data)

I have a below values and i want to group them by urlid and status values. I have used a group by function that these did not work for me. I have tried use a lag and lead methods but i cant handle that. A same ouptut i can receive using a python3 script, but i does not work in cloud and i have to download all table to generate that. Is it possible to do that in ORACLE11 database?

CREATE TABLE test_group_by (
urlid text,
status text,
date_scraped date)

INSERT INTO test_group_by (urlid, statuses, date_scraped)
values
('1','active','2019-06-15'),
('1','active','2019-07-15'),
('1','active','2019-08-15'),
('1','inactive','2019-09-15'),
('1','active','2019-11-15'),
('1','active','2019-12-15'),
('2','active','2019-06-15'),
('2','inactive','2019-07-15'),
('2','active','2019-08-15')

On right there is a output which i want to get

Upvotes: 1

Views: 70

Answers (2)

Popeye
Popeye

Reputation: 35930

If you have only two statuses then you can use following:

Select urlid, statuses, 
       min(date_scraped), 
       max(date_scraped)
  From
  (Select t.*, 
          Sum(case when statuses = 'active' then 0 else 1 end) as sm
     From your_table t)
Group by urlid, statuses, sm

Cheers!!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

This is a gaps-and-islands. One solution is the difference of row numbers:

select urlid, status, min(date_scraped), max(date_scraped)
from (select t.*,
             row_number() over (partition by urlid order by date_scraped) as seqnum,
             row_number() over (partition by urlid, status order by date_scraped) as seqnum_s
      from test_group_by t
     ) t
group by urlid, status, (seqnum - seqnum_s)
order by urlid, min(date_scraped);

Here is a db<>fiddle. It uses Postgres because your DDL does not work for Oracle.

Upvotes: 1

Related Questions