Nuovi posts

Cerca

Calcoli con le Gerarchie

Nores

il 11 lug 2023, 09:07

5 risposte

Nores

Pochi posts

Iscritto da: apr, 2021

Messaggi:: 7

Calcoli con le Gerarchie

11 lug 2023, 09:11

Ciao a tutti,

sono nuovo del forum e volevo sottoporvi questo dilemma che da un po' mi attanaglia.

Ho sviluppato una tabella gerarchica la cui pivot mi fornisce correttamente una gerarchia a nove livelli. Ovviamente non tutti i rami hanno nove livelli ma, certamente tutte le foglie hanno un valore che, nel mio caso, sono degli importi.

tutte le foglie sono racchiuse in un'altra tabella che fornisce gli importi.

Il mio scopo é quello di trovare, con codice SQL, tutte le somme corrette ramo per ramo a partire, ovviamente dalle foglie. Mi spiego con un esempio:

Se il padre P ha due figli F1 e F2 che a loro volta hanno, F1 due figli N1 e N2 e F2 tre figli G1, G2 e G3, io vorrei creare una tabella che calcoli i valori di P e di F1 e F2 conoscendo i valori delle foglie N1, N2, G1, G2 e G3 in modo che:

P=F1+F2=N1+N2+G1+G2+G3

F1=N1+N2

F2=G1+G2+G3

ovviamente se pensate che ho nove livelli con molti figli e foglie vi renderete conto che la cosa si complica, ma il concetto é questo.

Ho cercato di usare delle join con dei raggruppamenti tramite group by per livelli ma non funziona perché credo ci vogliano più indici e per me le gerarchie sono ancora un po' fumose. Ovviamente non chiedo la pappa pronta ma se avete anche dei riferimenti teorici o esempi sono pronto a studiarli.

Grazie per chi vorrà aiutarmi

Ciao e a presto.

5 Risposte

S
Sgrubak
Utente Attivo
Iscritto da
feb, 2021
Messaggi:
482
Re: Calcoli con le Gerarchie
11 lug 2023, 15:14
L'approccio che userei io è quello della ricorsione mediante CTE.
Fa qualche tentativo ed al massimo vediamo entrando più nel dettaglio.

asql

Molto attivo

Iscritto da: dic, 2018

Messaggi:: 515

Re: Calcoli con le Gerarchie

11 lug 2023, 17:32

Salve,

si, Sgrubak correttamente indica l'utilizzo di cte ricorsive…

probabilmente scriverei qualche cosa di simile al triviale seguente esempio

USE tempdb;
GO
CREATE TABLE dbo.Relation (
	Id int NOT NULL PRIMARY KEY,
	Name varchar(10) NOT NULL,
	Parent int NULL
	);

CREATE TABLE dbo.Data (
	Id int NOT NULL PRIMARY KEY IDENTITY,
	RelationId int NOT NULL 
		CONSTRAINT fk_Data$has$Relation
			FOREIGN KEY REFERENCES dbo.Relation
				(Id),
	Value decimal(18,4)
	);
GO
INSERT INTO dbo.Relation
	VALUES (1, 'A', NULL)
		, (11, 'A1', 1)
		, (101, 'A2.1', 11), (102, 'A2.2', 11)
		, (1001, 'A3.1', 101), (1002, 'A3.2', 101)
		, (10001, 'A4', 1001)
		, (21, 'B1', 1)
		, (201, 'B2', 21)
		, (2001, 'B3.1', 201)
		, (20001, 'B4.1', 2001), (20002, 'B4.2', 2001);

INSERT dbo.Data
	VALUES (1, 1)
		, (11, 10)
		, (101, 101), (102, 102)
		, (1001, 1001), (1002, 1002)
		, (10001, 10001)
		, (21, 21)
		, (201, 201)
		, (2001, 2001)
		, (20001, 20001), (20002, 20002);
GO

PRINT 'Proiezione gerarchica';
WITH cte AS
(
    SELECT CAST(r.Name AS varchar(30)) AS hierPath 
		, r.Id, r.Name, r.Parent
		, 1 AS lvl
		, d.Value
		FROM dbo.Relation r
			JOIN dbo.Data d ON d.RelationId = r.Id
		WHERE r.Parent IS NULL
    UNION ALL
	SELECT CAST(c.hierPath + ' / ' + r.Name AS varchar(30)) as hierPath 
		, r.Id, r.Name, r.Parent    
		, c.lvl +1
		, d.Value
	    FROM cte c
			INNER JOIN dbo.Relation r ON r.Parent = c.Id
			JOIN dbo.Data d ON d.RelationId = r.Id
)
SELECT c.hierPath, c.Id, c.Parent, c.Name, c.lvl
	, c.Value + SUM(ISNULL(c2.Value, 0.0)) AS VALUE
	FROM cte c
		left outer join cte c2 on c2.hierPath <> c.hierPath and left(c2.hierPath, LEN(c.hierPath)) = c.hierPath
	GROUP BY c.hierPath, c.Id, c.Parent, c.Name, c.lvl, c.Value
	ORDER BY hierPath;

GO
DROP TABLE dbo.Data, dbo.Relation
--<---------
Proiezione gerarchica
hierPath                       Id          Parent      Name       lvl         VALUE
------------------------------ ----------- ----------- ---------- ----------- ------------
A                              1           NULL        A          1           54444.0000
A / A1                         11          1           A1         2           12217.0000
A / A1 / A2.1                  101         11          A2.1       3           12105.0000
A / A1 / A2.1 / A3.1           1001        101         A3.1       4           11002.0000
A / A1 / A2.1 / A3.1 / A4      10001       1001        A4         5           10001.0000
A / A1 / A2.1 / A3.2           1002        101         A3.2       4           1002.0000
A / A1 / A2.2                  102         11          A2.2       3           102.0000
A / B1                         21          1           B1         2           42226.0000
A / B1 / B2                    201         21          B2         3           42205.0000
A / B1 / B2 / B3.1             2001        201         B3.1       4           42004.0000
A / B1 / B2 / B3.1 / B4.1      20001       2001        B4.1       5           20001.0000
A / B1 / B2 / B3.1 / B4.2      20002       2001        B4.2       5           20002.0000

Non e' leggerissima….

salutoni romagnoli
–
Andrea

N
Nores
Pochi posts
Iscritto da
apr, 2021
Messaggi:
7
Re: Calcoli con le Gerarchie
19 lug 2023, 09:16
11/07/2023 - Sgrubak ha scritto:
L'approccio che userei io è quello della ricorsione mediante CTE.
Fa qualche tentativo ed al massimo vediamo entrando più nel dettaglio.
Grazie mille, sto già cercando la WITH adatta…ma non é facile.
Eventualmente faccio sapere…. ;-)

Nores

Pochi posts

Iscritto da: apr, 2021

Messaggi:: 7

Re: Calcoli con le Gerarchie

19 lug 2023, 09:29

11/07/2023 - asql ha scritto:

Salve,

si, Sgrubak correttamente indica l'utilizzo di cte ricorsive…

probabilmente scriverei qualche cosa di simile al triviale seguente esempio

USE tempdb;
GO
CREATE TABLE dbo.Relation (
	Id int NOT NULL PRIMARY KEY,
	Name varchar(10) NOT NULL,
	Parent int NULL
	);

CREATE TABLE dbo.Data (
	Id int NOT NULL PRIMARY KEY IDENTITY,
	RelationId int NOT NULL 
		CONSTRAINT fk_Data$has$Relation
			FOREIGN KEY REFERENCES dbo.Relation
				(Id),
	Value decimal(18,4)
	);
GO
INSERT INTO dbo.Relation
	VALUES (1, 'A', NULL)
		, (11, 'A1', 1)
		, (101, 'A2.1', 11), (102, 'A2.2', 11)
		, (1001, 'A3.1', 101), (1002, 'A3.2', 101)
		, (10001, 'A4', 1001)
		, (21, 'B1', 1)
		, (201, 'B2', 21)
		, (2001, 'B3.1', 201)
		, (20001, 'B4.1', 2001), (20002, 'B4.2', 2001);

INSERT dbo.Data
	VALUES (1, 1)
		, (11, 10)
		, (101, 101), (102, 102)
		, (1001, 1001), (1002, 1002)
		, (10001, 10001)
		, (21, 21)
		, (201, 201)
		, (2001, 2001)
		, (20001, 20001), (20002, 20002);
GO

PRINT 'Proiezione gerarchica';
WITH cte AS
(
    SELECT CAST(r.Name AS varchar(30)) AS hierPath 
		, r.Id, r.Name, r.Parent
		, 1 AS lvl
		, d.Value
		FROM dbo.Relation r
			JOIN dbo.Data d ON d.RelationId = r.Id
		WHERE r.Parent IS NULL
    UNION ALL
	SELECT CAST(c.hierPath + ' / ' + r.Name AS varchar(30)) as hierPath 
		, r.Id, r.Name, r.Parent    
		, c.lvl +1
		, d.Value
	    FROM cte c
			INNER JOIN dbo.Relation r ON r.Parent = c.Id
			JOIN dbo.Data d ON d.RelationId = r.Id
)
SELECT c.hierPath, c.Id, c.Parent, c.Name, c.lvl
	, c.Value + SUM(ISNULL(c2.Value, 0.0)) AS VALUE
	FROM cte c
		left outer join cte c2 on c2.hierPath <> c.hierPath and left(c2.hierPath, LEN(c.hierPath)) = c.hierPath
	GROUP BY c.hierPath, c.Id, c.Parent, c.Name, c.lvl, c.Value
	ORDER BY hierPath;

GO
DROP TABLE dbo.Data, dbo.Relation
--<---------
Proiezione gerarchica
hierPath                       Id          Parent      Name       lvl         VALUE
------------------------------ ----------- ----------- ---------- ----------- ------------
A                              1           NULL        A          1           54444.0000
A / A1                         11          1           A1         2           12217.0000
A / A1 / A2.1                  101         11          A2.1       3           12105.0000
A / A1 / A2.1 / A3.1           1001        101         A3.1       4           11002.0000
A / A1 / A2.1 / A3.1 / A4      10001       1001        A4         5           10001.0000
A / A1 / A2.1 / A3.2           1002        101         A3.2       4           1002.0000
A / A1 / A2.2                  102         11          A2.2       3           102.0000
A / B1                         21          1           B1         2           42226.0000
A / B1 / B2                    201         21          B2         3           42205.0000
A / B1 / B2 / B3.1             2001        201         B3.1       4           42004.0000
A / B1 / B2 / B3.1 / B4.1      20001       2001        B4.1       5           20001.0000
A / B1 / B2 / B3.1 / B4.2      20002       2001        B4.2       5           20002.0000

Non e' leggerissima….

salutoni romagnoli
–
Andrea

Ciao Andrea,

si, hai perfettamente ragione, é molto pesante se consideri la mia gerarchia di partenza di 1530 record su nove livelli.

La tua query introduce, effettivamente, degli spunti molto interessanti che non avevo considerato e che sto esaminando per comprenderli a modo e implementarli al mio caso.

Ti ringrazio moltissimo, eventualmente ti tengo aggiornato se vuoi

Ciao

salutoni emiliani

S
Sgrubak
Utente Attivo
Iscritto da
feb, 2021
Messaggi:
482
Re: Calcoli con le Gerarchie
19 lug 2023, 12:16
19/07/2023 - Nores ha scritto:
sto già cercando la WITH adatta…ma non é facile.
Ottimo! Se posso, ti suggerisco di concentrarti maggiormente sul tirare fuori i record base, ossia quelli della prima query a monte della UNION.
Fatto quello, ad innescare la ricorsione non ci vuole molto, dato che la gerarchia ce l'hai già piuttosto chiara nei campi. L'importante è farsi costruire i campi corretti da usare nel GROUP BY successivo, per ottenere le SUM dei vari rami, ma l'esempio di Andrea vale già più di mille parole.

Devi accedere o registrarti per scrivere nel forum

5 risposte