Skip to content

Instantly share code, notes, and snippets.

@javier
Last active June 14, 2022 16:46
Show Gist options
  • Save javier/7883f0ebd57b539f88948938a90f869f to your computer and use it in GitHub Desktop.
Save javier/7883f0ebd57b539f88948938a90f869f to your computer and use it in GitHub Desktop.
Questdb basic demo
CREATE TABLE sensors (ID LONG, make STRING, city STRING);
INSERT INTO sensors
SELECT
x ID, --increasing integer
rnd_str('Eberle', 'Honeywell', 'Omron', 'United Automation', 'RS Pro') make,
rnd_str('New York', 'Miami', 'Boston', 'Chicago', 'San Francisco') city
FROM long_sequence(10000) x
;
CREATE TABLE readings
AS(
SELECT
x ID,
timestamp_sequence(to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'), rnd_long(1,10,0) * 100000L) ts,
rnd_double(0)*8 + 15 temp,
rnd_long(0, 10000, 0) sensorId
FROM long_sequence(10000000) x)
TIMESTAMP(ts)
PARTITION BY MONTH;
SELECT count() FROM readings;
SELECT avg(temp) FROM readings;
SELECT *
FROM readings
JOIN(
SELECT ID sensId, make, city
FROM sensors)
ON readings.sensorId = sensId;
SELECT city, max(temp)
FROM readings
JOIN(
SELECT ID sensId, city
FROM sensors) a
ON readings.sensorId = a.sensId;
SELECT ts, city, make, avg(temp)
FROM readings timestamp(ts)
JOIN
(SELECT ID sensId, city, make
FROM sensors
WHERE city='Miami' AND make='Omron') a
ON readings.sensorId = a.sensId
WHERE ts IN '2019-10-21;1d' -- this is an interval between 21-10 and 1 day later
DROP TABLE readings;
DROP TABLE sensors;
docker run -p 9000:9000 \
-p 9009:9009 \
-p 8812:8812 \
-p 9003:9003 \
questdb/questdb
# for other methods go to https://questdb.io/docs/get-started/binaries
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment