Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выборка со слиянием столбцов N-го количества таблиц  [new]
aries15ssh
Member

Откуда: Крым
Сообщений: 4
Доброго времени суток!
Ввиду недостатка знаний SQL возник вопрос по выборке из большого количества таблиц.
Во всех таблицах имеется столбец ID , и различные уникальные числовые столбцы, представляющие интерес, но у одного ID может быть как несколько значений, так и ID может отсутствовать в одной из таблиц.

Пример:

t1

CID | SUMA | .......|
-------------------
1 | 100.00 |
1 | 10.50 |
1 | 10.00 |
2 | 40.50 |
3 | 0.00 |
100 | 33.33 |

t2

CID | DOLD | .......|
-------------------
1 | 10.00 |
1 | 0.00 |
1 | 3.00 |
2 | 10.50 |
3 | 20.00 |
200 | 66.00 |


t3

CID | OPL | .......|
-------------------
1 | 70.00 |
1 | 0.00 |
1 | 30.00 |
2 | 70.50 |
3 | 60.00 |
300 | 50.00 |


Нужно получить результат в виде суммированных одинаковых ID каждой таблицы.
Что-то вида:

CID | SUMA | DOLG | OPL
----------------------------------
1 | 120.50 | 13.00 | 100.00
2 | 40.50 | 10.50 | 70.50
3 | 0.00 | 20.00 | 60.00
100 | 33.33 | 0.00 | 0.00
200 | 0.00 | 66.00 | 0.00
300 | 0.00 | 0.00 | 50.00



Когда выборка ограничивалась двумя таблицами я делал так:
select t1x.CID, t2x.CID, t1x.sump, t2x.sumd from (select t1.CID, SUM(t1.SUMA) sump FROM t1 group by t1.CID) t1x full join (select t2.CID, SUM(t2.dolg)sumd FROM t2 group by t2.CID) t2x ON t1x.INN=t2x.INN
результат потом ровняли в экзэле, но когда таблиц стало больше десятка, такой метод уже не работает...
Подскажите, как правильно построить запрос
16 дек 16, 17:05    [20013288]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
buven
Member

Откуда:
Сообщений: 792
aries15ssh
результат потом ровняли в экзэле, но когда таблиц стало больше десятка, такой метод уже не работает...

Почему перестало?
Задача у вас какая то странная... Можно залезть в долг без выставленной суммы, можно заплатить непонятно за что, для выставленной суммы нет долгов... Явно к CID нужен еще один атрибут или несколько атрибутов, чтобы хоть что-то сошлось. Дата может какая...
Вот это вот

CID | SUMA | DOLG | OPL
2 | 40.50 | 10.50 | 70.50

сведет с ума любого бухгалтера:) как может при выставленной сумме в 40.50 и оплате 70.50 висеть еще долг какой то по этому CID?
16 дек 16, 17:17    [20013362]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
aries15ssh,

это у вас под каждую "сумму" новая таблица? оригинально :)
по простому, как вы пишете (Id, Sum)можно свернуть всё в UNIONA ALL потом развернуть тупым CASE WHEN
16 дек 16, 17:23    [20013404]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
o-o
Guest
получить всеобщий список CID как угодно (main),
остальных всех к нему левосоединить:
left join on main.CID = t1.CID
...
left join on main.CID = tn.CID

всем сумму в селекте,
один group by main.CID на всех.
16 дек 16, 17:30    [20013448]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
o-o
Guest
хотя нет, все, пятница.
агрегаты надо отдельно считать, раз там несколько строк на 1 CID может быть
16 дек 16, 17:34    [20013460]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
o-o
Guest
значит, нечего халявить, 10 таблиц -- 10 агрегатов считаем отдельно.
и не надо в 1 запрос все пихать.
по каждой таблице считаем суммы, кладем во временныe таблицы.
10 временных таблиц на выходе.
(сложить все в процедуру и когда надо будет, добавите 11-ую, 12-ую и 13-ую)
ну и потом все, как сказано ранее: left join ко всеобщему списку CID.
правда уже без всякого group by
16 дек 16, 17:43    [20013505]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
aries15ssh
Member

Откуда: Крым
Сообщений: 4
buven
Задача у вас какая то странная...

Да задача корявая по определению, пытаюсь выбрать суммы из двух абсолютно не связанных систем, одна из которых распределенная территориально... отсюда и полный дурдом с суммами. Радует только, что CID генериться одинаково на базе персональных данных и позволяет однозначно привязать клиента к суммам. Бухгалтерам еще предстоит как-то это свести/подчистить перед конвертацией в третий комплекс...

TaPaK
это у вас под каждую "сумму" новая таблица? оригинально :)

Мне поплохело, когда я увидел в профайлере около 30 таблиц при элементарном запросе из нашего софта контактных данных и сумм операций по одному клиенту.

o-o
получить всеобщий список CID как угодно (main), остальных всех к нему левосоединить

Спасибо, алгоритм понял, но как вытащить всеобщий список CID из всех таблиц не могу понять? Проблема в том, что админить БД я начал буквально пару месяцев назад... изучаю, читаю, но познаний явно не достаточно, по этому вопрос возможно элементарный, за что дико извиняюсь :)
19 дек 16, 14:05    [20021825]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
o-o
Guest
aries15ssh
как вытащить всеобщий список CID из всех таблиц не могу понять

чтобы по второму разу не вычитывать ваши таблицы,
сформируйте список из полученных временных таблиц с агрегатами:
with cte as
(
select CID from #t1
union
select CID from #t2
...
union
select CID from #tn
)

select CID
into #CID
from cte;
19 дек 16, 14:12    [20021895]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
o-o,

так а чего уже полностью не забирать и агрегировать? вряд ли там миллионы :)
19 дек 16, 14:14    [20021911]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
o-o
Guest
и так забирает и агрегирует.
в чем ваш вопрос?
19 дек 16, 14:25    [20022017]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
aries15ssh
Member

Откуда: Крым
Сообщений: 4
o-o,
Огромная благодарность, выборку сделал!

Попутно возник вопрос относительно оптимальности запроса:
Если упихнуть всё в один запрос вида, будет ли это хуже/лучше/без_разницы варианта с агрегацией во временные таблицы?
select all_CID.CID, t1x.sump, t2x.sumd, t3x.sumo
from (select t1.CID ИНН FROM t1 union select t2.CID FROM t2 union select t3.CID FROM t3) all_CID 
left join (select t1.CID, SUM(t1.SUMA) sump FROM p group by t1.CID) t1x ON all_CID.CID=t1x.CID 
left join (select t2.CID, SUM(t2.DOLG) sumd FROM p group by t2.CID) t2x ON all_CID.CID=t2x.CID 
left join (select t3.CID, SUM(t3.OPL) umo FROM p group by t3.CID) t3x ON all_CID.CID=t3x.CID


Тестирую всё на тестовой базе с малым количеством записей и разницы во времени исполнения не заметил, а боевая база содержит от 50 до 300 тыс. строк на таблицу, и хочу определиться с оптимальным вариантом.
20 дек 16, 11:59    [20026342]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
o-o
Guest
300 000 строк это не пойми какой объем,
вы в гигабайтах скажите.
по нашим объемам скажу сразу: если не лить во временные таблицы, а агрегировать тучу таблиц вот так в одном запросе,
выйдет гораздо дольше.
он все равно сольет на диск промежуточные результаты.
т.е. у меня сделан мониторинг темпдб, куда я складирую тех,
кто навалил в темпдб более гига юзерскими либо системными объектами.
так вот любители CTE постоянно сливают в виде системного добра.
----
что вам мешает разок засечь время на реальных данных?
если время выполнения запроса вас устраивает, не городите огород времянок.
20 дек 16, 12:19    [20026426]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
o-o
Guest
наши любители CTE.
слив только на них и на любителях сортировок

К сообщению приложен файл. Размер - 30Kb
20 дек 16, 12:31    [20026470]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
aries15ssh,
WITH 
cte( CID,SUMA,DOLG,OPL ) AS (
  SELECT
    CID, SUM( SUMA ), NULL, NULL 
  FROM (
    VALUES
      (   1, 100.00 ),
      (   1,  10.50 ),
      (   1,  10.00 ),
      (   2,  40.50 ),
      (   3,   0.00 ),
      ( 100,  33.33 )
    ) t1( CID, SUMA )
  GROUP BY
    CID
  UNION ALL
  SELECT 
    CID, NULL, SUM( DOLG ), NULL 
  FROM (
    VALUES
      (   1, 10.00 ),
      (   1,  0.00 ),
      (   1,  3.00 ),
      (   2, 10.50 ),
      (   3, 20.00 ),
      ( 200, 66.00 )
    ) t2( CID, DOLG )
  GROUP BY
    CID
  UNION ALL
  SELECT 
    CID, NULL, NULL, SUM( OPL )
  FROM (
    VALUES
      (   1, 70.00 ),
      (   1,  0.00 ),
      (   1, 30.00 ),
      (   2, 70.50 ),
      (   3, 60.00 ),
      ( 300, 50.00 )
    ) t3( CID, OPL )
  GROUP BY
    CID
)
SELECT 
  CID,
  SUMA = SUM( SUMA ),
  DOLG = SUM( DOLG ),
  OPL = SUM( OPL )
FROM 
  cte
GROUP BY
  CID
20 дек 16, 14:40    [20027253]     Ответить | Цитировать Сообщить модератору
 Re: Выборка со слиянием столбцов N-го количества таблиц  [new]
aries15ssh
Member

Откуда: Крым
Сообщений: 4
У меня суммарный объем баз около 28Гб, при 32Гб оперативы гипотетически весь запрос может быть выполнен в RAM :)
Спасибо всем! буду пробовать на живой базе вечером.
20 дек 16, 17:11    [20028380]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить