Discussion :

[davelop]

Bonjour à tous,

Avis aux experts en SQL pour m'aider à créer une vue sur une table dans SQL Server car je ne m'en sors pas

En simplifiant ma table voici à quoi elle ressemble :


Je voudrais faire une vue sur cette table pour effectuer ces différences comptages :


Et voici l'explications des comptages des 3 colonnes de cette vue :
1ère colonne : On compte le nombre de fois où le service (SERV?) est dans la colonne Auteur.
2ème colonne : On compte le nombre distinct de fois où le service est dans la colonne Co-auteurs sauf s’il est déjà dans la colonne Auteur.
3ème colonne : On compte le nombre de fois où le service est le seul représenté dans les 2 colonnes Auteur et Co-auteurs -->En gros 1 seule couleur (service) sur l'enregistrement.

Vous me seriez d'une grande aide, si vous trouviez comment faire cette vue.
En vous remerciant d'avance beaucoup

[escartefigue]

Bonjour

Plusieurs remarques

• dans l'exemple, pour NOTE6, SERV1 est à la fois auteur et deux fois coauteur, c'est une coquille ? Si c'est correct, comment faut-il compter les valeurs : distinctes ou toutes ?
• et surtout, cette table est très mal modélisée : une colonne ne doit jamais contenir une liste de valeurs, c'est un viol de 1NF qui induit des performances désastreuses et des requêtes complexes

Donc la première chose à faire, c'est de revoir cette table, plutôt que de construire une requête complexe (recherche dans une chaine de caractères) et contre performante (non indexable).

[CosmoKnacki]

dans l'exemple, pour NOTE6, SERV1 est à la fois auteur et deux fois coauteur, c'est une coquille ?

Je me suis posé la même question et j'en suis arrivé à la conclusion que non. Il y a une entité qui n'apparait pas, c'est l'employé (qui appartient à un service). Donc si plusieurs employés distincts d'un même service rédige une note, on peut dire par abus de langage qu'un service est auteur principale d'une note et plusieurs fois co-auteur de cette même note.

[davelop]

Bonjour escartefigue et CosmoKnacki ,

Merci de vos réponses.

Alors j'ai représenté la table comme ça pour simplifier la demande mais la structure de la base est plutôt comme ça en simplifiant toujours un peu :
Annuaire_1 et Annuaire_2 sont bien une seule table



- 1 note possède 1 seul auteur : la table "Notes" est lié à la table "annuaire" pour récupérer le service de l'auteur
- 1 note possède 0 à N co-auteur : la table "notes_coauteurs" fais le lien avec la table annuaire pour récupérer les services des co-auteurs.

Et donc je voudrais faire les 3 comptages de notes par service comme décris dans mon 1er message.
Oui c'est possible qu'il y est plusieurs fois le même service co-auteurs car 2 personnes du même service peuvent être co-auteur mais dans ce cas on ne le compte qu'une fois.

J'espère que ma demande est plus claire. Merci encore.

[SQLpro]

Vous pouvez vous en sortir comme cela :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
WITH
TA AS
(
SELECT Auteur, COUNT(*) AS Nb_notes_Auteur
FROM   voici_à_quoi_elle_ressemble
GROUP  BY Auteur
),
TC AS
(
SELECT DISTINCT table_notes, TRIM(value) AS CoAuteur 
FROM   voici_à_quoi_elle_ressemble
       OUTER APPLY string_split(Co_auteurs, ',') 
WHERE  Auteur <> TRIM(value) 
),
TCD AS
(
SELECT CoAuteur, COUNT(*) AS NB_Notes_CoAuteur
FROM   TC
GROUP  BY CoAuteur
),
TT AS
(
SELECT DISTINCT Auteur, table_notes, TRIM(value) AS CoAuteur 
FROM   voici_à_quoi_elle_ressemble AS R
       OUTER APPLY string_split(Co_auteurs, ',') 
),
TTT AS
(
SELECT DISTINCT Auteur, table_notes, CoAuteur, 
       COUNT(*) OVER(PARTITION BY table_notes) AS N
FROM   TT
),
AA AS
(
SELECT Auteur
FROM   TA
UNION
SELECT CoAuteur
FROM   TC
)
SELECT AA.Auteur, 
       COALESCE(Nb_notes_Auteur, 0) AS Nb_notes_Auteur, 
       COALESCE(NB_Notes_CoAuteur, 0) AS NB_Notes_CoAuteur,
       COALESCE(CASE N WHEN 1 THEN 1 ELSE 0 END, 0) AS Nb_notes_avec_tous_les_auteurs
