SQL Server - дублювати рядки і додавати стовпець ітератора дат між двома значеннями дати

У мене є таблиця з типами varchar, varchar, датою та датою:

NAME | ID   | FROM       | THRU
Bob  | A123 | 10/30/2010 | 11/2/2010
Bob  | B567 | 10/30/2010 | 11/2/2010

Я хочу додати стовпець "Дата служби" (DOS), який дублює рядки та ітерації для кожного дня між датами FROM і THRU, включно з ними. Завершена таблиця має виглядати так:

NAME | ID   | FROM       | THRU       | DOS
Bob  | A123 | 10/30/2010 | 11/02/2010 | 10/30/2010
Bob  | A123 | 10/30/2010 | 11/02/2010 | 10/31/2010
Bob  | A123 | 10/30/2010 | 11/02/2010 | 11/01/2010
Bob  | A123 | 10/30/2010 | 11/02/2010 | 11/02/2010
Bob  | B567 | 10/30/2010 | 11/02/2010 | 10/30/2010
Bob  | B567 | 10/30/2010 | 11/02/2010 | 10/31/2010
Bob  | B567 | 10/30/2010 | 11/02/2010 | 11/01/2010
Bob  | B567 | 10/30/2010 | 11/02/2010 | 11/02/2010

Я бачив іншу відповідь, що використовував cte, але не зберігав початкових значень дати і додавав колонку DOS. Як я можу це зробити в SQL Server?

 1
додано Змінено
Перегляди: 7
джерело
ro nl ja ru fr es pt de zh hi bn ar kk uz be tr
Це відмінний випадок для календаря (календарна таблиця матиме рядок для кожної дати і буде містити всі дати). Є багато ресурсів в Інтернеті про те, як швидко створити його. Після того, як ви можете приєднатися до існуючої таблиці, наприклад, SELECT yourtable. *, Calendartable.calendardate З вашого тесту WHERE, calendartable.calendardate
додано Автор JNevill, джерело
10/30 не є дійсним значенням для datetime . Тип даних datetime повертає значення з точністю до 1/300 секунди, а не, ну, не впевнені, що це таке; місяць/день (так, за який рік?), місяць/рік (так який день?)? Які реальні значення у вас є, або реальні типи даних? Якщо ви дійсно зберігаєте дати у форматі, як MM/dd , то це буде неможливо працювати, коли ви переходите з року в рік.
додано Автор Larnu, джерело
@Larnu ви маєте рацію. Я додав значення року, щоб бути більш чітким і змінив datetime на сьогоднішній день.
додано Автор user3347996, джерело

6 Відповіді

Я думаю, що календарний стіл тут не зовсім правильний. Оскільки ви хочете, щоб послідовні дати дати таблицю, здається, хороший спосіб піти.

Спочатку налаштуємо дані.

declare @Something table
(
    NAME varchar(10)
    , ID varchar(10)
    , DateFrom date
    , THRU date
)

insert @Something values
('Bob', 'A123', '20101030', '20101102')
, ('Bob', 'B567', '20101030', '20101102')

Далі нам потрібна наша таблиця. Я тримаю один як перегляд на моїй системі, і це швидко бурхливе з нульовим читанням. Ви можете регулювати кількість рядків відповідно до ваших потреб.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

Тепер запит на вашу ситуацію досить простий.

select s.Name
    , s.ID
    , s.DateFrom
    , s.THRU
    , DOS = DATEADD(day, t.N - 1, DateFrom)
from @Something s
join cteTally t on t.N <= datediff(day, DateFrom, THRU) + 1
order by s.Name
    , s.ID
    , t.N
3
додано
джерело
Напевно, не має значення. У будь-якому випадку ви приєднуєте 750 мільйонів рядків до іншої таблиці, щоб збільшити кількість рядків.
додано Автор Sean Lange, джерело
Мій стіл має понад 750 мільйонів рядків. Чи буде календарна таблиця або таблиця з таблицями швидше для такого великого столу?
додано Автор user3347996, джерело

Я часто використовую рекурсивні CTE для такого роду речей:

with cte as (
      select t.ame, t.id, t.from, t.thru, t.from as dos
      from t
      union all
      select cte.ame, cte.id, cte.from, cte.thur, dateadd(day, 1, dos)
      from cte
      where dos < t.thru
     )
select cte.*
from cte
option (maxrecursion 0);
2
додано
джерело
@SeanLange. . . Проблема полягає в тому, що, коли ви потрапляєте у великі діапазони, join також може зайняти багато часу. Добре почати з цього (на мій погляд) є виміри Аарона Бертрана ( sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1 ). Моя перевага полягає в тому, що це стандартний SQL, не вимагає додаткових таблиць і працює для будь-якої кількості значень. Я б не стверджував, що це найшвидший метод.
додано Автор Gordon Linoff, джерело
Ніяка велика угода якщо діапазон є малий але для великих діапазонів (де-небудь біля 1.000) це може дійсно приїхати пригнічений. Рекурсивні ctes для генерації послідовних значень дійсно приховані RBAR. sqlservercentral.com/articles/T-SQL/74118
додано Автор Sean Lange, джерело
У його статті рекурсивний cte є другим повільним варіантом, який він протестував.
додано Автор Sean Lange, джерело
Я ніколи не давав багато кредо стандарту ANSI сумісний sql. У мої 20 років у бізнесі я перемикав бази даних для системи рівно нуль разів. І навіть якщо б це сталося, було б багато інших речей, які повинні були б відбутися. Я використовую рекурсивний метод cte з точки зору, так що він ніде не зберігається і є блискавичною.
додано Автор Sean Lange, джерело

Якщо у вас немає таблиці календаря (настійно рекомендується), іншим варіантом є таблиця під назвою ad hoc

Приклад

Select A.* 
      ,DOS = B.D
 From  YourTable A
 Cross Apply (
                Select Top (DateDiff(DAY,[FROM],[THRU])+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[FROM]) 
                  From  master..spt_values n1,master..spt_values n2
             ) B

Повернення

NAME    ID      FROM        THRU        DOS
Bob     A123    2010-10-30  2010-11-02  2010-10-30
Bob     A123    2010-10-30  2010-11-02  2010-10-31
Bob     A123    2010-10-30  2010-11-02  2010-11-01
Bob     A123    2010-10-30  2010-11-02  2010-11-02
Bob     B567    2010-10-30  2010-11-02  2010-10-30
Bob     B567    2010-10-30  2010-11-02  2010-10-31
Bob     B567    2010-10-30  2010-11-02  2010-11-01
Bob     B567    2010-10-30  2010-11-02  2010-11-02
2
додано
джерело
@SeanLange Яблука та апельсини :) У вас є приєднання, і я отримав CROSS APPLY. Тим не менш, я готовий посперечатися, що ваш буде більш ефективним. +1
додано Автор John Cappelletti, джерело
@SeanLange Тягар, який ми повинні нести.
додано Автор John Cappelletti, джерело
Виглядає досить схоже на моє. +1
додано Автор Sean Lange, джерело
Якщо це так, то, ймовірно, його не можна виявити на цьому невеликому наборі даних. Єдина різниця в продуктивності полягає у створенні таблиці таблиць. Між нами є 2 з 4 або більше "правильних" відповідей. :)
додано Автор Sean Lange, джерело

Схоже, вам потрібна таблиця календаря . Тоді вона стає такою ж простою, як:

SELECT YT.Name,
       YT.ID,
       YT.[From],
       YT.Thru,
       CT.CalendarDate AS DOS
FROM dbo.YourTable YT
     JOIN dbo.CalendarTable CT ON CONVERT(date,YT.[From]) <= CT.CalendarDate
                              AND CONVERT(date,YT.Thru) >= CT.CalendarDate;

Зауважте, що я використав власну таблицю календаря, не має той самий стовпець (назви) як посилання, однак посилання надає всю необхідну інформацію про те, як розробити. Вам просто потрібно переконатися, що ви використовуєте назви стовпців, відповідні для вашої таблиці.

2
додано
джерело

Схоже, що CROSS APPLY виконає завдання

CREATE TABLE T(
  [NAME] varchar(3), 
  [ID] varchar(4), 
  [FROM] datetime, 
  [THRU] datetime
);

INSERT INTO T
    ([NAME], [ID], [FROM], [THRU])
VALUES
    ('Bob', 'A123', '2001-10-30 00:00:00', '2001-11-02 00:00:00'),
    ('Bob', 'B567', '2001-10-30 00:00:00', '2001-11-02 00:00:00');

SELECT T.*,
       DATEADD(Day, TT.N, [FROM]) DOS
FROM T CROSS APPLY (VALUES (0), (1), (2), (3)) TT(N)

Повернення:

+------+------+---------------------+---------------------+---------------------+
| NAME |  ID  |        FROM         |        THRU         |         DOS         |
+------+------+---------------------+---------------------+---------------------+
| Bob  | A123 | 30/10/2001 00:00:00 | 02/11/2001 00:00:00 | 30/10/2001 00:00:00 |
| Bob  | A123 | 30/10/2001 00:00:00 | 02/11/2001 00:00:00 | 31/10/2001 00:00:00 |
| Bob  | A123 | 30/10/2001 00:00:00 | 02/11/2001 00:00:00 | 01/11/2001 00:00:00 |
| Bob  | A123 | 30/10/2001 00:00:00 | 02/11/2001 00:00:00 | 02/11/2001 00:00:00 |
| Bob  | B567 | 30/10/2001 00:00:00 | 02/11/2001 00:00:00 | 30/10/2001 00:00:00 |
| Bob  | B567 | 30/10/2001 00:00:00 | 02/11/2001 00:00:00 | 31/10/2001 00:00:00 |
| Bob  | B567 | 30/10/2001 00:00:00 | 02/11/2001 00:00:00 | 01/11/2001 00:00:00 |
| Bob  | B567 | 30/10/2001 00:00:00 | 02/11/2001 00:00:00 | 02/11/2001 00:00:0  |
+------+------+---------------------+---------------------+---------------------+
0
додано
джерело

Можна створити дату обслуговування як обчислений стовпець. Щоб збільшити дату, можна спробувати:

SELECT DATEADD(day, 1, '2017/08/25') AS DateAdd;
0
додано
джерело
Як це відповідає на їхнє запитання? Вони хочуть, щоб кожна дата знаходилася між FROM та THRU.
додано Автор Sean Lange, джерело
Як це створює додаткові рядки? Вираз, як це, надає додатковий стовпець, а не додаткові рядки.
додано Автор Larnu, джерело