STDDEV, STDDEV_SAMP¶

Syntaxe¶

Fonction d’agrégation

{ STDDEV | STDDEV_SAMP } ( [ DISTINCT ] <expr1> )

Fonction de fenêtre

{ STDDEV | STDDEV_SAMP } ( [ DISTINCT ] <expr1> ) OVER (
                                                       [ PARTITION BY <expr2> ]
                                                       [ ORDER BY <expr3> [ ASC | DESC ] [ <window_frame> ] ]
                                                       )

Pour plus d’informations sur la syntaxe window_frame , voir Syntaxe et utilisation du cadre de fenêtre .

Arguments¶

expr1

Une expression qui est évaluée à une valeur numérique. Il s’agit de l’expression sur laquelle est calculé l’écart-type.

expr2

C’est l’expression facultative à partir de laquelle effectuer la partition.

expr3

C’est l’expression facultative à partir de laquelle effectuer le classement dans chaque partition.

Renvoie¶

Le type de données de la valeur renvoyée est DOUBLE.

Si tous les enregistrements d’un groupe sont NULL, cette fonction renvoie NULL.

Notes sur l’utilisation¶

• Pour les entrées à enregistrement unique, STDDEV et STDDEV_SAMP renvoient tous deux NULL. Cela est différent du comportement Oracle, où STDDEV_SAMP renvoie NULL pour un seul enregistrement et STDDEV renvoie 0.

• Lorsqu’elle est transmise à une expression VARCHAR, cette fonction convertit implicitement l’entrée en valeurs à virgule flottante. Si la conversion ne peut pas être effectuée, une erreur est renvoyée.

• Lorsque cette fonction est appelée en tant que fonction de fenêtre et que la clause OVER contient une clause ORDER BY :

  • Le mot-clé DISTINCT est interdit et entraîne une erreur de compilation SQL.

  • Un cadre de fenêtre doit être spécifié. Si vous ne spécifiez pas de cadre de fenêtre, un cadre de fenêtre cumulatif implicite est utilisé :

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

  Pour plus d’informations sur les cadres de fenêtre, y compris la syntaxe et des exemples, voir Syntaxe et utilisation du cadre de fenêtre.

  Pour plus d’informations sur les cadres de fenêtre implicites, voir Notes sur l’utilisation du cadre de fenêtre.

Exemples de fonctions d’agrégation¶

L’exemple suivant calcule l’écart type d’un petit échantillon de nombres entiers :

CREATE TABLE t1 (c1 INTEGER);
INSERT INTO t1 (c1) VALUES
    (6),
   (10),
   (14)
   ;
SELECT STDDEV(c1) FROM t1;

Copy

+----------+
| STDDEV() |
|----------|
|        4 |
+----------+

Notez que la fonction STDDEV_SAMP renvoie le même résultat :

SELECT STDDEV_SAMP(c1) FROM t1;

Copy

+-----------------+
| STDDEV_SAMP(C1) |
|-----------------|
|               4 |
+-----------------+

L’exemple suivant utilise une petite table nommée menu_items, qui répertorie les articles mis en vente par un camion de restauration. Si vous souhaitez créer et charger cette table, voir Créer et charger la table menu_items.

Pour trouver l’écart type de l’échantillon pour le coût des marchandises vendues (COGS) et le prix de vente pour les lignes Dessert , exécutez cette requête :

SELECT menu_category, STDDEV(menu_cogs_usd) stddev_cogs, STDDEV(menu_price_usd) stddev_price
  FROM menu_items
  WHERE menu_category='Dessert'
  GROUP BY 1;

Copy

+---------------+-------------+--------------+
| MENU_CATEGORY | STDDEV_COGS | STDDEV_PRICE |
|---------------+-------------+--------------|
| Dessert       |  1.00519484 |  1.471960144 |
+---------------+-------------+--------------+

Exemple de fonction de fenêtre¶

L’exemple suivant utilise également la table menu_items (voir Créer et charger la table menu_items), mais appelle la fonction STDDEV en tant que fonction de fenêtre.

