SQL dinking about

[abkfenris]

A rough and dirty way to get rate of change in addition to the current level of a gage's first sensor. Could use a similar method for correlations and to label them.

SELECT g.name, g.slug, s.name, s.suffix, sa.datetime, sa.value,
    CASE
        WHEN sa.value < sal.value AND (sa.value/sal.value) < 0.95 THEN 'falling'
        WHEN sa.value > sal.value AND (sa.value/sal.value) > 1.05 THEN 'climbing'
        ELSE 'steady'
    END
    AS change
FROM gages g
, LATERAL (
    SELECT *
    FROM sensors
    WHERE gage_id = g.id
    ORDER BY sensors.id
    LIMIT 1
) s
, LATERAL (
    SELECT *
    FROM samples
    WHERE sensor_id = s.id
    ORDER BY samples.datetime DESC
    LIMIT 1
) sa
, LATERAL (
    SELECT *
    FROM samples
    WHERE sensor_id = s.id
    ORDER BY samples.datetime DESC
    LIMIT 1 OFFSET 1
) sal
ORDER BY sa.datetime

Gage name	slug	Sensor name	suffix	datetime	value
Bull Branch at Twin Bridges	me-bull-branch			2015-09-14 20:46:55	91	steady
Cheticamp River above Robert Brook	ns-cheticamp	Level	m	2015-10-09 02:00:00	0.918	steady
Moose River at McKeever NY	ny-moose-river-mckeever	Gage Height	ft	2015-10-09 05:30:00	2.15	steady
Black River at Watertown	ny-black-river-watertown	Level	ft	2015-10-09 05:30:00	1580	steady
Diamond River near Wentworth Location	diamond-river-nh-wentworth		ft	2015-10-09 06:15:00	76	steady
Wild River at Rt 2	me-wild-river	Height	ft	2015-10-09 06:45:00	2.42	steady
Saco River at Bartlett	nh-saco-bartlett	Discharge	cfs	2015-10-09 06:45:00	62	steady
Richardson Lake	richardson-lake-me	FT	ft	2015-10-09 07:15:00.514057	1445.12	steady
Neilson River	qc-neilson	Discharge	cms	2015-10-09 07:15:00.668934	0.54	steady
Rapid River at Middle Dam	rapid-river-me-middle-dam	CFS	cfs	2015-10-09 07:15:00.878683	500	steady
Kennebec River at Harris Station	kennebec-river-harris-station	CFS	cfs	2015-10-09 07:15:01.032526	373	climbing