Catégories :: Syntaxe de requête

RESAMPLE¶

Renvoie un ensemble de données qui comprend à la fois les lignes d’entrée et les lignes générées pour les points de données manquants, compte tenu d’une granularité temporelle définie par l’utilisateur.

Syntaxe¶

FROM <object_reference> [ [ AS ] <alias_name> ]
  RESAMPLE(
    USING <time_series_column>
    INCREMENT BY <time_series_constant>
    [ PARTITION BY <partition_column> [ , ... ] ]
    [ METADATA_COLUMNS
        { IS_GENERATED() | BUCKET_START() } [ [ AS ] <alias_name> ] [ , ... ] ]
    )

Paramètres requis¶

FROM object_reference

Spécifie le nom d’une table ou d’une autre référence d’objet contenant l’ensemble des données d’entrée, tel qu’une sous-requête. Pour plus d’informations sur les références des objets, consultez FROM.

USING time_series_column

Spécifie la colonne qui contient les valeurs temporelles des séries temporelles. La colonne doit être un Type de données de date et d’heure ou un type de données numériques. Par exemple, les valeurs d’horodatage UNIX peuvent être stockées dans les colonnes NUMBER(38,0), où 1743447600 est équivalent à 2025-3-31 12:00:00.

INCREMENT BY time_series_constant

Spécifie une constante INTERVAL ou une constante numérique, selon le type de données de la colonne USING. Cette constante représente la largeur de chaque intervalle de temps. Toutes les tranches sont alignées par rapport au 1er janvier 1970 à minuit (1970-01-01 00:00:00). La fonction TIME_SLICE utilise le même alignement ; pour plus d’informations, voir les notes sur l’utilisation de TIME_SLICE <label-time_slice_usage_notes>.

Lorsque le paramètre USING spécifie une colonne de date ou d’heure, l’expression INCREMENT BY doit être une constante INTERVAL.
Lorsque le paramètre USING spécifie une colonne numérique, l’expression INCREMENT BY doit également être numérique.

Le point de départ dune série temporelle générée est basé sur l’heure minimal de la time_series_constant.

Si cette constante est une constante numérique, elle doit être positive (supérieure à 0).

Paramètres facultatifs¶

[ AS ] alias_name

Spécifie un nom alternatif pour la référence de l’objet. L’alias peut être utilisé dans n’importe quelle autre sous-clause à l’intérieur de la clause FROM. Les noms d’alias doivent suivre les règles de Identificateurs d’objet.

PARTITION BY partition_column

Partitionne le jeu de résultats sur une ou plusieurs colonnes d’entrée et génère de nouvelles lignes dans chaque partition.

METADATA_COLUMNS {function} [ [ AS ] {alias_name} ]

Ajoute une ou plusieurs colonnes de métadonnées au jeu de résultats rééchantillonné. Pour ajouter les colonnes, appelez une ou les deux fonctions suivantes :

IS_GENERATED(): Ajoute une colonne is_generated au jeu de résultats qui indique quelles lignes sont nouvelles (générées par l’opération RESAMPLE) et quelles lignes existaient déjà.
BUCKET_START(): Ajoute une colonne bucket_start au jeu de résultats. Cette colonne renvoie la valeur qui marque le début du compartiment actuel ou de l’intervalle que l’opération RESAMPLE produit, en fonction des valeurs de la colonne spécifiée dans la clause USING. Vous pouvez utiliser la colonne BUCKET_START pour identifier à quel intervalle appartient une ligne particulière après le rééchantillonnage.

Si vous spécifiez les deux colonnes de métadonnées, séparez-les par une virgule.

Les colonnes générées peuvent avoir des alias. Les noms d’alias doivent suivre les règles de Identificateurs d’objet.

Notes sur l’utilisation¶

Une constante INTERVAL dans la clause INCREMENT BY doit remplir les conditions suivantes :
- La constante doit être égale ou supérieure à une second. Les unités plus petites (millisecond, microsecond, nanosecond) ne sont pas prises en charge.
- Lorsque la colonne USING est un type de données DATE, vous ne pouvez pas spécifier une unité dans l’intervalle qui est plus granulaire que day (hour, minute, second). Par exemple, les constantes 'INTERVAL 1 day, 2 hours' et 'INTERVAL 25 hours' ne sont pas autorisées.
- Pour éviter toute ambiguïté, certaines parties de date ne peuvent pas être mélangées. Les parties de date prises en charge se répartissent en trois groupes discrets :
  - year, quarter, month
  - week
  - day, hour, minute, second
  Par exemple, les intervalles suivants, qui empiètent sur plusieurs groupes, ne sont pas autorisés :
  - INTERVAL '1 week, 3 days'
  - INTERVAL '2 weeks, 12 hours'
  - INTERVAL '3 months, 1 week'

En ce qui concerne les jointures, la construction RESAMPLE fonctionne de la même manière que la construction SAMPLE / TABLESAMPLE. Le rééchantillonnage s’applique à une seule table, pas à toutes les tables précédentes ni à l’expression entière antérieure à la clause RESAMPLE. Pour rééchantillonner le résultat dune jointure, utilisez une sous-requête pour la jointure, puis rééchantillonnez la table résultante. Voir Échantillonnage avec jointures.
La clause RESAMPLE est évaluée avant que les conditions de la clause WHERE soient appliquées. Si vous voulez rééchantillonner un ensemble de données filtré, filtrez-le d’abord (par exemple, en créant une nouvelle table que vous pouvez rééchantillonner ou en utilisant une sous-requête calculée d’abord à l’intérieur de la requête RESAMPLE principale). La requête suivante rééchantillonne la table entière, puis supprime tout sauf les lignes pour Atlanta et Boston.
```
SELECT *
  FROM heavy_weather
    RESAMPLE(
      USING start_time
      INCREMENT BY INTERVAL '1 day')
  WHERE city IN('Atlanta','Boston')
  ORDER BY start_time, city, county;
```
Une réécriture potentielle avec une sous-requête serait :
```
SELECT *
  FROM (SELECT * FROM heavy_weather WHERE city IN('Atlanta','Boston'))
    RESAMPLE(
      USING start_time
      INCREMENT BY INTERVAL '1 day')
  ORDER BY start_time, city, county;
```
Lorsque des lignes rééchantillonnées sont générées, elles contiennent des valeurs NULL pour les colonnes qui ne sont pas partitionnées. Si vous utilisez une clause WHERE pour filtrer les colonnes qui ne sont pas partitionnées, votre filtre peut être appliqué aux lignes générées de manière inattendue.

Par exemple, étant donné que la requête suivante ne dispose pas de clause PARTITION BY, elle ne fonctionne pas comme prévu. Dans les lignes générées, city et county sont NULL. La clause WHERE filtre alors toutes les lignes générées ; seules les lignes d’origine qui correspondent aux critères de filtrage sont conservées.
```
SELECT *
  FROM march_temps
    RESAMPLE(
      USING observed
      INCREMENT BY INTERVAL '5 minutes')
  WHERE city = 'Big Bear City' AND county = 'San Bernardino'
  ORDER BY observed;
```
Pour résoudre le problème, la requête suivante est partitionnée par city et county. La clause RESAMPLE génère des lignes pour des combinaisons de city et county qui existent dans les données sources. La condition de la clause WHERE préserve les lignes générées appropriées, car city et county ont des valeurs non-NULL dans ces lignes.
```
SELECT *
  FROM march_temps
    RESAMPLE(
      USING observed
      INCREMENT BY INTERVAL '5 minutes'
      PARTITION BY city, county)
  WHERE city = 'Big Bear City' AND county = 'San Bernardino'
  ORDER BY observed;
```
En comparaison,

Exemples¶

Les exemples suivants montrent comment utiliser la construction RESAMPLE dans les requêtes.

Exemple de RESAMPLE utilisant une colonne numérique¶

L’exemple suivant a un horodatage UNIX dans le tableau source. Cette colonne numérique est spécifiée dans la clause RESAMPLE en tant que colonne USING. Créez et chargez la table suivante :

CREATE OR REPLACE TABLE sensor_data_unixtime (device_id VARCHAR(10), unixtime NUMBER(38,0), avg_temp NUMBER(6,4), vibration NUMBER (5,4), motor_rpm INT);

INSERT INTO sensor_data_unixtime VALUES
  ('DEVICE3', 1696150802, 36.1103, 0.4226, 1560),
  ('DEVICE3', 1696150803, 35.2987, 0.4326, 1561),
  ('DEVICE3', 1696150804, 40.0001, 0.3221, 1562),
  ('DEVICE3', 1696150805, 38.0422, 0.3333, 1589),
  ('DEVICE3', 1696150807, 33.1524, 0.4865, 1499),
  ('DEVICE3', 1696150808, 32.0422, 0.4221, 1498),
  ('DEVICE3', 1696150809, 31.1519, 0.4751, 1600),
  ('DEVICE3', 1696150810, 29.1524, 0.4639, 1605),
  ('DEVICE3', 1696150812, 35.2987, 0.4336, 1585),
  ('DEVICE3', 1696150813, 40.0000, 0.4226, 1560)
;

À présent, exécutez la requête RESAMPLE suivante.

SELECT * FROM sensor_data_unixtime
  RESAMPLE(USING unixtime INCREMENT BY 1) ORDER BY unixtime;

+-----------+------------+----------+-----------+-----------+
| DEVICE_ID |   UNIXTIME | AVG_TEMP | VIBRATION | MOTOR_RPM |
|-----------+------------+----------+-----------+-----------|
| DEVICE3   | 1696150802 |  36.1103 |    0.4226 |      1560 |
| DEVICE3   | 1696150803 |  35.2987 |    0.4326 |      1561 |
| DEVICE3   | 1696150804 |  40.0001 |    0.3221 |      1562 |
| DEVICE3   | 1696150805 |  38.0422 |    0.3333 |      1589 |
| DEVICE3   | 1696150806 |     NULL |      NULL |      NULL |
| DEVICE3   | 1696150807 |  33.1524 |    0.4865 |      1499 |
| DEVICE3   | 1696150808 |  32.0422 |    0.4221 |      1498 |
| DEVICE3   | 1696150809 |  31.1519 |    0.4751 |      1600 |
| DEVICE3   | 1696150810 |  29.1524 |    0.4639 |      1605 |
| DEVICE3   | 1696150811 |     NULL |      NULL |      NULL |
| DEVICE3   | 1696150812 |  35.2987 |    0.4336 |      1585 |
| DEVICE3   | 1696150813 |  40.0000 |    0.4226 |      1560 |
+-----------+------------+----------+-----------+-----------+

La requête suivante échoue, car l’expression INCREMENT BY doit être une constante numérique positive lorsque la colonne USING est numérique :

SELECT * FROM sensor_data_unixtime
  RESAMPLE(USING unixtime INCREMENT BY INTERVAL '1 second') ORDER BY unixtime;

009954 (42601): SQL compilation error:
RESAMPLE INCREMENT BY has to be numeric type when USING parameter is numeric.

Exemple de RESAMPLE qui renvoie uniquement les lignes générées¶

L’exemple suivant illustre la table march_temps (telle que créée dans Utilisation de la clause RESAMPLE) et comprend des colonnes de métadonnées nommées generated_row et bucket_start dans le résultat :

CREATE OR REPLACE TABLE march_temps_every_five_mins AS
  SELECT * FROM march_temps
    RESAMPLE(
      USING observed
      INCREMENT BY INTERVAL '5 minutes'
      PARTITION BY city, county
      METADATA_COLUMNS IS_GENERATED() AS generated_row, BUCKET_START()
      )
  ORDER BY observed;

La requête suivante renvoie uniquement les lignes générées à partir de la table march_temps_every_five_mins :

SELECT * FROM march_temps_every_five_mins
  WHERE generated_row = 'True';

+-------------------------+-------------+------------------+----------------+---------------+-------------------------+
| OBSERVED                | TEMPERATURE | CITY             | COUNTY         | GENERATED_ROW | BUCKET_START            |
|-------------------------+-------------+------------------+----------------+---------------+-------------------------|
| 2025-03-15 09:45:00.000 |        NULL | Big Bear City    | San Bernardino | True          | 2025-03-15 09:45:00.000 |
| 2025-03-15 09:50:00.000 |        NULL | Big Bear City    | San Bernardino | True          | 2025-03-15 09:50:00.000 |
| 2025-03-15 10:00:00.000 |        NULL | South Lake Tahoe | El Dorado      | True          | 2025-03-15 10:00:00.000 |
| 2025-03-15 10:00:00.000 |        NULL | Big Bear City    | San Bernardino | True          | 2025-03-15 10:00:00.000 |
| 2025-03-15 10:05:00.000 |        NULL | South Lake Tahoe | El Dorado      | True          | 2025-03-15 10:05:00.000 |
| 2025-03-15 10:05:00.000 |        NULL | Big Bear City    | San Bernardino | True          | 2025-03-15 10:05:00.000 |
| 2025-03-15 10:15:00.000 |        NULL | Big Bear City    | San Bernardino | True          | 2025-03-15 10:15:00.000 |
+-------------------------+-------------+------------------+----------------+---------------+-------------------------+

Exemple de RESAMPLE qui utilise BUCKET_START() pour agréger des lignes rééchantillonnées¶

L’exemple suivant utilise la colonne de métadonnées bucket_start pour agréger les lignes rééchantillonnées. La requête compte le nombre d’observations par ville qui ont la même heure de début de compartiment, étant donné un jeu de résultats rééchantillonné qui est incrémenté par intervalle d’un jour. Pour exécuter cet exemple, créez la table march_temps, comme décrit dans Utilisation de la clause RESAMPLE.

SELECT bucket_start, county, COUNT(*)
  FROM march_temps
    RESAMPLE(
      USING observed
      INCREMENT BY INTERVAL '1 day'
      METADATA_COLUMNS IS_GENERATED(), BUCKET_START()
      )
  WHERE IS_GENERATED = 'False'
  GROUP BY bucket_start, county;

+-------------------------+----------------+----------+
| BUCKET_START            | COUNTY         | COUNT(*) |
|-------------------------+----------------+----------|
| 2025-03-15 00:00:00.000 | El Dorado      |        4 |
| 2025-03-15 00:00:00.000 | San Bernardino |        4 |
+-------------------------+----------------+----------+

Exemple de RESAMPLE qui utilise BUCKET_START() pour filtrer les lignes non uniformes¶

Vous pouvez utiliser la colonne de métadonnées bucket_start pour filtrer les données non uniformes d’un jeu de résultats rééchantillonné. Par exemple :

SELECT *
  FROM march_temps
    RESAMPLE(
      USING observed
      INCREMENT BY INTERVAL '5 minutes'
      METADATA_COLUMNS BUCKET_START() AS bucket_first_row
      )
  WHERE observed = bucket_first_row
  ORDER BY observed;

Cette requête rééchantillonne la table, puis supprime deux lignes d’origine qui ne sont pas conformes à l’intervalle de 5 minutes (celles avec des valeurs 09:49:00 et 10:18:00).