La fonction de fenêtre sépare les lignes en fonction de la colonne menu_category. Par conséquent, l’écart type est calculé une fois pour chaque catégorie et cette valeur est répétée dans le résultat pour chaque ligne du groupe. Dans cet exemple, les lignes doivent être regroupées à la fois par catégorie de menu et par coût des marchandises vendues.

SELECT menu_category, menu_cogs_usd,
    STDDEV(menu_cogs_usd) OVER(PARTITION BY menu_category) stddev_cogs
  FROM menu_items
  GROUP BY 1,2
  ORDER BY menu_category;

Copy

La sortie suivante est un jeu de résultats partiels pour cette requête (les premières 15 lignes) :

+---------------+---------------+--------------+
| MENU_CATEGORY | MENU_COGS_USD |  STDDEV_COGS |
|---------------+---------------+--------------|
| Beverage      |          0.50 | 0.1258305738 |
| Beverage      |          0.65 | 0.1258305738 |
| Beverage      |          0.75 | 0.1258305738 |
| Dessert       |          1.25 | 1.054751155  |
| Dessert       |          3.00 | 1.054751155  |
| Dessert       |          1.00 | 1.054751155  |
| Dessert       |          2.50 | 1.054751155  |
| Dessert       |          0.50 | 1.054751155  |
| Main          |          4.50 | 3.444051572  |
| Main          |          2.40 | 3.444051572  |
| Main          |          1.50 | 3.444051572  |
| Main          |         11.00 | 3.444051572  |
| Main          |          8.00 | 3.444051572  |
| Main          |          NULL | 3.444051572  |
| Main          |         12.00 | 3.444051572  |
...

Créer et charger la table menu_items¶

Pour créer et insérer des lignes dans la table menu_items utilisée dans certains exemples de fonctions, exécutez les commandes SQL suivantes. (Cette table contient 60 lignes. Elle est basée sur la table menu de la base de données d’échantillons Tasty Bytes, mais n’est pas identique à celle-ci).

CREATE OR REPLACE TABLE menu_items(
  menu_id INT NOT NULL,
  menu_category VARCHAR(20),
  menu_item_name VARCHAR(50),
  menu_cogs_usd NUMBER(7,2),
  menu_price_usd NUMBER(7,2)
  );

INSERT INTO menu_items VALUES(1,'Beverage','Bottled Soda',0.500,3.00);
INSERT INTO menu_items VALUES(2,'Beverage','Bottled Water',0.500,2.00);
INSERT INTO menu_items VALUES(3,'Main','Breakfast Crepe',5.00,12.00);
INSERT INTO menu_items VALUES(4,'Main','Buffalo Mac & Cheese',6.00,10.00);
INSERT INTO menu_items VALUES(5,'Main','Chicago Dog',4.00,9.00);
INSERT INTO menu_items VALUES(6,'Main','Chicken Burrito',3.2500,12.500);
INSERT INTO menu_items VALUES(7,'Main','Chicken Pot Pie Crepe',6.00,15.00);
INSERT INTO menu_items VALUES(8,'Main','Combination Curry',9.00,15.00);
INSERT INTO menu_items VALUES(9,'Main','Combo Fried Rice',5.00,11.00);
INSERT INTO menu_items VALUES(10,'Main','Combo Lo Mein',6.00,13.00);
INSERT INTO menu_items VALUES(11,'Main','Coney Dog',5.00,10.00);
INSERT INTO menu_items VALUES(12,'Main','Creamy Chicken Ramen',8.00,17.2500);
INSERT INTO menu_items VALUES(13,'Snack','Crepe Suzette',4.00,9.00);
INSERT INTO menu_items VALUES(14,'Main','Fish Burrito',3.7500,12.500);
INSERT INTO menu_items VALUES(15,'Snack','Fried Pickles',1.2500,6.00);
INSERT INTO menu_items VALUES(16,'Snack','Greek Salad',4.00,11.00);
INSERT INTO menu_items VALUES(17,'Main','Gyro Plate',8.00,12.00);
INSERT INTO menu_items VALUES(18,'Main','Hot Ham & Cheese',7.00,11.00);
INSERT INTO menu_items VALUES(19,'Dessert','Ice Cream Sandwich',1.00,4.00);
INSERT INTO menu_items VALUES(20,'Beverage','Iced Tea',0.7500,3.00);
INSERT INTO menu_items VALUES(21,'Main','Italian',6.00,11.00);
INSERT INTO menu_items VALUES(22,'Main','Lean Beef Tibs',6.00,13.00);
INSERT INTO menu_items VALUES(23,'Main','Lean Burrito Bowl',3.500,12.500);
INSERT INTO menu_items VALUES(24,'Main','Lean Chicken Tibs',5.00,11.00);
INSERT INTO menu_items VALUES(25,'Main','Lean Chicken Tikka Masala',10.00,17.00);
INSERT INTO menu_items VALUES(26,'Beverage','Lemonade',0.6500,3.500);
INSERT INTO menu_items VALUES(27,'Main','Lobster Mac & Cheese',10.00,15.00);
INSERT INTO menu_items VALUES(28,'Dessert','Mango Sticky Rice',1.2500,5.00);
INSERT INTO menu_items VALUES(29,'Main','Miss Piggie',2.600,6.00);
INSERT INTO menu_items VALUES(30,'Main','Mothers Favorite',4.500,12.00);
INSERT INTO menu_items VALUES(31,'Main','New York Dog',4.00,8.00);
INSERT INTO menu_items VALUES(32,'Main','Pastrami',8.00,11.00);
INSERT INTO menu_items VALUES(33,'Dessert','Popsicle',0.500,3.00);
INSERT INTO menu_items VALUES(34,'Main','Pulled Pork Sandwich',7.00,12.00);
INSERT INTO menu_items VALUES(35,'Main','Rack of Pork Ribs',11.2500,21.00);
INSERT INTO menu_items VALUES(36,'Snack','Seitan Buffalo Wings',4.00,7.00);
INSERT INTO menu_items VALUES(37,'Main','Spicy Miso Vegetable Ramen',7.00,17.2500);
INSERT INTO menu_items VALUES(38,'Snack','Spring Mix Salad',2.2500,6.00);
INSERT INTO menu_items VALUES(39,'Main','Standard Mac & Cheese',3.00,8.00);
INSERT INTO menu_items VALUES(40,'Dessert','Sugar Cone',2.500,6.00);
INSERT INTO menu_items VALUES(41,'Main','Tandoori Mixed Grill',11.00,18.00);
INSERT INTO menu_items VALUES(42,'Main','The Classic',4.00,12.00);
INSERT INTO menu_items VALUES(43,'Main','The King Combo',12.00,20.00);
INSERT INTO menu_items VALUES(44,'Main','The Kitchen Sink',6.00,14.00);
INSERT INTO menu_items VALUES(45,'Main','The Original',1.500,5.00);
INSERT INTO menu_items VALUES(46,'Main','The Ranch',2.400,6.00);
INSERT INTO menu_items VALUES(47,'Main','The Salad of All Salads',6.00,12.00);
INSERT INTO menu_items VALUES(48,'Main','Three Meat Plate',10.00,17.00);
INSERT INTO menu_items VALUES(49,'Main','Three Taco Combo Plate',7.00,11.00);
INSERT INTO menu_items VALUES(50,'Main','Tonkotsu Ramen',7.00,17.2500);
INSERT INTO menu_items VALUES(51,'Main','Two Meat Plate',9.00,14.00);
INSERT INTO menu_items VALUES(52,'Dessert','Two Scoop Bowl',3.00,7.00);
INSERT INTO menu_items VALUES(53,'Main','Two Taco Combo Plate',6.00,9.00);
INSERT INTO menu_items VALUES(54,'Main','Veggie Burger',5.00,9.00);
INSERT INTO menu_items VALUES(55,'Main','Veggie Combo',4.00,9.00);
INSERT INTO menu_items VALUES(56,'Main','Veggie Taco Bowl',6.00,10.00);
INSERT INTO menu_items VALUES(57,'Dessert','Waffle Cone',2.500,6.00);
INSERT INTO menu_items VALUES(58,'Main','Wonton Soup',2.00,6.00);
INSERT INTO menu_items VALUES(59,'Main','Mini Pizza',null,null);
INSERT INTO menu_items VALUES(60,'Main','Large Pizza',null,null);