sql प्रत्येक समूह की शीर्ष 1 पंक्ति प्राप्त करें




tsql sql-server-2005 (12)

यह सबसे वेनिला टीएसक्यूएल है जिसके साथ मैं आ सकता हूं

    SELECT * FROM DocumentStatusLogs D1 JOIN
    (
      SELECT
        DocumentID,MAX(DateCreated) AS MaxDate
      FROM
        DocumentStatusLogs
      GROUP BY
        DocumentID
    ) D2
    ON
      D2.DocumentID=D1.DocumentID
    AND
      D2.MaxDate=D1.DateCreated

मेरे पास एक सारणी है जिसे मैं प्रत्येक समूह के लिए नवीनतम प्रविष्टि प्राप्त करना चाहता हूं। तालिका यहां दी गई है:

DocumentStatusLogs तालिका

|ID| DocumentID | Status | DateCreated |
| 2| 1          | S1     | 7/29/2011   |
| 3| 1          | S2     | 7/30/2011   |
| 6| 1          | S1     | 8/02/2011   |
| 1| 2          | S1     | 7/28/2011   |
| 4| 2          | S2     | 7/30/2011   |
| 5| 2          | S3     | 8/01/2011   |
| 6| 3          | S1     | 8/02/2011   |

तालिका को DocumentID द्वारा समूहीकृत किया जाएगा और अवरोही क्रम में DateCreated द्वारा क्रमबद्ध किया जाएगा। प्रत्येक DocumentID , मैं नवीनतम स्थिति प्राप्त करना चाहता हूं।

मेरा पसंदीदा आउटपुट:

| DocumentID | Status | DateCreated |
| 1          | S1     | 8/02/2011   |
| 2          | S3     | 8/01/2011   |
| 3          | S1     | 8/02/2011   |
  • क्या प्रत्येक समूह से केवल शीर्ष प्राप्त करने के लिए कोई समग्र कार्य है? नीचे छद्म कोड GetOnlyTheTop देखें:

    SELECT
      DocumentID,
      GetOnlyTheTop(Status),
      GetOnlyTheTop(DateCreated)
    FROM DocumentStatusLogs
    GROUP BY DocumentID
    ORDER BY DateCreated DESC
    
  • यदि ऐसा फ़ंक्शन मौजूद नहीं है, तो क्या कोई तरीका है जिससे मैं आउटपुट प्राप्त कर सकता हूं?

  • या पहली जगह, क्या यह असामान्य डेटाबेस के कारण हो सकता है? मैं सोच रहा हूं, क्योंकि जो मैं खोज रहा हूं वह केवल एक पंक्ति है, क्या यह status मूल तालिका में भी स्थित होनी चाहिए?

अधिक जानकारी के लिए कृपया मूल तालिका देखें:

वर्तमान Documents तालिका

| DocumentID | Title  | Content  | DateCreated |
| 1          | TitleA | ...      | ...         |
| 2          | TitleB | ...      | ...         |
| 3          | TitleC | ...      | ...         |

क्या माता-पिता की मेज इस तरह होनी चाहिए ताकि मैं आसानी से अपनी स्थिति तक पहुंच सकूं?

| DocumentID | Title  | Content  | DateCreated | CurrentStatus |
| 1          | TitleA | ...      | ...         | s1            |
| 2          | TitleB | ...      | ...         | s3            |
| 3          | TitleC | ...      | ...         | s1            |

अद्यतन मैंने अभी "लागू" का उपयोग करने के बारे में सीखा है जो ऐसी समस्याओं का समाधान करना आसान बनाता है।


यदि आप प्रदर्शन के बारे में चिंतित हैं, तो आप इसे MAX () के साथ भी कर सकते हैं:

SELECT *
FROM DocumentStatusLogs D
WHERE DateCreated = (SELECT MAX(DateCreated) FROM DocumentStatusLogs WHERE ID = D.ID)

ROW_NUMBER () को आपके SELECT कथन में सभी पंक्तियों की एक प्रकार की आवश्यकता होती है, जबकि MAX नहीं करता है। अपनी क्वेरी को तेजी से तेज करना चाहिए।


SELECT * FROM
DocumentStatusLogs JOIN (
  SELECT DocumentID, MAX(DateCreated) DateCreated
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ) max_date USING (DocumentID, DateCreated)

डेटाबेस सर्वर क्या है? यह कोड उन सभी पर काम नहीं करता है।

आपके प्रश्न के दूसरे भाग के बारे में, मुझे कॉलम के रूप में स्थिति शामिल करने के लिए उचित लगता है। आप DocumentStatusLogs को लॉग के रूप में छोड़ सकते हैं, लेकिन फिर भी मुख्य तालिका में नवीनतम जानकारी संग्रहीत कर सकते हैं।

बीटीडब्ल्यू, यदि आपके पास पहले से ही दस्तावेज़ तालिका में DateCreated कॉलम है, तो आप उस DocumentStatusLogs का उपयोग करके DocumentStatusLogs शामिल हो सकते हैं (जब तक DateCreated DocumentStatusLogs में अद्वितीय है)।

संपादित करें: एमएसएसक्यूएल उपयोग करने का समर्थन नहीं करता है, इसलिए इसे यहां बदलें:

ON DocumentStatusLogs.DocumentID = max_date.DocumentID AND DocumentStatusLogs.DateCreated = max_date.DateCreated

यह काफी पुराना धागा है, लेकिन मैंने सोचा कि मैं अपने दो सेंट फेंक दूंगा जैसा स्वीकार्य उत्तर मेरे लिए विशेष रूप से अच्छा काम नहीं करता है। मैंने बड़े डेटासेट पर जीबीएन के समाधान की कोशिश की और इसे बहुत धीमा पाया (> SQL सर्वर 2012 में 5 मिलियन से अधिक रिकॉर्ड पर 45 सेकंड)। निष्पादन योजना को देखते हुए यह स्पष्ट है कि मुद्दा यह है कि इसे एक एसओआरटी ऑपरेशन की आवश्यकता होती है जो चीजों को काफी धीमा कर देती है।

यहां एक विकल्प है जिसे मैंने इकाई ढांचे से उठाया जिसके लिए कोई एसओआरटी ऑपरेशन की आवश्यकता नहीं है और गैर-क्लस्टर इंडेक्स खोज करता है। यह उपरोक्त रिकॉर्ड सेट पर निष्पादन समय को <2 सेकंड तक घटा देता है।

SELECT 
[Limit1].[DocumentID] AS [DocumentID], 
[Limit1].[Status] AS [Status], 
[Limit1].[DateCreated] AS [DateCreated]
FROM   (SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM [dbo].[DocumentStatusLogs] AS [Extent1]) AS [Distinct1]
OUTER APPLY  (SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
    FROM (SELECT 
        [Extent2].[ID] AS [ID], 
        [Extent2].[DocumentID] AS [DocumentID], 
        [Extent2].[Status] AS [Status], 
        [Extent2].[DateCreated] AS [DateCreated]
        FROM [dbo].[DocumentStatusLogs] AS [Extent2]
        WHERE ([Distinct1].[DocumentID] = [Extent2].[DocumentID])
    )  AS [Project2]
    ORDER BY [Project2].[ID] DESC) AS [Limit1]

अब मैं ऐसा कुछ मान रहा हूं जो मूल प्रश्न में पूरी तरह से निर्दिष्ट नहीं है, लेकिन यदि आपका टेबल डिज़ाइन ऐसा है कि आपका आईडी कॉलम एक ऑटो-वृद्धि आईडी है, और डेटक्रेटेड प्रत्येक प्रविष्टि के साथ वर्तमान दिनांक पर सेट है, फिर भी उपरोक्त मेरी क्वेरी के साथ चलने के बिना आप वास्तव में दिनांकित करने के क्रम में आईडी पर ऑर्डर करने से जीबीएन के समाधान (लगभग आधा निष्पादन समय) के लिए एक बड़ा प्रदर्शन बढ़ावा प्राप्त कर सकते हैं क्योंकि यह एक समान क्रम क्रम प्रदान करेगा और यह एक तेज़ प्रकार है।


प्रत्येक समूह से शीर्ष 1 का चयन करने के लिए मेरा कोड

select a.* from #DocumentStatusLogs a where 
 datecreated in( select top 1 datecreated from #DocumentStatusLogs b
where 
a.documentid = b.documentid
order by datecreated desc
)

परिदृश्य में जहां आप row_count () का उपयोग करने से बचना चाहते हैं, आप बाएं जुड़ने का भी उपयोग कर सकते हैं:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
left join DocumentStatusLogs filter 
    ON ds.DocumentID = filter.DocumentID
    -- Match any row that has another row that was created after it.
    AND ds.DateCreated < filter.DateCreated
-- then filter out any rows that matched 
where filter.DocumentID is null 

उदाहरण स्कीमा के लिए, आप "सबक्वायरी में नहीं" का भी उपयोग कर सकते हैं, जो आमतौर पर बाएं जुड़ने के समान आउटपुट में संकलित होता है:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
WHERE ds.ID NOT IN (
    SELECT filter.ID 
    FROM DocumentStatusLogs filter
    WHERE ds.DocumentID = filter.DocumentID
        AND ds.DateCreated < filter.DateCreated)

नोट, इस मामले में प्राथमिक कुंजी "आईडी" में तालिका में कम से कम एक सिंगल-कॉलम अद्वितीय कुंजी / बाधा / अनुक्रमणिका नहीं होने पर सबक्वायरी पैटर्न काम नहीं करेगा।

इनमें से दोनों प्रश्न पंक्ति_count () क्वेरी (क्वेरी विश्लेषक द्वारा मापा गया) से अधिक "महंगा" होते हैं। हालांकि, आपको उन परिदृश्यों का सामना करना पड़ सकता है जहां वे परिणाम तेजी से लौटते हैं या अन्य अनुकूलन सक्षम करते हैं।


यह विषय पर सबसे आसानी से पाया गया प्रश्न है, इसलिए मैं इसे एक आधुनिक उत्तर देना चाहता था (दोनों मेरे संदर्भ के लिए और दूसरों की मदद करने के लिए)। अधिक और पहले मान का उपयोग करके आप उपर्युक्त क्वेरी का संक्षिप्त कार्य कर सकते हैं:

select distinct DocumentID
  , first_value(status) over (partition by DocumentID order by DateCreated Desc) as Status
  , first_value(DateCreated) over (partition by DocumentID order by DateCreated Desc) as DateCreated
From DocumentStatusLogs

यह एसक्यूएल सर्वर 2008 और ऊपर में काम करना चाहिए। पहले खंड को ओवर क्लॉज का उपयोग करते समय चुनिंदा शीर्ष 1 को पूरा करने के तरीके के रूप में सोचा जा सकता है। ओवर ने चयन सूची में समूहकरण की अनुमति दी है ताकि नेस्टेड सबक्वायरीज़ लिखने की बजाय (जैसे मौजूदा उत्तरों में से कई), यह एक और अधिक पठनीय फैशन में करता है। उम्मीद है की यह मदद करेगा।


यह SQLite में चेक किया गया है कि आप GROUP BY के साथ निम्न सरल क्वेरी का उपयोग कर सकते हैं

SELECT MAX(DateCreated), *
FROM DocumentStatusLogs
GROUP BY DocumentID

यहां MAX प्रत्येक समूह से अधिकतम दिनांक प्राप्त करने में सहायता करता है।

लेकिन ऐसा लगता है कि MYSQL * -columns को अधिकतम दिनांक के मान के साथ संबद्ध नहीं करता है :(


;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

यदि आप प्रति दिन 2 प्रविष्टियों की अपेक्षा करते हैं, तो यह मनमाने ढंग से एक को चुन देगा। एक दिन के लिए दोनों प्रविष्टियां प्राप्त करने के लिए, इसके बजाय DENSE_RANK का उपयोग करें

सामान्यीकृत या नहीं, यह निर्भर करता है कि आप यह करना चाहते हैं:

  • 2 स्थानों में स्थिति बनाए रखें
  • स्थिति इतिहास को संरक्षित करें
  • ...

जैसा कि यह खड़ा है, आप स्थिति इतिहास को संरक्षित करते हैं। यदि आप मूल तालिका में भी नवीनतम स्थिति चाहते हैं (जो denormalisation है) तो आपको माता-पिता में "स्थिति" बनाए रखने के लिए एक ट्रिगर की आवश्यकता होगी। या इस स्थिति इतिहास तालिका ड्रॉप।


मैंने यहां विभिन्न सिफारिशों पर कुछ समय लगाया है, और परिणाम वास्तव में शामिल तालिका के आकार पर निर्भर करते हैं, लेकिन सबसे संगत समाधान क्रॉस आवेदन का उपयोग कर रहा है ये परीक्षण SQL Server 2008-R2 के विरुद्ध चलाए गए थे, जिसमें एक तालिका का उपयोग किया गया था 6,500 रिकॉर्ड, और 137 मिलियन रिकॉर्ड के साथ एक और (समान स्कीमा)। पूछे जाने वाले कॉलम तालिका पर प्राथमिक कुंजी का हिस्सा हैं, और तालिका की चौड़ाई बहुत छोटी है (लगभग 30 बाइट्स)। SQL सर्वर द्वारा वास्तविक निष्पादन योजना से समय की सूचना दी जाती है।

Query                                  Time for 6500 (ms)    Time for 137M(ms)

CROSS APPLY                                    17.9                17.9
SELECT WHERE col = (SELECT MAX(COL)…)           6.6               854.4
DENSE_RANK() OVER PARTITION                     6.6               907.1

मुझे लगता है कि वास्तव में आश्चर्यजनक बात यह थी कि इसमें शामिल पंक्तियों की संख्या के बावजूद क्रॉस आवेदन के लिए समय कितना संगत था।


मैंने अभी cross apply करने का तरीका सीखा है। इस परिदृश्य में इसका उपयोग कैसे करें:

 select d.DocumentID, ds.Status, ds.DateCreated 
 from Documents as d 
 cross apply 
     (select top 1 Status, DateCreated
      from DocumentStatusLogs 
      where DocumentID = d.DocumentId
      order by DateCreated desc) as ds

SELECT o.*
FROM `DocumentStatusLogs` o                   
  LEFT JOIN `DocumentStatusLogs` b                   
  ON o.DocumentID = b.DocumentID AND o.DateCreated < b.DateCreated
 WHERE b.DocumentID is NULL ;

यदि आप डेटक्रेटेड द्वारा हालिया दस्तावेज़ ऑर्डर को वापस करना चाहते हैं, तो यह केवल DocumentID द्वारा शीर्ष 1 दस्तावेज़ लौटाएगा





greatest-n-per-group