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




tsql sql-server-2005 (10)

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

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            |

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


इसे इस्तेमाल करे:

        SELECT [DocumentID], 
        [tmpRez].value('/x[2]','varchar(20)') as [Status],
 [tmpRez].value('/x[3]','datetime') as [DateCreated] 
FROM (
        SELECT [DocumentID],
    cast('<x>'+max(cast([ID] as varchar(10))+'</x><x>'+[Status]+'</x><x>'
    +cast([DateCreated] as varchar(20)))+'</x>' as XML) as [tmpRez]
        FROM DocumentStatusLogs
        GROUP by DocumentID) as [tmpQry]

ऊपर से क्लिंट के भयानक और सही उत्तर की पुष्टि:

नीचे दिए गए दो प्रश्नों के बीच प्रदर्शन दिलचस्प है। 52% शीर्ष पर है। और 48% दूसरा वाला है। ORDER BY के बजाय DISTINCT का उपयोग करके प्रदर्शन में 4% सुधार। लेकिन ऑर्डर द्वारा कई स्तंभों को सॉर्ट करने का लाभ है।

IF (OBJECT_ID('tempdb..#DocumentStatusLogs') IS NOT NULL) BEGIN DROP TABLE #DocumentStatusLogs END

CREATE TABLE #DocumentStatusLogs (
    [ID] int NOT NULL,
    [DocumentID] int NOT NULL,
    [Status] varchar(20),
    [DateCreated] datetime
)

INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (2, 1, 'S1', '7/29/2011 1:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (3, 1, 'S2', '7/30/2011 2:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 1, 'S1', '8/02/2011 3:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (1, 2, 'S1', '7/28/2011 4:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (4, 2, 'S2', '7/30/2011 5:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (5, 2, 'S3', '8/01/2011 6:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 3, 'S1', '8/02/2011 7:00:00')

विकल्प 1:

    SELECT
    [Extent1].[ID], 
    [Extent1].[DocumentID],
    [Extent1].[Status], 
    [Extent1].[DateCreated]
FROM #DocumentStatusLogs AS [Extent1]
    OUTER APPLY (
        SELECT TOP 1
            [Extent2].[ID], 
            [Extent2].[DocumentID],
            [Extent2].[Status], 
            [Extent2].[DateCreated]
        FROM #DocumentStatusLogs AS [Extent2]
        WHERE [Extent1].[DocumentID] = [Extent2].[DocumentID]
        ORDER BY [Extent2].[DateCreated] DESC, [Extent2].[ID] DESC
    ) AS [Project2]
WHERE ([Project2].[ID] IS NULL OR [Project2].[ID] = [Extent1].[ID])

विकल्प 2:

SELECT 
    [Limit1].[DocumentID] AS [ID], 
    [Limit1].[DocumentID] AS [DocumentID], 
    [Limit1].[Status] AS [Status], 
    [Limit1].[DateCreated] AS [DateCreated]
FROM (
    SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM #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 #DocumentStatusLogs AS [Extent2]
            WHERE [Distinct1].[DocumentID] = [Extent2].[DocumentID]
        )  AS [Project2]
        ORDER BY [Project2].[ID] DESC
    ) AS [Limit1]

एम $ प्रबंधन स्टूडियो: पहले ब्लॉक को हाइलाइट करने और चलाने के बाद, विकल्प 1 और विकल्प 2 दोनों को हाइलाइट करें, राइट क्लिक करें -> [प्रदर्शन अनुमानित निष्पादन योजना]। फिर परिणामों को देखने के लिए पूरी चीज चलाएं।

विकल्प 1 परिणाम:

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

विकल्प 2 परिणाम:

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

ध्यान दें:

जब मैं 1-से- (कई में से 1) में शामिल होना चाहता हूं तो मैं आवेदन का उपयोग करता हूं।

मैं जॉइन का उपयोग करता हूं अगर मैं 1 से कई लोगों में शामिल होना चाहता हूं, या कई से ज्यादा।

मैं ROW_NUMBER () के साथ सीटीई से बचता हूं जब तक कि मुझे कुछ उन्नत करने की आवश्यकता नहीं होती है और विंडोिंग प्रदर्शन दंड के साथ ठीक है।

मैं कहां या ऑन क्लॉज में EXISTS / IN subqueries से बचता हूं, क्योंकि मैंने अनुभव किया है कि इससे कुछ भयानक निष्पादन योजनाएं उत्पन्न हुई हैं। लेकिन माइलेज भिन्न होता है। जहां और जब आवश्यक हो निष्पादन योजना और प्रोफ़ाइल प्रदर्शन की समीक्षा करें!


प्रत्येक समूह से शीर्ष 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
)

मुझे पता है कि यह एक पुराना धागा है लेकिन TOP 1 WITH TIES समाधान के TOP 1 WITH TIES काफी अच्छा है और कुछ समाधानों के माध्यम से पढ़ने में मददगार हो सकता है।

select top 1 with ties
   DocumentID
  ,Status
  ,DateCreated
from DocumentStatusLogs
order by row_number() over (partition by DocumentID order by DateCreated desc)

शीर्ष खंड के बारे में अधिक here पाया जा सकता here


मैंने यहां विभिन्न सिफारिशों पर कुछ समय लगाया है, और परिणाम वास्तव में शामिल तालिका के आकार पर निर्भर करते हैं, लेकिन सबसे संगत समाधान क्रॉस आवेदन का उपयोग कर रहा है ये परीक्षण 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

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


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

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

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


यह काफी पुराना धागा है, लेकिन मैंने सोचा कि मैं अपने दो सेंट फेंक दूंगा जैसा स्वीकार्य उत्तर मेरे लिए विशेष रूप से अच्छा काम नहीं करता है। मैंने बड़े डेटासेट पर जीबीएन के समाधान की कोशिश की और इसे बहुत धीमा पाया (> 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]

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


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

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 को पूरा करने के तरीके के रूप में सोचा जा सकता है। ओवर ने चयन सूची में समूहकरण की अनुमति दी है ताकि नेस्टेड सबक्वायरीज़ लिखने की बजाय (जैसे मौजूदा उत्तरों में से कई), यह एक और अधिक पठनीय फैशन में करता है। उम्मीद है की यह मदद करेगा।


;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 है) तो आपको माता-पिता में "स्थिति" बनाए रखने के लिए एक ट्रिगर की आवश्यकता होगी। या इस स्थिति इतिहास तालिका ड्रॉप।


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




greatest-n-per-group