Structure de base de données SQL pour N éléments avec M paramètres variant dans le temps

Question

Voici les éléments:

• item_1
• ...
• item_N

N est d'environ quelques centaines. De nouveaux éléments peuvent être ajoutés parfois.

Chaque élément a un ensemble de paramètres:

• parameter_1
• ...
• parameter_M

M est d'environ 20-30. Ces paramètres sont mesurés plusieurs fois par jour (indépendamment les uns des autres, à des heures différentes) et doivent être stockés dans une base de données. Il y parameter_j(item_i)a donc une multitude de paires valeur-temps:

• <t0, v0> (la première mesure de paramètre_j (item_i))
• ...
• <tk, vk> (la dernière mesure de paramètre_j (item_i))

Requêtes de base à effectuer:

• toutes les valeurs d'un paramètre pour un article (triées par moment de la mesure)
• les dernières valeurs de tous les paramètres pour tous les éléments

De quelle structure de base de données (tables) ai-je besoin pour effectuer ces requêtes efficacement ? Pourriez-vous s'il vous plaît fournir du code SQL pour ces deux types de requêtes?

Answer 1

J'irais probablement avec une structure comme celle-ci:

Je fournirai des codes MS SQL.

Exemple de code pour créer ces tables:

IF NOT OBJECT_ID('tempdb..#Items') IS NULL DROP TABLE #Items
CREATE TABLE #Items (
    ID int NOT NULL,
    ItemName varchar(255) NOT NULL,
    PRIMARY KEY (ID)
)

IF NOT OBJECT_ID('tempdb..#Measurements') IS NULL DROP TABLE #Measurements
CREATE TABLE #Measurements (
    ItemID int NOT NULL,
    MeasurementDate datetime NOT NULL,
    Param1 varchar(255),
    Param2 float,
    Param3 varchar(255),
    ParamN binary
    FOREIGN KEY (ItemID) REFERENCES #Items (ID)
)

J'ai rempli de données factices via ce code:

INSERT INTO #Items values ( 0, 'Item1' )
INSERT INTO #Items values ( 1, 'Item2' )

INSERT INTO #Measurements (ItemID, MeasurementDate, Param2) values (0, GetDate(), 2.3)

INSERT INTO #Measurements (ItemID, MeasurementDate, Param3) values (1, DATEADD(day, 3, GetDate()), 'red')

INSERT INTO #Measurements (ItemID, MeasurementDate, Param3) values (1, DATEADD(day, 2, GetDate()), 'blue')

INSERT INTO #Measurements (ItemID, MeasurementDate, Param2) values (0, DATEADD(day, 5, GetDate()), 4.0)

Requête pour ,, toutes les valeurs d'un paramètre pour un élément (triées en fonction du moment de la mesure) ":

Code:

SELECT #Measurements.MeasurementDate, #Measurements.Param2
FROM #Measurements INNER JOIN #Items ON #Measurements.ItemID = #Items.ID
WHERE #Items.ItemName LIKE '%Item1%'
ORDER BY #Measurements.MeasurementDate

Production:

Requête pour ,, les dernières valeurs de tous les paramètres pour tous les éléments ":

Code (voir l'explication SQL Server obtient la dernière valeur par date ):

SELECT TOP 1 WITH TIES
*
FROM #Measurements
ORDER BY row_number() over (partition by ItemID order by MeasurementDate desc)

Production: