una - sumar dos columnas de diferentes tablas en sql

Cómo calcular la suma de valores en un árbol usando SQL

Question

Necesito sumar puntos en cada nivel ganado por un árbol de usuarios. El nivel 1 es la suma de los puntos de usuarios de los usuarios 1 nivel debajo del usuario. Nivel 2 es el nivel 1 puntos de los usuarios 2 niveles debajo del usuario, etc.

El cálculo ocurre una vez al mes en un servidor que no es de producción, sin preocuparse por el rendimiento.

¿Cómo se vería el SQL para hacerlo?

Si estás confundido, no te preocupes, ¡yo también lo estoy!

Tabla de usuario:

ID    ParentID    Points
1     0           230
2     1           150
3     0           80
4     1           110
5     4           54
6     4           342

Tree:
0
|---/
1    3
| /
2  4---
    /  /
     5  6

La salida debería ser:

ID    Points    Level1     Level2
1     230       150+110    150+110+54+342
2     150
3     80
4     110       54+342
5     54
6     342

Sintaxis y funciones de SQL Server preferiblemente ...

Answer 1

La siguiente tabla:

Id   ParentId
1   NULL
11    1
12    1
110 11
111 11
112 11
120 12
121 12
122 12
123 12
124 12

Y la siguiente tabla de cantidad:

Id     Val
110 500
111 50
112 5
120 3000
121 30000
122 300000

Solo las hojas (último nivel) Id tienen un valor definido. La consulta SQL para obtener los datos se ve así:

;WITH Data (Id, Val) AS
(
    select t.Id, SUM(v.val) as Val from dbo.TestTable t
    join dbo.Amount v on t.Id = v.Id
    group by t.Id
)

select cd.Id, ISNULL(SUM(cd.Val), 0) as Amount FROM
(
    -- level 3
    select t.Id, d.val from TestTable t
    left join Data d on d.id = t.Id

    UNION

    -- level 2
    select t.parentId as Id, sum(y.Val) from TestTable t
    left join Data y on y.id = t.Id
    where t.parentId is not null
    group by t.parentId

    UNION

    -- level 1
    select t.parentId as Id, sum(y.Val) from TestTable t
    join TestTable c on c.parentId = t.Id
    left join Data y on y.id = c.Id
    where t.parentId is not null
    group by t.parentId
) AS cd
group by id

esto resulta en el resultado:

Id     Amount
1     333555
11   555
12   333000
110 500
111 50
112 5
120 3000
121 30000
122 300000
123 0
124 0

Espero que esto ayude.

Answer 2

Los árboles no funcionan bien con SQL. Si tiene muy (muy) muy pocos accesos de escritura, podría cambiar la implementación del árbol para utilizar conjuntos anidados, lo que haría que esta consulta sea increíblemente fácil.

Ejemplo (si no me equivoco):

SELECT SUM(points) 
FROM users 
where left > x and right < y 

Sin embargo, cualquier cambio en el árbol requiere tocar una gran cantidad de filas. Probablemente sea mejor hacer la recursión en tu cliente.

Answer 3

Ok, esto te da los resultados que estás buscando, pero no hay garantías de que no me haya perdido algo. Considéralo un punto de partida. Utilicé SQL 2005 para hacer esto, SQL 2000 no es compatible con CTE

WITH Parent (id, GrandParentId, parentId, Points, Level1Points, Level2Points)
AS
(
    -- Find root
    SELECT id,  
            0 AS GrandParentId,
            ParentId,
            Points,
            0 AS Level1Points,
            0 AS Level2Points
    FROM tblPoints ptr
    WHERE ptr.ParentId = 0

    UNION ALL (
    -- Level2 Points
    SELECT pa.GrandParentId AS Id,
            NULL AS GrandParentId,
            NULL AS ParentId,
            0 AS Points, 
            0 AS Level1Points,
            pa.Points  AS Level2Points
    FROM tblPoints pt
            JOIN Parent pa ON pa.GrandParentId = pt.Id 
    UNION  ALL
    -- Level1 Points
    SELECT pt.ParentId AS Id,
            NULL AS GrandParentId,
            NULL AS ParentId,
            0 AS Points, 
            pt.Points AS Level1Points,
            0 AS Level2Points
    FROM tblPoints pt
            JOIN Parent pa ON pa.Id = pt.ParentId AND pa.ParentId IS NOT NULL 
    UNION  ALL
    -- Points
    SELECT pt.id,
            pa.ParentId AS GrandParentId,
            pt.ParentId,
            pt.Points, 
            0 AS Level1Points,
            0 AS Level2Points
    FROM tblPoints pt
            JOIN Parent pa ON pa.Id = pt.ParentId AND pa.ParentId IS NOT NULL )
)
SELECT id, 
    SUM(Points) AS Points,  
    SUM(Level1Points) AS Level1Points,
    CASE WHEN SUM(Level2Points) > 0 THEN  SUM(Level1Points) + SUM(Level2Points) ELSE 0 END AS Level2Points
FROM Parent
GROUP BY id 
ORDER by id

Answer 4

Puede escribir una función recursiva simple para hacer el trabajo. Mi MSSQL está un poco oxidado, pero se vería así:

CREATE FUNCTION CALC
(
@node integer,
)
returns 
(
@total integer
)
as
begin
    select @total = (select node_value from yourtable where node_id = @node);

    declare @children table (value integer);
    insert into @children   
    select calc(node_id) from yourtable where parent_id = @node;

    @current = @current + select sum(value) from @children;
    return
end

Answer 5

SQL en general, como otros dijeron, no maneja bien tales relaciones. Normalmente, se necesita una tabla sustituta de "relaciones" (id, parent_id, clave única on (id, parent_id)), donde:

• cada vez que agrega un registro en ''tabla'', usted:

  INSERT INTO relations (id, parent_id) VALUES ([current_id], [current_id]);

  INSERT INTO relations (id, parent_id) VALUES ([current_id], [current_parent_id]);

  INSERT INTO relations (id, parent_id) SELECT [current_id], parent_id FROM relations WHERE id = [current_parent_id];

• tener lógica para evitar ciclos

• asegúrese de que las actualizaciones y eliminaciones de las "relaciones" se manejen con procedimientos almacenados

Dada esa tabla, quieres:

SELECT rel.parent_id, SUM(tbl.points)
FROM table tbl INNER JOIN relations rel ON tbl.id=rel.id
WHERE rel.parent_id <> 0
GROUP BY rel.parent_id;

Answer 6

Si está trabajando con árboles almacenados en una base de datos relacional, le sugiero que consulte "conjunto anidado" o "recorrido de árbol preordenar modificado". El SQL será tan simple como eso:

SELECT id, 
       SUM(value) AS value 
FROM table 
WHERE left>left/_value/_of/_your/_node 
  AND right<$right/_value/_of/_your/_node;

... y haz esto por cada nodo que te interese.

Quizás esto lo ayude: http://www.dbazine.com/oracle/or-articles/tropashko4 o use google.

Answer 7

Si estuviera usando Oracle DBMS sería bastante sencillo ya que Oracle admite consultas en árbol con la sintaxis CONNECT BY / STARTS WITH . Para SQL Server, creo que puede ser útil encontrar Common Table Expressions

Answer 8

Tienes unas cuantas opciones:

1. Use un cursor y una llamada de función recursiva definida por el usuario (es bastante lenta)
2. Cree una tabla de caché, actualícela en INSERT usando un desencadenador (es la solución más rápida pero podría ser problemática si tiene muchas actualizaciones en la tabla principal)
3. Haga un cálculo recursivo del lado del cliente (preferiblemente si no tiene demasiados registros)

Answer 9

Yo diría: crea un procedimiento almacenado, probablemente tenga el mejor rendimiento. O si tiene un número máximo de niveles, puede crear subconsultas, pero tendrán un rendimiento muy poort.

(O podría obtener MS SQL Server 2008 y obtener las nuevas funciones de jerarquía ...;))