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
19 votes
6 answers
33k 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 ...
  • 767
18 votes
7 answers
4k views

Group daily schedule into [Start date; End date] intervals with the list of week days

I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. There can be various gaps in the sequence ...
16 votes
7 answers
4k views

Form groups of consecutive rows with same value

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 (date, id_type) ...
  • 163
13 votes
2 answers
10k views

Select longest continuous sequence

I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specific column. Consider the following table: lap_id (serial), lap_no (int), car_type (...
  • 341
12 votes
4 answers
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 ...
  • 3,993
12 votes
5 answers
3k 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'), ('...
  • 31.4k
11 votes
4 answers
14k views

How do I merge similar records with different validity dates?

The table I am working on has three components: An ID column (primary key in another table) Some data columns Date valid from/to columns. Values: ID Data From To 1 a 2015-01-01 ...
  • 255
10 votes
4 answers
2k views

Gaps and islands: client solution vs T-SQL query

Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client? To be specific, let us provide some test data: CREATE TABLE dbo.Numbers ( n INT NOT NULL ...
  • 7,154
10 votes
1 answer
3k 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 ...
  • 515
10 votes
1 answer
9k 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 ...
9 votes
3 answers
11k views

Using Row_Number to find consecutive row count

I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row If my data looks like this 724 727 728 733 735 737 743 747 ...
9 votes
1 answer
10k 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 ...
  • 59.6k
8 votes
3 answers
8k views

Can I calculate ROW_NUMBER() for only consecutive records?

I need to calculate sequence numbers for consecutive values. That sounds like a job for ROW_NUMBER()! DECLARE @Data TABLE ( Sequence TINYINT NOT NULL PRIMARY KEY, Subset CHAR(1) NOT ...
8 votes
2 answers
5k 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 ...
  • 183
7 votes
2 answers
4k 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-...
  • 1,153
6 votes
4 answers
7k 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 ...
  • 63
6 votes
3 answers
2k views

Finding contiguous ranges in grouped data

I have a table with the following structure: CREATE TABLE `Rings` ( ID_RingType CHAR(2), Number MEDIUMINT UNSIGNED, ID_User INT(11) ); and with data: INSERT INTO `Rings` VALUES ('AA',...
  • 324
6 votes
4 answers
673 views

Group data by non-unique keys by distinct time range

I have data in an Oracle table like the following: PROJ_NBR STATUS START_DT AL20 AC 1/14/2010 4:31 AL20 AC 1/14/2010 4:32 AL20 AC ...
  • 269
6 votes
2 answers
2k views

Updating rows conditionally with a gapless ascending value

A user has a table they would like to update. Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less. The rows ...
  • 23.7k
6 votes
2 answers
1k views

Performance tuning for gap analysis of table

I have a table which stores the sequence (counter) of data received from devices out in the field. At any rate, these sequences need to be in order within a configurable time span, but can come into ...
6 votes
1 answer
326 views

Selecting 'edge' records in a sequence of data

I have a table of items which run sequentially by date with both a start and end for each entry: ID | Start | End ---------------------------------------- 1 | 2012-08-20 00:00 | 2012-08-...
  • 317
6 votes
2 answers
8k views

Check if contiguous date interval exists

I have a table in PostgreSQL which describes some events that have a start date and an end date: CREATE TABLE my_table ( event_id serial NOT NULL, start_date timestamp without time zone NOT NULL,...
  • 829
6 votes
1 answer
595 views

Show current row "win streak"

I need to show the running win/loss streak per row in a query, so given the table below, the query should return the "expected" column. I've tried some approaches with window functions, but no success....
6 votes
2 answers
24k 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: | ...
6 votes
1 answer
934 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 ...
  • 195
6 votes
2 answers
616 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 ...
6 votes
1 answer
2k 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 ...
6 votes
1 answer
5k views

Grouping data based on cumulative sum

I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb. I am using ...
  • 63
5 votes
3 answers
5k views

How to group data within a time span

I have a group of rows sorted by date as below: 2017-01-01 2017-01-01 2017-01-02 2017-01-03 2017-01-04 2017-01-17 2017-01-18 2017-01-18 2017-01-18 2017-01-19 2017-01-19 2017-01-26 2017-01-27 2017-01-...
5 votes
1 answer
937 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 ...
5 votes
1 answer
262 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 ...
5 votes
2 answers
659 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 -> ( -> ...
  • 131
5 votes
1 answer
839 views

identify gaps and islands

I have a database that has the following structure +---------------------+------------+---------------+ | timestamp | EngOilP_sd | CompOilLVL_sd | +---------------------+------------+-------...
4 votes
2 answers
215 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 ...
  • 61
4 votes
4 answers
5k 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 ...
4 votes
2 answers
222 views

Grouping rows in ranges

Let's say I have the following (contrived) table of monitoring data: RowID DateStamp Success Error ----- ----------------- ------- ----------- 1001 5/...
  • 9,732
4 votes
4 answers
6k views

Continuous Date Ranges - Gaps and Islands

CREATE TABLE T1 (Asset_Id int, Trailer_Id int, AssignStart datetime, AssignEnd DATETIME) ; INSERT INTO T1 (Asset_Id, Trailer_Id, AssignStart, AssignEnd) VALUES (37124, 32607, '2018-04-19 ...
  • 783
4 votes
1 answer
67 views

Merging two tables by minimal interval values

I have two tables with two columns keeping tabs of categorical values, such as: Table 1 +----+-------+-----+-----------+ | ID | Begin | End | Condition | +----+-------+-----+-----------+ | 1 | 1 ...
4 votes
2 answers
2k views

Postgres lag() function has huge impact on query

As reported to my previous question (that for completeness is reported here), I've solved my problem using the window function LAG to get the time between the previous and next row, sum them and then ...
  • 181
4 votes
1 answer
240 views

Counting repeated occurences for a group of fields

I am trying to count the continuous repeated value for a field in order to obtain the maximum repeated occurrences for each person in the table. eg: Time person Result 08:12:36 ...
  • 68
4 votes
2 answers
161 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 ...
4 votes
3 answers
2k views

Find numbers not used in a column

How can I find in a column filled with numbers, the ones that are not used. Table_A: 'id' int(11) unsigned NOT NULL AUTO_INCREMENT, 'client_code' int(4) unsigned NOT NULL, 'client_name' ...
  • 153
3 votes
1 answer
2k views

Find which numbers in [1, 161] are not in the result set?

How to find which two number in [1, 161] is not in the result set? Can I find this two numbers using sql command? mysql> select blog_id from wp_blogs; ... | 149 | | 150 | | 151 | | ...
  • 159
3 votes
1 answer
5k views

Getting each status change in a table

I have a table that holds status changes per item. The item can go in and out of a status multiple times. ItemTable (ItemId int, Status varchar(50), DateOfChange date) I need to pull the date of ...
  • 631
3 votes
2 answers
669 views

Grouping categorical values in a sequence

I have a table with categorical values in a sequence, as such: CREATE TABLE cat (name, v1, v2) AS VALUES ('John', 1::int, 3::int), ('John', 3, 4), ('John', 4, 9), ('Mike', 9, 11), ...
3 votes
3 answers
70 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 ...
  • 169
3 votes
3 answers
5k views

Calculate number of consecutive years (not gapless)

I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this: Order_year, Contact 2015, 123 2014, 123 2013, 123 2011, 123 2010, 123 2015, 234 2014, ...
  • 31
3 votes
1 answer
3k views

Stored procedure to check for missing fields in a table

I have a stored procedure that accepts a string parameter, a comma separated list of required column names that the SP should check a particular table and make sure those columns have data in them... ...
  • 175
3 votes
1 answer
116 views

How do I aggregate related rows when there's no group key?

I'm using MariaDB 10.6 and have a table: CREATE TABLE velocities ( `state` int(8) NOT NULL, `timestamp` datetime NOT NULL, `velocity` decimal(5,2) NOT NULL, `name` varchar(15) NOT NULL ) ...
  • 133
3 votes
1 answer
764 views

Mysql get records more then 3 in interval of 1 minute, return group_concat ID

Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3. CREATE TABLE test ( `id` bigint NOT NULL AUTO_INCREMENT, created_date ...
  • 239