Annonce: TSQL Challenges

**agemis31** · 08/05/2009, 14h54

Bonjour à tous,

Je vous signale, pour ceux qui veulent s'amuser tout en progressant en T-SQL, que vous pouvez trouver des défis intéressants sur TSQL Challenges

Le projet est à l'initiative de Jacob Sebastian, MVP SQL Server.

Bon amusement

@+

**SQLpro** · 11/05/2009, 14h42

Aller un petite solution au challenge 6 :

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
;
WITH 
T_DAY AS
(
SELECT DISTINCT DATEPART(weekday, VisitDate) AS WD, 
       UPPER(SUBSTRING(DATENAME(weekday, VisitDate), 1, 3)) AS WDN
FROM   @T
),
T_CUMUL AS -- summing for the finest date aggregate (day of week)
(
SELECT YEAR(VisitDate) AS Y, DATEPART(week, VisitDate) AS W, 
       DAY(VisitDate) AS D, MONTH(VisitDate) AS M,
       DATEPART(weekday, VisitDate) AS WD, Page, SUM(NbVisitors) AS CUMUL
FROM   @T
GROUP  BY YEAR(VisitDate), DATEPART(week, VisitDate), 
          DATEPART(weekday, VisitDate), DAY(VisitDate), MONTH(VisitDate), Page
),
T_PAGE AS -- retrieving all pages
(
SELECT DISTINCT Page
 FROM   @T
),
T_CROSS AS -- crossing pages and date aggregate
(
SELECT P.Page, Y, W, WD, D, M
FROM   T_PAGE AS P
       CROSS JOIN (SELECT DISTINCT Y, W, WD, D, M FROM T_CUMUL) AS C
),
T_ALL AS -- calculating all entries of weeks/month/year, transforming nulls in 0
(
SELECT M.*, COALESCE((SELECT CUMUL 
                      FROM   T_CUMUL AS C
                      WHERE  M.Page = C.Page
                        AND  M.Y = C.Y
                        AND  M.W = C.W
                        AND  M.WD = C.WD), 0) AS TOTAL
FROM   T_CROSS AS M
),
T_FLAT AS -- flatening the pages
(
SELECT Y, W, WD, D, M, 
       SUM(HOME) AS HOME, SUM(CONTACT) AS CONTACT, SUM(PRODUCTS) AS PRODUCTS
FROM   (SELECT Y, W, WD, D, M, TOTAL AS HOME, 0 AS CONTACT, 0 AS PRODUCTS
        FROM   T_ALL
        WHERE  Page = 'Home'
        UNION ALL
        SELECT Y, W, WD, D, M, 0 AS HOME, TOTAL AS CONTACT, 0 AS PRODUCTS
        FROM   T_ALL
        WHERE  Page = 'Contact'
        UNION ALL
        SELECT Y, W, WD, D, M, 0 AS HOME, 0 AS CONTACT, TOTAL AS PRODUCTS
        FROM   T_ALL
        WHERE  Page = 'Products') AS T
GROUP BY Y, W, WD, D, M
),
T_LEVEL AS -- introducing the summating levels
(
SELECT Y, W, WD, D, M, 0 AS LEVEL, HOME, CONTACT, PRODUCTS
FROM   T_FLAT
UNION ALL
SELECT Y, W, NULL, NULL, NULL, 1, SUM(HOME), SUM(CONTACT), SUM(PRODUCTS)
FROM   T_FLAT
GROUP  BY Y, W
UNION ALL
SELECT Y, NULL, NULL, NULL, NULL, 2, SUM(HOME), SUM(CONTACT), SUM(PRODUCTS)
FROM   T_FLAT
GROUP  BY Y
)
-- comectics purpose : PERIOD_ID, PERIOD
SELECT CAST(Y AS CHAR(4)) 
        + CASE WHEN W IS NULL THEN 'T' ELSE '' END
        + CASE WHEN W < 10 THEN '0' ELSE '' END 
        + COALESCE(CAST(W AS VARCHAR(2)), '')
        + CASE WHEN L.WD IS NULL AND W IS NOT NULL 
                  THEN 'T' ELSE COALESCE(CAST(L.WD AS CHAR(1)), '') 
          END AS PERIOD_ID,
       LEVEL,
       CASE WHEN W IS NULL 
               THEN 'TOTAL YEAR ' + CAST(Y AS CHAR(9))
            WHEN L.WD IS NULL AND W IS NOT NULL 
               THEN 'TOTAL WEEK ' + 
                  CASE WHEN W < 10 
                     THEN '0' ELSE '' 
                  END + COALESCE(CAST(W AS VARCHAR(2)), '') 
            ELSE '   ' + WDN + '  ' + CAST(D AS VARCHAR(2)) 
                 + '/' + CAST(M AS VARCHAR(2))
       END AS PERIOD,
       HOME, CONTACT, PRODUCTS
FROM    T_LEVEL AS L
        LEFT OUTER JOIN T_DAY AS D
              ON L.WD = D.WD
ORDER BY PERIOD_ID;
GO

A +

**elsuket** · 11/05/2009, 19h17

Bonjour,

Une autre :

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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
SET DATEFIRST 1
SET DATEFORMAT YMD
SET NOCOUNT ON
SET LANGUAGE us_english
GO
 
DECLARE @T TABLE (ID INT IDENTITY(1,1), VisitDate DATETIME, Page NVARCHAR(15),NbVisitors INT) 
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-23','Home',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-24','Home',14)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-26','Home',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Home',3)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Home',4)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-01','Home',33)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-03','Home',2)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-26','Contact',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Contact',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Contact',35)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-30','Contact',13)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Products',8)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Products',12)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-01','Products',16)
 
;WITH
	YEAR_TOTAL AS
	(
		SELECT CAST(VisitYear AS CHAR(4)) + 'T' AS PeriodID,
				2 AS LEVEL,
				53 AS VisitWeek,
				'TOTAL YEAR ' + CAST(VisitYear AS CHAR(4)) AS Period,
				Home,
				Contact,
				Products		
		FROM
		(
			SELECT YEAR(VisitDate) AS VisitYear,
					53 AS VisitWeek,
					Page,
					NbVisitors
			FROM @T
		) AS BRUT
		PIVOT
		(
			SUM(NbVisitors)
			FOR Page IN (Home, Contact, Products)
		) AS PVT
	),
	WEEK_TOTAL AS
	(
		SELECT CAST(VisitYear AS CHAR(4)) + CAST(VisitWeek AS VARCHAR(2)) + 'T' AS PeriodID,
				1 AS LEVEL,
				VisitWeek,
				'TOTAL WEEK ' + CAST(VisitWeek AS VARCHAR(2)) AS Period,
				Home,
				Contact,
				Products
		FROM
		(
			SELECT YEAR(VisitDate) AS VisitYear,
					DATEPART(week, VisitDate) AS VisitWeek,
					Page,
					NbVisitors
			FROM @T
		) AS BRUT
		PIVOT
		(
			SUM(NbVisitors)
			FOR Page IN (Home, Contact, Products)
		) AS PVT
	),
	DAY_TOTAL AS
	(
		SELECT CAST(YEAR(PeriodID) AS CHAR(4)) + CAST(DATEPART(week, PeriodID) AS VARCHAR(2)) + CAST(DATEPART(weekday, PeriodID) AS CHAR(1)) AS PeriodID,
				LEVEL,
				CAST(DATEPART(week, PeriodID) AS VARCHAR(2)) AS VisitWeek,
				Period + ' ' + CAST(DAY(Date) AS VARCHAR) + '/' + CAST(MONTH(Date) AS VARCHAR) AS Period,
				Home,
				Contact,
				Products
		FROM
		(
			SELECT PeriodID,
					0 AS LEVEL,
					LEFT(UPPER(DATENAME(weekday, PeriodID)), 3) AS Period,
					CAST(PeriodID AS DATETIME) AS Date,
					ISNULL(Home, 0) AS Home,
					ISNULL(Contact, 0) AS Contact,
					ISNULL(Products, 0) AS Products
			FROM
			(
				SELECT PeriodID, Home, Contact, Products
				FROM
				(
					SELECT VisitDate AS PeriodID,
							Page,
							NbVisitors
					FROM @T
				) AS BRUT
				PIVOT
				(
					SUM(NbVisitors)
					FOR Page IN (Home, Contact, Products)
				) AS PVT
			) AS ROTATE
		) AS GET_DAY_NAMES
	)
SELECT PeriodID, Level, Period, Home, Contact, Products
FROM
(
		SELECT PeriodID, Level, VisitWeek, Period, Home, Contact, Products
		FROM YEAR_TOTAL
	UNION
		SELECT PeriodID, Level, VisitWeek, Period, Home, Contact, Products
		FROM WEEK_TOTAL
	UNION
		SELECT PeriodID, Level, VisitWeek, Period, Home, Contact, Products
		FROM DAY_TOTAL
) AS FINAL

@++

**agemis31** · 11/05/2009, 19h30

Oups !

Pas mal

C'est marrant de voir l'approche de chacun.

Ca doit en motiver certains non ?

Comme les challenges actuels sont individuels. Merci d'envoyer vos réponses à Jacob Sebastian

Si ca vous intéresse, on peut faire une review après la publication des solutions.

@+

**Chtulus** · 11/05/2009, 22h51

Bonsoir tout le monde,

Bon je résume mon sentiment en ce moment:

=

Faut que je me mettes au boulot !

**lmalta** · 12/05/2009, 10h04

Bonjour,

Arrhh WITH ne fonctionne pas sous SQL2000 je n'ai donc pas pu tester les solutions proposées

Ma solution toute bête
trois niveaux demadés donc trois union
la colonne PERIOD_ID suffit à trier l'ensemble :

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
 
select PERIOD_ID,min(LEVEL) LEVEL ,min(PERIOD) PERIOD 
,sum(case Page when 'Home' then sumNbVisitors else 0 end) as HOME
,sum(case Page when 'Contact' then sumNbVisitors else 0 end) as CONTACT
,sum(case Page when 'Products' then sumNbVisitors else 0 end )as PRODUCTS
from 
(
select Annee+Semaine+Jour as PERIOD_ID,LEVEL ,PERIOD,Page , sum(sumNbVisitors)  sumNbVisitors from 
            (
                        select convert(char(4),YEAR(VisitDate)) as Annee,
                   'T' as Semaine,
                   ' ' as Jour,
                        2 AS LEVEL,
                        'TOTAL YEAR '+convert(char(4),YEAR(VisitDate)) AS PERIOD,
                        Page,
                        sum(NbVisitors) sumNbVisitors
                        from @T 
                    group by convert(char(4),YEAR(VisitDate))
                    ,VisitDate,Page
            ) y group by Annee+Semaine+Jour,LEVEL ,PERIOD,Page
union
select Annee+Semaine+Jour as PERIOD_ID,
LEVEL ,
PERIOD,Page ,  sum(sumNbVisitors)  sumNbVisitors  from 
            (
                        select convert(char(4),YEAR(VisitDate)) as Annee,
                   convert(varchar(2),DATEPART(wk,VisitDate)) as Semaine,
                   'T' as Jour,
                        1 AS LEVEL,
                        'TOTAL WEEK '+convert(varchar(2),DATEPART(wk,VisitDate)) AS PERIOD,
                        Page,
                        sum(NbVisitors) sumNbVisitors
                        from @T 
                    group by convert(char(4),YEAR(VisitDate)),convert(varchar(2),DATEPART(wk,VisitDate))
                    ,VisitDate,Page
            ) w group by Annee+Semaine+Jour,LEVEL ,PERIOD,Page
union
            select Annee+Semaine+Jour as PERIOD_ID,LEVEL ,PERIOD,Page , sumNbVisitors  from 
            (
                        select convert(char(4),YEAR(VisitDate)) as Annee,
                               convert(varchar(2),DATEPART(wk,VisitDate)) as Semaine,
                               convert(char(1),DATEPART(dw,VisitDate)) as Jour,
                               0 AS LEVEL,
                                    convert(char(3), Upper (DATENAME(month, VisitDate))) 
                                   +' '+convert(varchar(2),DAY(VisitDate))+'/'+convert(varchar(2),MONTH(VisitDate)) as PERIOD,
                                   Page,
                                   sum(NbVisitors) sumNbVisitors
                                   from @T group by convert(char(4),YEAR(VisitDate)),convert(varchar(2),DATEPART(wk,VisitDate)),convert(char(1),DATEPART(dw,VisitDate))
                              ,VisitDate,Page
                        ) d
) a
group by PERIOD_ID
order by 1

A+
Louis

Annonce: TSQL Challenges

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager