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.
132
questions
3
votes
1answer
42 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 ...
2
votes
1answer
36 views
How to collapse rows with individual, contiguous dates into single rows with date range start and end
Given a data set like this:
Id
Date
Value
1
01/01/2021
100
1
01/02/2021
100
1
01/03/2021
100
...
... (rows omitted for brevity)
...
1
12/29/2021
100
1
12/30/2021
100
1
12/31/2021
100
2
01/01/...
1
vote
1answer
47 views
Query to find if there are more than X occurences within any period of given length minutes
I have spent many hours thinking about a solution for my problem but I give up.
Let's imagine a table
user_id | occurred_at
-- OK match example
1 | 2020-01-01 08:00:00 <- First match of ...
2
votes
2answers
67 views
Gaps and Islands
I have a table with StartDate and EndDate. I wanted to get the missing Date and The script I have does it. But if given this data:
LocationID StartDate EndDate
---------- ---------- ----------
...
1
vote
2answers
77 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 ...
0
votes
1answer
58 views
Calculate difference of days between dates in different rows, based on consecutive value of a column, for each customer_id
I have a table like this one, that shows payments from each client, for different dates:
| stream_datetime | customer_id | order_status | rn |
|:----------------:|:-----------:|:------------:|:--:|
| ...
2
votes
2answers
74 views
Group by time interval and output the source and destination station_id and count
I am stuck with a query:
CREATE TABLE public.bulk_sample (
serial_number character varying(255),
validation_date timestamp, -- timestamp of entry and exit
station_id integer,
...
1
vote
1answer
65 views
SQL Server - Get the Date Ranges
I have a table with start date and end date. For example,
PlaceID | StartDate | EndDate
1 | 2020-01-01 | 2020-01-04
1 | 2020-01-10 | 2020-01-15
2 | 2020-01-03 | 2020-01-06
2 | ...
0
votes
1answer
76 views
How to collapse contiguous single day start/end date ranges in MySQL
(edited - I oversimplified the original problem)
I'm trying to figure out a MySQL query that will collapse one-day long contiguous start/end date ranges. Here is my sample input data:
INSERT INTO ...
0
votes
1answer
47 views
How do I find the first row of the longest occurrences of repeating numbers?
I have a table with the following structure:
id,center,shelf_id,occupied,remaining
1,East,1_1_1,1,0
2,East,1_1_2,1,0
3,East,1_1_3,1,4
4,East,1_1_4,1,0
5,East,1_1_5,1,0
6,East,1_2_1,1,3
7,East,1_2_2,1,...
0
votes
1answer
50 views
Gaps and Islands Across Fields With Start and End Timestamps
I am working with GPS data which I want to group by locations (to six decimal places of the coordinate value) and generate from and until times for.
For simplicity in this example I'm making the ...
0
votes
1answer
31 views
Need to identify non contigious rows
My table structure is as follows:
create table daily_summary
(
id uuid default uuid_generate_v4() not null
primary key,
machine bigint ...
0
votes
1answer
42 views
MSSQL - convert list of datetimes in a date range
I have one system which stores data like:
CREATE TABLE Reservations
([UserId] int, [RoomId] int, [TypeId] int, [Date] datetime)
;
Every row in the table means that Room is reserved for User of ...
0
votes
1answer
58 views
Gap Finding - Dates
Essentially i face a islands & gaps problem but unlike many of the examples i found i do not have a unique ID to group over to find my gaps.
Data is timestamp without timezone formatted and an ...
1
vote
1answer
33 views
Merging Continous Date Ranges with upto 63 days in between
I am trying to write a query that will take multiple date ranges and combine them into a contiguous date range. The catch is that I have to allow for up to 63 days in between the last termination date ...
1
vote
1answer
80 views
Determine Order Streak
For a particular customer, I have data returned by a query that looks like this:
OrderDate OrderCount
2020-01-12 0
2020-01-13 17
2020-01-14 22
2020-01-15 21
2020-01-16 30
2020-01-17 12
2020-...
1
vote
2answers
82 views
Query for condition over time
Say we have a table setup like this:
+---------+--------------+---------------+
| id text | time integer | value decimal |
+---------+--------------+---------------+
| 1 | 1 | ...
2
votes
1answer
67 views
Gaps and islands with GPS breadcrumb data
I've got a gaps and islands (GAI) problem that I can't seem to crack to save my life. Most of the GAI problems I've seen involve data in the form of:
[Id] [StartDateTime] [EndDateTime]
But mine in ...
0
votes
1answer
45 views
GROUP BY with date ranges when values change but then change back
Consider a historical table where a value was changed and then changed back:
value | date
3 | 1/1/2019
3 | 1/2/2019
3 | 1/3/2019
4 | 1/4/2019
4 | 1/5/2019
3 | 1/6/2019
3 | ...
2
votes
1answer
31 views
Add Gameweek Fixeture based on Datefield
having the following table i need to add a new Column called "Round" to represent Round 1, Round 2 and so on, based on the Date Column in the table. Same Round should be given to matches happening in ...
-4
votes
1answer
46 views
How to get the users who viewed a video on 95%?
There is a table with viewed chunks.
video_id | user_id | start_second | end_second
1 | 1 | 0 | 10
1 | 1 | 0 | 20
1 | 2 | 1 ...
2
votes
0answers
80 views
How to perform a running count on null values that resets on non-null values
I am trying to calculate a column that performs a running count of consecutive null values, but the running count will reset upon non-null values. Currently I am trying to do it with a window function
...
4
votes
2answers
104 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
126 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
309 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
39 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
255 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
172 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
96 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
70 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
45 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
76 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)
--------------------------------------------------------------...
2
votes
2answers
3k 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 ...
1
vote
1answer
432 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
612 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
414 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
93 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
66 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
184 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 ...
6
votes
3answers
4k 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 ...
6
votes
1answer
2k 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 ...
-1
votes
3answers
8k 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
147 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 ...
3
votes
4answers
4k 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
121 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
775 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
166 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
471 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
89 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 ...
