Une requête de sous-échantillonnage est une requête d'agrégation temporelle. Elle est couramment utilisée dans les applications de séries chronologiques pour réduire la fréquence d'échantillonnage des données.
Moteurs et versions
Les requêtes de sous-échantillonnage sont prises en charge uniquement par LindormTSDB 3.4.15 et les versions ultérieures.
Pour plus d'informations sur la consultation et la mise à niveau de la version LindormTSDB de l'instance Lindorm, consultez les Notes de version de LindormTSDB et la rubrique Mettre à niveau la version mineure du moteur d'une instance Lindorm.
Syntaxe
select_sample_by_statement ::= SELECT ( select_clause | '*' )
FROM table_identifier
WHERE where_clause
SAMPLE BY time_interval [ OFFSET offset_interval ] [ FILL fill_option ]
select_clause ::= selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector ::= tag_identifier | time | function_identifier '(' field_identifier [ ',' function_args ] ')'
where_clause ::= relation ( AND relation )*
relation ::= ( field_identifier | tag_identifier ) operator term
operator ::= '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | CONTAINS | CONTAINS KEY
time_interval ::= interval units | 0
offset_interval ::= interval unitsFonctions d'agrégation prises en charge
La clause SAMPLE BY sous-échantillonne chaque série chronologique individuelle. Pour plus d'informations sur les séries chronologiques, consultez le modèle de données.
La clause SAMPLE BY prend en charge les fonctions d'agrégation suivantes :
|
Fonction |
Description |
|
Calcule la somme des valeurs dans chaque fenêtre temporelle spécifiée. |
|
|
Calcule la moyenne des valeurs dans chaque fenêtre temporelle spécifiée. |
|
|
Compte le nombre de valeurs dans chaque fenêtre temporelle spécifiée. |
|
|
Renvoie la valeur minimale dans chaque fenêtre temporelle spécifiée. |
|
|
Renvoie la valeur maximale dans chaque fenêtre temporelle spécifiée. |
|
|
Renvoie la première valeur dans chaque fenêtre temporelle spécifiée. |
|
|
Renvoie la dernière valeur dans chaque fenêtre temporelle spécifiée. |
|
|
Calcule le centile des valeurs dans chaque fenêtre temporelle spécifiée. |
|
|
Renvoie la valeur la plus récente dans toute la plage temporelle. |
|
|
Calcule le taux de variation entre la valeur actuelle et la valeur de la ligne précédente. |
|
|
Calcule la différence entre la valeur actuelle et la valeur de la ligne précédente. |
Les requêtes de sous-échantillonnage ne permettent le filtrage que par les colonnes de tags, et non par les colonnes de champs. Les filtres appliqués à une colonne de champ sont ignorés et peuvent entraîner des résultats inattendus. Pour filtrer sur une colonne de champ, utilisez une sous-requête afin d'appliquer le filtre aux résultats de la requête de sous-échantillonnage.
Prenons l'exemple de la fonction latest(field, n), où temperature est une colonne de champ :
SELECT device_id,region,time,temperature FROM (SELECT device_id,region,time,latest(temperature, 2) AS temperature FROM sensor WHERE device_id in ('F07A1260', 'F07A1261') SAMPLE BY 0) WHERE temperature > 45.0;Exemples
Dans une requête SELECT, vous devez spécifier une fonction d'agrégation pour chaque colonne de champ, mais pas pour les colonnes de tags.
Les exemples suivants utilisent une table d'exemple nommée sensor avec le schéma et les données suivants :
-- Create the sample table sensor.
CREATE TABLE sensor (
device_id VARCHAR NOT NULL,
region VARCHAR NOT NULL,
time TIMESTAMP NOT NULL,
temperature DOUBLE,
humidity BIGINT,
PRIMARY KEY(device_id, region, time)
);
-- Insert data into the table.
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 09:00:00',0,9);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 12:01:00',1,45);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 14:03:00',2,46);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 20:00:00',10,47);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-02-10 12:00:30',3,40);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-03-01 12:01:00',4,41);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-03-08 12:08:00',5,42);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-05-01 13:00:00',6,43);Exemples de sous-échantillonnage et de sous-requêtes
Vous ne pouvez pas imbriquer une sous-requête dans une requête de sous-échantillonnage, mais vous pouvez utiliser une requête de sous-échantillonnage comme sous-requête.
-
Exemple 1 : Effectuez un sous-échantillonnage aligné sur UTC par défaut. Cette requête agrège chaque série chronologique en fenêtres temporelles de 8 heures et calcule le nombre pour chaque fenêtre.
SELECT device_id,region,time,count(humidity) AS count_humidity FROM sensor WHERE device_id='F07A1260' sample by 8h;Le résultat suivant est renvoyé :
+-----------+----------+---------------------------+----------------+ | device_id | region | time | count_humidity | +-----------+----------+---------------------------+----------------+ | F07A1260 | north-cn | 2021-01-01T08:00:00+08:00 | 3 | | F07A1260 | north-cn | 2021-01-01T16:00:00+08:00 | 1 | +-----------+----------+---------------------------+----------------+ -
Exemple 2 : Effectuez un sous-échantillonnage aligné sur UTC avec un décalage. Cette requête agrège chaque série chronologique en fenêtres temporelles de 8 heures, décale le début de chaque fenêtre de 3 heures et calcule le nombre.
SELECT device_id,region,time,count(humidity) AS count_humidity FROM sensor WHERE device_id='F07A1260' sample by 8h offset 3h;Le résultat suivant est renvoyé :
+-----------+----------+---------------------------+----------------+ | device_id | region | time | count_humidity | +-----------+----------+---------------------------+----------------+ | F07A1260 | north-cn | 2021-01-01T03:00:00+08:00 | 1 | | F07A1260 | north-cn | 2021-01-01T11:00:00+08:00 | 2 | | F07A1260 | north-cn | 2021-01-01T19:00:00+08:00 | 1 | +-----------+----------+---------------------------+----------------+ -
Exemple 3 : Effectuez un sous-échantillonnage aligné sur UTC pour correspondre à minuit dans un fuseau horaire local. Cette requête agrège chaque série chronologique en fenêtres temporelles de 24 heures et décale le début de 16 heures pour s'aligner sur 00:00 dans le fuseau horaire UTC+08:00. Ensuite, la requête calcule le nombre.
SELECT device_id,region,time,count(humidity) AS count_humidity FROM sensor WHERE device_id='F07A1260' sample by 24h offset 16hLe résultat suivant est renvoyé :
+-----------+----------+---------------------------+----------------+ | device_id | region | time | count_humidity | +-----------+----------+---------------------------+----------------+ | F07A1260 | north-cn | 2021-01-01T00:00:00+08:00 | 4 | +-----------+----------+---------------------------+----------------+ -
Exemple 4 : La clause
SAMPLE BYne peut pas être utilisée directement avecGROUP BY,LIMIT OFFSETouORDER BY. Pour utiliser ces clauses, encapsulez la requêteSAMPLE BYdans une sous-requête.SELECT device_id, max(avg_humidity) AS max_humidity FROM (SELECT device_id,region,time,avg(humidity) AS avg_humidity FROM sensor sample by 8h) group by device_id;Le résultat suivant est renvoyé :
+-----------+--------------+ | device_id | max_humidity | +-----------+--------------+ | F07A1261 | 43.000000 | | F07A1260 | 47.000000 | +-----------+--------------+ -
Exemple 5 : Utilisez
LIMIT OFFSETdans une requête externe pour limiter le nombre de résultats d'une sous-requête de sous-échantillonnage.SELECT device_id,region, avg_humidity FROM (select device_id,region,time,avg(humidity) AS avg_humidity FROM sensor sample by 8h) limit 1 offset 1;Le résultat suivant est renvoyé :
+-----------+----------+--------------+ | device_id | region | avg_humidity | +-----------+----------+--------------+ | F07A1261 | north-cn | 40.000000 | +-----------+----------+--------------+
Exemples de sous-échantillonnage avec interpolation
-
Exemple 1 : Interpolez les données avec une valeur fixe.
SELECT * from (select device_id,region,time, avg(humidity) AS humidity FROM sensor WHERE device_id='F07A1260' sample by 2h fill 1) order by device_id;Le résultat suivant est renvoyé :
+-----------+----------+---------------------------+-----------+ | device_id | region | time | humidity | +-----------+----------+---------------------------+-----------+ | F07A1260 | north-cn | 2021-01-01T08:00:00+08:00 | 9.000000 | | F07A1260 | north-cn | 2021-01-01T10:00:00+08:00 | 1.000000 | | F07A1260 | north-cn | 2021-01-01T12:00:00+08:00 | 45.000000 | | F07A1260 | north-cn | 2021-01-01T14:00:00+08:00 | 46.000000 | | F07A1260 | north-cn | 2021-01-01T16:00:00+08:00 | 1.000000 | | F07A1260 | north-cn | 2021-01-01T18:00:00+08:00 | 1.000000 | | F07A1260 | north-cn | 2021-01-01T20:00:00+08:00 | 47.000000 | +-----------+----------+---------------------------+-----------+ -
Exemple 2 : Interpolez les données avec la valeur de la fenêtre temporelle suivante.
SELECT * from (select device_id,region,time,avg(humidity) AS humidity FROM sensor WHERE device_id='F07A1260' sample by 2h fill after) order by device_id;Le résultat suivant est renvoyé :
+-----------+----------+---------------------------+-----------+ | device_id | region | time | humidity | +-----------+----------+---------------------------+-----------+ | F07A1260 | north-cn | 2021-01-01T08:00:00+08:00 | 9.000000 | | F07A1260 | north-cn | 2021-01-01T10:00:00+08:00 | 45.000000 | | F07A1260 | north-cn | 2021-01-01T12:00:00+08:00 | 45.000000 | | F07A1260 | north-cn | 2021-01-01T14:00:00+08:00 | 46.000000 | | F07A1260 | north-cn | 2021-01-01T16:00:00+08:00 | 47.000000 | | F07A1260 | north-cn | 2021-01-01T18:00:00+08:00 | 47.000000 | | F07A1260 | north-cn | 2021-01-01T20:00:00+08:00 | 47.000000 | +-----------+----------+---------------------------+-----------+
Exemples de transformation post-sous-échantillonnage
-
Exemple 1 : Sous-échantillonnez les données en fenêtres de 2 heures à l'aide de la fonction
AVG(). Ensuite, calculez leratede variation sur les résultats sous-échantillonnés.SELECT device_id,region,time,rate(avg(humidity)) AS rate_humidity FROM sensor WHERE device_id='F07A1260' sample by 2h;Le résultat suivant est renvoyé :
+-----------+----------+---------------------------+---------------+ | device_id | region | time | rate_humidity | +-----------+----------+---------------------------+---------------+ | F07A1260 | north-cn | 2021-01-01T12:00:00+08:00 | 0.002500 | | F07A1260 | north-cn | 2021-01-01T14:00:00+08:00 | 0.000139 | | F07A1260 | north-cn | 2021-01-01T20:00:00+08:00 | 0.000046 | +-----------+----------+---------------------------+---------------+ -
Exemple 2 : Sous-échantillonnez les données en fenêtres de 2 heures. Ensuite, calculez le
deltades résultats sous-échantillonnés.SELECT device_id,region,time,delta(avg(humidity)) AS humidity FROM sensor WHERE device_id='F07A1260' sample by 2h;Le résultat suivant est renvoyé :
+-----------+----------+---------------------------+-----------+ | device_id | region | time | humidity | +-----------+----------+---------------------------+-----------+ | F07A1260 | north-cn | 2021-01-01T12:00:00+08:00 | 36.000000 | | F07A1260 | north-cn | 2021-01-01T14:00:00+08:00 | 1.000000 | | F07A1260 | north-cn | 2021-01-01T20:00:00+08:00 | 1.000000 | +-----------+----------+---------------------------+-----------+
Interpolation
Le sous-échantillonnage divise d'abord chaque série chronologique en fenêtres temporelles spécifiées et applique une fonction d'agrégation aux points de données de chaque fenêtre. Si une fenêtre temporelle ne contient aucun point de données, la requête ne renvoie aucun résultat pour cet intervalle. Utilisez l'interpolation pour combler ces lacunes avec une valeur générée. Par exemple, une requête de sous-échantillonnage génère des résultats aux horodatages t+0, t+20 et t+30. Si la fenêtre temporelle est de 10 unités, il existe une lacune à t+10. Si vous spécifiez une politique de remplissage pour l'interpolation, la requête peut générer une valeur à t+10.
Le tableau suivant décrit les politiques de remplissage disponibles pour l'interpolation.
|
Politique de remplissage |
Valeur interpolée |
|
none |
Ne remplit pas les fenêtres temporelles vides (par défaut). |
|
zero |
Remplit les fenêtres temporelles vides avec 0. |
|
linear |
Effectue une interpolation linéaire pour remplir les fenêtres temporelles vides. |
|
previous |
Remplit les fenêtres temporelles vides avec la valeur de la fenêtre temporelle précédente. |
|
near |
Remplit les fenêtres temporelles vides avec la valeur de la fenêtre temporelle la plus proche. |
|
after |
Remplit les fenêtres temporelles vides avec la valeur de la fenêtre temporelle suivante. |
|
fixed |
Remplit les fenêtres temporelles vides avec une valeur fixe spécifiée. |