Discussion :

[cedrickab]

Bonjour
J'ai une table SQL avec le squelette suivant :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE public.capge
 
(
    artist character varying(500) COLLATE pg_catalog."default",
    album character varying(500) COLLATE pg_catalog."default",
    song_album character varying(500) COLLATE pg_catalog."default",
    date_ecoute timestamp without time zone
)

Cette table contient une liste de son écouté de différents artiste, j'essaye de trouver les artistes les plus écoutés par semaine.
j'ai essayé cette query

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
with t1 as 
(select *, 
row_number() over (partition BY EXTRACT(month from date_ecoute) order by count(song_album) DESC) as rn from capge)
select * from t1
where rn <= 10

j'obtient comme erreur

ERROR: ERREUR: la colonne « capge.artist » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat
LINE 2: (select *,

J'utilise PostgreSQL , est ce que quelqu'un pourrait m'aider svp ?

[escartefigue]

Bonjour,

Avec la table telle que fournie, la requête suivante doit faire l'affaire

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select extract(week from date_ecoute) as sem
     , song_album                     as song
     , count(*)                       as nbr
from capge
group by  extract(week from date_ecoute)
        , song_album

Cela étant dit, il faut d'urgence revoir le modèle de données : dans une table des écoutes, il ne devrait pas y avoir de titre d'album, ni de titre de chanson, ni de titre d'auteur, mais uniquement des clefs étrangères faisant références aux tables qui vont bien et une date et heure d'écoute (plus éventuellement la référence toujours sous forme de FK de l'auditeur, si l'écoute est nominative).
De plus, le typage des données est mal choisi varchar(500) pour un auteur, sérieusement...
Enfin, pourquoi extraire le mois alors que vous voulez un résultat par semaine...

[cedrickab]

Bonjour ,
J'ai essayé votre solution et ça a effectivement résolu partiellement mon problème, je l'ai un peu modifié la requête parceque les valeurs j'ai plusieurs années dans ma colonne date_ecoute et je voulais un classement ordonné comme suit :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
select extract(week from date_ecoute) as sem
     , extract(year from date_ecoute) as yea
     , song_album                     as song
     , count(*)                       as nbr
 
from capge
group by  extract(week from date_ecoute)
		,extract(year from date_ecoute)
	    , song_album
 
order by yea DESC,sem ASC, nbr DESC

Le problème c'est que je ne peux pas mettre de limit à un group by pour récupérer les top (2 par exemple) par année et par semaine, c'est pour ça qu'initialement je voulais un row_number() . J'ai finalement pu faire ma requête précédente mais le résultat me donne les tops toute année confondue.

Concernant le squelette de la base de données elle n'est pas volontaire, j'ai juste creer la table en fonction du d'un fichier csv que j'ai importé. Et pour le varchar(500) je me suis retrouvé avec des erreurs en diminuant la taille des chaînes.

[Séb.]

Concernant le squelette de la base de données elle n'est pas volontaire, j'ai juste creer la table en fonction du d'un fichier csv que j'ai importé.

Rien ne t'oblige à suivre le modèle de données plat de ton CSV
En l'état ça peut fonctionner, mais tôt ou tard tu seras confronté à des difficultés. Et plutôt tôt que tard.

Le problème c'est que je ne peux pas mettre de limit à un group by pour récupérer les top (2 par exemple) par année et par semaine, c'est pour ça qu'initialement je voulais un row_number() . J'ai finalement pu faire ma requête précédente mais le résultat me donne les tops toute année confondue.

Il faut partitionner selon l'année et la semaine :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
WITH dataset (y, w, song_album, n, r) AS (
    SELECT ALL
        EXTRACT(YEAR FROM date_ecoute), EXTRACT(WEEK FROM date_ecoute),
        song_album, COUNT(*),
        RANK() OVER (PARTITION BY EXTRACT(YEAR FROM date_ecoute), EXTRACT(WEEK FROM date_ecoute) ORDER BY COUNT(*) DESC)
    FROM capge
    GROUP BY 1, 2, 3
)
SELECT ALL *
FROM dataset 
WHERE r <= 2
ORDER BY y DESC, w DESC, r ASC, song_album ASC;

Je n'ai pas de serveur PostgreSQL et tu ne fournis pas de jeu de données, mais l'idée y est.

[cedrickab]

Bonjour,
je test ça dans la journée et je vous dit ce que ça a donné.
Concernant le modèle de la base de données vous me suggérez de les séparer en combien de table? Le problème c’est que j’ai que ces 4 colonnes dans mon CSV et je vois pas comment faire des bonnes jointures si je les sépare pour mes requêtes .

[Séb.]

Concernant le modèle de la base de données vous me suggérez de les séparer en combien de table?

Ton CSV n'a que les colonnes "artist", "album", "song_album" et "date_ecoute" ? Jamais d'identifiants ? (ex. ISMN)
song_album contient un numéro de piste ? Les collaborations, les feats sont représentées ? "artist", c'est l'artiste de l'album ou de la piste ?
Tu ne donnes pas d'exemple de ta source de données...

Un exemple simpliste, avec 1 artiste par album :

artist
--------
id (PK)
name

album
--------
id (PK)
name
artist_id (FK => artist.id)

track
--------
id (PK)
number # Numéro de piste sur l'album
name
album_id (FK => album.id)

listen
--------
id (PK)
track_id (FK => track.id)
listened_at

Le problème c’est que j’ai que ces 4 colonnes dans mon CSV et je vois pas comment faire des bonnes jointures si je les sépare pour mes requêtes .

Il faudrait récupérer/définir des identifiants pour l'artiste, l'album et la piste.
Sinon tu auras du mal à gérer les homonymes et à monter en charge.
Bon après tout dépend de l'ampleur du projet et des fonctionnalités visées. Dans l'absolu tu pourrais très bien te satisfaire d'un simple import brut de ton CSV