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

Question

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

додано 12 грудень 2018 при 06:13 Автор user3347996 Змінено 12 грудень 2018 при 06:17

Перегляди: 18

джерело

ro nl ja ru fr es pt de zh hi bn ar kk uz be tr

Це відмінний випадок для календаря (календарна таблиця матиме рядок для кожної дати і буде містити всі дати). Є багато ресурсів в Інтернеті про те, як швидко створити його. Після того, як ви можете приєднатися до існуючої таблиці, наприклад,

 SELECT
yourtable. *, Calendartable.calendardate З вашого тесту WHERE,
calendartable.calendardate

додано 12 грудень 2018 при 06:23, Автор JNevill, джерело

10/30 не є дійсним значенням для datetime . Тип даних datetime повертає значення з точністю до 1/300 секунди, а не, ну, не впевнені, що це таке; місяць/день (так, за який рік?), місяць/рік (так який день?)? Які реальні значення у вас є, або реальні типи даних? Якщо ви дійсно зберігаєте дати у форматі, як MM/dd , то це буде неможливо працювати, коли ви переходите з року в рік.

додано 12 грудень 2018 при 06:15, Автор Larnu, джерело

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

додано 12 грудень 2018 при 06:18, Автор 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

додано 12 грудень 2018 при 06:31 Автор Sean Lange

джерело

Напевно, не має значення. У будь-якому випадку ви приєднуєте 750 мільйонів рядків до іншої таблиці, щоб збільшити кількість рядків.

додано 12 грудень 2018 при 06:52, Автор Sean Lange, джерело

Мій стіл має понад 750 мільйонів рядків. Чи буде календарна таблиця або таблиця з таблицями швидше для такого великого столу?

додано 12 грудень 2018 при 06:51, Автор user3347996, джерело

Автор Gordon Linoff · Answer 1 · 2018-12-12T06:40Z

Я часто використовую рекурсивні 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

додано 12 грудень 2018 при 06:40 Автор Gordon Linoff

джерело

@SeanLange. . . Проблема полягає в тому, що, коли ви потрапляєте у великі діапазони, join також може зайняти багато часу. Добре почати з цього (на мій погляд) є виміри Аарона Бертрана ( sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1 ). Моя перевага полягає в тому, що це стандартний SQL, не вимагає додаткових таблиць і працює для будь-якої кількості значень. Я б не стверджував, що це найшвидший метод.

додано 12 грудень 2018 при 06:46, Автор Gordon Linoff, джерело

Ніяка велика угода якщо діапазон є малий але для великих діапазонів (де-небудь біля 1.000) це може дійсно приїхати пригнічений. Рекурсивні ctes для генерації послідовних значень дійсно приховані RBAR. sqlservercentral.com/articles/T-SQL/74118

додано 12 грудень 2018 при 06:42, Автор Sean Lange, джерело

У його статті рекурсивний cte є другим повільним варіантом, який він протестував.

додано 12 грудень 2018 при 06:49, Автор Sean Lange, джерело

Я ніколи не давав багато кредо стандарту ANSI сумісний sql. У мої 20 років у бізнесі я перемикав бази даних для системи рівно нуль разів. І навіть якщо б це сталося, було б багато інших речей, які повинні були б відбутися. Я використовую рекурсивний метод cte з точки зору, так що він ніде не зберігається і є блискавичною.

додано 12 грудень 2018 при 06:51, Автор Sean Lange, джерело

Автор John Cappelletti · Answer 2 · 2018-12-12T06:35Z

Якщо у вас немає таблиці календаря (настійно рекомендується), іншим варіантом є таблиця під назвою 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

Автор Larnu · Answer 3 · 2018-12-12T06:27Z

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

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;

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

Автор Sami · Answer 4 · 2018-12-12T06:52Z

Схоже, що 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  |
+------+------+---------------------+---------------------+---------------------+

Автор Pratik Somaiya · Answer 5 · 2018-12-12T06:31Z

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

SELECT DATEADD(day, 1, '2017/08/25') AS DateAdd;

0

додано 12 грудень 2018 при 06:31 Автор Pratik Somaiya

джерело

Як це відповідає на їхнє запитання? Вони хочуть, щоб кожна дата знаходилася між FROM та THRU.

додано 12 грудень 2018 при 06:32, Автор Sean Lange, джерело

Як це створює додаткові рядки? Вираз, як це, надає додатковий стовпець, а не додаткові рядки.

додано 12 грудень 2018 при 06:32, Автор Larnu, джерело