sql - तिथि या सीमा के लिए न्यूनतम मूल्य-उत्पाद आईडी, मूल्य और दिनांक सीमा के आधार पर दी गई तिथि सीमा के लिए न्यूनतम मूल्य निर्धारित करना




tsql sql-server-2012 (3)

क्या आपके पास एक कैलेंडर / तिथि तालिका उपलब्ध है? यदि हां, तो आप अपनी तालिका की अवधि के भीतर हर तिथि के लिए प्रति उत्पाद न्यूनतम मूल्य प्राप्त करने में आपकी सहायता करने के लिए तिथि तालिका का उपयोग कर सकते हैं।

इसके बाद आप अपने प्रत्येक अवधि की शुरुआत और समाप्ति तिथियां उसी उत्पाद आईडी के साथ अगले और पिछले रिकॉर्ड देखकर प्राप्त कर सकते हैं। ऐसा करने के लिए आप लैग और लीड फ़ंक्शंस का उपयोग कर सकते हैं। यह आपको आपके इच्छित समूहों में से प्रत्येक की बाहरी सीमा देता है

वहां से यह आपके अंतिम परिणाम प्राप्त करने के लिए बस थोड़ी छोटी है। मैंने नीचे एक उदाहरण दिया है, जिसे आपको इच्छित परिणाम देना चाहिए।

--Get the best price per date for each product
WITH BestPricePerDate AS (
    SELECT 
        Id,
        MIN(Price) Price,
        c.[Date]
    FROM [YourTable] yt
        INNER JOIN dbo.Calendar c
            ON c.[Date] BETWEEN yt.StartDate AND yt.EndDate
    GROUP BY Id, [Date]
),
--Check whether the date is the start or the end of a period
PeriodsMarkedPerId AS(
    SELECT 
        Id,
        Price,
        [Date],
        CASE WHEN 
            ISNULL(LAG(Price,1) OVER (PARTITION BY Id ORDER BY [Date]),-1) <> Price 
            OR ISNULL(LAG([Date],1) OVER (PARTITION BY Id ORDER BY [Date]),'1999-01-01') <> DATEADD(DAY,-1,[Date]) THEN 1 ELSE 0 END IsStartDate,
        CASE WHEN 
            ISNULL(LEAD(Price,1) OVER (PARTITION BY Id ORDER BY [Date]),-1) <> Price 
            OR ISNULL(LEAD([Date],1) OVER (PARTITION BY Id ORDER BY [Date]),'1999-01-01') <> DATEADD(DAY,1,[Date]) THEN 1 ELSE 0 END IsEndDate
    FROM BestPricePerDate
),
--Keep only the start and end date records
PeriodStartAndEndDates AS(
    SELECT 
        Id, 
        Price,
        [Date],
        IsStartDate,
        IsEndDate
    FROM PeriodsMarkedPerId
    WHERE IsStartDate = 1 OR IsEndDate = 1
),
--Move StartDate and EndDate to one record
StartAndEndDatesOnSameRow AS(
    SELECT 
        Id, 
        Price, 
        [Date] AS StartDate,
        LEAD([Date],1) OVER (ORDER BY Id, [Date]) AS EndDate,
        IsStartDate
    FROM PeriodStartAndEndDates
)
--Get the resulting periods
SELECT Id, Price, StartDate, EndDate 
FROM StartAndEndDatesOnSameRow
WHERE IsStartDate = 1
ORDER BY Id, StartDate

यदि आपके पास कोई दिनांक तालिका नहीं है, तो आप आसानी से एक बना सकते हैं। वेब के चारों ओर इस के कई उदाहरण हैं

आशा है कि ये आपकी मदद करेगा!

मुझे वास्तव में आशा है कि आप में से कुछ चुनौतियों की तरह हैं मेरे पास उत्पाद आईडी, मूल्य और दिनांक सीमा की एक तालिका है, जब उन की कीमतें सक्रिय हैं।

+----+-------+---------------------+---------------------+
| Id | Price |      StartDate      |       EndDate       |
+----+-------+---------------------+---------------------+
|  1 |    19 | 2016-12-01 00:00:00 | 2017-12-01 23:59:59 |
|  1 |    18 | 2017-01-01 00:00:00 | 2018-01-12 23:59:59 |
|  1 |    17 | 2017-02-03 00:00:00 | 2017-03-03 23:59:59 |
|  1 |    16 | 2018-01-01 00:00:00 | 2018-03-02 23:59:59 |
|  2 |    15 | 2017-01-01 00:00:00 | 2017-03-05 23:59:59 |
|  2 |    15 | 2017-03-06 00:00:00 | 2017-03-31 23:59:59 |
|  2 |    30 | 2017-04-01 00:00:00 | 2017-05-03 23:59:59 |
|  3 |    12 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 |
|  3 |    12 | 2017-02-01 00:00:00 | 2017-02-28 23:59:59 |
|  4 |    14 | 2017-01-01 00:00:00 | 2017-04-05 23:59:59 |
|  4 |    14 | 2017-04-01 00:00:00 | 2017-04-30 23:59:59 |
|  4 |    12 | 2017-04-15 00:00:00 | 2017-05-30 23:59:59 |
|  5 |    20 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 |
|  5 |    20 | 2017-03-01 00:00:00 | 2017-03-31 23:59:59 |
|  6 |    15 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 |
|  6 |    15 | 2017-02-01 00:00:00 | 2017-02-28 23:59:59 |
|  6 |    15 | 2017-04-01 00:00:00 | 2017-04-30 23:59:59 |
+----+-------+---------------------+---------------------+

SQLFiddle: http://sqlfiddle.com/#!6/39288/1

मुझे उसे एक प्रारूप में प्राप्त करना होगा जहां:

  1. तिथि अवधि का एक ही आईडी और मूल्य होता है जो कि "टच" (अर्थात आईडी # 3) एक अवधि में विलय कर दिया जाता है।

  2. ओवरलैप करने की तारीख की अवधि (यानी आईडी # 4) एक अवधि में विलय कर दी जाती है।

  3. सबसे कम कीमत प्रत्येक उत्पाद के लिए और किस श्रेणी के दौरान दिखाया गया है।

  4. दिनांक सीमाएं जो अंतराल हैं और एक ही कीमत में विलय नहीं है और अलग-अलग पंक्तियां हैं (अर्थात आईडी # 5)।

परिणाम होना चाहिए:

+----+-------+---------------------+---------------------+
| Id | Price |      StartDate      |       EndDate       |
+----+-------+---------------------+---------------------+
|  1 |    19 | 2016-12-01 00:00:00 | 2016-12-31 23:59:59 |
|  1 |    18 | 2017-01-01 00:00:00 | 2017-02-02 23:59:59 |
|  1 |    17 | 2017-02-03 00:00:00 | 2017-03-03 23:59:59 |
|  1 |    19 | 2017-03-04 00:00:00 | 2017-12-01 23:59:59 |
|  1 |    18 | 2017-12-02 00:00:00 | 2017-12-31 23:59:59 |
|  1 |    16 | 2018-01-01 00:00:00 | 2018-03-02 23:59:59 |
|  2 |    15 | 2017-01-01 00:00:00 | 2017-03-31 23:59:59 |
|  2 |    30 | 2017-04-01 00:00:00 | 2017-05-03 23:59:59 |
|  3 |    12 | 2017-01-01 00:00:00 | 2017-02-28 23:59:59 |
|  4 |    14 | 2017-01-01 00:00:00 | 2017-04-14 23:59:59 |
|  4 |    12 | 2017-04-15 00:00:00 | 2017-05-30 23:59:59 |
|  5 |    20 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 |
|  5 |    20 | 2017-03-01 00:00:00 | 2017-03-31 23:59:59 |
|  6 |    15 | 2017-01-01 00:00:00 | 2017-02-28 23:59:59 |
|  6 |    15 | 2017-04-01 00:00:00 | 2017-04-30 23:59:59 |
+----+-------+---------------------+---------------------+

कुल मिलाकर, यह अनिवार्य रूप से दो तिथियों के बीच सर्वोत्तम मूल्य का निर्धारण कर रहा है।

मैंने इस तालिका के साथ अतीत में काम किया है और इसे सी # में हल करने में सक्षम था, लेकिन इस बार मुझे एक शुद्ध टीएसक्यूएल दृष्टिकोण की आवश्यकता है

मैं पहले से ही कुछ गहरी नेस्टेड सीटीई के नीचे चला गया और मेरे दिमाग को खो दिया है जिसके परिणामस्वरूप कहीं न कहीं वे मिलना चाहिए। किसी के लिए अग्रिम धन्यवाद जो सहायता कर सकते हैं

संपादित करें: मैंने वांछित परिणाम भी गड़बड़ कर दिए क्योंकि यह बहुत भ्रमित है फिक्स्ड (मुझे लगता है)।

संपादित करें 2: उदाहरण:

+------+-------+-------------------------+-------------------------+
|  Id  | Price |        StartDate        |         EndDate         |
+------+-------+-------------------------+-------------------------+
| 8611 | 31.98 | 2017-06-06 00:00:00.000 | 2017-09-24 23:59:59.000 |
| 8611 | 31.98 | 2017-09-25 00:00:00.000 | 2017-12-31 23:59:59.000 |
| 8611 | 28.78 | 2017-07-31 00:00:00.000 | 2017-09-30 23:59:59.000 |
| 8611 | 28.78 | 2017-10-30 00:00:00.000 | 2017-12-31 23:59:59.000 |
+------+-------+-------------------------+-------------------------+

@ गॉर्डन लिनॉफ़ के परिणाम निम्न के लिए:

+------+-------+-------------------------+-------------------------+
|  Id  | Price |        StartDate        |         EndDate         |
+------+-------+-------------------------+-------------------------+
| 8611 | 28.78 | 2017-06-06 00:00:00.000 | 2017-12-31 23:59:59.000 |
+------+-------+-------------------------+-------------------------+

परिणाम होना चाहिए:

+------+-------+-------------------------+-------------------------+
|  Id  | Price |        StartDate        |         EndDate         |
+------+-------+-------------------------+-------------------------+
| 8611 | 31.98 | 2017-06-06 00:00:00.000 | 2017-07-30 23:59:59.000 |
| 8611 | 28.78 | 2017-07-31 00:00:00.000 | 2017-09-30 23:59:59.000 |
| 8611 | 31.98 | 2017-10-01 00:00:00.000 | 2017-10-29 23:59:59.000 |
| 8611 | 28.78 | 2017-10-30 00:00:00.000 | 2017-12-31 23:59:59.000 |
+------+-------+-------------------------+-------------------------+

आप एक अवधि की शुरुआत को परिभाषित कर सकते हैं जो ओवरलैप नहीं करता है। यह मुश्किल है, लेकिन वर्तमान पंक्ति को छोड़कर exists या मौजूदा समाई तिथि का संचयी अधिकतम उपयोग करके किया जा सकता है

फिर, प्रत्येक गैर-ओवरलैप एक समूह की शुरुआत है। उस समूह को एकत्रीकरण के लिए इस्तेमाल किया जा सकता है:

select id, min(startDate) as startDate, max(endDate) as endDate, min(price) as price
from (select t.*,
             sum(case when prev_endDate < dateadd(second, -1, startDate)
                      then 1 else 0
                 end) over (partition by id order by startdate) as grp
      from (select t.*,
                   max(endDate) over (partition by id
                                      order by startdate
                                      rows between unbounded preceding and 1 preceding
                                     ) as prev_endDate
            from t
           ) t
     ) t
group by id, grp;

मैं 100% यकीन नहीं करता हूँ कि यह काम करता है मैं इसके लिए संचयी अधिकतम समाप्ति तिथि का उपयोग करने के बारे में सोचा था। मुझे पूरा यकीन है कि यह सभी अतिव्यापी मामलों को शामिल करता है, लेकिन मुझे कुछ याद हो सकता है


यह समझा जाना चाहिए कि प्रदर्शन दृष्टिकोण से @temp टेबल और #temp टेबल के बीच कोई अंतर नहीं है जो चर के पक्ष में हैं। वे एक ही स्थान (tempdb) में रहते हैं और उसी तरह लागू होते हैं। सभी अंतर अतिरिक्त सुविधाओं में दिखाई देते हैं। यह आश्चर्यजनक पूर्ण लेखन देखें: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

यद्यपि ऐसे मामले हैं जहां तालिका या स्केलर फ़ंक्शंस में एक अस्थायी तालिका का उपयोग नहीं किया जा सकता है, v2016 से पहले अन्य मामलों के लिए (जहां फ़िल्टर किए गए इंडेक्स को तालिका चर में भी जोड़ा जा सकता है) आप बस #temp तालिका का उपयोग कर सकते हैं।

Tempdb में नामित इंडेक्स (या बाधाओं) का उपयोग करने की कमी यह है कि नाम तब संघर्ष कर सकते हैं। न केवल सैद्धांतिक रूप से अन्य प्रक्रियाओं के साथ बल्कि प्रक्रिया के अन्य उदाहरणों के साथ अक्सर आसानी से आसानी से जो एक ही इंडेक्स को #temp तालिका की प्रतिलिपि पर रखने का प्रयास करेगा।

नाम संघर्ष से बचने के लिए, ऐसा कुछ आमतौर पर काम करता है:

declare @cmd varchar(500)='CREATE NONCLUSTERED INDEX [ix_temp'+cast(newid() as varchar(40))+'] ON #temp (NonUniqueIndexNeeded);';
exec (@cmd);

यह बीमा करता है कि नाम एक ही प्रक्रिया के साथ-साथ निष्पादन के बीच भी हमेशा अद्वितीय होता है।





sql tsql sql-server-2012