Stack Overflow
Stack Overflow

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.

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.

1372 questions
9
votes
5 answers

MySQL: group by consecutive days and count groups

I have a database table which holds each user's checkins in cities. I need to know how many days a user has been in a city, and then, how many visits a user has made to a city (a visit consists of consecutive days spent in a city). So, consider I…
linkyndy
  • 17,038
  • 20
  • 114
  • 194
9
votes
7 answers

Get list with start and end values from table of datetimes

Currently i have a table built up like this way DeviceID Timestamp Value ---------------------------------------- Device1 1.1.2011 10:00:00 3 Device1 1.1.2011 10:00:01 4 Device1 1.1.2011 10:00:02 4 Device1 …
Oliver
  • 43,366
  • 8
  • 94
  • 151
9
votes
2 answers

SQL query for run-length, or consecutive identical value encoding

My goal is to take a set of data that is ordered by id and return a resultset that indicates the number of consecutive rows where the val column is identical. E.g. given this data: | id | val | | 1 | 33 | | 2 | 33 | | 3 | 44 | | 4 | 28 | | …
Terrence Brannon
  • 4,760
  • 7
  • 42
  • 61
9
votes
5 answers

PostgreSQL - column value changed - select query optimization

Say we have a table: CREATE TABLE p ( id serial NOT NULL, val boolean NOT NULL, PRIMARY KEY (id) ); Populated with some rows: insert into p (val) values (true),(false),(false),(true),(true),(true),(false); ID VAL 1 1 2 0 3 0 4 …
Nailgun
  • 3,999
  • 4
  • 31
  • 46
9
votes
3 answers

Method of finding gaps in time series data in MySQL?

Lets say we have a database table with two columns, entry_time and value. entry_time is timestamp while value can be any other datatype. The records are relatively consistent, entered in roughly x minute intervals. For many x's of time, however, an…
TheDog
  • 355
  • 3
  • 12
8
votes
2 answers

Need help finding the correct T-SQL Query

I am not quite sure how to go about doing this. Basically I have have a table like this UserId DateRequested Approved ApprovedBy Notes ------------ ----------------------- -------- ----------- ----- 1 2011-05-26 …
Umair
  • 3,063
  • 1
  • 29
  • 50
8
votes
1 answer

Min/Max Date Values over Large Date Range depending on Value

I'm querying a snapshot of customer data that contains the snapshot date, the customer ID and the 'value' of that customer on that day. I use the LAG function to return the previous days value to know if there is a drop/rise/complete loss/complete…
Jeff Scott
  • 105
  • 5
8
votes
5 answers

Insert Dates in the return from a query where there is none

We are building a query to count the number of events per hour, per day. Most days there are hours that do not have any activity and therefore where the query is run the count of activities per hour show up but there are gaps and the query excludes…
Fred Gordy
8
votes
3 answers

Can window function LAG reference the column which value is being calculated?

I need to calculate value of some column X based on some other columns of the current record and the value of X for the previous record (using some partition and order). Basically I need to implement query in the form SELECT ,
Roman-Stop RU aggression in UA
  • 14,905
  • 3
  • 48
  • 53
8
votes
1 answer

Make PostgreSQL choose the next minimal available id

I would like to make PostgreSQL choose the first next available id so that no error occurs in the following case: CREATE TABLE test( id serial PRIMARY KEY, name varchar ); Then: INSERT INTO test VALUES (2,'dd'); INSERT INTO test (name) VALUES…
user4495098
  • 93
  • 1
  • 4
8
votes
5 answers

how find "holes" in auto_increment column?

when I DELETE, as example, the id 3, I have this: id | name 1 | 2 | 4 | 5 | ... now, I want to search for the missing id(s), because i want to fill the id again with: INSERT INTO xx (id,...) VALUES (3,...) is there a way to search for…
skyline26
  • 1,994
  • 4
  • 23
  • 35
7
votes
3 answers

SQL to select consecutive records with the same value

I have a table with measurements. Measurement is done every minute. I need to select only rows having the same sample_value more than once consecutively for the same device_id. Here are initial data: sample_date sample_time device_id …
Tax Max
  • 83
  • 1
  • 1
  • 6
7
votes
4 answers

Postgres windowing (determine contiguous days)

Using Postgres 9.3, I'm trying to count the number of contiguous days of a certain weather type. If we assume we have a regular time series and weather…
Kip
  • 97
  • 6
7
votes
2 answers

Finding gaps (missing records) in database records using SQL

I have a table with records for every consecutive hour. Each hour has some value. I want a T-SQL query to retrieve the missing records (missing hours, the gaps). So for the DDL below, I should get a record for missing hour 04/01/2010 02:00 AM…
Tony_Henrich
  • 42,411
  • 75
  • 239
  • 374
7
votes
4 answers

How to fill timestamp gaps in a Postgres query?

Given the following table: CREATE TABLE channel1m ( ts TIMESTAMP WITHOUT TIME ZONE NOT NULL, itemId BIGINT, value BIGINT ) in which a row may be inserted each minute, per itemId, as follows: ts itemId …
luisfarzati
  • 8,649
  • 6
  • 29
  • 27
1 2
3
…
91 92