All Questions
Tagged with gaps-and-islands group-by
16 questions
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
0
votes
1
answer
370
views
Possible to GROUP BY across different columns in mariadb/mysql
I have a table full of events similar to this:
`date` | start_time | end_time | length
--------------------------------------------
2022-01-10 | 07:00:00 | 16:00:00 | 9
2022-01-11 | 07:00:...
3
votes
1
answer
163
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
2k
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/...
- 31
2
votes
1
answer
33
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 ...
- 133
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
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
2
answers
610
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 ...
- 2,837
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
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
6
votes
2
answers
695
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 ...
- 117
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',...
- 334
2
votes
1
answer
1k
views
Group by maximum consecutive row
I have data like the following:
id | src_ip | dst_ip | port |
----|----------------|--------------|------|
256 | 192.168.61.200 | 10.75.64.222 | 80 |
257 | 192.168.61.200 | 10.75.64....
- 43
21
votes
6
answers
34k
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 ...
- 817
6
votes
4
answers
677
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