एसक्यूएल सर्वर - पंक्तियों को डुप्लिकेट करें और दो डेट वैल्यू के बीच डेट इटेटर कॉलम जोड़ें

मेरे पास एक तालिका है, जिसमें varchar, varchar, date और date है:

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 सर्वर में इसे कैसे पूरा कर सकता हूं?

1
यह एक कैलेंडर तालिका के लिए एक महान usecase है (एक कैलेंडर तालिका में प्रत्येक तिथि के लिए एक पंक्ति होगी और इसमें सभी तिथियां शामिल होंगी)। कैसे जल्दी से एक बनाने के लिए ऑनलाइन संसाधनों का एक बहुत कुछ कर रहे हैं। एक बार आपके पास यह आ जाता है कि आप अपनी मौजूदा तालिका में शामिल हो सकते हैं जैसे Select yourtable। *, Calendartable.calendardate from yourtable WHERE calendartable.calendardate BETWEEN yourtable.FROM और yourtable .THRU
जोड़ा लेखक JNevill, स्रोत
10/30 datetime के लिए मान्य मान नहीं है। डेटाटाइम डेटाटाइप एक सेकंड के 1/300 वें मूल्य के लिए सटीक मान देता है, ठीक है, निश्चित नहीं है कि क्या है; महीना/दिन (तो, किस वर्ष के लिए?), महीना/वर्ष (तो क्या दिन?) आपके पास वास्तविक मूल्य या वास्तविक डेटाटिप्स क्या हैं? यदि आप वास्तव में MM/dd जैसे प्रारूप में दिनांक संग्रहीत कर रहे हैं, तो जब आप एक वर्ष से दूसरे वर्ष में जाते हैं, तो आपके साथ काम करना असंभव होगा।
जोड़ा लेखक 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, स्रोत

मैं अक्सर इस तरह की चीज़ के लिए पुनरावर्ती 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
जोड़ा
@ सीनलाइन । । समस्या यह है कि जब आप बड़ी रेंज में आते हैं, तो join s में भी लंबा समय लग सकता है। इसके साथ शुरू करने के लिए एक अच्छी जगह (मेरी राय में) हारून बर्ट्रेंड के माप हैं ( sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1 )। मेरी प्राथमिकता यह है क्योंकि यह मानक एसक्यूएल है, अतिरिक्त तालिकाओं की आवश्यकता नहीं है, और किसी भी संख्या में मानों के लिए काम करता है। मैं यह दावा नहीं करूंगा कि यह सबसे तेज़ प्रदर्शन करने वाला तरीका है।
जोड़ा लेखक Gordon Linoff, स्रोत
कोई बड़ी बात नहीं अगर रेंज छोटी है लेकिन बड़ी रेंज के लिए (लगभग 1,000 के आसपास) यह वास्तव में खराब हो सकती है। अनुक्रमिक मूल्यों को उत्पन्न करने के लिए पुनरावर्ती खदान वास्तव में छिपा हुआ है RBAR। sqlservercentral.com/articles/T-SQL/74118=
जोड़ा लेखक Sean Lange, स्रोत
अपने लेख में पुनरावर्ती cte दूसरा सबसे धीमा विकल्प है जिसका उन्होंने परीक्षण किया।
जोड़ा लेखक Sean Lange, स्रोत
मैंने कभी भी मानक एएनएसआई अनुरूप एसक्यूएल के लिए बहुत अधिक विश्वसनीयता नहीं दी है। व्यवसाय में मेरे 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 सेब और संतरे :) आप एक मिल गया है और मैं एक फसल मिला है। हालांकि, मैं शर्त लगाने को तैयार हूं कि आप अधिक प्रदर्शन करेंगे। +1
जोड़ा लेखक John Cappelletti, स्रोत
@ सीनलेग का बोझ हमें उठाना होगा
जोड़ा लेखक John Cappelletti, स्रोत
काफी हद तक मेरी जैसी दिखती है। +1
जोड़ा लेखक Sean Lange, स्रोत
यदि ऐसा है तो शायद इस छोटे डेटासेट पर पता नहीं लगाया जा सकता है। टैली टेबल बनाने में केवल प्रदर्शन अंतर है। हमारे बीच हमारे पास 4 में से 2 या अधिक "सही" उत्तर हैं। :)
जोड़ा लेखक 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, स्रोत