태그 통계

CREATE TAG TABLE device_metrics (
    name VARCHAR(80) PRIMARY KEY, -- Tag ID column
    time DATETIME BASETIME,        -- Timestamp column
    value DOUBLE SUMMARIZED        -- Value column with SUMMARIZED
)
TAG_STAT_ENABLE=1; -- Property (default, can be omitted)

-- Retrieve min/max time boundaries for a specific tag
SELECT min_time, max_time
FROM v$your_tag_table_stat -- Replace 'your_tag_table' with the actual table name
WHERE name = 'specific_tag_id';

-- Retrieve min/max time boundaries for multiple tags
SELECT name, min_time, max_time
FROM v$your_tag_table_stat
WHERE name IN ('tag_id_1', 'tag_id_2', 'tag_id_3');

-- Retrieve row count and min/max values for a specific tag (requires SUMMARIZED)
SELECT row_count, min_value, max_value
FROM v$your_tag_table_stat
WHERE name = 'specific_tag_id';

-- Retrieve all statistics for all tags
SELECT *
FROM v$your_tag_table_stat;

-- Retrieve the actual data record corresponding to the most recent entry for a tag
SELECT *
FROM your_tag_table
WHERE name = 'specific_tag_id'
  AND time = (SELECT recent_row_time
              FROM v$your_tag_table_stat
              WHERE name = 'specific_tag_id');

-- Retrieve the actual data record corresponding to the minimum value occurrence for a tag
SELECT *
FROM your_tag_table
WHERE name = 'specific_tag_id'
  AND time = (SELECT min_value_time
              FROM v$your_tag_table_stat
              WHERE name = 'specific_tag_id');

-- Drop existing table if necessary
-- DROP TABLE tag;

-- Create the TAG table, enabling statistics and marking 'value' for summary
CREATE TAG TABLE tag (
    name VARCHAR(80) PRIMARY KEY,
    time DATETIME BASETIME,
    value DOUBLE SUMMARIZED -- Ensure SUMMARIZED is present for value stats
)
tag_partition_count=1, -- Example property
TAG_STAT_ENABLE=1;    -- Explicitly set (though default=1)

# Example import command (adjust path and options as needed)
machbase-neo shell import --input /work/homes.csv --timeformat s TAG

-- Display the columns and types of the auto-generated statistics view
DESC v$tag_stat;

-- Efficiently get row counts using the statistics view
SELECT name, row_count
FROM v$tag_stat
ORDER BY name;

-- Compare with direct count on the base table (will be slower for large tables)
SELECT name, COUNT(*) AS direct_count
FROM tag
GROUP BY name
ORDER BY name;

-- Get the earliest and latest timestamps for specific tags
SELECT name, min_time, max_time
FROM v$tag_stat
WHERE name IN ('use', 'gen', 'temperature');

-- Get the full data record for the most recently added 'use' tag data
SELECT *
FROM tag
WHERE name = 'use'
  AND time = (SELECT recent_row_time FROM v$tag_stat WHERE name = 'use');

-- Get the full data record when the minimum 'temperature' was recorded
SELECT *
FROM tag
WHERE name = 'temperature'
  AND time = (SELECT min_value_time FROM v$tag_stat WHERE name = 'temperature');

-- Get the recorded value at the maximum time for the 'gen' tag
SELECT value
FROM tag
WHERE name = 'gen'
  AND time = (SELECT max_time FROM v$tag_stat WHERE name = 'gen');

-- Table 1: Full Statistics Enabled (Standard Case)
DROP TABLE IF EXISTS stat1;
CREATE TAG TABLE stat1 (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE SUMMARIZED) TAG_STAT_ENABLE=1;

-- Table 2: SUMMARIZED Keyword Omitted
DROP TABLE IF EXISTS stat2;
CREATE TAG TABLE stat2 (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE) TAG_STAT_ENABLE=1; -- No SUMMARIZED

-- Table 3: Statistics Disabled via Property
DROP TABLE IF EXISTS stat3;
CREATE TAG TABLE stat3 (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, value DOUBLE SUMMARIZED) TAG_STAT_ENABLE=0; -- Stats explicitly disabled

-- Insert Identical Sample Data into all three tables
INSERT INTO stat1 VALUES('tag-0', TO_DATE('2022-08-11'), 10);
INSERT INTO stat1 VALUES('tag-0', TO_DATE('2022-08-13'), 20);
INSERT INTO stat1 VALUES('tag-0', TO_DATE('2022-08-14'), 5);
INSERT INTO stat1 VALUES('tag-1', TO_DATE('2023-08-12'), 200);
INSERT INTO stat1 VALUES('tag-1', TO_DATE('2023-08-13'), 50);
INSERT INTO stat1 VALUES('tag-1', TO_DATE('2023-08-15'), 120);

INSERT INTO stat2 VALUES('tag-0', TO_DATE('2022-08-11'), 10);
INSERT INTO stat2 VALUES('tag-0', TO_DATE('2022-08-13'), 20);
INSERT INTO stat2 VALUES('tag-0', TO_DATE('2022-08-14'), 5);
INSERT INTO stat2 VALUES('tag-1', TO_DATE('2023-08-12'), 200);
INSERT INTO stat2 VALUES('tag-1', TO_DATE('2023-08-13'), 50);
INSERT INTO stat2 VALUES('tag-1', TO_DATE('2023-08-15'), 120);

INSERT INTO stat3 VALUES('tag-0', TO_DATE('2022-08-11'), 10);
INSERT INTO stat3 VALUES('tag-0', TO_DATE('2022-08-13'), 20);
INSERT INTO stat3 VALUES('tag-0', TO_DATE('2022-08-14'), 5);
INSERT INTO stat3 VALUES('tag-1', TO_DATE('2023-08-12'), 200);
INSERT INTO stat3 VALUES('tag-1', TO_DATE('2023-08-13'), 50);
INSERT INTO stat3 VALUES('tag-1', TO_DATE('2023-08-15'), 120);

-- Query Statistics for Table 1 (Full Stats)
SELECT * FROM v$stat1_stat;
-- Expected: All columns populated, including MIN/MAX_VALUE and their times.

-- Query Statistics for Table 2 (No SUMMARIZED)
SELECT * FROM v$stat2_stat;
-- Expected: MIN_VALUE, MIN_VALUE_TIME, MAX_VALUE, MAX_VALUE_TIME columns will be NULL.
--          ROW_COUNT, MIN_TIME, MAX_TIME, RECENT_ROW_TIME will be populated.

-- Query Statistics for Table 3 (TAG_STAT_ENABLE=0)
SELECT * FROM v$stat3_stat;
-- Expected: No rows returned (the view exists but holds no statistics).
--          Statistics collection is entirely disabled for this table.