Fonctions de table¶

Exemples simples de fonctions de table¶

Par exemple, les fonctions suivantes sont appropriées en tant que fonctions de table :

• Une fonction qui accepte un numéro de compte et une date, et renvoie tous les frais facturés à ce compte à cette date. (Plus d’un frais peut avoir été facturé à une date donnée).

• Une fonction qui accepte un ID utilisateur et renvoie les rôles de base de données attribués à cet utilisateur. (Un utilisateur peut avoir plusieurs rôles, notamment « sysadmin » et « useradmin »).

Fonctions dans lesquelles chaque ligne de sortie dépend de plusieurs lignes d’entrée¶

Les fonctions de table peuvent être regroupées en deux catégories en fonction du nombre de lignes d’entrée qui affectent chaque ligne de sortie :

• 1 à N

• M à N

Les fonctions décrites précédemment sont des fonctions de table 1 à N : chaque ligne de sortie dépend d’une seule ligne d’entrée. Par exemple, une fonction record_high_temperatures_for_date() peut produire plusieurs lignes de sortie (une pour chaque ville ayant atteint un record à cette date). Chaque ligne de sortie pour une date d’entrée spécifique ne dépend que de cette date ; chaque ligne de sortie est indépendante des lignes pour toutes les autres dates.

Snowflake prend en charge également les fonctions de table M à N : chaque ligne de sortie peut dépendre de plusieurs lignes d’entrée. Par exemple, si une fonction génère une moyenne mobile des prix des actions, cette fonction utilise les prix des actions de plusieurs lignes d’entrée (plusieurs dates) pour générer chaque ligne de sortie.

De façon plus générale, dans une fonction M à N, un groupe de M lignes d’entrée produit un groupe de N lignes de sortie. M peut être une ou plusieurs lignes. N peut être zéro, une ou plusieurs lignes.

Par exemple, dans une moyenne mobile de 10 jours, M est 10. N est 1, car chaque groupe de 10 lignes d’entrée produit un prix moyen.

Fonctions de table intégrées et fonctions de table définies par l’utilisateur¶

Snowflake fournit des centaines de fonctions intégrées, dont beaucoup sont des fonctions de table. Les fonctions de table intégrées sont répertoriées dans des fonctions de table définies par le système.

Les utilisateurs peuvent également écrire leurs propres fonctions, appelées fonctions définies par l’utilisateur ou « UDFs ». Certaines UDFs sont scalaires, d’autres sont tabulaires. Les fonctions de table définies par l’utilisateur sont appelées « UDTFs ». Pour obtenir des informations sur les UDFs (y compris les UDTFs), voir Vue d’ensemble des fonctions définies par l’utilisateur.

Les fonctions de table intégrées et les fonctions de table définies par l’utilisateur suivent généralement les mêmes règles ; par exemple, elles sont appelées de la même manière à partir d’instructions SQL.

Utilisation d’une fonction de table dans la clause FROM¶

Une table contient un ensemble de lignes. Une fonction de table renvoie un ensemble de lignes. Les tables et les fonctions de table sont utilisées dans des contextes qui attendent un ensemble de lignes. Plus précisément, les fonctions de table sont utilisées dans la clause FROM d’une instruction SQL.

Pour aider le compilateur SQL à reconnaître une fonction de table comme une source de lignes, Snowflake exige que l’appel de la fonction de table soit encapsulé par le mot-clé TABLE().

Par exemple, l’instruction suivante appelle une fonction de table nommée record_high_temperatures_for_date(), qui prend une valeur DATE comme argument :

SELECT city_name, temperature
    FROM TABLE(record_high_temperatures_for_date('2021-06-27'::DATE))
    ORDER BY city_name;

Copy

Pour plus d’informations sur la syntaxe de TABLE(), voir Littéraux de table.

Les fonctions de table, comme les fonctions en général, peuvent accepter zéro, un ou plusieurs arguments d’entrée à chaque appel. Chaque argument doit être une expression scalaire.

Pour plus de détails sur la syntaxe des appels de fonctions de table, voir Syntaxe (dans cette rubrique).

Utilisation d’une table comme entrée d’une fonction de table¶

L’argument d’une fonction de table peut être un littéral ou une expression, telle qu’une colonne d’une table. Par exemple, l’instruction SELECT ci-dessous transmet les valeurs d’une table comme arguments à une fonction de table :

CREATE OR REPLACE table dates_of_interest (event_date DATE);
INSERT INTO dates_of_interest (event_date) VALUES
    ('2021-06-21'::DATE),
    ('2022-06-21'::DATE);

CREATE OR REPLACE FUNCTION record_high_temperatures_for_date(d DATE)
    RETURNS TABLE (event_date DATE, city VARCHAR, temperature NUMBER)
    as
    $$
    SELECT d, 'New York', 65.0
    UNION ALL
    SELECT d, 'Los Angeles', 69.0
    $$;

SELECT
        doi.event_date as "Date", 
        record_temperatures.city,
        record_temperatures.temperature
    FROM dates_of_interest AS doi,
         TABLE(record_high_temperatures_for_date(doi.event_date)) AS record_temperatures
      ORDER BY doi.event_date, city;
+------------+-------------+-------------+
| Date       | CITY        | TEMPERATURE |
|------------+-------------+-------------|
| 2021-06-21 | Los Angeles |          69 |
| 2021-06-21 | New York    |          65 |
| 2022-06-21 | Los Angeles |          69 |
| 2022-06-21 | New York    |          65 |
+------------+-------------+-------------+

Les arguments d’une fonction de table peuvent provenir d’autres sources de type table, notamment de vues et d’autres fonctions de table.