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.
109
questions
4
votes
2answers
73 views
Grouping rows in ranges
Let's say I have the following (contrived) table of monitoring data:
RowID DateStamp Success Error
----- ----------------- ------- -----------
1001 5/...
0
votes
1answer
101 views
Count Consecutive Codes
I'm searching for a query (cannot figure it out myself at the moment with the help of already found queries around here. The quest I have is the following one: I have a view that gives following ...
0
votes
2answers
38 views
Mysql consecutive days user wise
Table 'present'
+--------+------------+
| emp_id | pre_date |
+-----------+---------+
| 002 | 2019-04-01 |
| 001 | 2019-04-21 |
| 001 | 2019-04-15 |
| 001 | 2019-04-19 |
| 002 | 2019-...
0
votes
1answer
29 views
How to select blocks from a result by condition?
Example table:
master_id 1 created_at 22.02.1997
master_id 1 created_at 22.03.1997
master_id 1 created_at 22.04.1997
master_id 1 created_at 22.07.1997
master_id 1 created_at 22.08.1997
master_id 1 ...
5
votes
1answer
87 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....
3
votes
2answers
88 views
Merge Only Events that Start immediately after the Previous Event without any Gap
How can i merge only the dates occur subsequently.?
(i.e, The Next Event begins immediately after the previous event completed).
Here, we need to consider some other Columns also for getting the ...
-2
votes
1answer
52 views
SQL Server Database flatten records
Sorry for the confusion. I have below attached table sql server database table holding continuous and non continuous records. When i say continuous that means EP of first record if Beginning Point(BP)....
1
vote
1answer
65 views
Combining separate columns into smallest possible related ranges
I'm trying to discard multiple records that may or may not overlap based on the smallest possible contiguous ranges. I thought of doing something similar to This, however the ranges are numeric ...
0
votes
0answers
35 views
how to counting time in partition (MYSQL)
I looking for how to counting time by partition in condition if value=1 occurs consecutively 3 or more times count 1 activity.
Showing result on this picture
2
votes
2answers
60 views
How to select blocks with sequential data and aggregate the ids
I have the following table:
id(int) startDate(timestamp) endDate(timestamp) plan_id(int) planned(bool) machine(int)
--------------------------------------------------------------...
1
vote
2answers
506 views
Find the OverLapped (Common) Date Range from Group of Date Ranges
How can I find the Overlapped (common date range) from a group of Date Ranges given?
The following table contains events and programmes (EID and PID respectively)
CREATE TABLE #EventsTBL
(
PID ...
0
votes
1answer
153 views
How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL
I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows:
42, 1
...
0
votes
1answer
216 views
Consecutive dates count
I have table data like below:
CLIAM_KEY MBR_ID SERV_FROM
clm1 mem1 01-JAN-18
clm2 mem1 02-JAN-18
clm3 mem1 03-JAN-18
clm3 mem1 04-JAN-18
clm4 mem1 ...
1
vote
1answer
171 views
Find missing timestamps grouped by key: finding gaps in my data
I have a table that has a timestamp, some data and a identifying key for the data source:
create table test_data (
id serial primary key,
key text,
timestamp timestamp with time zone
)...
0
votes
1answer
59 views
Add running count of specific state changes
I have a table in PostgreSQL with below fields, where timestamp is simplified as integers:
ts status
1 m
2 m
3 i
4 s
5 s
6 i
7 i
8 m
9 s
10 m
I want to break ...
1
vote
0answers
54 views
Finding the largest island size (redshift)
Current sql code will sum up all the max island sizes into one. I'm trying to only find what the max island size is.
select deviceid, grp -- min(readdate) as mn, max(readdate) as mx
from ( ...
1
vote
1answer
122 views
How to get row_number() within a group?
I have a table event_seating_lookup with 3 columns: (id int, event_id int, lookup_is_successful boolean).
For every event_seating_lookup record, I want to get a number of previous (lag) records with ...
4
votes
3answers
1k 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 ...
5
votes
1answer
463 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 ...
-4
votes
2answers
1k views
Determine consecutive occurrences of values
I have a table as shown below:
CAR NAME INSERT DATE
MERCEDES 2018-01-01
SEAT 2018-01-01
MERCEDES 2018-01-02
BMW 2018-01-02
MERCEDES 2018-01-03
MERCEDES 2018-01-04
...
0
votes
1answer
108 views
Count days between dates and find largest gap
I need to find all members in our database from a calendar month that do not have a gap of 10+ days between sending data / or in the month total i.e. everyone in April 2018. The table is set up like ...
2
votes
4answers
2k 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 ...
0
votes
0answers
109 views
Find consecutive entries in a table and group into another table
So ideally I'd like a table output such as:
consecutive_days | max_date
for a given user
where consecutive days is the number of days a person logged something and max_date was the last date in ...
0
votes
1answer
298 views
How to group records by a two-value column ignoring small gaps?
I have a subquery with a column named code that only has 1 and 0 values and an ID column because I query a lot of codes for each ID. I want to group by said column (and by ID) and by ocurrence but ...
4
votes
1answer
47 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 ...
1
vote
1answer
111 views
sqlite - Distinguish a column with the same value by range of id and count this range, take the higher one
I have a small sqlite table
CREATE TABLE IF NOT EXISTS intersection (
cnt NUMERIC NOT NULL,
sweep NUMERIC)
from what i want to get the maximum value of "cnt" and later on the average of "...
0
votes
1answer
311 views
Finding gaps-and-islands with repeating group numbers
We have a MS-SQL dataset of tests run in various groupings and need to uniquely identify the groups
sr is the device identifier
tx is the TrackingIndex (insert order)
id is the loop order of each ...
1
vote
2answers
76 views
How can I assign group IDs depending on content?
I have the following table with data as shown below; linenrs are monotonically increasing but not necessarily consecutive; when the key field contains an ellipsis ... that indicates an entry that is ...
2
votes
2answers
62 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
3answers
57 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
2answers
458 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 ...
1
vote
3answers
436 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
84 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
3k 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
586 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
739 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
1k 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'),
('...
1
vote
2answers
261 views
Find termination and rehire dates
We have a time history table named PRTH that has EmployeeID, PostDate (this is the day worked), and hours. Our ERP system does not track termination and rehire date history so I have been asked to ...
5
votes
1answer
622 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
251 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
1k 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
...
3
votes
2answers
480 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):
+-------------------------+------+
| ...
2
votes
2answers
1k views
Window function to merge rows that meet a condition
I have the following schema
DROP TABLE IF EXISTS messages;
DROP TABLE IF EXISTS chats;
CREATE TABLE chats (
client integer NOT NULL,
provider integer NOT NULL,
PRIMARY KEY (client, provider)
);...
4
votes
2answers
91 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
97 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
2answers
107 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 ...
0
votes
1answer
866 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.
+------------+----------------+----------------+---------------+--------------+-----+
| ...
2
votes
2answers
549 views
In what case is a count(x or null) needed in Gaps and Islands?
In this answer, Erwin Brandstetter says:
count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
2
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
574 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....
