Platform Developers "1C: Enterprise 8" tried to do everything to not have to wrestle with coming up with complex algorithms for solving ordinary problems. But some non-trivial problems still remain. One of them is the task of calculating the balances on each day in the request.

The appearance of this problem is due to the fact that residues of the virtual table and with a period of revolutions "day" (for example) does not contain entries for the periods in which there was no speed. Obviously, this was done intentionally. Due to the fact that the determination of the value of residues within the results of the retention period associated with the solution of the problem of integration speed (calculation of cumulative). When using the basic instruction set of relational database management systems (without window functions) is quite resource-intensive task. Therefore, the rejection of the calculation in a virtual table residues and speed repetitive (and thus, in a sense, excess) values, in most cases, will save the execution time of the request. In more rare cases, when these data are needed, it is proposed to solve this problem by additional constructions in the query language or means of access control.

Although everything seems to solve the problem of calculating the balances on every day, "who in that much" and a lot of options for solutions, all known solutions can generally be divided into two classes in all.

The first basis is the speed table. Initial daily turnover summarized in the range from the beginning to date each time. This approach is exactly reproduces the work platform to identify residues within the period of storage to form a virtual table balances and turnovers. The only difference is that the summation is performed for each of the day. An example of this approach is the solution to the problem of article 6 of the [ minimalism ].

In the second approach is the basis of the table remains of those periods where there were revolutions. Remains for missed days are on the basis of the last sections: the nearest last residue for each date. An example of this approach is the decision from [ balances on each day period, one request - universal ].

Both in the first and in the second approach is required to generate a table of pre-date period. This is usually used: the production calendar, the table of exchange rates or artificial table date period, formed one of the conventional methods [ to work with dates in the query , that originates a request ].

The new method proposed in this article is based on a table residues, but do not require preforming table of dates. Due to the frequent repetition of a simple query remainder of the day it applies (domino principle) on the following days. Due to this omission in residues table quickly filled.

That proposed request:

 CHOOSE
	Nomenclature,
	Period,
	As VNalichiiKonechnyyOstatok
PUT Shag0
OF
	RegistrNakopleniya.TovaryNaSkladah.OstatkiIOboroty (& NachaloPerioda, & KonetsPerioda, Day,,)

Merge all

CHOOSE
	Nomenclature,
	&Beginning of period,
	0
OF
	RegistrNakopleniya.TovaryNaSkladah.OstatkiIOboroty (& NachaloPerioda, & KonetsPerioda, DAY,,)
;

//////// Repeated X times ////////
CHOOSE
	Nomenclature,
	Period,
	ESTNULL (MAX (It), MAX (So)) as it was
Step1 PUT
OF
	(CHOOSE
		HOW Nomenclature Nomenclature,
		Period as the period
		It was as it was,
		NULL as steel
	OF      
		Shag0 // table obtained in the previous step
	
	Merge all
	
	CHOOSE
		Nomenclature,
		DOBAVITKDATE (Period Day 1), // whenever twice days 1, 2, 4, and so on
		NULL,
		It was
	OF      
		Shag0
	WHERE
		DOBAVITKDATE (Period Day 1) <= & KonetsPerioda) like a bunch

grouped by
	Nomenclature,
	Period
;
//////// End repeats ////////

CHOOSE
	Nomenclature,
	Period,
	It was
OF
	ShagH

SORT BY
	Nomenclature,
	Period

The only difference between each repeating fragment of the following is twice the interval added to the date. To fill the interval of 32 days is enough five times, and the interval of the 1024 days - a ten-fold repetition of key fragment. The principle is probably understandable. Replays can be realized by building a dynamic query text or taking their number to reserve.

Perhaps someone given query may seem like a "Rube Goldberg machine" (it is shown in the image in the announcement). The resemblance really is. Here, too, used a domino effect. But in terms of efficiency is the opposite: given query is very economical. The best case is a large sparse remnants of the table. When, for instance, the turnover (and remainder) specified in the original table revolutions and residues just one day period, is required exactly N operations to fill the gaps of the interval N days. In the worst case, the lack of permits for the operation of the algorithm requires approximately N / 2 * log (N) time. It will be spent on unsuccessful attempts to extend the balance is already "busy" periods.

Probably, it is clear that the same query can be used for solving the problem of calculating the exchange rate for every day, the range of prices for daily and other periodic tasks interpolation information. To do this, the original table residues need to be replaced on a periodic table of the corresponding register data.

Thus, this approach works for solving any problem linear interpolation step in the request. To be specific, and because of the similarity of the principles can be called by the proposed method of dominoes.

For example, the article on the basis of the report attached SKD, deciding at the same time the problem of finding residues and prices every day. Interestingly, the proposed method allows to solve these problems simultaneously. The report is verified on the configuration 1C: ERP Enterprise Management 2.1, but should work in UT11 and KA2.

Special comparative performance testing of the new method and the conventional methods in this case are not performed. In other cases, [ Rapid determination of intervals in the request ] Similar approaches are beginning to outperform traditional for sufficiently large values of N. This is usually hundreds or thousands of dates in the period. Therefore, the decisive advantages in this method to already known to regular secondary data volumes as if not. Also peculiar beauty of the regularity of the final query.

Art object, exercise query language, a means of developing outlook or tool for analyzing large data - at least one of these applications to the above new ways, I hope there is.