mirror of
https://gitee.com/pnoker/iot-dc3.git
synced 2026-11-02 11:11:57 +08:00
pnoker
72e315ea96
Join 1242 keyword-split statement lines (COMMENT/CREATE/ALTER/LOAD), move the dc3_identity_audit_log table section and the schema fingerprint INSERT so every script orders DDL before seed data, and drop one duplicated COMMENT ON COLUMN. Statement multiset verified identical apart from the intended dedup. The canonical fingerprint changes to e81e3562... and is synchronized via schema_fingerprint.py --sync into the embedded ddl_hash and all four distribution points; existing initialized databases require re-initialization (flag day).
162 lines
7.7 KiB
SQL
162 lines
7.7 KiB
SQL
/*
|
|
* Copyright 2016-present the IoT DC3 original author or authors.
|
|
*
|
|
* This program is free software: you can redistribute it and/or modify
|
|
* it under the terms of the GNU Affero General Public License as
|
|
* published by the Free Software Foundation, either version 3 of the
|
|
* License, or (at your option) any later version.
|
|
*
|
|
* This program is distributed in the hope that it will be useful,
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
* GNU Affero General Public License for more details.
|
|
*
|
|
* You should have received a copy of the GNU Affero General Public License
|
|
* along with this program. If not, see <https://www.gnu.org/licenses/>.
|
|
*/
|
|
|
|
-- Point-value observability layer: continuous aggregates over the
|
|
-- dc3_point_value hypertable, plus a device-metadata view for Grafana.
|
|
--
|
|
-- Design notes:
|
|
-- * Two-level hierarchical cagg: 1-minute raw rollup -> 1-hour rollup.
|
|
-- The 1h cagg is built ON TOP of the 1m cagg so long-range dashboard
|
|
-- queries (7d / 30d) re-aggregate already-summarized buckets instead
|
|
-- of scanning the full hypertable.
|
|
-- * Both caggs are realtime aggregates (materialized_only=false): recent
|
|
-- samples that fall in the not-yet-materialized tail are merged live,
|
|
-- so dashboards see fresh data without waiting for the refresh policy.
|
|
-- * Aggregation slices by (tenant_id, driver_id, device_id, point_id),
|
|
-- matching the hypertable's compress_segmentby. This is the natural
|
|
-- drill-down axis for "per driver / per device / per point" observation.
|
|
-- * num_value carries the best-effort numeric projection of cal_value;
|
|
-- AVG/MIN/MAX/SUM only touch rows where it is NOT NULL (mirrors the
|
|
-- raw-table partial index idx_point_value_num_time). first()/last()
|
|
-- retain cal_value for string-typed points so they remain observable.
|
|
-- * point and device relate via profile_id only (a profile groups many
|
|
-- devices AND many points); point_value rows already carry both ids,
|
|
-- so dashboards JOIN metadata by those ids rather than a static map.
|
|
|
|
SET search_path TO dc3_history, public;
|
|
|
|
-- ----------------------------
|
|
-- 1-minute continuous aggregate
|
|
-- ----------------------------
|
|
-- One row per (tenant, driver, device, point, minute). Powers the
|
|
-- per-point trend panel and the global ingest-rate panel.
|
|
CREATE MATERIALIZED VIEW cagg_point_value_1m
|
|
WITH (timescaledb.continuous = true) AS
|
|
SELECT time_bucket(INTERVAL '1 minute', create_time) AS bucket,
|
|
tenant_id,
|
|
driver_id,
|
|
device_id,
|
|
point_id,
|
|
count(*) AS sample_count,
|
|
count(num_value) AS num_count,
|
|
avg(num_value) AS num_avg,
|
|
min(num_value) AS num_min,
|
|
max(num_value) AS num_max,
|
|
sum(num_value) AS num_sum,
|
|
first(cal_value, create_time) AS cal_first,
|
|
last(cal_value, create_time) AS cal_last
|
|
FROM dc3_point_value
|
|
GROUP BY bucket, tenant_id, driver_id, device_id, point_id
|
|
WITH NO DATA;
|
|
|
|
-- Drill-down index mirrors the dashboard filter shape.
|
|
CREATE INDEX idx_cagg_pv_1m_lookup
|
|
ON cagg_point_value_1m (tenant_id, device_id, point_id, bucket DESC);
|
|
|
|
-- Realtime aggregate: also read the raw tail not yet materialized.
|
|
ALTER MATERIALIZED VIEW cagg_point_value_1m
|
|
SET(timescaledb.materialized_only = false);
|
|
|
|
-- ----------------------------
|
|
-- 1-hour continuous aggregate (hierarchical)
|
|
-- ----------------------------
|
|
-- Built on the 1m cagg, so a 30-day range scans ~720 hourly rows per point
|
|
-- instead of ~43k minute rows. One row per (tenant, driver, device, point, hour).
|
|
CREATE MATERIALIZED VIEW cagg_point_value_1h
|
|
WITH (timescaledb.continuous = true) AS
|
|
SELECT time_bucket(INTERVAL '1 hour', bucket) AS bucket,
|
|
tenant_id,
|
|
driver_id,
|
|
device_id,
|
|
point_id,
|
|
sum(sample_count) AS sample_count,
|
|
sum(num_count) AS num_count,
|
|
sum(num_sum) / NULLIF(sum(num_count), 0) ::DOUBLE PRECISION
|
|
AS num_avg,
|
|
min(num_min) AS num_min,
|
|
max(num_max) AS num_max,
|
|
sum(num_sum) AS num_sum,
|
|
first(cal_first, bucket) AS cal_first,
|
|
last(cal_last, bucket) AS cal_last
|
|
FROM cagg_point_value_1m
|
|
-- GROUP BY the first output expression explicitly. The source view also has a
|
|
-- column named bucket, so GROUP BY bucket would resolve to the minute bucket
|
|
-- instead of the hourly time_bucket expression and invalidate the hierarchy.
|
|
GROUP BY 1, tenant_id, driver_id, device_id, point_id
|
|
WITH NO DATA;
|
|
|
|
CREATE INDEX idx_cagg_pv_1h_lookup
|
|
ON cagg_point_value_1h (tenant_id, device_id, point_id, bucket DESC);
|
|
|
|
ALTER MATERIALIZED VIEW cagg_point_value_1h
|
|
SET(timescaledb.materialized_only = false);
|
|
|
|
-- TimescaleDB exposes continuous aggregates as views in the PostgreSQL catalog.
|
|
COMMENT ON VIEW cagg_point_value_1m IS 'One-minute point-value continuous aggregate';
|
|
COMMENT ON VIEW cagg_point_value_1h IS 'One-hour hierarchical point-value continuous aggregate';
|
|
|
|
-- ----------------------------
|
|
-- Refresh policies
|
|
-- ----------------------------
|
|
-- Background refresh of the materialized window. Realtime mode still
|
|
-- answers queries from the live tail; the policy only backfills durable
|
|
-- buckets. Raw samples are kept 30 days (05-iot-dc3-history.sql, S16 tiered
|
|
-- lifecycle); the refresh offsets predate that on purpose so rebuilding the
|
|
-- materialized tiers after a drop still folds whatever raw history exists.
|
|
SELECT add_continuous_aggregate_policy('cagg_point_value_1m',
|
|
start_offset => INTERVAL '7 days',
|
|
end_offset => INTERVAL '1 minute',
|
|
schedule_interval => INTERVAL '1 minute');
|
|
|
|
SELECT add_continuous_aggregate_policy('cagg_point_value_1h',
|
|
start_offset => INTERVAL '180 days',
|
|
end_offset => INTERVAL '5 minutes',
|
|
schedule_interval => INTERVAL '5 minutes');
|
|
|
|
-- S16 tiered lifecycle: the minute tier ages out after one year; the hour tier
|
|
-- is kept forever. The timescale adapter registers the same policy with
|
|
-- if_not_exists, so embedded and standalone deployments converge.
|
|
SELECT add_retention_policy('cagg_point_value_1m', INTERVAL '365 days');
|
|
|
|
-- ----------------------------
|
|
-- Device metadata view for Grafana
|
|
-- ----------------------------
|
|
-- Deterministic join (device.driver_id is a single value): attaches the
|
|
-- driver name to each device so dashboard device pickers show the owning
|
|
-- driver without an extra join. Point/profile names are joined ad-hoc in
|
|
-- dashboard queries via the point_id/profile_id on each value row.
|
|
-- enable_flag is inverted in this schema: 0 = enabled, 1 = disabled.
|
|
CREATE OR REPLACE VIEW v_device_metadata AS
|
|
SELECT d.id AS device_id,
|
|
d.tenant_id,
|
|
d.device_name,
|
|
d.device_code,
|
|
d.driver_id,
|
|
d.profile_id,
|
|
d.enable_flag,
|
|
drv.driver_name,
|
|
drv.driver_code,
|
|
drv.service_name AS driver_service
|
|
FROM dc3_manager.dc3_device d
|
|
LEFT JOIN dc3_manager.dc3_driver drv
|
|
ON drv.id = d.driver_id
|
|
AND drv.tenant_id = d.tenant_id
|
|
AND drv.deleted = 0
|
|
WHERE d.deleted = 0;
|
|
|
|
COMMENT ON VIEW v_device_metadata IS 'Tenant-scoped device and logical driver metadata for observability queries';
|