Home

quackpipe

Fun Queries

CSV loose parsing for Influx/FLUX

SELECT strptime(_time,'%Y-%m-%dT%H:%M:%S%Z') as "Date", location, CAST(_value as FLOAT) as _value 
FROM read_csv_auto('https://influx-testdata.s3.amazonaws.com/noaa.csv', header=True, skip=3, ignore_errors=True)
WHERE _measurement == 'average_temperature'
LIMIT 100

CSV Window Function w/ remote data

SELECT strptime(_time,'%Y-%m-%dT%H:%M:%S%Z') as "Date", location, CAST(_value as FLOAT) as _value, 
    AVG(CAST(_value AS FLOAT)) OVER (
        PARTITION BY "location"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 6 HOURS PRECEDING
                  AND INTERVAL 6 HOURS FOLLOWING)
        AS "Moving Average"
FROM read_csv_auto('https://influx-testdata.s3.amazonaws.com/noaa.csv', header=True, skip=3, ignore_errors=True)
WHERE _measurement == 'average_temperature'
ORDER BY 1, 2
LIMIT 10