gaps-and-islands Questions
6
I have a database of users. I would like to create a graph based on userbase growth. The query I have now is:
SELECT DATE(datecreated), count(*) AS number FROM users
WHERE DATE(datecreated) > ...
Furlough asked 26/6, 2009 at 0:35
2
Solved
I have data like this:
table1
_____________
id way time
1 1 00:01
2 1 00:02
3 2 00:03
4 2 00:04
5 2 00:05
6 3 00:06
7 3 00:07
8 1 00:08
9 1 00:09
I would like to know in which time interval I wa...
Mutualism asked 16/6, 2015 at 20:50
1
Solved
I was wondering whether the island and gaps problems can be solved in R efficiently, similar to SQL. I have the following data available, if we examine one ID:
ID StartDate StartTime EndDate EndTi...
Filomenafiloplume asked 3/6, 2015 at 20:19
5
Solved
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...
Trichloromethane asked 7/6, 2014 at 15:54
2
Solved
Thanks to Mike for the suggestion to add the create/insert statements.
create table test (
pid integer not null,
date date not null,
primary key (pid, date)
);
insert into test values
(1,'201...
Coakley asked 30/1, 2015 at 0:40
1
Solved
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 V...
Grandiloquence asked 26/1, 2015 at 14:52
1
Solved
I have got the set of data as follow
name date
x 2014-01-01
x 2014-01-02
y 2014-01-03
x 2014-01-04
and I'm trying to get this result
name date row_num
x 2014-01-01 1
x 2014-01-02 2
y 2014-01-0...
Agbogla asked 28/12, 2014 at 21:59
3
Solved
Not using MSSQL or DB2 or Oracle.
No CTE.
No OVERLAP predicate.
No INTERVAL data type.
The situation: on a vehicle to be repaired work can not start until
all parts ordered for the job have bee...
Gaultiero asked 28/8, 2011 at 23:45
1
Solved
I have NAME and PAY, but I need CHANGEGROUP in this example:
NAME PAY DATE CHANGEGROUP
Sally 12 10/01/2011 1
Sally 12 10/01/2011 1
Sally 12 11/02/2011 1
Sally 12 11/02/2011 1
Sally 12 12/01/2012 1...
Ekaterinodar asked 16/10, 2014 at 21:57
2
Solved
This is the SQL database data:
UserTable
UserName | UserDate | UserCode
-------------------------------------------
user1 | 08-31-2014 | 232
user1 | 09-01-2014 | 232
user1 | 09-02-2014 | 0
user1 ...
Evacuee asked 30/9, 2014 at 9:22
3
Solved
I have a table:
create table DB.t1 (id SERIAL,name varchar(255));
and insert some data:
insert into DB.t1 (name) values ('name1');
insert into DB.t1 (id,name) values (5,'name2');
insert into DB...
Cordeelia asked 14/3, 2014 at 14:12
3
Solved
I have a veh_speed table with the fields vid, date_time, speed, status. My objective is to get the duration(start_date_time and end_date_time) of the vehicle with speed greater than 30. Currently I...
Basting asked 15/1, 2014 at 10:10
4
Solved
Could anybody give me an idea or hint how you could check for X consecutive days in a database table (MySQL) where logins (user id, timestamp) are stored?
Stackoverflow does it (e.g. badges like E...
Tithe asked 18/7, 2012 at 10:13
2
Solved
Here's an sql query I am using to get count of records in a table separated by week (only date is stored in table). It works as expected.
SELECT count(id), CONCAT('Week ',WEEK(complaintRaisedDate...
Guillermo asked 19/10, 2013 at 19:5
3
Solved
I have been working on the below but getting no results and the deadline is fast approaching. Also, there are over a million rows as the below. Appreciate your help on the below.
Objective: Group ...
Grapple asked 29/8, 2012 at 12:27
6
Solved
I manage a message based system in which a sequence of unique integer ids will be entirely represented at the end of the day, though they will not necessarily arrive in order.
I am looking for hel...
Elgar asked 3/12, 2011 at 0:5
1
Solved
I have a table with the following definition
CREATE TABLE mytable
(
id INT IDENTITY(1, 1) PRIMARY KEY,
number BIGINT,
status INT
)
and example data
INSERT INTO mytable
VALUES (100,0),
(10...
Hemipterous asked 11/6, 2013 at 14:8
3
Solved
There are a number of researchers observing the world's last remaining unicorns, on Easter Island1. Each day the researchers record which unicorn they sighted, the date of the sighting, the number ...
Etherege asked 4/10, 2012 at 9:1
2
Solved
(PostgreSQL 8.4)
Table "trackingMessages" stores tracking events between mobile devices (tm_nl_mobileid) and fixed devices (tm_nl_fixedId).
CREATE TABLE trackingMessages
(
tm_id SERIAL PRIMARY K...
Lousy asked 16/9, 2012 at 23:12
1
Solved
(PostgreSQL 8.4) I got a great introduction to SQL gaps-and-islands here on Stack Overflow but I still have a question. Many island detection CTEs are based on a running order of a timestamp and so...
Waves asked 17/9, 2012 at 21:46
2
Solved
I have been struggling with a problem that should be pretty simple actually but after a full week of reading, googling, experimenting and so on, my colleague and we cannot find the proper solution....
Wingard asked 16/8, 2012 at 9:4
2
Solved
Im trying to write a function which identifies groups of dates, and measures the size of the group.
I've been doing this procedurally in Python until now but I'd like to move it into SQL.
for exa...
Vesperal asked 10/7, 2012 at 14:29
5
Solved
You have a table table1 that contains id column, that is int(11), not null, auto_increment and starts from 1.
Suppose, you have 10,000 records. It is clear the id of the last record is 10,000.
On...
Sherrylsherurd asked 19/6, 2012 at 14:21
1
Solved
I have the following table which contains values read every 15 minutes from several different devices:
ID DeviceID Date Value
----------------------------------------------
1 3 24.08.2011 00:00:00...
Insistent asked 6/6, 2012 at 6:13
5
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,....
Clapper asked 17/4, 2012 at 10:15
© 2022 - 2024 — McMap. All rights reserved.