Notebook séance 10

Reprise des exercices Pandas en SQL On commence par se connecter à la base et à importer pandas.

%load_ext sql
%config SqlMagic.dsn_filename = "connections.ini"
%sql --section bd-pedago
%sql --connections
import pandas

Connecting to 'bd-pedago'

La table de la séance9 existe toujours, pas besoin de la recréer

Projections et filtres simples

Donner les valeurs de la colonne “RT”

%%sql
SELECT "RT"
FROM df

Running query in 'bd-pedago'

9594 rows affected.

Donner les différentes valeurs possible pour la colonne “Subject”

On utilise pour cela le mot clé DISTINCT, placé juste après le SELECT:

%%sql
SELECT DISTINCT "Subject"
FROM df

Running query in 'bd-pedago'

24 rows affected.

Le distinct peut fonctionner sur plusieurs colonnes, par exemple si la table data contient:

La requête

SELECT DISTINCT A, B
FROM data

produira

Filtrer la dataframe pour ne garder que les lignes dont le “RT” est plus petit que 10 000.

%%sql
SELECT *
FROM df
WHERE "RT" < 10000

Running query in 'bd-pedago'

4528 rows affected.

%sqlcmd explore --table df

df_subjects = %sql SELECT DISTINCT "Subject" FROM df

Running query in 'bd-pedago'

24 rows affected.

df_subjects.DataFrame().to_excel("subjects.xlsx")

On change le nombre de ligne affichées à 50

%config SqlMagic.displaylimit = 50

%%sql
SELECT DISTINCT "Subject"
FROM df

Running query in 'bd-pedago'

24 rows affected.

# On remet le nombre de lignes affichées à 10
%config SqlMagic.displaylimit = 10

Combiner les projections et les filtres

Indiquer la valeur de “RT” pour les essais en mode “Dic”.

%%sql
SELECT "RT"
FROM df
WHERE "Mode" = 'Dic'

Running query in 'bd-pedago'

4795 rows affected.

Créer une Series indiquant pour chaque essai si la réponse est juste ou non. Une réponse est juste si la distance A est inférieure à la distance B et la réponse est 1 ou si la distance A est supérieure à la distance B et la réponse est 2.

%%sql
SELECT index,
       (("Dist_A" < "Dist_B" AND "Response" = 1)
        OR ("Dist_A" > "Dist_B" AND "Response" = 2)) 
       AS juste
FROM df

Running query in 'bd-pedago'

9594 rows affected.

Pour avoir la colonne “juste” intégrée avec les autres, on peut faire une requête qui récupère tout df plus cette colonne:

%%sql
SELECT *,
       (("Dist_A" < "Dist_B" AND "Response" = 1)
        OR ("Dist_A" > "Dist_B" AND "Response" = 2)) 
       AS juste
FROM df

Running query in 'bd-pedago'

9594 rows affected.

On peut en faire une vue en ajoutant devant CREATE OR REPLACE VIEW un_nom AS, ce qui permettra d’utiliser le résultat comme s’il avait été mis dans une table un_nom. La différence est qu’une vue est toujours recalculée et a donc toujours des données correctes.

%%sql
DROP VIEW IF EXISTS dfj;
CREATE OR REPLACE VIEW dfj AS
SELECT *,
       (("Dist_A" < "Dist_B" AND "Response" = 1)
        OR ("Dist_A" > "Dist_B" AND "Response" = 2)) 
       AS juste
FROM df

Running query in 'bd-pedago'

%%sql
SELECT *
FROM dfj

Running query in 'bd-pedago'

9594 rows affected.

Indiquer les “Subject” qui ont répondu en moins de 4 000 (“RT” inférieur à 4 000)

%%sql
SELECT DISTINCT "Subject"
FROM df
WHERE "RT" < 4000

Running query in 'bd-pedago'

13 rows affected.

Agrégations globales

Donner la moyenne de “RT”.

%%sql
SELECT avg("RT") AS moyenne
FROM df

Running query in 'bd-pedago'

1 rows affected.

Donner le plus grand “RT” pour le “Subject” “P_ROS_336”

%%sql
SELECT max("RT") AS moyenne
FROM df
WHERE "Subject" = 'P_ROS_336'

Running query in 'bd-pedago'

1 rows affected.

Donner l’écart-type de “RT” pour les essais réussis (utiliser .std())

%%sql
SELECT stddev("RT") AS ecart_type
FROM dfj
WHERE juste

Running query in 'bd-pedago'

1 rows affected.

Groupes simples

Donner la moyenne du temps de réaction (“RT”) pour chaque “Subject”.

%%sql
SELECT "Subject", avg("RT") as Moyenne_RT
FROM df
GROUP BY "Subject"

Running query in 'bd-pedago'

24 rows affected.

Donner le nombre d’essais pour chaque “Subject” en utilisant COUNT(*).

%%sql
SELECT "Subject", COUNT(*) as nombre_essais
FROM df
GROUP BY "Subject"

Running query in 'bd-pedago'

24 rows affected.

Donner le nombre d’essais réussis pour chaque “Subject” en utilisant un filtre puis count(*).

%%sql
SELECT "Subject", COUNT(*) as essais_reussis
FROM dfj
WHERE juste
GROUP BY "Subject"

Running query in 'bd-pedago'

24 rows affected.