/
Implementation of data processing

Resolving totals for periodic information registers

Scope of application: managed application, mobile app, a common application.

1.1. For periodic information registers, it is recommended to allow totals if all of the following conditions are met:

• a large volume of data is expected in the register (for example, it is justified for a register with item prices; but it does not make sense for a register with exchange rates);
• the configuration provides frequency queries to slices of the latter at the current time and/or to slices of the former to obtain current data (i.e. when the period is not specified in the parameters of virtual tables Slice of the First And Slice of the Last);
• while the remaining conditions for virtual tables Slice of the First And Slice of the Last are set only to measurement values ​​(and separators in the mode Independently and jointly);
• register data access restrictions use only dimensions (and delimiters that are in mode Independently and jointly).

For a complete list of all conditions when queries use information register totals, seedocumentation for the 1C:Enterprise platform.

For example, if the configuration includes frequently executed queries to the register PricesNomenclatures to obtain current item prices:

SELECT Item. Article AS Article, PricesNomenclature. Price AS Price, . . . FROM Directory. Nomenclature AS Nomenclature LEFT CONNECTION Register of Information. PricesNomenclatures. SliceLast(, PriceView = &Type of Prices) HOW PricesNomenclatures Software PricesNomenclature. Nomenclature = Nomenclature. Link . . .

then, subject to all other conditions listed above, setting the property Allow totals: slice of recent ones will significantly speed up the execution of such queries, due to the fact that the selection will be performed directly from additional tables, which store only the last values ​​(for cutting the last ones) and the first values ​​(for cutting the first ones).

1.2. In addition, you should consider alternative options to revise requests to the register so that these conditions are met.

For example, if in some cases the data in the register PricesNomenclatures are recorded on a future date, and when selecting goods to this register, a query is always executed for the current date (the date is explicitly specified in the virtual table parameter Slice of the Last), then the results will not speed up the execution of such queries. Since the totals are built only for the first and last records of the register.

However, if, when opening the product selection form, you analyze whether there are registrars with a future date, and if there are none, you run another query for a slice of the latter without setting the date, then such a query will work faster.

2. In all other cases, totals should not be allowed for periodic information registers. First of all, if

• most often (always) queries are made to the virtual tables of the first/last periodic register of information for a specific period (for example, for the date of the document).
• in conditions for virtual tables Slice of the First And Slice of the Last Most often (always) subqueries and joins are used (calls “through a dot” to the fields of related tables). For example, in this case:

3. There is no need to provide a separate mechanism for recalculating totals in the configuration, since the updating of the totals tables is performed automatically every time a set of records is written to the register.

: Slice of the First And Slice of the Last Let's consider working with these virtual tables using 1C. Much more often used Slice of the Last, so let's start with it.

A slice of the latest allows you to obtain the last record of the information register for a specified date in the context of measurements. For the last (first) slice table, it is possible to specify two parameters in parentheses, separated by commas. The first parameter contains the date on which the slice is made (if the parameter is not specified, the slice is made on the current date). The second parameter is a condition in the 1C query language and allows you to set various selections. As a rule, measurements are used in these selections. All this sounds quite vague, so it’s impossible to do without an example.
So let us have a periodic register of information Price which stores prices by product and supplier. The frequency of the register is day.

The register contains the following entries

To begin with, we will get a slice of the latter without using parameters by executing the following request:

SELECT PriceSliceLast.Period AS Period, PriceSliceLast.Product AS Product, PriceSliceLast.Supplier AS Supplier, PriceSliceLast.Amount AS Amount FROM Register Information.Price.SliceLast AS PriceSliceLast

Since the parameters are not specified, the slice is performed on the current date - 02/01/2017. As a result, we get the following table

Here we see that the combination of dimensions Product + Supplier is unique, i.e. For each combination of register measurements, the record with the maximum date was taken, and the record date is less than or equal to the current date.

Let's say we need to do the same thing, but we want to get records with a date less than or equal to 01/15/2017. To do this, you need to change the row with the latest slice table in the request as follows

FROM RegisterInformation.Price.SliceLast(&CutDate,) AS PriceSliceLast

Before executing the request, of course, you need to pass a parameter to it &CutDate. Now the query result will look like this

And finally, imagine that we need to get a snapshot of the latest ones for the same date with the condition that we have the goods Pencil, and the supplier Stationery. To do this, specify the second parameter in the request

FROM RegisterInformation.Price.Last Cut(&CutDate, Product = &Product AND Supplier = &Supplier) AS PriceLast Cut

As a result, we get only one record

To avoid getting lost in all these parentheses and commas, it is better to use a query builder. I'll show you using the last request as an example.

After selecting a table with a slice of the latest ones in the query designer, you must click on the button Virtual Table Options and in the window that opens write

It is not difficult to guess that for the first slice the principle of operation is the same, except that the first record after the cut date is selected.

IN test configuration We have a periodic information register "PriceNomenclature" with the following source data:

The figure also shows the structure of the registry metadata. As we can see, the register contains the “Product” dimension with the reference type “Products”, as well as the “Price” numeric resource and the “OldPrice” attribute.

Let's say in a report we need to get a slice of the latest records for products and their prices with the condition that the old price is less than or equal to 50.

Two request options

I’ll say right away that we will consider the correct and incorrect options. Let’s start with the latter. This is a mistake that newbie programmers often make. And so, the following query was written for the report:

Request = New Request; Request. Text = " SELECT | | | | | FROM | Register of Information. PricesNomenclatures. Slice of the Latest HOW PricesNomenclatureSlice of the Latest|WHERE | PricesNomenclatureSliceLatest. Old price< = 50 " ;

Pay attention to the condition in the "WHERE" section. This is the main mistake! This query will not return a single record, and here's why: when using virtual tables, in our case "Last Slice", data is first fetched from the database according to the conditions described in virtual table, and then the actions described in the request text are performed (groupings, conditions in the “WHERE” section, sorting, etc.).

Therefore, in our example, the request does not return a result. First, he receives a slice of the latter, and only then sets the condition on the “Old Price” attribute. This is what it looks like in the diagram:

To solve the problem correctly, the condition for the “Old Price” attribute must be transferred to the conditions of the virtual table. This is what the correct request text will look like:

Request = New Request; Request. Text = " SELECT PricesNomenclatureSliceLatest. Period, PricesNomenclatureSliceLatest. Product, PricesNomenclatureSliceLatest. Price, PricesNomenclatureSliceLatest. Old price FROM Register of Information. PricesNomenclatures. SliceLast(, OldPrice< = 50 ) HOW PRICESNomenclatureSliceLatest"

Now the request will receive the correct data, since a slice of the latest prices will be received taking into account the condition for the “OldPrice” attribute.

results

It should be understood that the above applies to all cases of using virtual tables in queries (for accumulation registers, accounting registers, tasks, etc.).

This also implies the main rule for using virtual tables: “when using a virtual table, be sure to set the selection parameters directly in the virtual table, otherwise the query will receive unnecessary data, which will then be used for selection.”

Sometimes you need to use a query to obtain data for several dates at once from a periodic information register. A typical example is working with exchange rates. Let's consider an algorithm for solving this problem using an example.

Formulation of the problem

A document “Sales of Goods and Services” has been created in the database, in the header of which there is the attribute “Currency”. The request requires for each document to obtain the current exchange rate from the header on the date of the document. Currency rates are stored in the periodic information register “Currency Rates”.
A direct solution to this problem could be a query in a loop: obtaining all documents with their dates and currencies and, in the sample, accessing the virtual table with a slice of the latest “Currency Rates” register. But because a request in a loop is “bad”, let’s try to implement the task with one request.

Solution

To solve the problem, we will use the fact that the tables in the query can be connected not only for equality of fields.

SELECT Sales of Goods and Services. Link, Sales of Goods and Services. Currency, MAXIMUM (Currency Rates. Period) AS Period PLACE IN TPeriods Settings Rates FROM Document. Sales of Goods and Services HOW Sales of Goods and Services LEFT CONNECTION Register of Information. Currency Rates AS Currency Rates ON Sales of Goods servants.Currency = Currency Rates.Currency And Sales of GoodsServices.Date >= Currency Rates.Period GROUP Software Sales of Goods and Services. Link, Sales of Goods and Services. Currency; //////////////////////////////////////////////// ///////////////////////////// SELECT VTPeriodsSetting Rates.Link, VTPeriodsSetting Rates.Currency, RatesCurrency.Rate FROM VTPeriodsSetting Rates AS VTPeriodsSetting Rates LEFT CONNECTION RegisterInformation. Currency Rates AS Currency Rates ON VTPeriodsRate Settings.Period = Currency Rates.Period AND VTPeriodsRate Settings.Currency = Currency Rates.Currency

Procedure for request:

1. Obtaining the period for setting the exchange rate for each document. The documents are connected to the PHYSICAL table “Currency Rates”. Here you should pay attention to the connection conditions. The currencies must be equal, and the document date >= the period of the information register.
   As a result of such a connection, for each document, a set of rows will be obtained that satisfy the condition: all records of exchange rates for the currency of the document, established no later than the date of the document.
   The final step will be to group the rows to obtain the maximum rate period. As a result, for each document the required period for setting the exchange rate for the desired currency will be obtained (the maximum date for setting the exchange rate, but not more than the date of the document). The result is placed in the temporary table VTPeriodsSettingRates.
2. Getting a course. The temporary table VTPeriodsSetting Rates is connected to the PHYSICAL table “Currency Rates”. The connection occurs according to the Document Currency and the rate setting period defined in the second temporary table.