I hope you are doing well! Sorry for the basic question, however, I have been struggling to get the rows of the longest consecutive same value of the whole table and I was wondering what the query would be to achieve this? Example, let’s say that I have the table Learning: rowID values 1 1… Read More Get the Rows of the Longest Consecutive Same Value of the Whole Table
I have these tables: CREATE TABLE labels(user_id INT, session_id INT, start_time TIMESTAMP,mode TEXT); INSERT INTO labels (user_id,session_id,start_time,mode) VALUES (48,652,’2016-04-01 00:47:00+01′,’foot’), (9,656,’2016-04-01 00:03:39+01′,’car’),(9,657,’2016-04-01 00:26:51+01′,’car’), (9,658,’2016-04-01 00:45:19+01′,’car’),(46,663,’2016-04-01 00:13:12+01′,’car’); CREATE TABLE raw_data(user_id INT, session_id INT,timestamp TIMESTAMP) ; INSERT INTO raw_data(user_id, session_id,timestamp) VALUES (8,652,’2016-04-01 00:46:11.638+01′),(8,652,’2016-04-01 00:47:00.566+01′), (8,652,’2016-04-01 00:48:06.383+01′),(9,656,’2016-04-01 00:14:17.707+01′), (9,656,’2016-04-01 00:15:18.664+01′),(9,656,’2016-04-01 00:16:19.687+01′), (9,656,’2016-04-01 00:24:20.691+01′),(9,656,’2016-04-01 00:25:23.681+01′), (9,657,’2016-04-01 00:24:50.842+01′),(9,657,’2016-04-01 00:26:51.096+01′), (9,657,’2016-04-01… Read More Gap-and-island for more than time threshold
I have an Oracle table with time stamps and I need to check on all rows where the current row is bigger the the previous row by less than a minute and state the start and end time and if its bigger than a minute I need to start a new group as in the… Read More grouping rows with less than one minute separating row from previous row in Oracle
With a given dataset: WITH ranges AS ( select to_date(‘01.01.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_from, to_date(‘31.03.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_to from dual union select to_date(‘27.03.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_from, to_date(‘27.04.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_to from dual union select to_date(‘01.05.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_from, to_date(‘31.12.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_to from dual ) SELECT * FROM ranges; How to find the gap 28.04.2021-30.04.2021.? Also… Read More How to fill date range gaps Oracle SQL