Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [gaps-and-islands]

Gaps and islands problems involve finding a range of missing values (gaps) or a range of consecutive values (islands) in a sequence of numbers or dates.

Filter by
Sorted by
Tagged with
1
vote
2answers
649 views

Group rows by uninterrupted dates

I want to return data in such way that the data which have if code has continuous active dates and end dates it should give Min as active date and Max end date, but if code was discontinued it should ...
3
votes
3answers
59 views

Selecting row grouped by an “add-on” indicator

I have a (pre-existing, no I did not design it like this!) schema / dataset like this: CREATE TABLE t(id,name,seq,addon,kind) AS VALUES ( 1234::int, 'A' , 345::int, 'f'::bool, 'c' ), ( 1235 ...
1
vote
3answers
553 views

sql server 2014 - Find gaps in sequence on 2 different fields group by third field

I need to detect the gaps between the sequences for each different seller considering that sequence is in 2 different rows considering only supplied range (avoiding numbers below the first record and ...
3
votes
2answers
88 views

Grouping Subsets of Rows with Null Values within an Ordered Set

Lets say we have an table where each row is a day, and it is ordered by this day column. Then we have left joined a membership data set showing which day the members were active (and not). Lets say ...
6
votes
1answer
4k views

Selecting “consecutive” rows with same values

I have a table with the following data: userID tStamp status ------ ------------------- ------ Jason 2017-10-18 03:20:00 idle Brown 2017-10-18 03:20:28 idle Brown ...
1
vote
2answers
691 views

Merge date ranges on updates

Given a table of date ranges, e.g. Start Date, End Date, I've seen a few examples online of how to merge ranges on the fly, but I'm able to merge them as the user makes updates. So for example, if 01/...
4
votes
1answer
981 views

Carry over long sequence of missing values with Postgres

I have a table like this: create table foo (foo_label text, foo_price int, foo_date date); insert into foo ( values ('aaa', 100, '2017-01-01'), ('aaa', NULL, '2017-02-...
12
votes
5answers
2k views

Populating missing data based on previous month-end values

Given the following data: create table #histories ( username varchar(10), account varchar(10), assigned date ); insert into #histories values ('PHIL','ACCOUNT1','2017-01-04'), ('...
5
votes
1answer
672 views

Gaps and Islands - Finding the Nearest Island

I am working with the following scenario where I have temporal data that falls into islands and gaps. Every once in a while I need to associate an event that falls within an existing gap to its ...
0
votes
2answers
312 views

Any way to calculate frequency of a value without a cursor?

Given I have a column that has an increment value and I know that value is going to climb to X (X being arbitrary) I would like to count what the longest repetitive streak is without the last count (...
1
vote
1answer
2k views

Grouping similar rows together based on time

I have a table that records all the changes done to a model by a user. The table looks something like this: (SQLFiddle) In the table an action_hash is generated based on the model, model_id and ...
3
votes
2answers
594 views

Validate id sequence consistency in records in Redshift

I have a backend that log event to Redshift, for each event it generates a unique id. The id is a sequential number. I have something like (events table): +-------------------------+------+ | ...
1
vote
1answer
2k views

Count and Group data based on consecutive occurrence

Tricky for me to define the problem, maybe you understand it through my dummy data. I have this data: PK, TaskPK 1, 1 2, 1 3, 2 4, 2 5, 5 6, 1 7, 1 8, 2 9, 2 10, 5 ...
4
votes
2answers
99 views

List chronological pageviews without adjacent duplicates in PostgreSQL

I've got a log of pageviews. Multiple pageviews with the same path may occur next to each other if say the user refreshes the page. To see the user's journey through the site I'd like to select ...
1
vote
2answers
98 views

Difference between date value change

Link: Query (duration) different between data value change I am trying to write a query that would get me the duration of a status in a table. This query needs to work in SQL Server 2008. Say I have ...
1
vote
1answer
3k views

Total hours worked per employee per day

I have a table like this: SQLfiddle: http://www.sqlfiddle.com/#!9/340c8 CREATE TABLE Table1 (ID int, empid int, time datetime, state int); +--------------+---------------------+-----------------+ | ...
1
vote
2answers
109 views

return continuous set of IDs based on two other columns

Given a table such as the one below, how can I ask PostgreSQL to return the first set of n rows in which one column has a certain value (code=0) and the values of the second column (named segment) are ...
6
votes
4answers
5k views

Finding missing gaps of data in a table with ~2.5 Million rows

I work for an economics consulting company and all of our data is housed in SQL Server 11 (2012). Our data essentially consists of a date column, a raw data column, several calculations columns and a ...
0
votes
1answer
1k views

How to check if logs has 7 consecutive days?

Objective Below is the example data format. I need to get the in day 1, 2, 3, and so on.. On each account. +------------+----------------+----------------+---------------+--------------+-----+ | ...
1
vote
1answer
1k views

How to fill gaps with calendar table, count and group by

I'm building a bar chart. I would like a query which counts the entries from a particular day and groups them together as one row with a count. I have the following query working and I have a working ...
13
votes
7answers
1k views

Grouping or Window

I have a situation I think can be solved using window function but I'm not sure. Imagine the following table CREATE TABLE tmp ( date timestamp, id_type integer ) ; INSERT INTO tmp ...
3
votes
1answer
5k views

Solving “Gaps and Islands” with row_number() and dense_rank()?

How does one solve the islands part of gaps-and-islands with dense_rank() and row_number(). I've seen this now a few times and I'm wondering if someone could explain it, Let's use something like this ...
1
vote
1answer
698 views

Is WITH RECURSIVE the right tool for my task?

Table setup: create table users(id bigserial, group_id bigint); insert into users(group_id) values (1), (1), (2), (1), (3), (3); Query: WITH RECURSIVE r AS ( SELECT users.id, users....
-2
votes
1answer
131 views

postgres select aggregate timespans

I have a table with the following structure: timstamp-start, timestamp-stop 1,5 6,10 25,30 31,35 ... I am only interested in continuous timespans e.g. the break between a timestamp-end and the ...
8
votes
2answers
3k views

Select rows starting from specified value until another specified value

I have a table containing several columns and want to check for a column value, select the rows after that column value until a separate column value shows up. I have tried using BETWEEN, but if the ...
5
votes
2answers
6k views

How to get minimum and maximum for grouped timestamps

I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each. So a table that looks like this: | ...
4
votes
2answers
1k views

How to convert list of dates to list of date ranges grouped by a condition

Given a data set in the below format: State AuditDate 1 2016-11-01 1 2016-11-02 1 2016-11-03 0 2016-11-04 1 2016-11-05 0 2016-11-06 0 2016-11-07 1 ...
6
votes
1answer
643 views

GROUP BY possible sequences

I have a table containing the following data, using Postgres 9.6: log_id | sequence | made_at (timestamp) 206480 1 1 206480 1 2 206480 2 3 206480 3 ...
0
votes
1answer
104 views

Counting sessions - TSQL

If we have a table in SQL Server with the following data: ID Log_Time 1110 2016-10-31 20:34:50.000 1110 2016-10-31 20:34:58.000 1110 2016-10-31 20:35:03.000 1110 2016-11-01 01:28:29....
0
votes
1answer
72 views

Rank groups based on order [closed]

A B C 1 132 1 1 155 2 1 132 3 1 132 4 1 155 5 1 155 ...
1
vote
1answer
58 views

Can I iterate a value depending if the previous is what I want?

Suppose that I have this table in PostgreSQL 9.4: CREATE TABLE trajs ( id serial NOT NULL PRIMARY KEY, obj_id integer, traj_id integer, geom geometry, t timestamp without time ...
16
votes
6answers
22k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
5
votes
2answers
568 views

find the beginning and ending values of sequential runs

This article does the inverse of what I need: Find which numbers in [1, 161] are not in the result set? Given the sample data they used: mysql> CREATE TABLE wp_blogs -> ( -> ...
2
votes
1answer
485 views

Select a range of values

I have the following data in rows: 1 2 3 6 7 15 16 I want to write a SELECT statement to return contiguous ranges. Given the sample data above, the output would be: 1,2,3 6,7 15,16
1
vote
1answer
2k views

MySQL - Finding Gaps of Date Ranges - Overlapping Dates

I have been struggling for a long time with generating gaps in date ranges, according to a set. CREATE TABLE `terms` ( `id` int NOT NULL PRIMARY KEY, `term_begin` date NOT NULL, `term_end` date ...
6
votes
2answers
396 views

Query to get queue position for each group

I have a queue table with the following sample data: id company location 1 acme new york 2 acme philadelphia 3 genco st.louis 4 genco san diego 5 genco san francisco 6 acme ...
12
votes
4answers
2k views

Calculate Total Visits

I am trying to write a query where I have to calculate number of visits for a customer by taking care of overlapping days. Suppose for itemID 2009 start date is 23rd and end date is 26th therefore ...
4
votes
4answers
4k views

Find continuous records with equal values

I have a table named "TOOLS" . Sample data: ID TAG 1 0 2 1 3 1 4 1 5 0 6 0 7 1 8 1 9 0 So, the SQL query should ...
2
votes
1answer
584 views

How to get max sequence of rows? [duplicate]

I have a table with statistic of games which has next rows (I only write about rows that has interest in this context): id BIGINT PRIMARY KEY date TIMESTAMP WITH TIME ZONE status ...
5
votes
1answer
181 views

Recursive Join to get Spans

I am pretty new to working in SQL at and have a gaps and islands problem in which I am trying to get a series of consecutive months of membership. Any member may have multiple memberships and their ...
6
votes
1answer
1k views

Running out of disk space running MySQL query

I have been trying to create a "gaps and islands" identification routine in MySQL. See the previous question regarding setup here and computational resources required here. I increased the RAM of the ...
1
vote
1answer
57 views

Extract start and end per group of rows, where only the end can be identified

I have a table that looks like this. id date_from date_to description vehicle 1 2015-10-21 08:00 2015-10-21 10:00 GARAGE TO CLIENT 1 1 2 2015-10-21 12:00 ...
2
votes
1answer
443 views

Measure time duration between events, partitioned by hours

I have the following table in PostgreSQL 9.4, which persists users log on/off events (log on is event_type 1, log off is event_type 0). CREATE TABLE user_online_offline_events ( id serial, ...
4
votes
2answers
211 views

Help with query

I have following data. I am trying to get the number of days the project stayed in a particular status. Example below of raw data, I need to be able to get the result shown. Can I get some ...
2
votes
1answer
105 views

Computational power required for this query

I am not sure if the query I am trying to run is unreasonably large, in terms of computational power. The query will identify islands (and their duration) of a certain condition in my db. Things work ...
8
votes
1answer
2k views

Compacting a sequence in PostgreSQL

I have a id serial PRIMARY KEY column in a PostgreSQL table. Many ids are missing because I have deleted the corresponding row. Now I want to "compact" the table by restarting the sequence and ...
2
votes
1answer
184 views

identify multiple gaps and islands for n columns

In this previous question, I asked for a way of identifying some gaps and islands. with a table t below; +---------------------+------------+---------------+ | ts | EngOilP_sd | ...
3
votes
1answer
214 views

GROUP BY uninterrupted sequence of logs for same location

I'm having a hard time describing my problem or finding it via Google. I want to be able to group rows based on location and employee, but the group should be split if a row with the same location ...
5
votes
1answer
561 views

identify gaps and islands

I have a database that has the following structure +---------------------+------------+---------------+ | timestamp | EngOilP_sd | CompOilLVL_sd | +---------------------+------------+-------...
1
vote
3answers
180 views

Finding “islands” of holiday periods for employees

I'm looking for some tips to find a nice solution for my problem: Let's say I have a table, where days of vacation of employees are stored. e.g. CREATE TABLE test_dates ( id NUMBER ...