Article complet: Créer une table de dates
10/07/2009
, elsuket [SGBD][SQL Server] Créer une table de dates
Pourquoi ne pas créer une table de dates pour se faciliter les recherches dans des tables suivant la colonne de type date qu'elles contiennent ?
On peut vous demander par exemple de rechercher la quantité d'eau consommée par un parc de machines pour la deuxième semaine de chaque mois d'une année.
Imaginez un peu la complexité de la requête à écrire ...
Voyons comment on peut résoudre cette demande très facilement en créant une table de dates ...
Voici donc notre table :
---------------------------------
-- Nicolas SOUQUET - 10/07/2009 -
---------------------------------
CREATE TABLE TbDates
(
date DATETIME NOT NULL CONSTRAINT PK_TbDates_date PRIMARY KEY,
annee AS (YEAR(date)) PERSISTED NOT NULL,
mois AS(MONTH(date)) PERSISTED NOT NULL,
semaineDansAnnee AS(DATEPART(week, date)) PERSISTED NOT NULL,
semaineDansMois AS(DATEPART(week, date) - DATEPART(week, DATEADD(day, -DATEPART(day, date) + 1, date)) + 1) PERSISTED NOT NULL,
jourDansAnnee AS (DATEPART(dayofyear, date)) PERSISTED NOT NULL,
jourDansMois AS(DAY(date)) PERSISTED NOT NULL,
jourDansSemaine TINYINT NULL CONSTRAINT CHK_TbDates_jourDansSemaine CHECK(jourDansSemaine BETWEEN 0 AND 7),
)
GO
Comme toutes les colonnes sont calculées à l'exception de jourDansSemaine (parce que la fonction DATEPART(weekday, date) n'est pas déterministe, elle ne peut pas être utilisée comme spécification d'une colonne calculée), nous n'aurons donc qu'à insérer les dates dans la colonne date.
Comment allons-nous valuer la colonne jourDansSemaine ? Avec le trigger suivant :
---------------------------------------------------------------------------------
-- Nicolas SOUQUET - 10/07/2009 - Déclencheur qui calcule le jour de la semaine -
-- pour la table dbo.TbDates -
---------------------------------------------------------------------------------
CREATE TRIGGER TR_A_I_TbDates
ON dbo.TbDates
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.TbDates
SET jourDansSemaine = DATEPART(weekday, D.date)
FROM dbo.TbDates AS D
JOIN INSERTED AS I
ON D.date = I.date
END
GO
Et pour stocker les dates, il suffira d'exécuter la procédure stockée suivante :
-----------------------------------------------------------------------------------------
-- Nicolas SOUQUET - 10/07/2009 - Procédure de génération de tous les jours d'une année -
-----------------------------------------------------------------------------------------
ALTER PROCEDURE Ps_GenereDatesAnnee
@annee SMALLINT
AS
BEGIN
SET NOCOUNT ON
DECLARE @dateDeb DATETIME,
@dateFin DATETIME
SELECT @dateDeb = CAST(CAST(@annee AS VARCHAR) + '0101' AS DATETIME),
@dateFin = CAST(CAST(@annee AS VARCHAR) + '1231' AS DATETIME)
;WITH
CTE_DATES AS
(
SELECT @dateDeb AS Date
UNION ALL
SELECT Date + 1
FROM CTE_DATES
WHERE Date <= @dateFin
)
SELECT Date
FROM CTE_DATES
WHERE Date <= @dateFin
OPTION (MAXRECURSION 366)
END
Ainsi pour stocker tous les jours de l'année 2009, il suffira d'exécuter :
---------------------------------
-- Nicolas SOUQUET - 10/07/2009 -
---------------------------------
ALTER TABLE dbo.TbDates
ALTER COLUMN jourDansSemaine TINYINT NULL
GO
INSERT INTO dbo.TbDates (date)
EXEC dbo.Ps_GenereDatesAnnee 2009
GO
ALTER TABLE dbo.TbDates
ALTER COLUMN jourDansSemaine TINYINT NOT NULL
GO
Pour voir ce que contient la table après cette insertion :
---------------------------------
-- Nicolas SOUQUET - 10/07/2009 -
---------------------------------
SELECT date, annee, mois, semaineDansAnnee, semaineDansMois, jourDansAnnee, jourDansMois, jourDansSemaine
FROM dbo.TbDates
Finalement, pour rechercher la quantité d'eau consommée par un parc de machines pour la deuxième semaine de chaque mois de l'année 2009, nous devrons écrire :
---------------------------------
-- Nicolas SOUQUET - 10/07/2009 -
---------------------------------
SELECT SUM(T.conso) AS consoDeuxiemeSemaine2009
FROM dbo.maTable AS T
JOIN dbo.TbDates AS D
ON T.colonneDate = D.date
WHERE D.semaineDansLeMois = 2
AND D.annee = 2009
Une autre exemple : rechercher la consommation moyenne d'eau par semaine dans le mois, pour toute l'année 2009 :
---------------------------------
-- Nicolas SOUQUET - 10/07/2009 -
---------------------------------
SELECT semaineDansLeMois, AVG(consoSemaine)
FROM
(
SELECT D.semaineDansLeMois, SUM(T.conso) AS consoSemaine
FROM dbo.maTable AS T
JOIN dbo.TbDates AS D
ON T.colonneDate = D.date
WHERE D.annee = 2009
GROUP BY D.semaineDansLeMois
) AS SUB (semaineDansLeMois, consoSemaine)
GROUP BY semaineDansLeMois
ElSuket
Social Bookmarking:
Commentaires, Pingbacks:
"AS SUB(semaineDansLeMois,consoSemaine)"
Quel impact de repréciser les deux colonnes déjà présentes dans le SELECT?
17/05/2010 @ 22:58 Je pense que cela n'aucun impact; c'est plus une habitude de codage qui permet de trouver rapidement les colonnes dans une longue requête.
En revanche il est possible que cela ait un impact si les colonnes sont aliasées (elles n'auraient pas le même nom dans la table dérivée que dans la requête qui la définit).
Je teste et je vous tiens au courant :)
18/05/2010 @ 05:15 Je précise que j'emprunte la méthodo à SQLPro.
On commence par créer une table contenant les chiffres de 0 à 9, qui pourra reservir ensuite à d'autres cas similaires :
CREATE TABLE Chiffres (Num TINYINT) ;
INSERT INTO Chiffres (Num) VALUES (0) ;
INSERT INTO Chiffres (Num) VALUES (1) ;
INSERT INTO Chiffres (Num) VALUES (2) ;
INSERT INTO Chiffres (Num) VALUES (3) ;
INSERT INTO Chiffres (Num) VALUES (4) ;
INSERT INTO Chiffres (Num) VALUES (5) ;
INSERT INTO Chiffres (Num) VALUES (6) ;
INSERT INTO Chiffres (Num) VALUES (7) ;
INSERT INTO Chiffres (Num) VALUES (8) ;
INSERT INTO Chiffres (Num) VALUES (9) ;
Ensuite, il n'y a plus qu'à faire un petit produit cartésien de cette table par elle-même, autant de fois que de puissances de 10 nécessaires :
INSERT INTO dbo.TbDates (Date)
SELECT CAST('2009-01-01' AS DATETIME) + U + 10 * D + 100 * C
FROM Chiffres U
CROSS JOIN Chiffres D
CROSS JOIN Chiffres C
WHERE CAST('2009-01-01' AS DATETIME) + U + 10 * D + 100 * C
21/07/2010 @ 14:01
INSERT INTO dbo.TbDates (Date)
Je reprends la requête en entier :
SELECT CAST('2009-01-01' AS DATETIME) + U + 10 * D + 100 * C
FROM Chiffres U
CROSS JOIN Chiffres D
CROSS JOIN Chiffres C
WHERE CAST('2009-01-01' AS DATETIME) + U + 10 * D + 100 * C
21/07/2010 @ 14:02 INSERT INTO dbo.TbDates (Date)
SELECT CAST('2009-01-01' AS DATETIME) + U + 10 * D + 100 * C
FROM Chiffres U
CROSS JOIN Chiffres D
CROSS JOIN Chiffres C
WHERE CAST('2009-01-01' AS DATETIME) + U + 10 * D + 100 * C
21/07/2010 @ 14:04 WHERE CAST('2009-01-01' AS DATETIME) + U + 10 * D + 100 * C
21/07/2010 @ 14:07 Vous devez être identifié pour poster un commentaire.
Liste des blogs
Le blog d'ElSuket
ElSüket
Catégories
Rechercher
| Lun | Mar | Mer | Jeu | Ven | Sam | Dim |
|---|---|---|---|---|---|---|
| 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 |
Liens
Indexation
- [SGBD][SQL Server] Différence entre fragmentation interne et externe d'un index
Quand on parle de la fragmentation physique des indexes, on parle aussi de fragmentation externe. Celle-ci se "produit" lorsque l'ordre logique des pages de l'index est incorrect. Les nouvelles valeurs de clé de l'index sont alors insérées dans de nouvelles pages d'index, qui désordonnent l'ordre original de la clé de l'index.
Quand on parle de fragmentation logique de l'index, on parle aussi de fragmentation interne : c'est le cas lorsque la quantité de données stockée dans les pages de l'index est plus petite que la quantité maximale de données que peut stocker une page.
ElSuket
- [.NET][SGBD][SQL Server] Lister les caractéristiques des indexes sous SQL Server 2005 et 2008
Voici une requête qui nous permet de retrouver pour tout index :
- la liste de ses colonnes clé
- la liste de ses colonnes incluses
- la définition de son filtre
- le script de création de cet index
- [SGBD][SQL Server] Lister les colonnes des index d'une base de données
Voici une petite requête qui permet de lister les colonnes de tous les index d'une base de données, avec leur type et l'ordre des colonnes dans la clé de l'index :
]suite
- [SGBD][SQL Server] Purge du cache de plans
Il est possible qu'un jour vous trouviez dans les journaux de SQL Server le libellé suivant :
SQL Server has encountered n occurrence(s) of cachestore flush for the (partie du cache de plans) cachestore due to some database maintenance or reconfigure operations"
Ce message n'apparaît qu'à partir du SP2 de SQL Server 2005, et il est écrit par intervalles de 5 minutes.
La purge du cache de plans peut se produire dans les cas suivants :
]suite
- [SGBD][SQL Server] Recherche d'indexes manquants sous SQL Server 2005
Une nouvelle fonctionnalité intéressante, introduite avec SQL Server 2005, est la recherche d’indexes manquants.
Elle permet, de façon très simple, de trouver les indexes manquants qui pourraient simplifier le travail
du moteur de base de données s’ils étaient posés sur des tables de base ou des vues indexées.
Néanmoins, cette fonctionnalité comporte quelques limitations, qui doivent être prise en compte avant qu’on
ait décidé de créer l’index conseillé par SQL Server.
Comme vous le verrez, plusieurs sujets sont connexes à cet article, mais nous ne les aborderons pas ici.
Ils seront l’objet de prochains articles.
- [.NET][SGBD][SQL Server] Rechercher les index inutiles
Si les index représentent l'optimisation la plus simple à mettre en place, on souhaite néanmoins conserver le minimum d'entre-eux, car leur maintenance lors de l'exécution de requêtes de modifications de données (INSERT, UPDATE, DELETE) peut être coûteuse, surtout sur des tables volumineuses.
Voyons comment collecter cette information ...
]suite
- [.NET][SGBD][SQL Server] Une procédure pour connaître l'état physique et l'utilisation des index
Voici une petite procédure stockée qui permet de connaître l'état physique des index (nombre de pages du niveau feuille, fragmentation et taux d'utilisation des pages) en même temps que la façon dont ils sont utilisés (nombres de seeks et de scans, ...).
Elle est utilisable pour collecter ces statistiques sur l'ensemble d'une base de données, ou bien sur une table en particulier
]suite
- [SGBD][SQL Server] Une procédure stockée pour défragmenter les indexes sous SQL Server 2005 et ultérieur
Voici une petite procédure stockée que l'on peut exécuter régulièrement dans un job pour défragmenter les indexes de toutes les bases de données, en fixant les seuils de nombre de page et de pourcentage moyen de fragmentation
- [SGBD][SQL Server] [Agent SQL Server] Gestion de l'historique
Outre sa principale fonctionnalité de gestion d'exécutions, l'Agent SQL Server comporte quelques fonctionnalités intéressantes concernant la gestion de son historique.
]suite
Snippets
- [SGBD][SQL Server] Calculer le Buffer Cache Hit Ratio
S'il est une valeur intéressante à suivre pour le performances d'un serveur, c'est bien le Buffer Cache Hit Ratio, ou Taux d'accès au cache des tampons, car celui-ci est un rapport du nombre de pages lues en RAM par rapport au nombre de pages lues à partir des disques.
Voici une petite requête pour trouver sa valeur :
- [SGBD][SQL Server] Caractériser une adresse IP
Voici une procédure stockée pour :
- vérifier une adresse IP
- chercher si celle-ci est privée
- chercher la classe de cette adresse IP
- [SGBD][SQL Server] Comment exécuter une procédure stockée dès le démarrage de SQL Server ?
Nous allons voir comment on peut exécuter n'importe quelle procédure stockée dès le démarrage de SQL Server, à l'aide de la procédure stockée sp_procoption
- [SGBD][SQL Server] Concaténer les valeurs d'une colonne dans une variable
Il est souvent demandé s'il est possible de concaténer les valeurs d'une colonne dans une variable, éventuellement en les séparant avec un symbole de ponctuation.
Voici comment faire, sans curseur ni expression de table commune ...
]suite
- [SGBD][SQL Server] Connaître l'état d'un service : xp_servicecontrol
Comment connaître simplement l'état d'un service ?
Est-il possible d'arrêter et de démarrer un service avec une requête ?
- [SGBD][SQL Server] Consulter l'historique des restaurations de bases de données
Voici une petite requête qui permet de voir toutes les restaurations de bases de données d'une instance ...
- [SGBD][SQL Server] Créer une table de dates
Pourquoi ne pas créer une table de dates pour se faciliter les recherches dans des tables suivant la colonne de type date qu'elles contiennent ?
On peut vous demander par exemple de rechercher la quantité d'eau consommée par un parc de machines pour la deuxième semaine de chaque mois d'une année.
Imaginez un peu la complexité de la requête à écrire ...
Voyons comment on peut résoudre cette demande très facilement en créant une table de dates ...
- [SGBD][SQL Server] Découper une chaîne en sous-chaînes de longueur fixe
Certains me désignent sur le forum SQL Server comme l'homme qui murmure à l'oreille des CTE.
En voici donc une nouvelle !
- [SGBD][SQL Server] Détecter un caractère unicode dans une chaîne de caractères
Voici une petite fonction qui permet de vérifier la présence d'un caractère qui n'est pas supporté par la norme ASCII dans une chaîne de caractères ...
- [SGBD][SQL Server] Exemples d'affectations de variables en ligne sous SQL Server 2008
Voici quelques exemples simples de code qui montrent que sous SQL Server 2008, on peut non seulement se passer des sempiternelles deux lignes de code nécessaires jusqu'en version 2005 pour déclarer et affecter d'un valeur une variable, mais on peut aller encore plus loin ...
- [SGBD][SQL Server] Exporter le code de vues, procédures stockées, triggers, fonctions SQL vers un fichier
Pour effectuer une revue de code ou pour gérer des versions de code en phase de développement, il peut être intéressant d'exporter les codes des modules SQL vers un fichier.
Voyons comment réaliser cela, à l'aide de l'utilitaire en ligne de commande BCP ...
Divers
Syndiquez ce blog 
Articles :
Commentaires :
![[Valid RSS]](/img/valid-rss.png "Validate my RSS feed")
![[Valid Atom 1.0]](/img/valid-atom.png "Validate my Atom 1.0 feed")
Copyright © 2000-2012 - www.developpez.com