Сегодня мы рассмотрим: Настоящие ценители музыки знают, что для качественного...
При организации выборок в реальных задачах в подавляющем большинстве случаев организуется отбор данных в соответствии с некоторыми критериями.
В случае, когда выборка делается из реальной таблицы, никаких сложностей не возникает. Данные обрабатываются абсолютно тривиально:
В том случае, когда источником в запросе выступает виртуальная таблица, ситуация становится несколько сложнее.
Язык запросов позволяет наложить условие на выборку из виртуальных таблиц двумя способами: в предложении ГДЕ и с помощью параметров виртуальных таблиц. Оба способа приведут к одному результату (за исключением некоторых специфических случаев), но, тем не менее, они далеко не эквиваленты.
Мы уже знаем, что виртуальные таблицы потому и называются виртуальными, что в базе их на самом деле нет. Формируются они только в тот момент, когда к ним обращается запрос. Несмотря на это, нам (то есть, тем, кто составляет запрос) удобно рассматривать виртуальные таблицы именно как реально существующие. Что же произойдёт в системе 1С Предприятие 8, когда составленный нами запрос всё-таки обратится к виртуальной таблице?
На первом шаге, система построит виртуальную таблицу. На втором шаге из полученной таблицы будут выбраны записи, удовлетворяющие условию, заданному в предложении ГДЕ:
Хорошо видно, что в итоговую выборку попадут не все записи из виртуальной таблицы (а, следовательно, и из базы данных), а только те, которые удовлетворяют заданному условию. А остальные записи просто будут исключены из результата.
Таким образом, система проделает не просто бесполезную, а двойную бесполезную работу! Сначала будут затрачены ресурсы на построение виртуальной таблицы на основе лишних данных (на рисунке они помечены как «области данных А и Б»), а потом ещё будет проделана работа по фильтрации этих данных из окончательного результата.
Нельзя ли сразу, на этапе построения виртуальной таблицы, отказаться от использования ненужных данных? Оказывается, можно. Именно для этого и предназначены параметры виртуальных таблиц:
Параметризируя виртуальную таблицу, мы сразу ограничиваем объём данных, который будет обрабатываться запросом.
В чем заключается различие значений параметра виртуальной таблицы "МетодДополнения"?
Когда МетодДополнения установлен в "движения", то будут выданы только те периоды в которых были движения. Когда установлен "ДвиженияИГраницыПериода", тогда к вышеуказанным движениям добавятся 2 записи: движения на начало и конец заданного в параметрах ВТ периода. Поле "Регистратор" при этом для этих 2-х записей будет пустым.
Создадим отчет с одни набором данных запрос:
ВЫБРАТЬ ТоварыНаСкладахОстатки. Склад, ТоварыНаСкладахОстатки. Номенклатура, ТоварыНаСкладахОстатки. КоличествоОстаток ИЗ РегистрНакопления. ТоварыНаСкладах. Остатки(&МояДата ,) КАК ТоварыНаСкладахОстаткиТеперь перейдем на вкладку параметры и увидим что система, помимо нашего параметра &МояДата создала еще и параметр &Период.
Для того, чтобы наглядно наблюдать за периодами, создадим основную форму отчета и поместим на нее табличное поле с данными: КомпоновщикНастроек.Настройки.ПараметрыДанных
Сохраним отчет и откроем его в предприятии. В табличном поле с параметры отображается только параметр &Период:
Соответственно, любое изменение этого параметра не даст нужного результата.
Почему недоступен параметр &МояДата? Конечно же потому что на вкладке параметры у него установлена галку Ограничение доступности .
Снимаем галку. Теперь в доступных параметрах видим оба. Только при формировании отчета увидим, что отчет реагирует на параметр &Период, а не на &МояДата.
В данном примере самое простое переименовать в запросе параметр &МояДата на &Период и добиться нужного результата. Но может быть у Вас запрос, в котором уже использовался параметр &Период, или Ваши религиозные взгляды не разрешают Вам использовать этот параметр, в любом случае можно решить проблему так:
ВЫБРАТЬ ТоварыНаСкладахОстатки. Склад, ТоварыНаСкладахОстатки. Номенклатура, ТоварыНаСкладахОстатки. КоличествоОстаток ИЗ РегистрНакопления. ТоварыНаСкладах. Остатки({&МояДата} ,) КАК ТоварыНаСкладахОстаткиUPD от пользователя Boo :
Главная проблема при использовании «стандартных» (добавляемых системой) параметров в том что при использовании в отчете нескольких виртуальных таблиц, в случае определения этого параметра, его значение будет использоваться во всех остальных случаях взамен «собственных».
Приведу пример:
ВЫБРАТЬ РаботникиСП.Сотрудник, РаботникиСП.ПричинаИзмененияСостояния, РаботникиСП.Период, РаботникиСПДругаяДата.Период КАК Период2, РаботникиСПДругаяДата.ПричинаИзмененияСостояния КАК ПричинаИзмененияСостояния2 ИЗ РегистрСведений.РаботникиОрганизаций.СрезПоследних(&Период , Сотрудник = &Сотрудник ) КАК РаботникиСП ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.РаботникиОрганизаций.СрезПоследних(&ДругаяДата ,) КАК РаботникиСПДругаяДата ПО РаботникиСП.Сотрудник = РаботникиСПДругаяДата.СотрудникВо втором подзапросе, в качестве параметра даты среза будет использовано значение «стандартного» параметра ПЕРИОД, а не значение ДругаяДата.
Данный «глюк» будет наблюдаться даже в том случае если второй подзапрос вывести во второй набор данных и связать уже средствами СКД. Вариант с использованием во втором запросе ваыражения типа «ДОБАВИТЬКДАТЕ(&Период, МЕСЯЦ, -1) » тоже не сработает, месяц не вычтется. А вот переименование в запросе параметра «Период» в, например, «ПерваяДата», решает эту проблему.
Кстати, точно такая же проблема наблюдается с виртуальными таблицами регистров накопления и бухгалтерии, используемыми для получения, например, оборотов. Там система добавляет параметры «НачалоПериода» и «КонецПериода».
Так что в случае запросов даже чуть повышенной сложности, есть смысл выключать доступность и использование «стандартных периодов».
Платформа формирует виртуальные таблицы Остатки, Обороты, и Остатки и обороты; для оборотных только Обороты:
Виртуальные таблицы не существуют физически в базе данных, они генерятся именно на уровне платформы, для повышения быстроты и удобства доступа разработчика к данным.
Перед построением виртуальных таблиц, данные можно отобрать по параметрам. Набор параметров различен для разных таблиц. Для таблицы Остатков это период остатков и произвольное условие:
Для таблицы Оборотов период разбивается на два (начало и конец), и добавляется настройка Периодичности:
Для таблица Остатков и оборотов добавляется настройка Метода дополнения:
Рассмотрим подробнее все эти параметры.
- Параметр Период (Начало периода, Конец периода) могут принимать значения типа Дата:
а также типа Момент времени и Граница:
Важно, что в случае использования типа Дата либо Момент времени (комбинации Даты и Ссылки) сама секунда времени , указанная в поле выборки, не учитывается в выборке данных! Она уже "за пределами" нее, выборка строится до/после заданного момента.
В случае использования типа Граница, этот нюанс настраивается, так как можно указать тип границы: показывать данные включая или исключая заданную секунду:
- Параметр Периодичность - позволяет определить период, к которому будут приведены записи. Например, если периодичность Год, то все записи будут показаны как на 01 января.
Отдельно интересен вариант периодичности Авто, он позволяет в одном запросе привести данные к нескольким периодам. В конструкторе запроса при этом становятся доступны все варианты периода, можно выбрать нужные:
Результат:
- Параметр Метод дополнения - определяет, строить ли запрос только на основании движений за период (вариант Движения), или учитывать и начальные остатки (вариант Движения и границы периода). Отличные на выходе в следующем: если например по номенклатуре были остатки на начало, но не было оборотов за период, то в случае Движений она не попадет в выборку; в случае Движения и границы периодов - попадет с нулевым оборотом
- Параметр Условие - позволяет наложить произвольное условие на измерение регистра:
Вопрос 07.46 экзамена 1С:Профессионал по платформе. При определении в виртуальной таблице "ОстаткиИОбороты" варианта периодичности "Авто":
- система автоматически подберет периодичность получения оборотов исходя из правила: чтобы в результате оказалось не менее 3-х значений периодов, но не более 12
- система автоматически подберет периодичность получения оборотов исходя из правила: чтобы в результате оказалось не менее 5-ти значений периодов, но не более 10
- в полях выборки запроса разработчик сможет выбирать поля периода с разной периодичностью
Правильный ответ третий, разбор выше.
Статья описывает физическую реализацию виртуальной таблицы остатков конфигурации, работающей в клиент-серверном режиме работы на примере использования СУБД MS SQL Server.
Применимость
В статье рассматривается платформа «1С:Предприятие» редакции 8.3.5.1383. В актуальной версии платформы возможны некоторые изменения в тексте, описанного в материале, запроса T-SQL, выполняемого на стороне сервера СУБД.
Устройство виртуальной таблицы остатков
Рассмотрим, в какой запрос к СУБД трансформируется запрос с использованием виртуальной таблицы остатков регистра накопления. Для примера будет рассматриваться следующий текст запроса:
ВЫБРАТЬ
ТоварныеЗапасыОстатки.Товар
,
ТоварныеЗапасыОстатки.Склад
,
ТоварныеЗапасыОстатки.КоличествоОстаток
ИЗ
РегистрНакопления.ТоварныеЗапасы.Остатки
(&Дата
,
Склад
=
&Склад
)
КАК
ТоварныеЗапасыОстатки
Сначала при помощи метода глобального контекста ПолучитьСтруктуруХраненияБазыДанных()
получим список таблиц базы данных, в которых хранятся данные регистра накопления «ТоварныеЗапасы»:
Состав полей основной таблицы регистра накопления и таблицы итогов приведен ниже:
Хранение итогов для данного регистра настроено в режиме «1С:Предприятие 8» следующим образом:
Параметры в рассматриваемом запросе заполним следующим образом:
Платформа преобразует текст запроса в следующий запрос, который и будет выполнен на сервере СУБД:
SELECT
Q_000_T_001.Fld82
,
Q_000_T_001.Fld83
,
Q_000_T_001.Fld84Balance
FROM
(SELECT
Fld82
,
Fld83
,
FROM
(SELECT
Fld82
,
Fld83
,
SUM
(Fld84
)
AS
Fld84Balance
FROM
AccumRgT85
WHERE
Period
=
DATETIME
(3999
,
11
,
1
)
AND
((Fld83
=
))
AND
(Fld84
<>
0
)
AND
(Fld84
<>
0
)
GROUP
BY
Fld82
,
Fld83
HAVING
Fld84Balance
<>
0
UNION
ALL
SELECT
Fld82
,
Fld83
,
SUM
(CASE
WHEN
RecordKind
=
0
THEN
–
Fld84
ELSE
Fld84
END
)
AS
Fld84Balance
FROM
AccumRg81
WHERE
Period
>=
DATETIME
(2012
,
9
,
1
)
AND
Period
<
DATETIME
(3999
,
11
,
1
)
AND
Active
AND
((Fld83
=
9:))
GROUP
BY
Fld82
,
Fld83
HAVING
Fld84Balance
<>
0
)
T
GROUP
BY
Fld82
,
Fld83
HAVING
Fld84Balance
<>
0
)
Q_000_T_001
Разберем подробнее полученный запрос.
Сначала при помощи первого запроса, входящего в объединение, выбираются данные из итоговой таблицы AccumRgT85. Итоги получаются на дату хранения текущих итогов (01.11.3999), дополнительно накладывается условие на поле Склад (поскольку такое условие использовалось в параметрах виртуальной таблицы). Дополнительно выполняется проверка на отсутствие в результате строк с нулевыми остатками.
Обратите внимание, что производится группировка по выбранным в тексте запроса измерениям. Именно поэтому не требуется в тексте на языке запросов «1С:Предприятие» дополнительно выполнять группировку по измерениям.
Во втором запросе объединения используется таблица движений регистра AccumRg81. В зависимости от вида движения (если RecordKind равно 0, то это Приход, в противном случае – Расход) проставляется знак в выражении. Платформа выбирает данные за период с даты, указанной в качестве параметра виртуальной таблицы, по дату хранения текущих итогов (01.11.3999).
Кроме этого отбираются только активные записи, поле Склад должно быть равно заданному значению. Как и в первом запросе объединения, здесь также производится группировка по выбранным измерениям и отбрасываются записи с нулевыми значениями ресурсов.
Если используется СУБД MS SQL Server и для базы данных установлено смещение дат 2000, то все даты будут храниться с указанным смещением, т.е. вместо 01.11.3999 Вы увидите 01.11.5999.
Если для регистра накопления отключить текущие итоги, то платформа сначала получит последние итоги, рассчитанные на дату, раньше указанной в параметре Период виртуальной таблицы.
Затем аналогично эти данные будут дополнены из таблицы движений, но только за период с даты последних итогов по период виртуальной таблицы.
SELECT
Q_000_T_001.Fld82
,
Q_000_T_001.Fld83
,
Q_000_T_001.Fld84Balance
FROM
(SELECT
Fld82
,
Fld83
,
SUM
(Fld84Balance
)
AS
Fld84Balance
FROM
(SELECT
Fld82
,
Fld83
,
SUM
(Fld84
)
AS
Fld84Balance
FROM
AccumRgT85
WHERE
Period
=
DATETIME
(2012
,
4
,
1
)
AND
((Fld83
=
9:))
AND
(Fld84
<>
0
)
AND
(Fld84
<>
0
)
GROUP
BY
Fld82
,
Fld83
HAVING
Fld84Balance
<>
0
UNION
ALL
SELECT
Fld82
,
Fld83
,
SUM
(CASE
WHEN
RecordKind
=
0
THEN
Fld84
ELSE
–
Fld84
END
)
AS
Fld84Balance
FROM
AccumRg81
WHERE
Period
>=
DATETIME
(2012
,
4
,
1
)
AND
Period
<
DATETIME
(2012
,
9
,
1
)
AND
Active
AND
((Fld83
=
9:))
GROUP
BY
Fld82
,
Fld83
HAVING
Fld84Balance
<>
0
)
T
GROUP
BY
Fld82
,
Fld83
HAVING
Fld84Balance
<>
0
)
Q_000_T_001
Обратите внимание на следующее условие в тексте запроса.