SQL Server - Қайталанатын жолдар мен күнді қайталау бағанын екі күн мәндерінің арасында қосыңыз

Менде varchar, varchar, күні және күні бар үстел бар:

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

Жолдарды қайталайтын және күн сайын, соның ішінде FROM және THRU күндер арасында қайталанатын Қызмет Күні (DOS) бағанын қосқым келеді. Аяқталған кесте келесідей болуы керек:

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 серверінде қалай орындауға болады?

1
Бұл күнтізбе кестесі үшін керемет usecase (күнтізбелік кестеде әр күн үшін жол болады және барлық күндерді қамтиды). Біреуін жылдам жасаудың онлайн тәртібінде көптеген ресурстар бар. Кейін сіз өзіңіздің кестеңізге тек SELECT your table (кестені SELECT) ретінде қосуыңызға болады *, calendartable.calendardate FROM сіздің кестесі WHERE calendartable.calendardate yourtable.FROM және yourtable.THRU
қосылды автор JNevill, көзі
10/30 - datetime үшін жарамды мән емес. Datetime datatype мәні 1/300 секундына дәл мәнді қайтарады, бірақ, бұл не екенін білмеймін; ай/күн (солай, қай жылы?), ай/жыл (сол күні қандай?)? Сізде нақты құндылықтар не нақты деректер бар ма? Егер шынымен күндерді MM/dd сияқты пішімде сақтайтын болса, онда сіз бір жыл өткеннен кейін жұмыс істей алмайсыз.
қосылды автор Larnu, көзі
@Larnu сіз дұрыс. Мен бүгінгі күнге дейін нақты және өзгертілген уақытты сақтау үшін бір жыл мәнін қостық.
қосылды автор 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, көзі

Мен жиі осындай нәрсеге арналған рекурсивті КТЖ қолданады:

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, көзі
Егер ауқым аз болса да, үлкен диапазондарда (1000-ға жуық жерде) болса, онда бұл шынымен мазасыздану мүмкін емес. Регурсты мәндерді генерациялау үшін рекурсивті ctes - бұл шынымен жасырын RBAR. sqlservercentral.com/articles/T-SQL/74118
қосылды автор Sean Lange, көзі
Өз мақаласында рекурсивтік cte - ол сыналған екінші нұсқасы.
қосылды автор Sean Lange, көзі
Мен ешқашан ANSI стандарттарына сай келетін стандартты SQL-кодты берген емеспін. Менің бизнесімдегі 20 жылымда жүйеге арналған деректер қорын нөлдік уақытта ауыстырдым. Тіпті егер бұл орын алса, басқа да көптеген нәрселер болар еді. Мен рекурсивті cte техникасын көрініспен қолданамын, сондықтан ол еш жерде сақталмайды және жылдам найзағай болып табылады.
қосылды автор Sean Lange, көзі

Егер сізде Күнтізбе кестесі жоқ болса (өте ұсынылады), басқа параметр - уақытша кесте

Мысал

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, көзі
Егер бұл кішкентай деректер жиынтығында анықталмаса. Жалғыз өнімділік айырмашылығы кестені құруда. Біздің арамызда бізде 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;

Ескертпе, мен өзімнің күнтізбе кестесін қолдандым, сілтеме ретінде сол бағанды ​​(аттарды) жоқ, алайда сілтеме біреуін қалай жасау керектігіне қатысты барлық ақпарат қажеттілігін береді. YOu сіздің кестеңізге сәйкес баған атауларын пайдалануды қамтамасыз етуі керек еді.

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, көзі