Работа с регистрами через SQL (1С 7.7)
В 1С можно увидеть не очень удобное для работы в SQL понятие “регистры”. Цель у регистров довольно простая - обеспечить скорость подсчета чего-либо (суммы товара и прочего). С регистрами я начал работать при исследовании динамических свойств товара. Отмечу, извлекать из регистров историческую информацию очень накладно (приходится перебирать все документы, изменяющие динамические свойства). Вот я решил с вами поделиться очередной находкой, как при помощи средств SQL преобразовать регистр в другой вид таблицы.
Итак начнем
Связь таблиц для определения динамического свойства товара выглядит следующим образом в 1C:
Приведу краткое описание механизма.
Установка динамического свойства начинается с документа, который изменяет или устанавливает требуемое динамическое свойство. Документ оставляет изменение в регистре (в моем случае таблица ra9642), “1” делает свойство активным, а “-1” отменяет свойство. Остальные связи нужны для того, чтобы добраться до самого свойства. Только проведенный документ (значение _1sjourn.closed = 1) может двигать динамическое свойство, так что таблицу _1sjourn можно исключить из рассмотрения.
Итак, я хочу получить следующего рода таблицу: product_id, din_svo_id, date_start, date_finish. Такая таблица относится к классу темпоральных.
Что для этого надо сделать? Надо заменить регистр ra9642 и таблицу sc556 на темпоральную таблицу.
В результате мы можем ответить на следующий вопрос: “Какое динамическое свойство установлено у товара в произвольный момент времени?”. Для этого достаточно один раз перебрать регистр средствами SQL в обход стандартных процедур и получить готовую таблицу по всем динамическим свойствам. Для статистической базы - самое то, тогда для базы 1С не могу гарантировать оптимальную производительность. Выполнение запроса на реальной БД занимает 15125 ms (примерно 15 сек.) с возвращением данных (более 463 тыс. строк). Далее нужно перегрузить эти данные в статистическую базу и у вас все готово. Дальнейшая работа с данными зависит от оптимизации индексов и оптимизации ETL процессов. (Либо можно при следующей загрузки данных только догружать данные в таблицу за определенную дату. Но это уже техника настройки обновления таблиц).
Приступаем к практике
Для этого напишем “боевой” скрипт на SQL:
create view test as
select temp.*, sc556.descr as type_name_price
from
(
select t_from.sp9643 as product_id,
t_from.sp9647 as din_svo_id,
t_from.date_from,
min(t_to.date_to) as date_to
from (
select CAST(CAST(ra9642.DATE_TIME_IDDOC AS CHAR(8)) AS date) as date_from,
sp9643,
sp9647,
sp9645
from RA9642 where ra9642.sp9645 = 1
) as t_from inner join
(
select CAST(CAST(ra9642.DATE_TIME_IDDOC AS CHAR(8)) AS date) as date_to,
sp9643,
sp9647,
sp9645
from RA9642 where ra9642.sp9645 = -1
union
select cast('2999.12.31' AS date) as date_to,
sp9643,
sp9647,
-1
from RA9642 where ra9642.sp9645 = 1
)
as t_to ON t_from.sp9643 = t_to.sp9643 AND t_from.sp9647 = t_to.sp9647
where t_from.date_from <= t_to.date_to
group by t_from.sp9643,
t_from.sp9647,
t_from.date_from
) as temp inner join sc556 on sc556.ID = temp.din_svo_id;
create view test as нужен для создания представления и проведения тестирования правильности работы select запроса.
Идея работы
“1” и “-1” в регистре разбивают множество ra9642 на два различных подмножества, причем множество с “1” будет больше, чем множество с” -1” (логично, сначала устанавливаем свойство “1”, потом отменяем “-1”). Обзовем два множества t_from (“1”) и t_to (“-1”)
Сначала создадим множество t_from:
select CAST(CAST(ra9642.DATE_TIME_IDDOC AS CHAR(8)) AS date) as date_from,
sp9643, /*id_product*/
sp9647, /*id_свойства*/
sp9645 /*значение свойства*/
from RA9642
where ra9642.sp9645 = 1
Нужно выровнять мощность множеств или добиться того, чтобы t_to стало больше или равным t_from. Для этого используем следующий запрос, который формирует множество t_to:
select CAST(CAST(ra9642.DATE_TIME_IDDOC AS CHAR(8)) AS date) as date_to,
sp9643, /*id_product*/
sp9647, /*id_свойства*/
sp9645 /*значение свойства*/
from RA9642
where ra9642.sp9645 = -1
union
select CAST('2999.12.31' AS date) as date_to,
sp9643, /*id_product*/
sp9647, /*id_свойства*/
-1
from RA9642
where ra9642.sp9645 = 1
обратим внимание на условия отбора у двух операторов select, после чего станет ясно, какое множество мы получили (после union стоит запрос t_from, только значение “-1” отменяет динамическое свойство, и дата его отмены - '2999.12.31' ). Важный момент: нужно задать большую дату по умолчанию, к примеру '2999.12.31', так как эта дата будет у всех активных динамических свойств.
Далее начинаем объединять два множества t_from и t_to. Происходит объединение командой inner join
select t_from.sp9643 as product_id,
t_from.sp9647 as din_svo_id,
t_from.date_from,
min(t_to.date_to) as date_to
from
(...) as t_from inner join
(...) as t_to ON t_from.sp9643 = t_to.sp9643 AND t_from.sp9647 = t_to.sp9647
where t_from.date_from <= t_to.date_to
group by t_from.sp9643,
t_from.sp9647,
t_from.date_from
Разбираемся. При объединении множества нужно использовать inner join, так как множество t_to уже больше, чем t_from. Надо объединять по двум столбцам (так как один и тот же товар может иметь несколько динамических свойств), поэтому используем условие объединения on t_from.sp9643 = t_to.sp9643 and t_from.sp9647 = t_to.sp9647
Условие отбора where t_from.date_from <= t_to.date_to задает принцип объединения таблиц по дате. То есть за документом, устанавливающим свойство “1”, должен следовать документ с “-1” и с датой больше или равной “1”. Из всех дат, следующих за документом с “1” нужно выбрать минимальную дату с “-1” или min(t_to.date_to).
Отметим, что t_to.date_to приведена к формату Data. Если в один день устанавливались и отменялись свойства, и снова устанавливались и отменялись свойства, то тут могут быть “подводные камни”. Будьте внимательнее! Как выход - можно использовать тип Datetime.
Мне осталось только привести скрипты для создания БД с данными и опубликовать работу проверочного запроса.
CREATE TABLE sc556
(
row_id BIGINT
, id VARCHAR(15)
, descr VARCHAR(12)
, parentext BIGINT
, ismark BIGINT
, verstamp BIGINT
)
;
INSERT INTO sc556(row_id, id, descr, parentext, ismark, verstamp) VALUES (5423,'18S000','Распродажа',1,0,0);
INSERT INTO sc556(row_id, id, descr, parentext, ismark, verstamp) VALUES (5423,'18S001','Красная цена',1,0,0);
CREATE TABLE sc84
(
row_id BIGINT
, id VARCHAR(9)
, parentid VARCHAR(9)
, code BIGINT
, descr VARCHAR(40)
, isfolder BIGINT
, ismark BIGINT
, verstamp BIGINT
, sp85 BIGINT
, sp86 VARCHAR(9)
, sp208 BIGINT
, sp2417 BIGINT
, sp97 BIGINT
, sp5066 BIGINT
, sp5013 BIGINT
, sp94 VARCHAR(9)
, sp4427 VARCHAR(9)
, sp103 BIGINT
, sp104 BIGINT
, sp5012 BIGINT
, sp9466 BIGINT
, sp9502 BIGINT
, sp10358 BIGINT
, sp101 VARCHAR(13)
, sp95 TIMESTAMP
);
INSERT INTO sc84(row_id, id, parentid, code, descr, isfolder, ismark, verstamp, sp85, sp86, sp208, sp2417, sp97, sp5066, sp5013, sp94, sp4427, sp103, sp104, sp5012, sp9466, sp9502, sp10358, sp101, sp95) VALUES (34606,' EHG ',' EHC ',25975,'Terra VG Kids',2,0,3,701022,' BCX ',0,2,0,0,0,' BCX ',' X8U ',0,0,25,0,2,0,'Terra VG Kids',null);
INSERT INTO sc84(row_id, id, parentid, code, descr, isfolder, ismark, verstamp, sp85, sp86, sp208, sp2417, sp97, sp5066, sp5013, sp94, sp4427, sp103, sp104, sp5012, sp9466, sp9502, sp10358, sp101, sp95) VALUES (29647,' EHL ',' EHC ',25996,'Boston',2,0,6,601034,' BD2 ',0,2,0,0,0,' BD2 ',' XBN ',0,0,2,0,2,0,'Boston',null);
INSERT INTO sc84(row_id, id, parentid, code, descr, isfolder, ismark, verstamp, sp85, sp86, sp208, sp2417, sp97, sp5066, sp5013, sp94, sp4427, sp103, sp104, sp5012, sp9466, sp9502, sp10358, sp101, sp95) VALUES (19929,' EHH ',' EHC ',25991,'Hobart 25',2,0,2,310433,' BCY ',0,2,0,0,0,' BCY ',' X8S ',0,0,0,0,2,8000,'Hobart 25',null);
INSERT INTO sc84(row_id, id, parentid, code, descr, isfolder, ismark, verstamp, sp85, sp86, sp208, sp2417, sp97, sp5066, sp5013, sp94, sp4427, sp103, sp104, sp5012, sp9466, sp9502, sp10358, sp101, sp95) VALUES (4786,' EI0 ',' EHC ',26030,'Xpedition II',2,0,4,810033,' BDH ',0,2,0,0,0,' BDH ',' XAA ',0,0,5,0,2,8000,'Xpedition II',null);
INSERT INTO sc84(row_id, id, parentid, code, descr, isfolder, ismark, verstamp, sp85, sp86, sp208, sp2417, sp97, sp5066, sp5013, sp94, sp4427, sp103, sp104, sp5012, sp9466, sp9502, sp10358, sp101, sp95) VALUES (20143,' EI3 ',' EHC ',26231,'Hill 243053',2,0,1,243053,' BDK ',0,2,0,0,0,' BDK ',' X8Y ',0,0,53,0,2,0,'Hill 243053',null);
CREATE TABLE RA9642
(
iddoc VARCHAR(9)
, lineno_ VARCHAR(15)
, actno VARCHAR(15)
, debkred VARCHAR(15)
, iddocdef VARCHAR(15)
, date_time_iddoc VARCHAR(40)
, sp9643 VARCHAR(9)
, sp9647 VARCHAR(15)
, sp9645 BIGINT
);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' 4Y1V000','0','62','0','9648','2010121575LELC 4Y1V000',' EHL ','18S000',1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' 4Y1V000','0','83','0','9648','2010121575LELC 4Y1V000',' EI0 ','18S000',1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' 4YQG000','0','2','0','9648','20101216A8GBFK 4YQG000',' EHG ','18S000',1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' 7V9K000','0','2','0','9648','20111021A8UOEO 7V9K000',' EHL ','18S000',-1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' 8OT4000','0','3','0','9648','20111222CXHRBK 8OT4000',' EHL ','18S000',1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' 8OT4000','0','17','0','9648','20111222CXHRBK 8OT4000',' EI3 ','18S000',1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' 8OT4000','0','18','0','9648','20111222CXHRBK 8OT4000',' EHH ','18S001',1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' 9T3Y000','0','86','0','9648','20120217A4DPNK 9T3Y000',' EHH ','18S001',-1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' GAPJ000','0','32','0','9648','201209107AKSXC GAPJ000',' EHL ','18S000',-1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' GAPJ000','0','38','0','9648','201209107AKSXC GAPJ000',' EI3 ','18S000',-1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' JJRJ000','0','62','0','9648','201212119NUPWG JJRJ000',' EHH ','18S000',1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' KA4E000','0','22','0','9648','2012122570V81S KA4E000',' EHL ','18S000',1);
INSERT INTO RA9642(iddoc, lineno_, actno, debkred, iddocdef, date_time_iddoc, sp9643, sp9647, sp9645) VALUES (' KA4E000','0','38','0','9648','2012122570V81S KA4E000',' EI3 ','18S000',1);
Запускаем “боевой” скрипт. Далее проверочный скрипт:
SELECT sc84.descr, test.din_svo_id, test.date_from, test.date_to, test.type_name_price FROM public.test, public.sc84 WHERE sc84.id = test.product_id;
Вывод проверочного запроса:
Ссылки на материалы:
- Описание структуры таблиц на 1 С7: http://www.script-coding.com/v77tables.html
- Подробности
- Опубликовано: 23 Ноябрь 2013
- Просмотров: 4508