Vue SQL compliquée

**davelop** · 24/03/2023, 11h54

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 :
Nom : Table.PNG
Affichages : 229
Taille : 7,7 Ko

Je voudrais faire une vue sur cette table pour effectuer ces différences comptages :
Nom : Vue.PNG
Affichages : 235
Taille : 8,0 Ko

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** · 24/03/2023, 13h36

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** · 24/03/2023, 14h03

Envoyé par escartefigue

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** · 24/03/2023, 14h15

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

Nom : base.PNG
Affichages : 186
Taille : 17,1 Ko

Nom : base.PNG
Affichages : 186
Taille : 17,1 Ko

- 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** · 24/03/2023, 16h28

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** · 24/03/2023, 17h04

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** · 24/03/2023, 17h52

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** · 24/03/2023, 20h47

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** · 24/03/2023, 21h10

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** · 27/03/2023, 10h13

Envoyé par 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é.

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** · 27/03/2023, 15h27

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** · 27/03/2023, 16h25

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

**davelop** · 28/03/2023, 08h32

Bonjour skuatamad et à tous,

Alors voici les scripts de créations de tables et jeux d'enregistrements pour les tests (par contre sous SQL Server) :

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
83
84
85
86
CREATE TABLE [dbo].[annuaire](
	[id_annuaire] [int] NULL,
	[service] [nvarchar](50) NULL
) ON [PRIMARY]
 
 
CREATE TABLE [dbo].[note](
	[id_note] [int] NULL,
	[id_auteur] [int] NULL,
	[date_note] [smalldatetime] NULL
) ON [PRIMARY]
 
 
CREATE TABLE [dbo].[note_coauteur](
	[id_note] [int] NOT NULL,
	[id_co_auteur] [int] NOT NULL,
 CONSTRAINT [PK_note_coauteur] PRIMARY KEY CLUSTERED 
(
	[id_note] ASC,
	[id_co_auteur] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (1, N'serv1')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (2, N'serv2')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (3, N'serv3')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (4, N'serv4')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (5, N'serv5')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (6, N'serv6')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (7, N'serv7')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (8, N'serv1')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (9, N'serv7')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (10, N'serv5')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (11, N'serv1')
 
INSERT [dbo].[annuaire] ([id_annuaire], [service]) VALUES (12, N'serv9')
 
INSERT [dbo].[note] ([id_note], [id_auteur], [date_note]) VALUES (1, 1, CAST(N'2023-02-05T00:00:00' AS SmallDateTime))
 
INSERT [dbo].[note] ([id_note], [id_auteur], [date_note]) VALUES (2, 2, CAST(N'2023-02-05T00:00:00' AS SmallDateTime))
 
INSERT [dbo].[note] ([id_note], [id_auteur], [date_note]) VALUES (3, 5, CAST(N'2023-03-05T00:00:00' AS SmallDateTime))
 
INSERT [dbo].[note] ([id_note], [id_auteur], [date_note]) VALUES (4, 4, CAST(N'2023-03-05T00:00:00' AS SmallDateTime))
 
INSERT [dbo].[note] ([id_note], [id_auteur], [date_note]) VALUES (5, 1, CAST(N'2023-03-05T00:00:00' AS SmallDateTime))
 
INSERT [dbo].[note] ([id_note], [id_auteur], [date_note]) VALUES (6, 8, CAST(N'2023-03-05T00:00:00' AS SmallDateTime))
 
INSERT [dbo].[note] ([id_note], [id_auteur], [date_note]) VALUES (7, 12, CAST(N'2023-03-05T00:00:00' AS SmallDateTime))
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (1, 2)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (1, 3)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (1, 4)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (2, 2)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (2, 5)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (3, 5)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (4, 1)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (4, 6)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (5, 3)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (5, 7)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (5, 9)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (6, 1)
 
INSERT [dbo].[note_coauteur] ([id_note], [id_co_auteur]) VALUES (6, 11)

--> Dans 7 notes dans la table "note" dont 2 en février et 5 en mars

Résultat avec ta 1ère requête sans l'intégration de la date :

Code :

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

1
2
3
4
5
6
7
8
9
 
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
serv9	1	0	0

Résultat avec ta requête en intégrant les "group by annee, mois"

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
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

Merci encore

**skuatamad** · 28/03/2023, 10h57

Effectivement les NULLs viennent de la sous-requête COL1 et de la jointure externe.
Je ne pense pas qu'on puisse encore piloter la requête via COL1, il faut déterminer les 3 1eres colonnes indépendemment des calculs.
Quelque chose comme ça :

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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
  with table_service as (
    -- à supprimer si une table des services existent
select distinct service
  from annuaire
)
     , annee_mois as (
	-- idéalement à remplacer par une génération des couples (année,mois) sur la période annalysée
select distinct datepart(year, n.ladate) as annee
     , datepart(month, n.ladate) as mois
  from note n
)
     , service_annee_mois as (
select annee, mois, service
  from annee_mois
 cross join table_service
) 
     , 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
  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 sam.annee
     , sam.mois
     , sam.service
     , coalesce (c1.nb_note_auteur,0)
     , coalesce (c2.nb_dist_co_aut_pas_aut,0)
     , coalesce (c3.nb_aut_seul_co_aut, 0)
  from service_annee_mois sam
  left join col1 c1 
    on c1.annee  = sam.annee
   and c1.mois    = sam.mois
   and c1.service = sam.service
  left join col2 c2 
    on c2.annee  = sam.annee
   and c2.mois    = sam.mois
   and c2.service = sam.service
  left join col3 c3 
    on c3.annee  = sam.annee
   and c3.mois    = sam.mois
   and c3.service = sam.service
 order by sam.annee
     , sam.mois
     , sam.service;

**davelop** · 28/03/2023, 11h35

Et bien c'est nickel tout ça !

Je retrouve bien les comptes dans chacune des 3 colonnes après ce découpage par mois.

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
annee	mois	service	nb_note_auteur	nb_note_coauteur	nb_note_tousauteur
2023	3	serv1	2	1	1
2023	3	serv2	0	0	0
2023	3	serv3	0	1	0
2023	3	serv4	1	0	0
2023	3	serv5	1	0	1
2023	3	serv6	0	1	0
2023	3	serv7	0	1	0
2023	3	serv9	1	0	0
2023	2	serv1	1	0	0
2023	2	serv2	1	1	0
2023	2	serv3	0	1	0
2023	2	serv4	0	1	0
2023	2	serv5	0	1	0
2023	2	serv6	0	0	0
2023	2	serv7	0	0	0
2023	2	serv9	0	0	0

Il ne me reste plus qu'à adapter ces requêtes à mes vrais tables et champs.
Encore un grand merci pour ton aide précieuse.

Je mets la discussion en résolue dès que j'ai terminé.

**skuatamad** · 28/03/2023, 14h17

Dans un soucis de performance afin d'éviter le DISTINCT sur les date la table NOTE pour générer les couples (année, mois) il est préférable de générer cette partie, soit en s'appuyant sur une table calendrier, soit à la volée.
J'ai mis des dates fixe comme si c'était des paramètres mais on peut utiliser le min et le max des dates de la table NOTE à la place.

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
with t as (
select CAST(N'2022-02-01T00:00:00' AS SmallDateTime) as date_debut
     , CAST(N'2023-03-01T00:00:00' AS SmallDateTime) as date_fin
     , DATEDIFF(month, CAST(N'2022-02-01T00:00:00' AS SmallDateTime), CAST(N'2023-03-31T00:00:00' AS SmallDateTime)) as nb_mois
)
     , cte AS (
SELECT date_debut as la_date
     , 1 as i   
     , nb_mois as fin  
  FROM t
 UNION ALL
SELECT DATEADD (month , 1 , la_date )   
     , i + 1  
     , fin 
  FROM cte
 WHERE i < fin 
)
select cte.*
     , datepart(year, la_date) as annee
     , datepart(month, la_date) as mois
  from cte
 order by i ;

A partir de sqlserver 2022 il y a GENERATE_SERIES qui allège le code.

**davelop** · 28/03/2023, 16h28

Donc il faudrait simplement remplacer ceci dans la parenthèse du annee_mois as (...) de ton script précédent ?

Puis si ça fonctionne, remplacer les dates indiquées en dur par un "select min(ladate) from note as date_debut" et "select max(ladate) from note as date_fin"

Désolé je suis un peu largué avec mon niveau de SQL

**davelop** · 29/03/2023, 09h01

Bonjour skuatamad,

J'ai remarqué une erreur, du moins je me suis mal exprimé pour le comptage de la colonne 3
Quand je disais dans mon 1er message :

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.

En fait je voulais dire qu'on compte le nombre de fois où le service est seul auteur que se soit uniquement l'auteur de ce service (sans co-auteur) ou l'auteur et le(s) co-auteur(s) soit du même service.
Donc en résumé il faut ajouter au comptage de cette colonne 3, les notes rédigées par une personne du service tout seul sans co-auteur.
On compte l'id_note même s'il n'y pas de correspondance de cet id dans la table "note_coauteur"

Désolé de n'avoir pas été bien clair à ce sujet.
Et j'espère que cela n'impacte pas trop la requête. Je vois où ça se situe mais je ne sais pas comment la modifier...

Merci d'avance et promis c'est la dernière fois

**davelop** · 29/03/2023, 11h23

Je pense avoir trouvé en ajoutant cette condition "not exists" ci dessous en rouge à la définition de nb_aut_seul_co_aut pour la colonne 3:

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
 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)
           or not exists (select 1 
                               from note_coauteur nco
                               where nco.id_note = n.id_note)
                                         
 group by datepart(year, n.ladate)
     , datepart(month, n.ladate)
     , a.service
)

A ton avis ?

Vue SQL compliquée

Langage SQL

Discussions similaires

Partager

Partager