FROM   AA
       LEFT OUTER JOIN TA ON AA.Auteur = TA.Auteur
       LEFT OUTER JOIN TCD ON AA.Auteur = TCD.CoAuteur
       LEFT OUTER JOIN TTT ON AA.Auteur = TTT.Auteur AND TTT.N = 1;

Résultat :

Code :

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

1
2
3
4
5
6
7
8
9
Auteur       Nb_notes_Auteur NB_Notes_CoAuteur Nb_notes_avec_tous_les_auteurs
------------ --------------- ----------------- ------------------------------
SERV1        3               1                 1
SERV2        1               1                 0
SERV3        0               2                 0
SERV4        1               1                 0
SERV5        1               1                 1
SERV6        0               1                 0
SERV7        0               1                 0

Jeux d'essais :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE voici_à_quoi_elle_ressemble
(
table_notes VARCHAR(16),
Auteur VARCHAR(16),
Co_auteurs VARCHAR(8000)
);
 
INSERT INTO voici_à_quoi_elle_ressemble VALUES
('Note 1', 'SERV1', 'SERV2, SERV3, SERV4'),
('Note 2', 'SERV2', 'SERV2, SERV5'),
('Note 3', 'SERV5', 'SERV5'),
('Note 4', 'SERV4', 'SERV6, SERV1'),
('Note 5', 'SERV1', 'SERV3, SERV7, SERV7'),
('Note 6', 'SERV1', 'SERV1, SERV1');

La prochaine fois merci de respecter la charte de postage : https://www.developpez.net/forums/a6...gage-sql-lire/
notamment en postant le DDL de vos tables

A +

[skuatamad]

Une autre piste en utilisant le model normalisé présenté après :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
  with note_unique_co_auteur as (
select nca.id_note
  from annuaire a2 
  join note_coauteur nca on nca.id_co_auteur = a2.id_annuaire
 group by nca.id_note
having count(distinct a2.service) = 1 
)  
     , note_auteur_est_seul_co_auteur as (
select nca.id_note
  from annuaire a2 
  join note_coauteur nca on nca.id_co_auteur = a2.id_annuaire  
 where -- Seul co auteur
        exists (select 1 
                 from note_unique_co_auteur x
                where x.id_note = nca.id_note)
    -- co auteur est aussi auteur
   and exists (select 1 
                 from annuaire a 
                 join note n on n.id_auteur = a.id_annuaire 
                where n.id_note = nca.id_note
                  and a.service = a2.service)  
)
     , col3 as (
select a.service
     , count(*) as nb_aut_seul_co_aut
  from annuaire a 
  join note n on n.id_auteur = a.id_annuaire
 where exists (select 1 
                 from note_auteur_est_seul_co_auteur x
                where x.id_note = n.id_note)
 group by a.service  
)
     , col1 as (
select a.service
     , count(n.id_note) as nb_note_auteur
  from annuaire a
  left join note n on n.id_auteur = a.id_annuaire
 group by a.service 
)
     , col2 as (
select a2.service
     , count(distinct nca.id_note) as nb_dist_co_aut_pas_aut
  from annuaire a2 
  join note_coauteur nca on nca.id_co_auteur = a2.id_annuaire
 where not exists (select 1 
                     from annuaire a
                     join note n on n.id_auteur = a.id_annuaire
					where n.id_note = nca.id_note
                      and a.service = a2.service)
 group by a2.service 
)
select c1.service
     , c1.nb_note_auteur
	 , coalesce (c2.nb_dist_co_aut_pas_aut,0)
	 , coalesce (c3.nb_aut_seul_co_aut, 0)
  from col1 c1
  left join col2 c2 on c2.service = c1.service
  left join col3 c3 on c3.service = c1.service
  order by service;

En fabriquant le jeu de test suivant (sous oracle d'où les from dual) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
create table annuaire as 
select 1 as id_annuaire, 'serv1' as service from dual union all
select 2 as id_annuaire, 'serv2' as service from dual union all
select 3 as id_annuaire, 'serv3' as service from dual union all
select 4 as id_annuaire, 'serv4' as service from dual union all
select 5 as id_annuaire, 'serv5' as service from dual union all
select 6 as id_annuaire, 'serv6' as service from dual union all
select 7 as id_annuaire, 'serv7' as service from dual union all
select 8 as id_annuaire, 'serv1' as service from dual union all
select 9 as id_annuaire, 'serv7' as service from dual union all
select 10 as id_annuaire, 'serv5' as service from dual union all
select 11 as id_annuaire, 'serv1' as service from dual;
 
 
create table note as
select 1 as id_note, 1 as id_auteur from dual union all
select 2 as id_note, 2 as id_auteur from dual union all
select 3 as id_note, 5 as id_auteur from dual union all
select 4 as id_note, 4 as id_auteur from dual union all
select 5 as id_note, 1 as id_auteur from dual union all
select 6 as id_note, 8 as id_auteur from dual;
 
create table note_coauteur as 
select 1 as id_note, 2 as id_co_auteur from dual union all
select 1 as id_note, 3 as id_co_auteur from dual union all
select 1 as id_note, 4 as id_co_auteur from dual union all
select 2 as id_note, 2 as id_co_auteur from dual union all
select 2 as id_note, 5 as id_co_auteur from dual union all
select 3 as id_note, 5 as id_co_auteur from dual union all
select 4 as id_note, 6 as id_co_auteur from dual union all
select 4 as id_note, 1 as id_co_auteur from dual union all
select 5 as id_note, 3 as id_co_auteur from dual union all
select 5 as id_note, 7 as id_co_auteur from dual union all
select 5 as id_note, 9 as id_co_auteur from dual union all
select 6 as id_note, 1 as id_co_auteur from dual union all
select 6 as id_note, 11 as id_co_auteur from dual;

[davelop]

Super !
Merci à tous les 2 d'avoir pris le temps de m'aider à écrire ces 2 longues requêtes et de quoi tester avec la création des tables et leurs jeux d'enregistrements.
Je testerai cela lundi et vous tiendrais au courant.

Et désolé pour la DDL que je n'ai pas fourni.
J'ai bien lu la charte pour la prochaine fois

C'est top !
Bon weekend !

[skuatamad]

Ma 1ere solution me semblait un peu trop bourrin, mais au final je ne sais pas si celle là est vraiment plus subtile.
A voir en terme de perf et/ou de préférence pour la lisibilité.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
  with nb_note_co_auteur as (
select a2.service
     , nca.id_note
     , count(distinct a2.service) over (partition by nca.id_note) as nb_service_distinct_note
  from annuaire a2 
  join note_coauteur nca on nca.id_co_auteur = a2.id_annuaire
)
     , donnee as (
select a.service as service_auteur
     , n.id_note
     , nb_co.service as service_co_auteur
     , nb_co.nb_service_distinct_note as nb_service_distinct_note
  from annuaire a
  join note n on n.id_auteur = a.id_annuaire
  left join nb_note_co_auteur nb_co on nb_co.id_note = n.id_note
)
    -- à remplacer par la table des services si elle existe
     , service as (
select distinct service from ANNUAIRE
)
select s.service
     , count(distinct d3.id_note) as nb_note_auteur
     , count(distinct case when s.service = d2.service_co_auteur
                            and d2.service_auteur <> d2.service_co_auteur 
                           then d2.id_note
                       end) as nb_dist_co_aut_pas_aut
     , count(distinct case when s.service = d3.service_auteur
                            and d3.service_auteur = d3.service_co_auteur
                            and d3.nb_service_distinct_note = 1
                           then s.service
                       end) as nb_aut_seul_co_aut  
  from service s
  left join donnee d2 on d2.service_co_auteur = s.service
  left join donnee d3 on d3.service_auteur = s.service
 group by s.service
 order by s.service;

[davelop]

Merci ! C'est festival de possibilités à tester
Je verrais cela lundi au boulot.

Je vais abuser, mais est-il possible d'y intégrer ces comptages dans des group by annee, mois.
Je m'explique :
En fait, dans la table Notes, il y a un champ date_note et la finalité est d'avoir un regroupement de ces 3 comptages sur YEAR(date_note), MONTH(date_note)

En gros dans le résultat les champs :
Annee / Mois / Service / nb_notes_auteur / nb_notes_coauteurs / nb_notes_tousAuteurs

Et là c'est le top du top

[davelop]

Ma 1ere solution me semblait un peu trop bourrin, mais au final je ne sais pas si celle là est vraiment plus subtile.
A voir en terme de perf et/ou de préférence pour la lisibilité.

Bonjour skuatamad,

La 1ère solution fonctionne très bien.
Mais la 2ème solution m'indique une erreur : L'utilisation de DISTINCT n'est pas autorisée avec la clause OVER.

Mais la 1ère solution me convient très bien sinon.
En intégrant des "group by" pour l'année et le mois comme indiqué dans mon dernier message, ça serait parfait

Merci encore

[skuatamad]

Je ne sais pas pourquoi il y a une erreur sur le count distinct analytique car je n'ai pas sqlserver.

Concernant l'ajout de l'année et du mois j'imagine que la modification suivante doit suffire, si tel n'est pas le cas merci de fournir un jeu de test adapté.
J'ai utilisé DATEPART pour extraire le mois et l'année, à adapter en cas d'erreur de syntaxe, je n'ai pas testé cette requête.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
  with note_unique_co_auteur as (
select nca.id_note
  from annuaire a2 
  join note_coauteur nca on nca.id_co_auteur = a2.id_annuaire
 group by nca.id_note
having count(distinct a2.service) = 1 
)  
     , note_auteur_est_seul_co_auteur as (
select nca.id_note
  from annuaire a2 
  join note_coauteur nca on nca.id_co_auteur = a2.id_annuaire  
 where -- Seul co auteur
        exists (select 1 
                 from note_unique_co_auteur x
                where x.id_note = nca.id_note)
    -- co auteur est aussi auteur
   and exists (select 1 
                 from annuaire a 
                 join note n on n.id_auteur = a.id_annuaire 
                where n.id_note = nca.id_note
                  and a.service = a2.service)  
)
     , col3 as (
select datepart(year, n.ladate) as annee
     , datepart(month, n.ladate) as mois
     , a.service
     , count(*) as nb_aut_seul_co_aut
  from annuaire a 
  join note n on n.id_auteur = a.id_annuaire
 where exists (select 1 
                 from note_auteur_est_seul_co_auteur x
                where x.id_note = n.id_note)
 group by datepart(year, n.ladate)
     , datepart(month, n.ladate)
     , a.service
)
     , col1 as (
select datepart(year, n.ladate) as annee
     , datepart(month, n.ladate) as mois
     , a.service
     , count(n.id_note) as nb_note_auteur
  from annuaire a
  left join note n on n.id_auteur = a.id_annuaire
 group by datepart(year, n.ladate)
     , datepart(month, n.ladate)
     , a.service
)
     , col2 as (
select datepart(year, n2.ladate) as annee
     , datepart(month, n2.ladate) as mois
     , a2.service
     , count(distinct nca.id_note) as nb_dist_co_aut_pas_aut
  from annuaire a2 
  join note_coauteur nca on nca.id_co_auteur = a2.id_annuaire
  join note n2 on n2.id_note = nca.id_note
 where not exists (select 1 
                     from annuaire a
                     join note n on n.id_auteur = a.id_annuaire
					where n.id_note = nca.id_note
                      and a.service = a2.service)
 group by datepart(year, n2.ladate)
     , datepart(month, n2.ladate)
     , a2.service 
)
select c1.annee
     , c1.mois
     , c1.service
     , c1.nb_note_auteur
     , coalesce (c2.nb_dist_co_aut_pas_aut,0)
     , coalesce (c3.nb_aut_seul_co_aut, 0)
  from col1 c1
  left join col2 c2 
    on c2.annee = c1.annee
   and c2.mois = c1.mois
   and c2.service = c1.service
  left join col3 c3 
    on c3.annee = c1.annee
   and c3.mois = c1.mois
   and c3.service = c1.service
  order by c1.annee
     , c1.mois
     , c1.service;

[davelop]

Pas grave pour la 2ème requête, la 1ère me convient bien
Merci beaucoup

j'ai donc testé avec l'intégration de la date, cela semble fonctionner mais j'ai des valeurs à null, je regarderai plus précisément dès que possible.

En tout ca c'est super sympa de prendre tout ce temps pour m'aider.
Je me rend compte que j'ai de grosses lacunes en SQL

Je te fournirai le jeu de test de ce résultat demain

Code :

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

1
2
3
4
5
6
7
8
9
10
11
NULL	NULL	serv1	0	0	0
NULL	NULL	serv3	0	0	0
NULL	NULL	serv5	0	0	0
NULL	NULL	serv6	0	0	0
NULL	NULL	serv7	0	0	0
2023	2	serv1	1	0	0
2023	2	serv2	1	1	0
2023	3	serv1	2	1	1
2023	3	serv4	1	0	0
2023	3	serv5	1	0	1
2023	3	serv9	1	0	0