Skip to content

Instantly share code, notes, and snippets.

@msqr

msqr/year-partial-agg.sql

Created May 4, 2020 04:24
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save msqr/46188295ecc67832ea09f9a2129738ea to your computer and use it in GitHub Desktop.
Save msqr/46188295ecc67832ea09f9a2129738ea to your computer and use it in GitHub Desktop.
Download ZIP
Year partial aggregate query
Raw
year-partial-agg.sql
SELECT
datum.ts_start AS r_datum_ts_start
, datum.local_date AS r_datum_local_date
, datum.node_id AS r_datum_node_id
, datum.source_id AS r_datum_source_id
, datum.jdata AS r_datum_jdata
FROM (
(
SELECT
date_trunc('year', datum.ts_start::timestamp) AT TIME ZONE r.time_zone AS ts_start
, (date_trunc('year', datum.ts_start::timestamp) AT TIME ZONE r.time_zone)::date AS local_date
, datum.node_id
, datum.source_id
, solaragg.datum_agg_agg(jsonb_build_object( 'jdata', solaragg.jdata_from_datum(datum), 'jmeta', datum.jmeta) ORDER BY datum.ts_start) -> 'jdata' AS jdata
FROM solardatum.node_source_time_rounded(
?::bigint[]
, ?::text[]
, 'day'
, ?::timestamp
, ?::timestamp
) r
INNER JOIN solaragg.agg_datum_daily datum
ON datum.node_id = ANY(r.node_ids)
AND datum.source_id = ANY(r.source_ids)
AND datum.ts_start >= r.ts_start
AND datum.ts_start < r.ts_end
GROUP BY datum.node_id
, datum.source_id
, date_trunc('year', datum.ts_start::timestamp) AT TIME ZONE r.time_zone
) UNION ALL (
SELECT
date_trunc('year', datum.ts_start::timestamp) AT TIME ZONE r.time_zone AS ts_start
, (date_trunc('year', datum.ts_start::timestamp) AT TIME ZONE r.time_zone) ::date AS local_date
, datum.node_id
, datum.source_id
, solaragg.datum_agg_agg(jsonb_build_object( 'jdata', solaragg.jdata_from_datum(datum), 'jmeta', datum.jmeta) ORDER BY datum.ts_start) -> 'jdata' AS jdata
FROM solardatum.node_source_time_rounded(
?::bigint[]
, ?::text[]
, 'year'
, ?::timestamp
, ?::timestamp
) r
INNER JOIN solaragg.agg_datum_monthly datum
ON datum.node_id = ANY(r.node_ids)
AND datum.source_id = ANY(r.source_ids)
AND datum.ts_start >= r.ts_start
AND datum.ts_start < r.ts_end
GROUP BY datum.node_id
, datum.source_id
, date_trunc('year', datum.ts_start::timestamp) AT TIME ZONE r.time_zone
) UNION ALL (
SELECT
date_trunc('year', datum.ts_start::timestamp) AT TIME ZONE r.time_zone AS ts_start
, (date_trunc('year', datum.ts_start::timestamp) AT TIME ZONE r.time_zone)::date AS local_date
, datum.node_id
, datum.source_id
, solaragg.datum_agg_agg(jsonb_build_object( 'jdata', solaragg.jdata_from_datum(datum), 'jmeta', datum.jmeta) ORDER BY datum.ts_start) -> 'jdata' AS jdata
FROM solardatum.node_source_time_rounded(
?::bigint[]
, ?::text[]
, 'day'
, ?::timestamp
, ?::timestamp
) r
INNER JOIN solaragg.agg_datum_daily datum
ON datum.node_id = ANY(r.node_ids)
AND datum.source_id = ANY(r.source_ids)
AND datum.ts_start >= r.ts_start
AND datum.ts_start < r.ts_end
GROUP BY datum.node_id
, datum.source_id
, date_trunc('year', datum.ts_start::timestamp) AT TIME ZONE r.time_zone
)
) datum
ORDER BY r_datum_ts_start, r_datum_node_id, r_datum_source_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment