롤업

CREATE TAG TABLE table_name (
    name_column datatype PRIMARY KEY,
    time_column DATETIME BASETIME,
    value_column numeric_datatype [SUMMARIZED]
    [, additional_columns...]
)
WITH ROLLUP [ ( SEC | MIN | HOUR ) ] [ EXTENSION ];

-- Create SEC, MIN, HOUR Rollups
CREATE TAG TABLE sensor_data (...) WITH ROLLUP;

-- Create MIN, HOUR Rollups
CREATE TAG TABLE hourly_stats (...) WITH ROLLUP (MIN);

-- Create HOUR Rollup only
CREATE TAG TABLE daily_summary (...) WITH ROLLUP (HOUR);

-- Create SEC, MIN, HOUR Rollups with FIRST/LAST support
CREATE TAG TABLE detailed_sensor_data (...) WITH ROLLUP EXTENSION;

CREATE ROLLUP rollup_name
ON source_table_or_rollup_name ( source_value_column )
INTERVAL interval_value ( SEC | MIN | HOUR )
[ EXTENSION ];

-- Create a 30-second Rollup based on the 'tag_data' table's 'value' column
CREATE ROLLUP _tag_data_rollup_30sec ON tag_data(value) INTERVAL 30 SEC;

-- Create a 10-minute Rollup based on the previously created 30-second Rollup
CREATE ROLLUP _tag_data_rollup_10min ON _tag_data_rollup_30sec INTERVAL 10 MIN;

-- Create a 15-minute Rollup with FIRST/LAST support
CREATE ROLLUP _tag_data_rollup_15min_ext ON tag_data(value) INTERVAL 15 MIN EXTENSION;

SELECT
    ROLLUP( time_unit, period, basetime_column [, origin ] ) AS rollup_time,
    AGGREGATE_FUNCTION( value_column ) AS aggregate_result
    [, other_aggregates... ]
FROM
    source_tag_table
WHERE
    [ time_range_predicate ]
    [ AND name_predicate ]
    [ AND other_predicates... ]
GROUP BY
    rollup_time -- Or GROUP BY ROLLUP(...) expression directly
ORDER BY
    rollup_time;

SELECT
    ROLLUP('hour', 1, time) AS rollup_time,
    MIN(value),
    MAX(value)
FROM tag_table
WHERE ...
GROUP BY rollup_time
ORDER BY rollup_time;

-- Hourly MIN and MAX values for TAG_00001 within a specific month
SELECT
    ROLLUP('hour', 1, time) as mtime,
    MIN(value),
    MAX(value)
FROM TAG
WHERE name = 'TAG_00001'
  AND time BETWEEN TO_DATE('2023-01-01 00:00:00') AND TO_DATE('2023-01-31 23:59:59')
GROUP BY mtime
ORDER BY mtime;

-- 15-minute average values, assuming a MIN or SEC level Rollup exists
SELECT
    ROLLUP('min', 15, time) AS rollup_interval,
    AVG(value)
FROM TAG
WHERE name = 'SENSOR_A'
GROUP BY rollup_interval
ORDER BY rollup_interval;

-- Daily FIRST and LAST values using Extension Rollup, aligning bins to Jan 1st, 2024
SELECT
    ROLLUP('day', 1, time, '2024-01-01') as day_interval,
    FIRST(time, value),
    LAST(time, value)
FROM TAG_WITH_EXTENSION
WHERE name = 'SENSOR_B'
GROUP BY day_interval
ORDER BY day_interval;

-- Weekly average, aligned to Mondays (assuming '2024-01-01' was a Monday)
SELECT
    ROLLUP('week', 1, time, '2024-01-01') AS week_start,
    AVG(value)
FROM TAG
WHERE name = 'SENSOR_C'
GROUP BY week_start
ORDER BY week_start;

-- Start the aggregation thread for a specific Rollup
EXEC ROLLUP_START('rollup_name');

-- Stop the aggregation thread for a specific Rollup
EXEC ROLLUP_STOP('rollup_name');

-- Force immediate aggregation processing for a specific Rollup, bypassing the normal interval wait time
EXEC ROLLUP_FORCE('rollup_name');

EXEC ROLLUP_START('_tag_data_rollup_30sec');
EXEC ROLLUP_STOP('_tag_data_rollup_10min');
EXEC ROLLUP_FORCE('_tag_rollup_hour'); -- Process pending data for the hourly rollup now

-- Delete all Rollup data for the specified table
DELETE FROM table_name ROLLUP;

-- Delete Rollup data before a specific timestamp for the specified table
DELETE FROM table_name ROLLUP BEFORE TO_DATE('YYYY-MM-DD HH24:MI:SS');

-- Delete all Rollup data for a specific tag within the table
DELETE FROM table_name ROLLUP WHERE name = 'specific_tag_id';

-- Delete Rollup data for a specific tag before a specific timestamp
DELETE FROM table_name ROLLUP WHERE name = 'specific_tag_id' AND time <= TO_DATE('YYYY-MM-DD HH24:MI:SS');

-- Remove all Rollup data associated with the 'TAG' table older than Jan 15, 2024
DELETE FROM TAG ROLLUP BEFORE TO_DATE('2024-01-15 00:00:00');

-- Remove all Rollup data for 'TAG01' from the 'TAG' table
DELETE FROM TAG ROLLUP WHERE name = 'TAG01';

-- Drop a specific Custom Rollup table
DROP ROLLUP rollup_name;

-- Drop a TAG table and all its dependent Rollup tables (Default and Custom)
DROP TABLE tag_table_name CASCADE;

-- Assuming _rollup_min depends on _rollup_sec
DROP ROLLUP _rollup_min;
DROP ROLLUP _rollup_sec;

-- Drop the 'sensor_data' TAG table and all associated Rollups
DROP TABLE sensor_data CASCADE;

SHOW ROLLUPGAP;

-- 1. Create a TAG table with default Rollups enabled
CREATE TAG TABLE iot_sensors (
    sensor_id VARCHAR(50) PRIMARY KEY,
    event_time DATETIME BASETIME,
    temperature DOUBLE SUMMARIZED -- SUMMARIZED required for Rollup on this column
)
WITH ROLLUP; -- Creates _iot_sensors_ROLLUP_SEC, _iot_sensors_ROLLUP_MIN, _iot_sensors_ROLLUP_HOUR

-- 2. Insert some sample data
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-03-10 10:05:15', 20.1);
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-03-10 10:15:30', 20.5);
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-03-10 10:55:00', 21.0);
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-03-10 11:05:00', 21.5);
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-03-10 11:35:45', 21.8);
INSERT INTO iot_sensors VALUES ('TEMP_B', '2024-03-10 10:10:00', 15.0);
INSERT INTO iot_sensors VALUES ('TEMP_B', '2024-03-10 11:10:00', 16.0);

-- Wait briefly for Rollup process or force it (optional)
-- EXEC ROLLUP_FORCE('_iot_sensors_ROLLUP_SEC');
-- EXEC ROLLUP_FORCE('_iot_sensors_ROLLUP_MIN');
-- EXEC ROLLUP_FORCE('_iot_sensors_ROLLUP_HOUR');

-- 3. Query the average hourly temperature for sensor TEMP_A
SELECT
    ROLLUP('hour', 1, event_time) AS hour_interval,
    AVG(temperature) AS avg_temp
FROM
    iot_sensors
WHERE
    sensor_id = 'TEMP_A'
    AND event_time BETWEEN TO_DATE('2024-03-10 10:00:00') AND TO_DATE('2024-03-10 12:00:00')
GROUP BY
    hour_interval
ORDER BY
    hour_interval;

/* Expected Approximate Output:
hour_interval                   avg_temp
---------------------------------------------------------------
2024-03-10 10:00:00 000:000:000 20.533...  -- Avg of 20.1, 20.5, 21.0
2024-03-10 11:00:00 000:000:000 21.65      -- Avg of 21.5, 21.8
*/

-- Prerequisite: Assume iot_sensors table exists from Example 1

-- 1. Create a custom 15-minute Rollup table based on the 'temperature' column
CREATE ROLLUP _iot_sensors_rollup_15min
ON iot_sensors (temperature)
INTERVAL 15 MIN;

-- Wait or force Rollup processing (optional)
-- EXEC ROLLUP_FORCE('_iot_sensors_rollup_15min');

-- 2. Query MIN and MAX temperature aggregated over 15-minute intervals for TEMP_A
SELECT
    ROLLUP('min', 15, event_time) AS interval_15min,
    MIN(temperature) AS min_temp,
    MAX(temperature) AS max_temp
FROM
    iot_sensors
WHERE
    sensor_id = 'TEMP_A'
    AND event_time BETWEEN TO_DATE('2024-03-10 10:00:00') AND TO_DATE('2024-03-10 12:00:00')
GROUP BY
    interval_15min
ORDER BY
    interval_15min;

/* Expected Approximate Output:
interval_15min                  min_temp    max_temp
---------------------------------------------------------------
2024-03-10 10:00:00 000:000:000 20.1        20.1        -- 10:00 to 10:14:59
2024-03-10 10:15:00 000:000:000 20.5        20.5        -- 10:15 to 10:29:59
2024-03-10 10:45:00 000:000:000 21.0        21.0        -- 10:45 to 10:59:59 (data at 10:55)
2024-03-10 11:00:00 000:000:000 21.5        21.5        -- 11:00 to 11:14:59
2024-03-10 11:30:00 000:000:000 21.8        21.8        -- 11:30 to 11:44:59
*/

-- 1. Create a TAG table with default Rollups and EXTENSION
DROP TABLE IF EXISTS iot_sensors_ext CASCADE; -- Clean up if exists
CREATE TAG TABLE iot_sensors_ext (
    sensor_id VARCHAR(50) PRIMARY KEY,
    event_time DATETIME BASETIME,
    pressure DOUBLE SUMMARIZED
)
WITH ROLLUP EXTENSION; -- Enable FIRST() and LAST()

-- 2. Insert sample data
INSERT INTO iot_sensors_ext VALUES ('PRES_1', '2024-03-10 09:01:00', 1000.1);
INSERT INTO iot_sensors_ext VALUES ('PRES_1', '2024-03-10 09:05:00', 1000.5); -- In 09:00 interval (the first value is 1000.1 at 09:01)
INSERT INTO iot_sensors_ext VALUES ('PRES_1', '2024-03-10 09:55:00', 1001.0); -- Last in 09:00 interval
INSERT INTO iot_sensors_ext VALUES ('PRES_1', '2024-03-10 10:02:00', 1001.2); -- First in 10:00 interval
INSERT INTO iot_sensors_ext VALUES ('PRES_1', '2024-03-10 10:08:00', 1001.5);
INSERT INTO iot_sensors_ext VALUES ('PRES_1', '2024-03-10 10:40:00', 1001.8); -- Last in 10:00 interval

-- Wait or force Rollup processing (optional)
-- EXEC ROLLUP_FORCE('_iot_sensors_ext_ROLLUP_SEC'); ... etc.

-- 3. Query the first and last pressure readings per hour for PRES_1
SELECT
    ROLLUP('hour', 1, event_time) AS hour_interval,
    FIRST(event_time, pressure) AS first_pressure,
    LAST(event_time, pressure) AS last_pressure
FROM
    iot_sensors_ext
WHERE
    sensor_id = 'PRES_1'
GROUP BY
    hour_interval
ORDER BY
    hour_interval;

/* Expected Approximate Output:
hour_interval                   first_pressure last_pressure
----------------------------------------------------------------------
2024-03-10 09:00:00 000:000:000 1000.1         1001.0
2024-03-10 10:00:00 000:000:000 1001.2         1001.8
*/

-- Assume 'iot_sensors' table has data for TEMP_A from 2024-03-01 to 2024-03-15

-- 1. Query Daily Average Temperature for TEMP_A
SELECT
    ROLLUP('day', 1, event_time) AS day_interval,
    AVG(temperature) AS avg_daily_temp
FROM
    iot_sensors
WHERE
    sensor_id = 'TEMP_A'
    AND event_time >= TO_DATE('2024-03-01') AND event_time < TO_DATE('2024-03-16')
GROUP BY
    day_interval
ORDER BY
    day_interval;

-- 2. Query Weekly Average Temperature for TEMP_A, aligning weeks starting on Monday ('2024-03-04')
SELECT
    ROLLUP('week', 1, event_time, '2024-03-04') AS week_start_monday, -- Specify origin for week alignment
    AVG(temperature) AS avg_weekly_temp
FROM
    iot_sensors
WHERE
    sensor_id = 'TEMP_A'
    AND event_time >= TO_DATE('2024-03-01') AND event_time < TO_DATE('2024-03-16')
GROUP BY
    week_start_monday
ORDER BY
    week_start_monday;

-- Assume the 'iot_sensors' table (from Example 1) has data spanning several months,
-- for example, from January 2024 to April 2024 for sensor 'TEMP_A'.
-- Ensure data exists for multiple months to see aggregation.
-- Example Data (add more if needed):
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-01-15 12:00:00', 18.0);
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-01-25 14:00:00', 18.5);
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-02-10 08:00:00', 19.0);
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-02-20 09:00:00', 19.2);
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-03-05 10:00:00', 19.5); -- Use data from previous examples too
INSERT INTO iot_sensors VALUES ('TEMP_A', '2024-03-20 11:00:00', 20.0);

-- 1. Query the Average Monthly Temperature for TEMP_A
-- The origin defaults to '1970-01-01', which works for standard calendar months.
SELECT
    ROLLUP('month', 1, event_time) AS month_interval, -- Aggregate data for each calendar month
    AVG(temperature) AS avg_monthly_temp,
    COUNT(temperature) AS data_points_per_month
FROM
    iot_sensors
WHERE
    sensor_id = 'TEMP_A'
    AND event_time >= TO_DATE('2024-01-01') AND event_time < TO_DATE('2024-04-01')
GROUP BY
    month_interval
ORDER BY
    month_interval;

/* Expected Approximate Output (values depend heavily on exact data):
month_interval                  avg_monthly_temp data_points_per_month
--------------------------------------------------------------------------
2024-01-01 00:00:00 000:000:000 18.25            2
2024-02-01 00:00:00 000:000:000 19.1             2
2024-03-01 00:00:00 000:000:000 20.628571...     7 -- (Including data from Example 1)
*/

-- 2. Query Quarterly (3-Month) SUM and COUNT for TEMP_A
-- Using period=3 with 'month' unit
SELECT
    ROLLUP('month', 3, event_time) AS quarter_interval, -- Aggregate data over 3-month periods
    SUM(temperature) AS sum_quarterly_temp,
    COUNT(temperature) AS data_points_per_quarter
FROM
    iot_sensors
WHERE
    sensor_id = 'TEMP_A'
    AND event_time >= TO_DATE('2024-01-01') AND event_time < TO_DATE('2024-04-01')
GROUP BY
    quarter_interval
ORDER BY
    quarter_interval;

/* Expected Approximate Output:
quarter_interval                sum_quarterly_temp data_points_per_quarter
----------------------------------------------------------------------------
2024-01-01 00:00:00 000:000:000 219.1              11 -- Sum/Count for Jan, Feb, Mar combined
*/

-- 3. Explicitly setting Origin (Optional, useful if non-standard month alignment needed)
-- Note: If setting origin for 'month', it MUST be the first day of some month.
SELECT
    ROLLUP('month', 1, event_time, '2024-01-01') AS month_interval, -- Origin explicitly set
    MIN(temperature) AS min_monthly_temp,
    MAX(temperature) AS max_monthly_temp
FROM
    iot_sensors
WHERE
    sensor_id = 'TEMP_A'
    AND event_time >= TO_DATE('2024-01-01') AND event_time < TO_DATE('2024-04-01')
GROUP BY
    month_interval
ORDER BY
    month_interval;

/* Expected Approximate Output:
month_interval                  min_monthly_temp max_monthly_temp
--------------------------------------------------------------------
2024-01-01 00:00:00 000:000:000 18.0             18.5
2024-02-01 00:00:00 000:000:000 19.0             19.2
2024-03-01 00:00:00 000:000:000 19.5             21.8 -- (Including data from Example 1)
*/

-- 1. Check the current Rollup gap status for all Rollups
SHOW ROLLUPGAP;

-- 2. Force immediate processing for a specific custom Rollup
-- EXEC ROLLUP_FORCE('_iot_sensors_rollup_15min');

-- 3. Delete Rollup data older than March 1st, 2024 from the iot_sensors table's Rollups
DELETE FROM iot_sensors ROLLUP BEFORE TO_DATE('2024-03-01 00:00:00');

-- 4. Drop the iot_sensors_ext table and all its associated Rollup tables
DROP TABLE iot_sensors_ext CASCADE;