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

Откуда:
Сообщений: 2
Здравствуйте. Некоторое время назад появилась необходимость выгружать данные напрямую с сервера.
Как пользоваться простыми запросами на выборку данных из БД разобраться получилось, но сейчас столкнулся с задачей по сложнее и оптимальный алгоритм выбрать не получается, в первую очередь из-за недостаточных знаний языка. Помогите пожалуйста оптимизировать запрос, или подскажите про какие функции почитать, которые позволят мне это сделать самому.

Ситуация. Есть торговые компании (1) которые закупаются напрямую от нашей компании, дальше они продукцию реализуют прочим торговым компаниям (2). От (1) отчеты есть всегда формате Excel, от (2) от части есть отчеты от второй части нет. Требуется определить основного поставщика для компании (2)(так как поставщиков может быть несколько) по каждой номенклатуре, в каждый месяц продажи. По текущей схеме расчет идет около 5 минут.

+
With A As -- Таблица фактов 1,7 млн строк
(Select
  cast (Dateadd (Year, -2000, _AccumRg7797._Period) as Date) as Период
, concat (year (_AccumRg7797._Period) - 2000 , month (_AccumRg7797._Period)) as месяц
, concat (year (_AccumRg7797._Period) - 2000 , Round ((month (_AccumRg7797._Period)+2)/3,0)) as Квартал
, year (_AccumRg7797._Period) - 2000 as год
--, CсылкаПартнер = cast (_AccumRg7797._Fld7843Rref as uniqueidentifier)
, CсылкаПартнерЦПР = cast (_Reference139._Fld7788Rref as uniqueidentifier)
--, CсылкаПокупатель = cast (_AccumRg7797._Fld7845Rref as uniqueidentifier)
, CсылкаПокупательЦПР = cast (Покупатели._Fld7788Rref as uniqueidentifier)
, Тип = ТК.Тип
, СсылкаНоменклатура = cast (_AccumRg7797._Fld7844Rref as uniqueidentifier)
, ФактическаяЦенаРуб = NULLIF(_Fld7847,0)/NULLIF(_Fld7846, 0)
, SaleIN_ЦенаРуб = NULLIF(_Fld10880,0)/NULLIF(_Fld7846,0)
, КоэфПересчета =  _Reference131._Fld7756
, КоличествоКГ = NULLIF(_Fld7846,0)/NULLIF(_Reference131._Fld7756,0)
, _AccumRg7797._Fld7846 as  [Количество, шт]
, _AccumRg7797._Fld7847 as [Сумма без НДС, руб.]
, _AccumRg7797._Fld7847 * ASD.СтавкаНДС / 100 as [Сумма НДС, руб.]
, _AccumRg7797._Fld7847 + ( _AccumRg7797._Fld7847 * ASD.СтавкаНДС / 100) as [Сумма c НДС, руб.]
, _AccumRg7797._Fld11224 as [Сумма в Рублях, руб.]
, _AccumRg7797._Fld11245 as [Сумма Базовые Цены без НДС, руб.]
, _AccumRg7797._Fld7848 as [Скидка]
, _AccumRg7797._Fld10880 as [Сумма SI, руб]
, ASD.СтавкаНДС
, _Reference94._Description as Валюта
, 
ТипОтчета = CASE cast(_Document7795._Fld7821 as uniqueidentifier)
     WHEN '00000000-0000-0000-0000-000000000000' THEN 'Продажи партнеров на прямых контрактах' -- для каждого отчета определяется к какой группе относится торговая компания из условия. Это группа (1)
     WHEN '00000001-0000-0000-0000-000000000000' THEN 'Продажи Оптовиков' -- это группа (2)
  ELSE 'нет данных'
  End 

from _AccumRg7797
left Join _Reference94
     ON _AccumRg7797._Fld10689Rref=_Reference94._IDRref
left Join _Reference131
    ON _AccumRg7797._Fld7844Rref=_Reference131._IDRref
left Join (
      select 
        СсылкаID = AAA._IdRref
        , СтавкаНДС=_Reference165._Fld3194
         From _Reference131 AAA
         left join _Reference165
         ON AAA._Fld2395Rref=_Reference165._IDRref
          ) ASD 
ON _AccumRg7797._Fld7844Rref=ASD.СсылкаID
left Join _Document7795 
       ON _AccumRg7797._RecorderRref  =_Document7795._IDrref
Left Join _Reference139
       ON _Reference139._IDRref = _AccumRg7797._Fld7843Rref
Left Join _Reference139 Покупатели
       ON Покупатели._IDRref = _AccumRg7797._Fld7845Rref
Left Join (Select 
   CASE _Enum7639._EnumOrder 
   WHEN 0 THEN 'Дистрибьютор'
   WHEN 1 THEN 'Субдистрибьютор'
   WHEN 2 THEN 'Оптовик'
   WHEN 3 THEN 'Аптека'
   WHEN 4 THEN 'Конечный потребитель'
  ELSE 'Тип клиента не указан'
  End as Тип
, A._IdRref 
From _Reference139 A
Left JOIN _Enum7639 
ON A._Fld7780Rref= _Enum7639._idRref
) as ТК
       ON ТК._IDRref = _AccumRg7797._Fld7845Rref
Where Тип <> 'Конечный потребитель' and Тип <> 'Аптека' --  отключая это условие расчет ведется 10 минут и выдает таймаут ожидания
)
---------------------------------------------------------------------------
, УникПар As -- Делаю уникальные список торговых компаний от которых были отчеты
(Select
Distinct CсылкаПартнерЦПР as УникПарСсылка
From A
Group by CсылкаПартнерЦПР
)
---------------------------------------------------------------------------
, УникПар2 As -- Делаю список торговых компаний из группы (1)
(Select
DISTINCT CсылкаПартнерЦПР as УникПар2Ссылка
From A
Where ТипОтчета = 'Продажи партнеров на прямых контрактах' 
)
---------------------------------------------------------------------------
,B as --к таблице А добавляю уникальные списки для дальнейшей фильтрации. Считает около 8 секунд
Select 
A.* 
,УникПар.УникПарСсылка as ПокупСПрод
,УникПар2.УникПар2Ссылка as ПартНаПрям
, Пок._Description as Пок - Добавил название покупателя для наглядности при проверке
, Пар._Description as Пар - Добавил название Поставщика для наглядности при проверке
, Ном._Description as Ном - Добавил название номенклатура для наглядности при проверке
From A
Left Join _Reference139 Пок
ON ПОК._Idrref = A.CсылкаПокупательЦПР
Left Join _Reference139 Пар
ON ПАР._Idrref = A.CсылкаПартнерЦПР
Left Join _Reference131 Ном
ON Ном._IdRref = A.СсылкаНоменклатура
Left Join УникПар      -- Чтобы определить от кого в дальнейшем были отчеты
ON УникПар.УникПарСсылка = A.CсылкаПокупательЦПР
Left Join УникПар2  -- Чтобы среди поставщиков остались только те кто попадает в список (1)
ON УникПар2.УникПар2Ссылка = A.CсылкаПартнерЦПР
Where УникПар.УникПарСсылка is not null
And УникПар2.УникПар2Ссылка is not null
)
--------------------------------------------------------------------------- с этого момента что-то пошло не так
,C1 as -- Группирую таблицу B 4 разных вариантах, а) при закупке и продаже месяц к месяцу
(Select 
Concat (CсылкаПокупательЦПР, СсылкаНоменклатура, Месяц) as ПокНомМес - для использования в следующей таблицк
, B.CсылкаПартнерЦПР as СсылкаОсновнойПоставщик
, B.Пок
, B.Пар
, B.Ном
, Месяц
, Sum ([Количество, шт]) as Сумм
From B
Group By Concat (CсылкаПокупательЦПР, СсылкаНоменклатура, Месяц)
, B.CсылкаПартнерЦПР
, B.Пок
, B.Пар
, B.Ном
, Месяц
)
---------------------------------------------------------------------------
,C2 as -- б) при закупке и продаже квартал к кварталу
(Select 
Concat (CсылкаПокупательЦПР, СсылкаНоменклатура, Квартал) as ПокНомМес
, B.CсылкаПартнерЦПР as СсылкаОсновнойПоставщик
, B.Пок
, B.Пар
, B.Ном
, Месяц
, Sum ([Количество, шт]) as Сумм
From B
Group By Concat (CсылкаПокупательЦПР, СсылкаНоменклатура, Квартал)
, B.CсылкаПартнерЦПР
, B.Пок
, B.Пар
, B.Ном
, Месяц
)
---------------------------------------------------------------------------
,C3 as -- в) при закупке и продаже год к году
(Select 
Concat (CсылкаПокупательЦПР, СсылкаНоменклатура, год) as ПокНомМес
, B.CсылкаПартнерЦПР as СсылкаОсновнойПоставщик
, B.Пок
, B.Пар
, B.Ном
, Месяц
, Sum ([Количество, шт]) as Сумм
From B
Group By Concat (CсылкаПокупательЦПР, СсылкаНоменклатура, год)
, B.CсылкаПартнерЦПР
, B.Пок
, B.Пар
, B.Ном
, Месяц
)
---------------------------------------------------------------------------
,C4 as -- г) без учета периода
(Select 
Concat (CсылкаПокупательЦПР, СсылкаНоменклатура) as ПокНомМес
, B.CсылкаПартнерЦПР as СсылкаОсновнойПоставщик
, B.Пок
, B.Пар
, B.Ном
, Месяц
, Sum ([Количество, шт]) as Сумм
From B
Group By Concat (CсылкаПокупательЦПР, СсылкаНоменклатура)
, B.CсылкаПартнерЦПР
, B.Пок
, B.Пар
, B.Ном
, Месяц
)

--------------------------------------------------------------------------
,D1 as -- Только на этом шаге начал определять кто же основной поставщик
(Select
C1.*
,row_number () over (Partition by C1.ПокНомМес Order By Сумм desc) as ОсновнойПоставщик
From C1)
--------------------------------------------------------------------------
,D2 as (Select
C2.*
,row_number () over (Partition by C2.ПокНомМес Order By Сумм desc) as ОсновнойПоставщик
From C2)
--------------------------------------------------------------------------
,D3 as (Select
C3.*
,row_number () over (Partition by C3.ПокНомМес Order By Сумм desc) as ОсновнойПоставщик
From C3)
--------------------------------------------------------------------------
,D4 as (Select
C4.*
,row_number () over (Partition by C4.ПокНомМес Order By Сумм desc) as ОсновнойПоставщик
From C4)
----------------------------------------------------------------------------
, E1 as 
(Select * From D1
Where ОсновнойПоставщик = 1)
----------------------------------------------------------------------------
, E2 as (Select * From D2
Where ОсновнойПоставщик = 1)
----------------------------------------------------------------------------
, E3 as (Select * From D3
Where ОсновнойПоставщик = 1)
----------------------------------------------------------------------------
, E4 as (Select * From D4
Where ОсновнойПоставщик = 1)
----------------------------------------------------------------------------
, F as - к исходной таблице добавляю столбец со ссылкой на основного поставщика
(Select 
   A.*
   ,case A.ТипОтчета 
        When 'Продажи партнеров на прямых контрактах' Then A.CсылкаПартнерЦПР - т.е. если поставщик из группы (1), то он сам для себя основной
        Else COALESCE(E1.СсылкаОсновнойПоставщик, E2.СсылкаОсновнойПоставщик, E3.СсылкаОсновнойПоставщик, E4.СсылкаОсновнойПоставщик)
    End as СсылкаОсновнойПоставщик
, Пок._Description as Пок
, Пар._Description as Пар
From A
Left Join _Reference139 Пок
ON ПОК._Idrref = A.CсылкаПокупательЦПР
Left Join _Reference139 Пар
ON ПАР._Idrref = A.CсылкаПартнерЦПР

     Left Join E1
ON E1.ПокНомМес = Concat(A.CсылкаПартнерЦПР,A.СсылкаНоменклатура,A.месяц)
     Left Join E2
ON E2.ПокНомМес = Concat(A.CсылкаПартнерЦПР,A.СсылкаНоменклатура,A.квартал)
     Left Join E3
ON E3.ПокНомМес = Concat(A.CсылкаПартнерЦПР,A.СсылкаНоменклатура,A.год)
     Left Join E4
ON E4.ПокНомМес = Concat(A.CсылкаПартнерЦПР,A.СсылкаНоменклатура)
)
----------------------------------------------------------------------------
Select * From F


Сообщение было отредактировано: 14 окт 19, 17:37
14 окт 19, 15:59    [21993862]     Ответить | Цитировать Сообщить модератору
 Re: Требуется помощь в оптимизации запроса  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Судя по названиям полей, это 1С.
Вообще тут принято описывать структуру данных и пример желаемого результата.
А в полях г..ноструктуры 1С без описания никто не будет...
14 окт 19, 22:49    [21994186]     Ответить | Цитировать Сообщить модератору
 Re: Требуется помощь в оптимизации запроса  [new]
AndSar
Member

Откуда:
Сообщений: 2
В сокращенном виде таблица А из примера принимает вид:

Тип Отчета|Ссылка Продавец |Ссылка покупатель|Ссылка номенклатура|Период|Кол-во, шт|Сумма руб|Результат Задачи (ссылка основной продавец)
(1)------------|12345------------------|456789-----------------|-------------------------------|-----------|---------------|---------------|12345
(1)------------|12345------------------|785965-----------------|-------------------------------|-----------|---------------|---------------|12345
(1)------------|12345------------------|23456------------------|-------------------------------|------------|---------------|---------------|12345
(2)------------|23456------------------|7896--------------------|-------------------------------|-----------|---------------|---------------|12345


Тип отчета (1) - это продажи партнеров, которые купили напрямую
Тип отчета (2) - это продажи партнеров, которые являлись покупателями (ссылка покупатель) при типе отчета (1) (строка 3 он покупатель, строка 4 уже продавец).

Задача: Добавить в таблицу столбец "Ссылка основной продавец", в котором для типа отчета (1) он будет равен столбцу "Ссылка продавец". А для типа отчета (2) нужно определить основного продавца. Т.е. Выбрать все строки где продавец (2) был покупателем (1), Сопоставить периоды, номенклатуру и по столбцу "Кол-во, шт" определить для него продавца.
Сложности следующие: У покупателя (2), может быть несколько продавцов - надо выбрать того кто продал больше.
Закупка покупателем (2) может не совпадать по периоду с моментом продажи, когда он уже выступает в роли продавца.

Надеюсь понятно описал задачу. А как описать структуру данных не знаю.
15 окт 19, 09:51    [21994297]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить