SQL リファレンス

SELECT * FROM table DURATION n MINUTE|HOUR|DAY [BEFORE n MINUTE|HOUR|DAY];

SELECT * FROM table WHERE column SEARCH 'keyword';

-- WITH ROLLUP で作成した TAG で実行
SELECT ROLLUP('hour', 1, time) AS rtime, AVG(value)
FROM tag_table
WHERE name = 'sensor-1'
GROUP BY rtime;

DELETE FROM table OLDEST n ROWS;
DELETE FROM table EXCEPT n ROWS|DAY;
DELETE FROM table BEFORE datetime;

-- 組み込みロールアップ付き Tag
CREATE TAG TABLE sensors (
    sensor_id VARCHAR(20) PRIMARY KEY,
    time DATETIME BASETIME,
    value DOUBLE SUMMARIZED
) WITH ROLLUP;

-- Log テーブル
CREATE TABLE logs (
    level VARCHAR(10),
    message VARCHAR(2000)
);

-- Volatile テーブル
CREATE VOLATILE TABLE cache (
    key VARCHAR(100) PRIMARY KEY,
    value VARCHAR(500)
);

-- Lookup テーブル
CREATE LOOKUP TABLE devices (
    device_id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);

-- VIEW
CREATE VIEW active_devices AS
SELECT device_id, name
FROM devices;

-- 最近のデータ
SELECT * FROM sensors
WHERE time BETWEEN now - 1h AND now;

-- 条件付き
SELECT * FROM logs WHERE level = 'ERROR' DURATION 1 DAY;

-- 集計
SELECT sensor_id, AVG(value) FROM sensors
WHERE time BETWEEN now - 1d AND now
GROUP BY sensor_id;

-- ロールアップ検索
SELECT ROLLUP('hour', 1, time) AS rtime, AVG(value)
FROM sensors
WHERE sensor_id = 'sensor-1'
  AND time BETWEEN now - 7d AND now
GROUP BY rtime;

-- ユーザーを作成
CREATE USER analyst IDENTIFIED BY 'password';

-- 権限を付与
GRANT SELECT ON sensors TO analyst;

-- パスワードを変更
ALTER USER analyst IDENTIFIED BY 'newpassword';