SQL Server - Ikkilangan satrlarni va ikki sana qiymatlari o'rtasida tarixiy iterator ustunini qo'shing

Varchar, varchar, sana va sana turlari bilan bir stol bor:

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

Qatordan takrorlashni va har kuni uchun qaytarib beradigan xizmatni (DOS) ustuni qo'shmoqchiman, FROM va THRU sanalari o'rtasida. Tayyorlangan jadval shunday bo'lishi kerak:

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 foydalangan boshqa javobni ko'rdim, lekin original sana qiymatlarini saqlamadi va DOS ustunini qo'shib qo'ydi. Buni SQL-serverda qanday qilib bajarishim mumkin?

1
Bu taqvim jadvali uchun ajoyib usecase (bir taqvim jadvali har bir sana uchun qatorga ega bo'ladi va barcha sanalarni o'z ichiga oladi). Tezda qanday qilib tezkor yaratish uchun onlayn resurslar ko'p. Siz o'zingizning mavjud jadvalingizga faqat SELECT yourtable. *, Calendartable.calendardate FROM yourtable WHERE calendartable.calendardate yourtable.FROM va yourtable.THRU
qo'shib qo'ydi muallif JNevill, manba
10/30 - datetime uchun joriy qiymat emas. Datetime ma'lumot turi aniq qiymatini 1/300 soniyasiga qaytaradi. oy/kun (shuning uchun, qaysi yil uchun?), oy/yil (qaysi kun?) Sizda mavjud bo'lgan haqiqiy qiymatlar yoki haqiqiy ma'lumotlar qanday? Agar siz haqiqatdan ham kunlarni MM/dd formatida saqlashni boshlasangiz, unda siz bir yildan ikkinchisiga o'tishingiz bilan ishlashingiz mumkin emas.
qo'shib qo'ydi muallif Larnu, manba
@Larnu sen haqsan. Bugungi kunga qadar aniqroq va o'zgartirilgan sana uchun bir yil qiymatini qo'shdim.
qo'shib qo'ydi muallif user3347996, manba

6 javoblar

Menimcha, taqvim jadvali bu erda juda to'g'ri vosita emas. Keyingi davrlarni istaganingizdan so'ng, jadvalning jadvali borishning yaxshi yo'li kabi ko'rinadi.

Avval siz ma'lumotlaringizni sozlashni xohlaymiz.

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

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

Keyinchalik, biz o'zimizning stolimizga muhtojmiz. Men birovni tizimimda ko'rinish sifatida saqlayman va u noldan o'qiydigan darajada tezkorlashadi. Qatorlar sonini sizning ehtiyojlaringizga mos ravishda moslashtirasiz.

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

Endi sizning vaziyatingiz uchun so'rov juda oddiy.

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
qo'shib qo'ydi
Ehtimol, ko'p farq qilmaydi. Qolaversa, qatorni 750 barobarga oshirish uchun boshqa stolga qo'shilasiz.
qo'shib qo'ydi muallif Sean Lange, manba
Mening jadvalim 750 milliondan ortiq qatorga ega. Katta jadval uchun taqvim jadvali yoki jadal jadval tezroq bo'lishi kerakmi?
qo'shib qo'ydi muallif user3347996, manba

Ushbu turdagi narsalar uchun tez-tez takrorlanadigan CTElarni ishlataman:

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
qo'shib qo'ydi
@SeanLange. . . Muammo katta diapazonga kirganingizda, join ni ham uzoq vaqt talab qilishi mumkin. Buning boshlanishi uchun yaxshi joy (mening fikrimcha) Aaron Bertranning o'lchovidir ( sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1 ). Mening ustunligim, bu standart SQL, qo'shimcha jadvallarni talab qilmasligi va har qanday miqdordagi qiymatlar uchun ishlashi. Men buni tezkor usul deb da'vo qilmayman.
qo'shib qo'ydi muallif Gordon Linoff, manba
Agar diapazon kichik bo'lsa-da, katta diapazonlarda (1000 ga yaqin joylarda) bu chindan ham pastga tushishi mumkin. Nominal qiymatlarni yaratish uchun nusxa ko'chirish ctes, albatta, RBAR tomonidan yashirin. sqlservercentral.com/articles/T-SQL/74118
qo'shib qo'ydi muallif Sean Lange, manba
O'zining maqolasida o'zining sinchkovlik bilan tekshirgan eng kichik variantidir.
qo'shib qo'ydi muallif Sean Lange, manba
Men hech qachon ANSI standartiga mos keladigan sqlga ko'p e'tiqodni bergan emasman. Mening biznesimdagi 20 yilligimda tizim uchun ma'lumotlar bazalarini nol marta o'zgartirdim. Agar shunday bo'lsa ham, boshqa ko'p narsalar bo'lishi kerak edi. O'z-o'zidan ravshanki takrorlash texnologiyasidan foydalaning, shuning uchun har qanday joyda saqlanmaydi va chaqmoq tezligida.
qo'shib qo'ydi muallif Sean Lange, manba

Agar sizda Taqvim jadvali bo'lmasa (tavsiya etiladi), boshqa variant esa maxsus vaqt jadvalidir

Misol

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

qaytaradi

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
qo'shib qo'ydi
@SeanLange Apple va Portakallar :) Siz birlashmaniz va CROSS APPLY bor. Ammo, men sizning pulingizni qo'yishga tayyorligimni yanada ko'proq bajarishga tayyorman. +1
qo'shib qo'ydi muallif John Cappelletti, manba
@SeanLange Biz ko'tarishimiz kerak bo'lgan yuk.
qo'shib qo'ydi muallif John Cappelletti, manba
Menga juda o'xshash. +1
qo'shib qo'ydi muallif Sean Lange, manba
Ehtimol, bu kichik ma'lumotlar majmuasida aniqlanmasa. Faqat bitta ishlash farqi, stollar jadvalini yaratishdan iborat. Bizning oramizda 4 ta yoki undan ortiq "to'g'ri" javob mavjud. :)
qo'shib qo'ydi muallif Sean Lange, manba

Sizga Taqvim jadvali kerakligiga o'xshaydi. So'ngra shunga o'xshash oddiy narsa bo'ladi:

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;

Eslatma: O'z Taqvim jadvalidan foydalanganmisiz, havola bilan bir xil ustun (ismlar) mavjud emas, biroq bu link barcha kerakli ma'lumotlarni loyihalashtirish bo'yicha kerakli ma'lumotlarni beradi. YOu sizning jadvalingiz uchun mos keladigan ustun nomlarini ishlatishingizga to'g'ri keladi.

2
qo'shib qo'ydi

cross APPLY kabi ishni bajaradi

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)

qaytaradi:

+------+------+---------------------+---------------------+---------------------+
| 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
qo'shib qo'ydi

Hisoblangan ustun sifatida Xizmat tarixini yaratishingiz mumkin. Kunni oshirish uchun quyidagilarni amalga oshirishingiz mumkin:

SELECT DATEADD(day, 1, '2017/08/25') AS DateAdd;
0
qo'shib qo'ydi
Bu ularning savoliga qanday javob beradi? Ular har kuni FROM va THRU orasida bo'lishini istaydilar.
qo'shib qo'ydi muallif Sean Lange, manba
Qanday qilib bu qo'shimcha qatorlarni yaratadi? Bu kabi ifoda qo'shimcha qatorlarni emas, balki qo'shimcha ustunni beradi.
qo'shib qo'ydi muallif Larnu, manba