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

Откуда:
Сообщений: 88
Добрый день. Подскажите как оптимальнее сделать следующий запрос.
Допустим есть таблица следующего формата:
DataAccountInDtKtOut
2015-02-01408178102313212311001090180
2015-02-014070281035647252320020100280
2015-02-284081781023132158930030110380
2015-02-284070281035458135140040120480
2015-03-014081781023132123150050130580
2015-03-014070281035647252360060140680
2015-03-314081781023132158970070150780
2015-03-314070281035458135180080160880

По этой таблице нужно сделать группировку по месяцам и по 5 первым символам account чтобы в итоге получилось что-то типа:
DataAcc2InDtKtOut
2015-024081740040200560
2015-024070260060220760
2015-034081712001202801360
2015-034070214001403001560


Так вот я делаю это тремя запросами, каждый из которых заново пробегает по все таблице:
select * into #in_tmp from (select left([DATA], 7)month, left(ACCOUNT, 5) acc2, SUM(in)infrom src_tbl where data like '%-%-01' group by left(ACCOUNT, 5), left([DATA], 7)) tbl;

select * into #dt_kt_tmp from (select left([data], 7)month, left(ACCOUNT, 5) acc2, sum(dt) ‘dt’, sum(kt) ‘kt’ from src_tbl where group by left(data, 7), left(ACCOUNT, 5)) tbl;

select * into #out_tmp from (select left([DATA], 7)month, left(ACCOUNT, 5) acc2, SUM(out) [out] from src_tbl where data = EOMONTH(data)	group by left(ACCOUNT, 5), left([DATA], 7)) tbl;


А потом джойню их в одну. Но мне кажется это мягко говоря не оптимально 3 раза пробегаться по одной таблице. Можно ли как-нибудь написать такой запрос, чтобы сервер пробегал по таблице только один раз и собирал сразу все три этих значения: in, dt_kt и out?
6 июн 17, 11:17    [20542558]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
iap
Member

Откуда: Москва
Сообщений: 46951
Badhabit,

судя по написанию апострофов, у вас не MSSQL!
6 июн 17, 11:22    [20542587]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Badhabit
Member

Откуда:
Сообщений: 88
iap
Badhabit,

судя по написанию апострофов, у вас не MSSQL!

в ворде правил текст вопроса, не обращайте внимания на апострофы и опечатки в запросах
6 июн 17, 11:34    [20542667]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7385
Badhabit,

а чем Вы готовы пожертвовать ради эффективности? Оптимально будет для left(ACCOUNT, 5), left([DATA], 7) создать два столбца в таблице и сделать по нему кластерный индекс. Условие группировки написать для этих столбцов.
6 июн 17, 13:38    [20543233]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
aleks2
Guest
Владислав Колосов
Badhabit,

а чем Вы готовы пожертвовать ради эффективности? Оптимально будет для left(ACCOUNT, 5), left([DATA], 7) создать два столбца в таблице и сделать по нему кластерный индекс. Условие группировки написать для этих столбцов.


Научил бы, лучше, страдальца CASE использовать.
6 июн 17, 13:43    [20543253]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Badhabit
Member

Откуда:
Сообщений: 88
aleks2
Владислав Колосов
Badhabit,

а чем Вы готовы пожертвовать ради эффективности? Оптимально будет для left(ACCOUNT, 5), left([DATA], 7) создать два столбца в таблице и сделать по нему кластерный индекс. Условие группировки написать для этих столбцов.


Научил бы, лучше, страдальца CASE использовать.

Подскажите пожалуйста, как case для этого случая использовать?

автор
а чем Вы готовы пожертвовать ради эффективности? Оптимально будет для left(ACCOUNT, 5), left([DATA], 7) создать два столбца в таблице и сделать по нему кластерный индекс. Условие группировки написать для этих столбцов.

индексы наверно не подойдут. дело в том что эта группировка как раз используется для определения корректности данных импортированных в таблицу бд, а размер таблиц может достигать 500-700ГБ, т.е. на создание индекса уйдет порядочное время. или я не прав?
6 июн 17, 14:09    [20543360]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7385
Badhabit,

имеется в виду

sum(case data like '%-%-01' when in else o end) in,

и т.п.

Один, конечно, лучше , чем три.
6 июн 17, 14:44    [20543520]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7385
sum(case when data like '%-%-01' then in else o end) in,
6 июн 17, 14:45    [20543524]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Badhabit
Member

Откуда:
Сообщений: 88
Владислав Колосов
Badhabit,

имеется в виду

sum(case data like '%-%-01' when in else o end) in,

и т.п.

Один, конечно, лучше , чем три.

