Блог

В нашей базе данных есть несколько таблиц, которые в значительной степени растут бесконечно, поэтому мы пытаемся создать задачу, которая будет периодически (возможно, ежедневно) свертывать старые данные и удалять записи, содержащиеся в свертке.

Это достаточно легко сделать, просто используя GROUP BY . Загвоздка в том, что это дочерние таблицы, которые также необходимо свернуть и ссылаться на новые заполненные строки родительской таблицы, на которые они ссылаются, и я понятия не имею, как это сделать, не выполняя некоторые действительно неудачные подзапросы.

В качестве примера скажем, я начинаю со следующих данных в двух таблицах:

Родительская таблица (ID — столбец идентификатора)

 ID  Type  Amount
--  ----  ------
 1     1   10.00
 2     1    3.00
 3     3    8.00
 4     3    9.00
 

Дочерняя таблица

 ParentID  Thing  Qty
--------  -----  ---
       1      8    3
       1      6    6
       2      8    4
       2      5    3
       3      8    2
       3      5    1
       4      5    4
 

Сначала я бы свернул родительскую таблицу в новые строки, которые сгруппированы по типу, а затем суммы суммируются вместе, что даст следующее:

 ID  Type  Amount
--  ----  ------
 5     1   13.00
 6     3   17.00
 

Запрос для создания вышеуказанных данных в родительском запросе будет выглядеть примерно следующим образом:

 INSERT INTO Parent (Type, Amount)
SELECT Type, SUM(Amount) AS Amount
FROM Parent
GROUP BY Type
 

Для дочерней таблицы я хочу сделать что-то подобное, за исключением группировки по «Thing» и суммирования количества с родительским идентификатором, ссылающимся на вновь созданный родительский элемент, который соответствует исходному родительскому элементу. Это создало бы что-то вроде следующего:

 ParentID  Thing  Qty
--------  -----  ---
       5      8    7
       5      5    3
       5      6    6
       6      5    5
       6      8    2
 

Этот запрос будет выглядеть примерно так, как показано ниже, но я не знаю, как получить родительский идентификатор или как связать с родительской таблицей:

 INSERT INTO Child (ParentID, Thing, Qty)
SELECT ?, Thing, SUM(Qty) AS Qty
FROM Child
INNER JOIN Parent
    ON ? = ?
GROUP BY ?, Thing
 

Я бы предположил, что это происходит довольно часто, так что кто-нибудь знает, как правильно справиться с этой ситуацией?

Первая таблица:

 Declare @max int = (Select MAX(ID) from parent)
Select @max

Select @max R ID,type,Amount
from 
(
Select type,sum(amount) 'Amount',ROW_NUMBER() over(order by type) 'R'
from parent
group by Type
) R
 

Вторая таблица:

 Select @max TYPE 'ParentID',[group],sum(Qty) 'Qty'
from child
join parent 
on id = parentid
group by type,[group]
order by [parentid],[group]