Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3 4   вперед  Ctrl      все
 Re: Список дат между двумя заданными  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31778
nekiyl
TaPaK, дело в том, что искал что-то подобное, но в данной ветке предлагались достаточно громоздкие решения и поэтому решил поделиться отсутствующим тут простым вариантом, так как другие люди так же могут искать решение данной задачи. Что касается шести лет, то в конкретном случае и не требуется больших промежутков, и вероятно в большом количестве случаев этого будет достаточно. По поводу доступа пользователей к базе данных в конкретной системе пользователи вообще не имеет доступа к база, а работает на уровне приложения, соответственно данной проблемы быть не может.
В этой ветке в итоге уже предложили красивое, лёгкое решение - сделать таблицу-календарь.

Кроме решения вывода в отчёте отсутствующих дат, она в реальных системах нужна, например, для хранения информации о рабочих и праздничных дней, ускорения (инедксации) поисков типа "все понедельники", а так же для хранения в статике всяких сложных расчётов, типа количества рабочих часов, и пр.
23 май 16, 21:28    [19209542]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
nekiyl,

Последнее приложение впечатляет
23 май 16, 21:40    [19209571]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
AlanDenton
А к чему сразу критиковать? :) Все выбирают решение в меру своих потребностей. Если я ожидаю, что будет малый диапазон, то либо заранее сгенеренная таблица либо spt_values. Если нужно необъяснимо много, то можно и так:

DECLARE @StartDate DATE = '20010101'
DECLARE @EndDate DATE = '20790101'

;WITH
    E1(N) AS (
        SELECT * FROM (
            VALUES
                (1),(1),(1),(1),(1),
                (1),(1),(1),(1),(1)
        ) t(N)
    ),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b),
    E4(N) AS (SELECT 1 FROM E2 a, E2 b),
    E8(N) AS (SELECT 1 FROM E4 a, E4 b)
SELECT DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY 1/0) - 1, @StartDate)
FROM (
    SELECT TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1) N
    FROM E8
) t


А так нельзя было ?

DECLARE @StartDate DATE = '20010101'
DECLARE @EndDate DATE = '20790101'

;WITH
    E1(N) AS (
        SELECT * FROM (
            VALUES
                (0),(1),(2),(3),(4),
                (5),(6),(7),(8),(9)
        ) t(N)
    ),
    E2(N) AS (SELECT a.N * 10 + b.N FROM E1 a, E1 b),
    E4(N) AS (SELECT a.N * 100 + b.n FROM E2 a, E2 b),
    E8(N) AS (SELECT a.N * 10000 + b.N FROM E4 a, E4 b)
SELECT DATEADD(DAY, N, @StartDate)
FROM (
    SELECT TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1) N
    FROM E8
) t
ORDER BY N
24 май 16, 12:08    [19211437]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
a_voronin
А так нельзя было ?
+ Нельзя
declare @n int;

set statistics time on;

print 'AlanDenton'
;WITH
    E1(N) AS (
        SELECT * FROM (
            VALUES
                (1),(1),(1),(1),(1),
                (1),(1),(1),(1),(1)
        ) t(N)
    ),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b),
    E4(N) AS (SELECT 1 FROM E2 a, E2 b),
    E8(N) AS (SELECT 1 FROM E4 a, E4 b)
select @n = ROW_NUMBER() OVER (ORDER BY 1/0) - 1 FROM E8
option (maxdop 1);

print 'a_voronin'
;WITH
    E1(N) AS (
        SELECT * FROM (
            VALUES
                (0),(1),(2),(3),(4),
                (5),(6),(7),(8),(9)
        ) t(N)
    ),
    E2(N) AS (SELECT a.N * 10 + b.N FROM E1 a, E1 b),
    E4(N) AS (SELECT a.N * 100 + b.n FROM E2 a, E2 b),
    E8(N) AS (SELECT a.N * 10000 + b.N FROM E4 a, E4 b)
SELECT @n = N from E8
option (maxdop 1);

set statistics time off;


AlanDenton

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 16411 ms, elapsed time = 16419 ms.

a_voronin

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 31216 ms, elapsed time = 31280 ms.
24 май 16, 13:02    [19211881]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
iljy
Member

Откуда:
Сообщений: 8711
a_voronin,

можно. Но есть нюанс. При использовании такого генератора оптимизатор считает возвращаемую им последовательность неупорядоченной. Как следствие, top вам может вернуть все, что угодно (теоретически, но думаю, на практике тоже можно этого добиться). А если вы добавите туда ORDER BY, то он сначала вынужден будет сгенерить всю последовательность, отсортировать ее, и только потом выбрать.
Ну и более практическое следствие (на датах это не проявится, он почему-то не умеет оптимизировать сортировку по DATEADD, но вообще на числах - легко): если вы захотите получить уже усеченную выдачу генератора отсортированной, то в плане появится явная операция сортировки. Проверьте для запроса типа
SELECT ROW_NUMBER() OVER (ORDER BY 1/0) + 100
FROM (
    SELECT TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1) N
    FROM E8
) t
order by 1
24 май 16, 13:07    [19211912]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
iljy
Member

Откуда:
Сообщений: 8711
a_voronin,

ну и да, как уже показал invm, вычисление выражения с умножениями занимает сильно больше времени, чем просто генерация последовательности целых чисел.

И если уж заговорили про скорость, то самый быстрый вариант это все еще таблица с целыми числами.
24 май 16, 13:14    [19211954]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
iljy
Как следствие, top вам может вернуть все, что угодно (теоретически, но думаю, на практике тоже можно этого добиться)
На практике такой набор сразу получится неупорядоченным, т.к. через кучу NL-в значения не могут вычисляться последовательно.
24 май 16, 13:57    [19212280]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
iljy
Member

Откуда:
Сообщений: 8711
invm,

не, ну теоретически можно попробовать подобрать порядок соединений и т.п., но баловство это, конечно.
24 май 16, 15:09    [19212930]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
по сабжу: только готовая таблица с датами, усиленная дополнительными полями: номер года, месяца, недели, праздники и т.д.

+ это лаконично и удобно, особенно в сложных запросах;
+ это можно использовать многократно;
+ это можно дополнить любыми нужными параметрами/полями;
+ это быстро, т.к. маленькая живая таблица + индексы;
+ это одинаково заработает на всех СУБД, т.е. это портабельно.

Искренне не понимаю, зачем предлагают разного рода генерацию списка внутри запросов. Идиотизм, ИМХО.
Жалко таблички с неск. полями ? :)
25 май 16, 09:13    [19215855]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31778
LSV
по сабжу: только готовая таблица с датами, усиленная дополнительными полями: номер года, месяца, недели, праздники и т.д.

+ это лаконично и удобно, особенно в сложных запросах;
+ это можно использовать многократно;
+ это можно дополнить любыми нужными параметрами/полями;
+ это быстро, т.к. маленькая живая таблица + индексы;
+ это одинаково заработает на всех СУБД, т.е. это портабельно.

Искренне не понимаю, зачем предлагают разного рода генерацию списка внутри запросов. Идиотизм, ИМХО.
Жалко таблички с неск. полями ? :)
Вот тоже не понимаю упорство, с которым люди хотят заменить использование константы в коде на сложные вычисления.

В рутинных инженерных расчётах, наверное, эти разработчики Pi тоже не вставляют в код как константу, а неделями соревнуются в оптимизации алгоритмов расчётов, для обычного вычисления площади сечения арматурного прутка?

Какие перемножения множеств? Какие spt_values?? Какие рекурсии???

Нужен календарь, используйте календарь, никаких других вариантов!

Все эти извращения нужны только для одного - заполнить таблицу-календарь после её создания. И такая задача не стоит обсуждения, в силу её разовости и примитивности.
25 май 16, 10:11    [19216185]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
alexeyvg
Нужен календарь, используйте календарь, никаких других вариантов!
Я бы сказал, что можно и не календарь, а просто таблу с числами
(если не требуются специфические переносы выходных, праздники и т.п.)
25 май 16, 10:32    [19216331]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31778
iap
alexeyvg
Нужен календарь, используйте календарь, никаких других вариантов!
Я бы сказал, что можно и не календарь, а просто таблу с числами
(если не требуются специфические переносы выходных, праздники и т.п.)
Это да, таблица с числами как вариант.
Для всяких управленческо-финансовых систем календарь нужен на 99%, в остальных по обстоятельствам.
А таблица с числами нужна практически везде. Можно, конечно, использовать и spt_values, но это менее эффективно и менее надёжно.

Димкин
В БД и так более 400 таблиц
Вообще, есть общий принцип.
Если бизнес требования можно реализовать кодом или моделью данных (таблицами), выбирайте второе.
Оно будет быстрее, проще, надёжнее, лучше для сопровождения, лучше для повторного использования, понятнее для новых сотрудников и для начальников.
Хоть даже 4000 таблиц, а не 400, не проблема. Календарь на 100 лет будет содержать 36500 строк и занимать, скажем, пусть даже мегабайт, ничего страшного.
Вы же не пишите: "в БД и так 100500 строк кода, куда ещё новый вставлять?" Почему такое предубеждение к таблицам? И такая любовь к коду? :-)
25 май 16, 10:47    [19216439]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
alexeyvg
Вообще, есть общий принцип.
Если бизнес требования можно реализовать кодом или моделью данных (таблицами), выбирайте второе.

Ух ты! А где такой принцип прописан?
25 май 16, 12:14    [19217099]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Jovanny
alexeyvg
Вообще, есть общий принцип.
Если бизнес требования можно реализовать кодом или моделью данных (таблицами), выбирайте второе.

Ух ты! А где такой принцип прописан?

в здаровой логике
25 май 16, 12:15    [19217104]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
TaPaK
Jovanny
пропущено...

Ух ты! А где такой принцип прописан?

в здаровой логике

А кто у нас носители этой самой логики?
25 май 16, 12:17    [19217120]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31778
Jovanny
alexeyvg
Вообще, есть общий принцип.
Если бизнес требования можно реализовать кодом или моделью данных (таблицами), выбирайте второе.

Ух ты! А где такой принцип прописан?
Прописан мной в цитируемом вами моём посте.
И в этом же посте приводится обоснование.
25 май 16, 12:21    [19217143]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Jovanny
TaPaK
пропущено...

в здаровой логике

А кто у нас носители этой самой логики?

да вообщем-то все кто понимают, что если по бизнесс-процессу необходимо генерировать какие-то части календаря, то здравый смысл хранить его ввиде таблицы, и не стесняясь расширяя его понятиями календарный - рабочий и т.п.
25 май 16, 12:21    [19217144]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
TaPaK,

В общем, кто с Вами согласен, тот здравомыслящий, кто нет - тот дебил.
Как-то так?

Ну ладно, это лирика.
Случай из практики. Было у нас приложение, использующее таблицу-календарь для различных отчётов.
И как-то кто-то по недомыслию или злому умыслу удалил там несколько строк.
И генерировали мы несколько месяцев удивительные отчёты, повествующие о том, как всё у нас хорошо.
Пока не обнаружили, что дни, когда у нас всё плохо, удалены из таблицы-календаря.
Отчёты-то генерировались за большие периоды, никто не анализировал каждый день.
25 май 16, 12:31    [19217237]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Jovanny
TaPaK,

В общем, кто с Вами согласен, тот здравомыслящий, кто нет - тот дебил.
Как-то так?

Ну ладно, это лирика.
Случай из практики. Было у нас приложение, использующее таблицу-календарь для различных отчётов.
И как-то кто-то по недомыслию или злому умыслу удалил там несколько строк.
И генерировали мы несколько месяцев удивительные отчёты, повествующие о том, как всё у нас хорошо.
Пока не обнаружили, что дни, когда у нас всё плохо, удалены из таблицы-календаря.
Отчёты-то генерировались за большие периоды, никто не анализировал каждый день.

был у нас случай серверная сгорела и так и жили какое-то время пока не заметили
25 май 16, 12:34    [19217268]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Jovanny
И как-то кто-то по недомыслию или злому умыслу удалил там несколько строк.
А если кто-то по недомыслию изменит запрос, генерирующий календарь?

Просто не нужно этим "кто-то" давать разрешений больше, чем необходимо. Т.е. проблема ваша - административная.