Ну т.е. в ситуации отсутствия индексов это будет самым оптимальным решением?
6 июн 17, 15:15    [20543633]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7385
Badhabit,

группировка одинаковая, значит можно три суммирования написать в одном запросе. Получите один просмотр таблицы вместо трех.
6 июн 17, 15:33    [20543740]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Badhabit
Member

Откуда:
Сообщений: 88
Владислав Колосов
Badhabit,

группировка одинаковая, значит можно три суммирования написать в одном запросе. Получите один просмотр таблицы вместо трех.

Спасибо большое за помощь!
Только еще один вопрос, а можно ли подобный финт сделать со следующей таблицей? Что-то никак я не придумаю как…
DataDt_accountKt_accountsum
2015-05-102020281012345678940817810123456789100
2015-05-124081781085296374420202810741789653200
2015-05-134070281079456456420202810123654565300
2015-05-144520681012345645240702810123245654400
2015-05-154081781023131564520202810321564564500
2015-06-014081781023131564520202810321564564600

В итоге мне нужно сгруппировать так же по месяцам и посчитать отдельно дт. и кт. обороты по счетам.
Я делаю это так:
select * into #dt from (select left(data, 7)month, left(DT_ACCOUNT, 5) ‘acc2’, sum(sum) dt_sum from src_tbl group by left(data, 7), left(DT_ACCOUNT, 5)) tbl;
select * into #kt from (select left(data, 7)month, left(KT_ACCOUNT, 5) ‘acc2’, sum(sum) kt_sum from src_tbl group by left(data, 7), left(KT_ACCOUNT, 5)) tbl;

И потом джойню в одну... и получается что-то типа (вроде правильно посчитал):
MonthAcc2 Dt_sumKt_sum
2015-0520202100500
2015-0540702300400
2015-0540817200100
2015-05452064000
2015-064081711000
2015-062020201100

Т.е. опять дважды читаю одну таблицу... Можно ли как-нибудь это объединить в один проход по таблице?
6 июн 17, 17:26    [20544289]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Badhabit,

UNPIVOT
6 июн 17, 17:48    [20544380]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7385
Badhabit,

не получится, т.к. разные условия группировки.
6 июн 17, 17:49    [20544387]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Remind
Member

Откуда: UK
Сообщений: 523
declare @t table (Data varchar(20),	Dt_account varchar(30),	Kt_account varchar(30),	sum int)

insert into @t
values
  ('2015-05-10',	'20202810123456789',	'40817810123456789',	100),
  ('2015-05-12',	'40817810852963744',	'20202810741789653',	200),
  ('2015-05-13',	'40702810794564564',	'20202810123654565',	300),
  ('2015-05-14',	'45206810123456452',	'40702810123245654',	400),
  ('2015-05-15',	'40817810231315645',	'20202810321564564',	500),
  ('2015-06-01',	'40817810231315645',	'20202810321564564',	600)

select * from @t

select 
  left(unpvt.Data, 7) as Month, 
  left(unpvt.Account, 5) as Account, 
  sum(case when unpvt.AccountType = 'Dt_account' then unpvt.sum end) dt_sum,
  sum(case when unpvt.AccountType = 'Kt_account' then unpvt.sum end) kt_sum
from @t
UNPIVOT
  (Account for AccountType in (Dt_account, Kt_account)) as unpvt
group by left(unpvt.Data, 7), left(unpvt.Account, 5)
6 июн 17, 17:51    [20544396]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать запрос для группировки данных  [new]
Badhabit
Member

Откуда:
Сообщений: 88
Remind
declare @t table (Data varchar(20),	Dt_account varchar(30),	Kt_account varchar(30),	sum int)

insert into @t
values
  ('2015-05-10',	'20202810123456789',	'40817810123456789',	100),
  ('2015-05-12',	'40817810852963744',	'20202810741789653',	200),
  ('2015-05-13',	'40702810794564564',	'20202810123654565',	300),
  ('2015-05-14',	'45206810123456452',	'40702810123245654',	400),
  ('2015-05-15',	'40817810231315645',	'20202810321564564',	500),
  ('2015-06-01',	'40817810231315645',	'20202810321564564',	600)

select * from @t

select 
  left(unpvt.Data, 7) as Month, 
  left(unpvt.Account, 5) as Account, 
  sum(case when unpvt.AccountType = 'Dt_account' then unpvt.sum end) dt_sum,
  sum(case when unpvt.AccountType = 'Kt_account' then unpvt.sum end) kt_sum
from @t
UNPIVOT
  (Account for AccountType in (Dt_account, Kt_account)) as unpvt
group by left(unpvt.Data, 7), left(unpvt.Account, 5)

Работает!)) Огромное спасибо!!!
7 июн 17, 11:08    [20545940]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить