Генерация числовой последовательности и последовательности дат при помощи языка запросов

Немного переделанная версия источника.

Генерация числовой последовательности

Иногда может возникнуть ситуация, когда необходимо в запросе получить последовательность чисел. Обычно это не является самоцелью, а служит промежуточным результатом для получения последовательности дат. Тут есть два способа, первый — это пойти в лоб и написать запрос, объединяющий столько запросов, сколько чисел нам необходимо получить. Например:

ВЫБРАТЬ

    1 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3

Второй способ заключается в использовании декартова произведения, особенностью которого является то, что мощность результата (количество строк) равно произведению мощностей участвующих в декартовом произведении таблиц. Т.е. если нам нужно получить последовательность 25 чисел мы можем использовать декартово произведение таблиц, каждая из которых содержит по 5 записей. В стандартном SQL для декартова произведения используется конструкция CROSS JOIN, в языке запросов 1С такая конструкция отсутствует, но мы можем воспользоваться обычным внутренним соединением, указав условие связи ИСТИНА. Далее приведен пример запроса, генерирующего последовательность от 1 до 25:

ВЫБРАТЬ

    5*a + b

    ИЗ

    (ВЫБРАТЬ

        0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4) КАК t1

        ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ

        1 КАК b ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5) КАК t2

        ПО ИСТИНА

В данном запросе мы используем декартово произведение двух таблиц содержащее по пять строк. Значение мы вычисляем по следующему принципу: считаем что в первой таблицы у нас содержатся пятёрки, а во второй единицы. Результат вычисляем по формуле 5*a + b. Чем больше мы хотим генерировать последовательность, тем больше таблиц в декартовом произведении нам необходимо использовать.

Генерация последовательности дат

Допустим перед нами стоит задача, сгенерировать последовательность дат месяца.

ВЫБРАТЬ ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(&МЕСЯЦ, МЕСЯЦ), ДЕНЬ, n) КАК Дата

ИЗ (ВЫБРАТЬ

    6*a + b КАК n

    ИЗ

    (ВЫБРАТЬ

        0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5) КАК t1

        ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ

        0 КАК b ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5) КАК t2

        ПО ИСТИНА) КАК T

    ГДЕ

        n < ДЕНЬ(КОНЕЦПЕРИОДА(&МЕСЯЦ, МЕСЯЦ))

Во вложенном запросе мы формируем числовую последовательность от 0 до 35, далее в запросе верхнего уровня мы генерируем последовательность дат, ограничивая её последним числом месяца. В качестве параметра &МЕСЯЦ в запрос можно передавать любую дату выбранного месяца.

Та же версия с меньшим числом вложенных запросов:

ВЫБРАТЬ ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(&МЕСЯЦ, МЕСЯЦ), ДЕНЬ, 6*a + b) КАК Дата

ИЗ (ВЫБРАТЬ

        0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5) КАК t1

        ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ

        0 КАК b ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5) КАК t2

        ПО ИСТИНА

    ГДЕ

        6*a + b < ДЕНЬ(КОНЕЦПЕРИОДА(&МЕСЯЦ, МЕСЯЦ))

Получение последовательности дат для одного года:

ВЫБРАТЬ ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(&ВыбранныйДень, ГОД), ДЕНЬ, n) КАК Дата

ИЗ (ВЫБРАТЬ
    20*(a - 1) + b - 1 КАК n
    ИЗ
    (ВЫБРАТЬ

       1 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9 ОБЪЕДИНИТЬ ВЫБРАТЬ 10 ОБЪЕДИНИТЬ ВЫБРАТЬ 11 ОБЪЕДИНИТЬ ВЫБРАТЬ 12 ОБЪЕДИНИТЬ ВЫБРАТЬ 13 ОБЪЕДИНИТЬ ВЫБРАТЬ 14 ОБЪЕДИНИТЬ ВЫБРАТЬ 15 ОБЪЕДИНИТЬ ВЫБРАТЬ 16 ОБЪЕДИНИТЬ ВЫБРАТЬ 17 ОБЪЕДИНИТЬ ВЫБРАТЬ 18 ОБЪЕДИНИТЬ ВЫБРАТЬ 19 ОБЪЕДИНИТЬ ВЫБРАТЬ 20) КАК t1
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
       1 КАК b ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9 ОБЪЕДИНИТЬ ВЫБРАТЬ 10 ОБЪЕДИНИТЬ ВЫБРАТЬ 11 ОБЪЕДИНИТЬ ВЫБРАТЬ 12 ОБЪЕДИНИТЬ ВЫБРАТЬ 13 ОБЪЕДИНИТЬ ВЫБРАТЬ 14 ОБЪЕДИНИТЬ ВЫБРАТЬ 15 ОБЪЕДИНИТЬ ВЫБРАТЬ 16 ОБЪЕДИНИТЬ ВЫБРАТЬ 17 ОБЪЕДИНИТЬ ВЫБРАТЬ 18 ОБЪЕДИНИТЬ ВЫБРАТЬ 19 ОБЪЕДИНИТЬ ВЫБРАТЬ 20) КАК t2

        ПО ИСТИНА) КАК T
    ГДЕ
        n < ДЕНЬГОДА(КОНЕЦПЕРИОДА(&ВыбранныйДень, ГОД))

Получение последовательности дат для выбранного периода (&ДатаНач, &ДатаКон) с разбивкой по дням:

 
ВЫБРАТЬ
    ДОБАВИТЬКДАТЕ(&ДатаНач, День, (Цифры4.Цифра * 1000
                                 + Цифры3.Цифра * 100
                                 + Цифры2.Цифра * 10 
                                 + Цифры1.Цифра * 1 
                                 )) КАК ДатаДень
ИЗ
    (
        ВЫБРАТЬ 0 КАК Цифра ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 1           ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 2             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 3             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 4             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 5             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 6             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 7             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 8             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 9
    ) КАК Цифры1
    СОЕДИНЕНИЕ 
    (
        ВЫБРАТЬ 0 КАК Цифра ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 1           ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 2             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 3             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 4             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 5             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 6             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 7             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 8             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 9
    ) КАК Цифры2
    ПО (  Цифры2.Цифра * 10 
        + Цифры1.Цифра * 1 
        ) <= РАЗНОСТЬДАТ(&ДатаНач, &ДатаКон, День)
    СОЕДИНЕНИЕ 
    (
        ВЫБРАТЬ 0 КАК Цифра ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 1           ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 2             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 3             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 4             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 5             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 6             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 7             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 8             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 9
    ) КАК Цифры3
    ПО (  Цифры3.Цифра * 100
        + Цифры2.Цифра * 10 
        + Цифры1.Цифра * 1 
        ) <= РАЗНОСТЬДАТ(&ДатаНач, &ДатаКон, День)
    СОЕДИНЕНИЕ 
    (
        ВЫБРАТЬ 0 КАК Цифра ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 1           ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 2             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 3             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 4             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 5             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 6             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 7             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 8             ОБЪЕДИНИТЬ ВСЕ 
        ВЫБРАТЬ 9
    ) КАК Цифры4
    ПО (  Цифры4.Цифра * 1000
        + Цифры3.Цифра * 100
        + Цифры2.Цифра * 10 
        + Цифры1.Цифра * 1 
        ) <= РАЗНОСТЬДАТ(&ДатаНач, &ДатаКон, День)
УПОРЯДОЧИТЬ ПО ДатаДень

Данная последовательность ограничена по длине периода (10000 дней), но для обычных алгоритмов этого вполне хватает.

Можно сократить запрос, если поместить цифры в отдельную временную таблицу:

ВЫБРАТЬ 0 КАК Цифра
ПОМЕСТИТЬ Цифры
                              ОБЪЕДИНИТЬ ВСЕ
        ВЫБРАТЬ 1             ОБЪЕДИНИТЬ ВСЕ
        ВЫБРАТЬ 2             ОБЪЕДИНИТЬ ВСЕ
        ВЫБРАТЬ 3             ОБЪЕДИНИТЬ ВСЕ
        ВЫБРАТЬ 4             ОБЪЕДИНИТЬ ВСЕ
        ВЫБРАТЬ 5             ОБЪЕДИНИТЬ ВСЕ
        ВЫБРАТЬ 6             ОБЪЕДИНИТЬ ВСЕ
        ВЫБРАТЬ 7             ОБЪЕДИНИТЬ ВСЕ
        ВЫБРАТЬ 8             ОБЪЕДИНИТЬ ВСЕ
        ВЫБРАТЬ 9
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ДОБАВИТЬКДАТЕ(&ДатаНач, День, (Цифры4.Цифра * 1000
                                 + Цифры3.Цифра * 100
                                 + Цифры2.Цифра * 10
                                 + Цифры1.Цифра * 1
                                 )) КАК ДатаДень
ИЗ
    Цифры КАК Цифры1
    СОЕДИНЕНИЕ Цифры КАК Цифры2
    ПО (  Цифры2.Цифра * 10
        + Цифры1.Цифра * 1
        ) <= РАЗНОСТЬДАТ(&ДатаНач, &ДатаКон, День)
    СОЕДИНЕНИЕ Цифры КАК Цифры3
    ПО (  Цифры3.Цифра * 100
        + Цифры2.Цифра * 10
        + Цифры1.Цифра * 1
        ) <= РАЗНОСТЬДАТ(&ДатаНач, &ДатаКон, День)
    СОЕДИНЕНИЕ Цифры КАК Цифры4
    ПО (  Цифры4.Цифра * 1000
        + Цифры3.Цифра * 100
        + Цифры2.Цифра * 10
        + Цифры1.Цифра * 1
        ) <= РАЗНОСТЬДАТ(&ДатаНач, &ДатаКон, День)
УПОРЯДОЧИТЬ ПО ДатаДень