Tout d’abord, nous alors construire notre table de logs de façon assez simple :
Un identifiant unique, un identifiant d’utilisateur (un matricule par exemple), un rôle et une date d’opération. Ça peut paraitre simpliste, mais il s’agit là d’un exemple à adapter à vos besoins.
CREATE TABLE LOG_DATA
(
ID NUMBER ( 10, 0 ) NOT NULL ENABLE,
USER_ID VARCHAR2 ( 10 BYTE ),
ROLE VARCHAR2 ( 50 BYTE ),
ACCESS_DATE DATE,
CONSTRAINT LOG_DATA_PK PRIMARY KEY ( ID )
USING INDEX
PCTFREE 10 INITRANS 2 MAXTRANS 255
NOLOGGING
COMPUTE STATISTICS
TABLESPACE TBSINDEX
ENABLE
)
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
TABLESPACE TBSDATA ;
On va ensuite créer une fonction qui fourni un tableau de date (au sens Oracle, c'est-à-dire qui contient également la partie horaire) dont chacun des éléments est séparé d’un nombre constant de minutes.
On commence par créer un type qui sera un tableau de dates :
create or replace TYPE date_array AS TABLE OF DATE;
Puis on crée une fonction de table (Table Function) qui retourne un tableau de n dates espacées de n minutes dont la plus grande valeur est l’heure système.
Les fonctions de table sont utilisées pour renvoyer des éléments PL / SQL qui se comportent comme les tables. Elles peuvent ainsi être interrogées comme une table ordinaire en utilisant la fonction TABLE dans la clause FROM. Les fonctions de table nécessitent que tous les éléments soient entièrement remplis avant que le tableau ne soit retourné. Les éléments étant conservés en mémoire, ce peut être un problème dans le cas de tableaux grandes taille qui consommeront alors beaucoup de mémoire et de prendre un certain temps avant de retourner la première ligne. Ces goulets d'étranglement potentiels rendent les fonctions de table impropres aux processus ETL (Extraction, Transformation, Loading). Les fonctions de table nécessitent que les lignes soient nommées et que le type des éléments de la table soit créé dans les objets de la base de données.
Pour des raisons de lisibilité, les fonctions utilisées sont regroupées dans un package.
CREATE OR REPLACE PACKAGE PCK_PIVOT AS
FUNCTION date_table(
minute IN NUMBER,
bar IN NUMBER
)
RETURN date_array PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY PCK_PIVOT AS
-----------------------------------------------------------------------------
--Procédure : trunc_date
-----------------------------------------------------------------------------
--Auteur : François Gros
--Date création : 19/03/2011
--Description : Fonction de calcul d'une date arrondie à n minutes en dessous
--
--Paramètres entrés :
-- inDate : date à arrondir
--
-- minute : nombre de minutes d'arrondi (modulo)
--
--Paramètres sortis :
-- N/A
--
--En Sortie :
-- Date arrondie
--
-----------------------------------------------------------------------------
FUNCTION trunc_date(
inDate IN DATE,
minute IN NUMBER
)
RETURN DATE
AS
outDate DATE;
step NUMBER;
BEGIN
step := 24 * 60 / minute;
outDate := Trunc(inDate, 'dd')
+
Trunc(
(Trunc(inDate, 'mi') - Trunc(inDate, 'dd')) * step
) / step;
RETURN outDate;
END trunc_date;
-----------------------------------------------------------------------------
--Procédure : date_table
-----------------------------------------------------------------------------
--Auteur : François Gros
--Date création : 19/03/2011
--Description : Fonction qui retourne une liste de dates espacées de n minutes
--
--Paramètres entrés :
-- minute : nombre de minutes qui espacent les valeurs de la liste
--
-- bar : nombre de valeurs de la liste
--
--Paramètres sortis :
-- N/A
--
--En Sortie :
-- Liste de dates sous la forme d'une table de DATE
--
-----------------------------------------------------------------------------
FUNCTION date_table(
minute IN NUMBER,
bar IN NUMBER
)
RETURN date_array PIPELINED
AS
dateRow DATE;
step NUMBER;
i NUMBER;
BEGIN
step := 24 * 60 / minute;
dateRow := trunc_date(SYSDATE - (1/step*(bar-1)), minute);
FOR i IN 0 .. (bar-1) LOOP
PIPE ROW(dateRow);
dateRow := dateRow + (1/step);
END LOOP;
RETURN;
END date_table;
END PCK_PIVOT;
/
Maintenant qu’on a les bases, il reste à faire vivre le tout en remplissant de notre table de log de données.
Le requête suivante est composée de plusieurs parties. La sous requête
SELECT da.column_value AS dh_min,
da.column_value + (1 / (24 * 60 / 15)) AS dh_max,
rownum AS bar_no
FROM (
TABLE(
CAST(
date_table(15, 8)
AS date_array
)
)
);
Permet de constituer une table à la volée qui contient 8 lignes où chaque tuple est constué d’une date/heure calée sur un quart d’heure, d’une deuxième date 15 minutes plus tard et un numéro d’ordre.
Le résultat de cette sous requête est croisé avec la table des logs pour obtenir un dénombrement du nombre d’utilisateur par rôle et par quart d’heure sur 2 heures (8 quarts d’heures)
SELECT *
FROM (
SELECT Count(distinct log.user_id) AS n,
bars.bar_no AS bar_no,
log.role AS role
FROM (
SELECT da.column_value AS dh_min,
da.column_value + (1 / (24 * 60 / 15)) AS dh_max,
rownum AS bar_no
FROM (
TABLE(
CAST(
PCK_PIVOT.date_table(15, 8)
AS date_array
)
)
) da
) bars,
log_data log
WHERE log.access_date >= bars.dh_min AND
log.access_date < bars.dh_max
GROUP BY bars.bar_no,
log.role
)
Enfin, à l’aide de la fonction PIVOT, on constitue un tuple où les colonnes C1 à C8 contiennent le nombre d’utilisateurs par rôle sur un des 8 quarts d’heures. Attention toutefois, contrairement à Excel qui offre des fonctions de tableaux croisés dynamiques, Oracle fait l’impasse sur le côté dynamique. C’est la clause
FOR bar_no IN (
1 AS C1,
2 AS C2,
3 AS C3,
4 AS C4,
5 AS C5,
6 AS C6,
7 AS C7,
8 AS C8
)
Qui définie les 8 colonnes, pas une de plus, pas une de moins.
SELECT role AS role,
Sum(C1) AS C1,
Sum(C2) AS C2,
Sum(C3) AS C3,
Sum(C4) AS C4,
Sum(C5) AS C5,
Sum(C6) AS C6,
Sum(C7) AS C7,
Sum(C8) AS C8
FROM (
SELECT *
FROM (
SELECT Count(distinct log.user_id) AS n,
bars.bar_no AS bar_no,
log.role AS role
FROM (
SELECT da.column_value AS dh_min,
da.column_value + (1 / (24 * 60 / 15)) AS dh_max,
rownum AS bar_no
FROM (
TABLE(
CAST(
PCK_PIVOT.date_table(15, 8)
AS date_array
)
)
) da
) bars,
log_data log
WHERE log.access_date >= bars.dh_min AND
log.access_date < bars.dh_max
GROUP BY bars.bar_no,
log.role
)
PIVOT (
Sum(n)
FOR bar_no IN (
1 AS C1,
2 AS C2,
3 AS C3,
4 AS C4,
5 AS C5,
6 AS C6,
7 AS C7,
8 AS C8
)
)
)
GROUP BY role
ORDER BY role;
FG
Aucun commentaire:
Enregistrer un commentaire