А вообще, во избежание подобного рода проблем, таблицы типа календаря и т.п. выносят в отдельную read-only БД, а в основной БД пользуются синонимами.
25 май 16, 12:38    [19217299]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
invm
Jovanny
И как-то кто-то по недомыслию или злому умыслу удалил там несколько строк.
А если кто-то по недомыслию изменит запрос, генерирующий календарь?

Просто не нужно этим "кто-то" давать разрешений больше, чем необходимо. Т.е. проблема ваша - административная.

А вообще, во избежание подобного рода проблем, таблицы типа календаря и т.п. выносят в отдельную read-only БД, а в основной БД пользуются синонимами.
От совсем тупого дурака поможет просто пустой триггер INSTEAD OF DELETE,INSERT,UPDATE.
А права на таблицу-календарь отнять у всех, кроме администратора на всё, кроме SELECTа.
25 май 16, 12:44    [19217347]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
iap
От совсем тупого дурака поможет просто пустой триггер INSTEAD OF DELETE,INSERT,UPDATE.
А права на таблицу-календарь отнять у всех, кроме администратора на всё, кроме SELECTа.
Безусловно.

Но таблицы в отдельной read-only БД имеют ряд преимуществ:
- не нужно в каждой БД иметь свои копии таких таблиц.
- экономим на блокировках, т.к. БД read-only.
25 май 16, 12:52    [19217428]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31778
Jovanny
Случай из практики. Было у нас приложение, использующее таблицу-календарь для различных отчётов.
И как-то кто-то по недомыслию или злому умыслу удалил там несколько строк.
И генерировали мы несколько месяцев удивительные отчёты, повествующие о том, как всё у нас хорошо.
Пока не обнаружили, что дни, когда у нас всё плохо, удалены из таблицы-календаря.
Отчёты-то генерировались за большие периоды, никто не анализировал каждый день.
Это в общем к делу не относится.
Удалить можно всё, что угодно, любые важные данные, и будет всё криво в отдельные дни, или даже во все дни.

Кстати, системные и справочные данные должны проверяться и заливаться при деплое версии, соответствовать проекту, их изменения должны прослеживаться в истории в сорс-контрле, и быть привязаны к базнес-задачам, проверены тестерами и т.д., а не корячиться разрабами на боевой базе ручками без контроля и проверок. В идеале.
Тогда таких багов будет меньше.

А по существу, вот у меня прописаны в обоснованиях 6 пунктов, у вас по ним нет возражений?

Понятно, что "принципы" - они и есть "принципы", т.е. в каждом конкретном случае может быть не все 6 плюсов, а меньше. И даже иногда выгоднее написать код, а не делать таблицы.

Тем не менее в большинстве случаев это верно.

Множества, средства контроля их целостности и механизмы построения планов запросов хорошо отлажены в РСУБД, разумно это использовать по максимуму.
25 май 16, 12:57    [19217482]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31778
invm
iap
От совсем тупого дурака поможет просто пустой триггер INSTEAD OF DELETE,INSERT,UPDATE.
А права на таблицу-календарь отнять у всех, кроме администратора на всё, кроме SELECTа.
Безусловно.

Но таблицы в отдельной read-only БД имеют ряд преимуществ:
- не нужно в каждой БД иметь свои копии таких таблиц.
- экономим на блокировках, т.к. БД read-only.
Да, это отличное решение, вопрос только в сложности системы, не избыточно ли такое решение?

Может, лучше вынести такие справочники в рид-онли файлгруппу?
25 май 16, 12:59    [19217499]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Т.е., вместо использования in-line табличной функции, генерирующей диапазон дат, надо так много телодвижений?
invm
таблицы типа календаря и т.п. выносят в отдельную read-only БД, а в основной БД пользуются синонимами.

iap
От совсем тупого дурака поможет просто пустой триггер INSTEAD OF DELETE,INSERT,UPDATE.
А права на таблицу-календарь отнять у всех, кроме администратора на всё, кроме SELECTа.

Насчёт справочников возражений нет, но для простой последовательности создавать таблицу - это так в духе SQL Server 2000.
25 май 16, 13:06    [19217549]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить