All Questions
Tagged with gaps-and-islands postgresql
61 questions
-1
votes
1
answer
135
views
How to sum values within blocks with Postgres?
Given this table called prices:
my_date
my_value
my_separator
2012-02-10
100
1
2012-02-11
120
1
2012-02-12
120
1
2012-02-13
110
1
2012-02-14
100
0
2012-02-15
115
0
2012-02-16
130
1
2012-02-17
...
- 1,203
0
votes
2
answers
1k
views
select row number increment on value change compared to previous return result
I'm experimenting with Postgresql queries a bit and am trying to figure out if it's possible to write a query that would increment a row number counter when returned value changes.
Example SOURCE (...
- 111
1
vote
1
answer
54
views
Identify if the sequence of visits to an area id was interrupted at any point
I have a PostgreSQL table df with the following information:
area_id
trip_nr
timestamp
a
3
29/06/2022 17:18:03
a
2
29/06/2022 17:18:02
a
1
29/06/2022 17:18:01
b
1
28/06/2022 19:21:03
c
3
28/06/...
- 35
0
votes
1
answer
155
views
Update row data with dynamic lag
I have a table of items and restaurants. We assume that if the restaurant entry is missing, it has to be completed with the last non-null restaurant value in the previous rows (as defined by ascending ...
- 101
1
vote
1
answer
272
views
SELECT / DELETE rows where consecutive null value count is greater than N
I have a table of the following structure, where all columns except the last are non-null:
ID
receiver
send_time
recv_time
1
A
00:00:00
00:00:01
2
A
00:00:01
NULL
3
A
00:00:02
NULL
4
A
00:00:03
...
- 13
1
vote
1
answer
33
views
Merging wifi session data if time between them is less than 15 minutes
I am trying to process network logs and join sessions together if the time between them is less than 15 minutes. The relevant fields are start time, end time, mac address, and wifi access point.
I am ...
- 13
0
votes
1
answer
2k
views
SQL (Postgres) Filling up missing rows with values from previous row and calculating on result set
I have got this dataset:
id | block_number | value | name
----+--------------+-------+------
2 | 47 | 100 | one
3 | 52 | 200 | one
4 | 58 | 120 | one
5 | ...
2
votes
1
answer
58
views
Identify unique trips for each user where walking is followed by trip start
I have a table trips with the following information
Row
User
Timestamp
Event
1
1
29/06/2022 17:18:03
Walking
2
1
29/06/2022 17:18:02
Walking
3
1
29/06/2022 17:18:01
Start
4
1
28/06/2022 19:21:03
...
- 35
2
votes
2
answers
618
views
How to remove only adjacent duplicates in an array?
While aggregating an array, I need to remove empty strings and then combine all adjacent identical values. E.g.:
["","product","product","","product",&...
- 125
2
votes
1
answer
1k
views
Cumulative running SUM for date ranges with gaps
I am looking for assistance to get a running sum of the difference between two dates that are on separate rows.
Table looks like:
client_id
name
autopay_status
contract_id
start_date
end_date
1
...
2
votes
1
answer
226
views
Aggregate query for pairs of arrays of events
db<>fiddle for all of the data and queries below
I have a table events with the following structure:
create table events (
correlation_id char(26) not null,
user_id bigint,
...
- 175
1
vote
1
answer
2k
views
Postgres find gaps between one date range and others
My question is similar to https://stackoverflow.com/a/41267928/2585154 , but in my case I need to divide/split date range by multiple date ranges, not only one.
In my case is also guaranteed that &...
- 143
1
vote
2
answers
999
views
How to select the latest continuous set of rows of a certain type?
(Using PostgreSQL) I'm looking to select the latest set of continuous (by date) rows that have a code 'A'. The latest row is always missing the "To Date" as it is assumed to be current. And ...
- 13
1
vote
1
answer
1k
views
Pick the first timestamp before a gap, but the last one of the day if there is no suitable gap
I have a TIMESTAMP column:
dates
2021-06-24 05:47:05
2021-06-24 09:47:05
2021-06-24 13:47:05
2021-06-24 17:47:05
I want to pick the first timestamp of a given day that is 3 hours or more before the ...
0
votes
1
answer
75
views
how to generate a sequence of values with differents formats in PostgreSQL
I want to find rows missing from a sequence of values in my table with differents formats.
For an example, look at this.
I want to find the missing lines via c_order_id and documentno. the one with '...
0
votes
2
answers
2k
views
How to find values missing from a sequence?
I want to find rows missing from a sequence of values in my table. For example, in this picture documentno 'YD4BC2006008' is missing.
I want to find the missing lines via c_order_id and documentno.
I ...
1
vote
1
answer
220
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 ...
- 13
4
votes
2
answers
3k
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 ...
- 191
-1
votes
1
answer
1k
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 |
|:----------------:|:-----------:|:------------:|:--:|
| ...
- 101
2
votes
2
answers
318
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,
...
0
votes
1
answer
195
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 ...
- 101
1
vote
2
answers
127
views
Query for condition over time
Say we have a table setup like this:
+---------+--------------+---------------+
| id text | time integer | value decimal |
+---------+--------------+---------------+
| 1 | 1 | ...
- 135
0
votes
1
answer
80
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 ...
- 3
6
votes
1
answer
916
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....
- 63
1
vote
1
answer
101
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 ...
- 113
2
votes
2
answers
446
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
1
answer
1k
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
...
- 352
1
vote
1
answer
1k
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
)...
- 145
0
votes
1
answer
351
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 ...
- 13
1
vote
1
answer
331
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 ...
- 1,274
6
votes
1
answer
6k
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
4
votes
1
answer
128
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 ...
- 175
1
vote
2
answers
206
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 ...
- 317
3
votes
2
answers
853
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),
...
- 175
3
votes
3
answers
73
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 ...
- 177
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,203
2
votes
2
answers
3k
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)
);...
- 259
5
votes
2
answers
168
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 ...
- 53
1
vote
2
answers
152
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 ...
- 1,949
3
votes
2
answers
2k
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 ...
- 64.7k
10
votes
1
answer
11k
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 ...
- 64.7k
1
vote
1
answer
2k
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....
- 11
20
votes
7
answers
6k
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)
...
- 303
6
votes
2
answers
28k
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:
| ...
- 163
-2
votes
1
answer
272
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 ...
- 129
6
votes
1
answer
1k
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 ...
- 205
1
vote
1
answer
72
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 ...
- 227
9
votes
2
answers
19k
views
Generate a series of dates for each group in a table
I have a balances table in PostgreSQL 9.3 that looks like this:
CREATE TABLE balances (
user_id INT
, balance INT
, as_of_date DATE
);
INSERT INTO balances (user_id, balance, as_of_date) VALUES
(...
- 341
2
votes
1
answer
870
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 ...
1
vote
1
answer
79
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 ...
