mysql - एसक्यूएल कॉलम पर अधिकतम मूल्य के साथ केवल पंक्तियों का चयन करें




aggregate-functions greatest-n-per-group (20)

मेरे पास दस्तावेजों के लिए यह तालिका है (यहां सरलीकृत संस्करण):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

मैं प्रति आईडी एक पंक्ति और केवल सबसे बड़ा संशोधन कैसे चुनूं?
उपर्युक्त डेटा के साथ, परिणाम में दो पंक्तियां होनी चाहिए: [1, 3, ...] और [2, 1, ..] । मैं MySQL का उपयोग कर रहा हूँ।

वर्तमान में मैं परिणाम लूप से पुरानी revs का पता लगाने और लिखने के लिए while लूप में चेक का उपयोग करता हूं। लेकिन क्या यह परिणाम प्राप्त करने का एकमात्र तरीका है? क्या कोई एसक्यूएल समाधान नहीं है?

अद्यतन करें
जैसा कि उत्तरों का सुझाव है, एक एसक्यूएल समाधान है, और यहां एक sqlfiddle डेमो है

अद्यतन 2
मैंने उपरोक्त sqlfiddle जोड़ने के बाद देखा, जिस दर पर सवाल उठाया गया है, वह उत्तर की ऊपरी दर को पार कर गया है। यह इरादा नहीं रहा है! पहेली जवाब पर आधारित है, विशेष रूप से स्वीकृत उत्तर।


पहली नजर में...

आपको MAX फ़ंक्शन के साथ GROUP BY क्लॉज की आवश्यकता है:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

यह इतना आसान नहीं है, है ना?

मैंने अभी देखा है कि आपको content कॉलम content आवश्यकता है।

SQL में यह एक बहुत ही आम प्रश्न है: कुछ समूह पहचानकर्ता के कॉलम में कुछ अधिकतम मान के साथ पंक्ति के लिए संपूर्ण डेटा खोजें। मैंने सुना है कि मेरे करियर के दौरान बहुत कुछ। असल में, यह एक प्रश्न था जिसका मैंने अपने वर्तमान नौकरी के तकनीकी साक्षात्कार में जवाब दिया था।

वास्तव में, यह इतना आम है कि स्टैक ओवरफ्लो समुदाय ने इस तरह के प्रश्नों से निपटने के लिए केवल एक टैग बनाया है: greatest-n-per-group ।

असल में, उस समस्या को हल करने के लिए आपके पास दो दृष्टिकोण हैं:

सरल group-identifier, max-value-in-group उप-क्वेरी के साथ जुड़ना

इस दृष्टिकोण में, आप पहले उप-क्वेरी में group-identifier, max-value-in-group (पहले से हल हो चुके हैं) को ढूंढते हैं। फिर आप group-identifier और max-value-in-group दोनों पर समानता के साथ उप-क्वेरी में अपनी तालिका में शामिल हों:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

स्वयं के साथ जुड़ना, शर्तों और फिल्टर में शामिल होना tweaking

इस दृष्टिकोण में, आप टेबल के साथ स्वयं में शामिल हो गए। समानता, निश्चित रूप से group-identifier । फिर, 2 स्मार्ट चालें:

  1. दूसरी जुड़ने की स्थिति में दाएं मूल्य से कम मूल्य मूल्य कम है
  2. जब आप चरण 1 करते हैं, तो वास्तव में अधिकतम मूल्य वाले पंक्ति में दाईं ओर NULL होगा (यह एक LEFT JOIN , याद है?)। फिर, हम शामिल परिणामों को फ़िल्टर करते हैं, केवल पंक्तियों को दिखाते हैं जहां दाएं तरफ NULL

तो आप के साथ समाप्त होता है:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

निष्कर्ष

दोनों दृष्टिकोण सटीक परिणाम लाते हैं।

यदि आपके पास group-identifier लिए max-value-in-group साथ दो पंक्तियां हैं, तो दोनों पंक्तियों के परिणामस्वरूप दोनों पंक्तियां परिणामस्वरूप होंगी।

दोनों दृष्टिकोण एसक्यूएल एएनएसआई संगत हैं, इस प्रकार, इसके "स्वाद" के बावजूद, आपके पसंदीदा आरडीबीएमएस के साथ काम करेंगे।

दोनों दृष्टिकोण भी अनुकूल हैं, हालांकि आपका माइलेज भिन्न हो सकता है (आरडीबीएमएस, डीबी संरचना, इंडेक्स इत्यादि)। तो जब आप दूसरे पर एक दृष्टिकोण चुनते हैं, बेंचमार्क । और सुनिश्चित करें कि आप वह व्यक्ति चुनते हैं जो आपको अधिक समझ में आता है।


अगर कोई एक लिंक बनाम की तलाश में है, तो यह मेरे लिए काम करता है:

public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
    var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
        .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );    

    return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}

इस बारे में कैसा है:

select all_fields.*  
from  (select id, MAX(rev) from yourtable group by id) as max_recs  
left outer join yourtable as all_fields  
on max_recs.id = all_fields.id

एक तीसरा समाधान जिसे मैंने शायद ही कभी देखा है, MySQL विशिष्ट है और ऐसा लगता है:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

हां यह भयानक लग रहा है (स्ट्रिंग और बैक इत्यादि में परिवर्तित) लेकिन मेरे अनुभव में यह आमतौर पर अन्य समाधानों की तुलना में तेज़ है। हो सकता है कि सिर्फ मेरे उपयोग के मामलों के लिए, लेकिन मैंने इसे लाखों अभिलेखों और कई अद्वितीय आईडी के साथ तालिकाओं पर उपयोग किया है। शायद ऐसा इसलिए है क्योंकि MySQL अन्य समाधानों को अनुकूलित करने में बहुत खराब है (कम से कम 5.0 दिनों में जब मैं इस समाधान के साथ आया था)।

एक महत्वपूर्ण बात यह है कि GROUP_CONCAT की स्ट्रिंग के लिए अधिकतम लंबाई है जो इसे बना सकती है। आप शायद group_concat_max_len चर सेट करके इस सीमा को उठाना चाहते हैं। और ध्यान रखें कि यदि आपके पास बड़ी संख्या में पंक्तियां हैं तो स्केलिंग पर यह एक सीमा होगी।

वैसे भी, उपर्युक्त सीधे काम नहीं करता है अगर आपका सामग्री फ़ील्ड पहले से ही टेक्स्ट है। उस स्थिति में आप शायद एक अलग विभाजक का उपयोग करना चाहते हैं, जैसे \ 0 शायद। आप group_concat_max_len सीमा में भी तेजी से भाग लेंगे।


कर्मचारी से चुनें जहां कर्मचारी। वेतन में (कर्मचारी समूह से कर्मचारी समूह से अधिकतम (वेतन) का चयन करें) कर्मचारी द्वारा आदेश


कुछ इस तरह?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

नौकरी करने का एक और तरीका ओवर पार्टिशन क्लॉज में MAX () विश्लेषणात्मक फ़ंक्शन का उपयोग कर रहा है

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

इस पोस्ट में पहले से प्रलेखित पार्टनर समाधान पर दूसरा है

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
      FROM YourTable
    ) t
  WHERE t.rank = 1 

यह 2 ओरेकल 10 जी पर अच्छी तरह से काम करें।


फिर भी एक और समाधान एक सहसंबंधित सबक्वायरी का उपयोग करना है:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

इंडेक्स (आईडी, रेव) पर सबकुछ लगभग सरल लुकअप के रूप में प्रस्तुत करता है ...

@ 1 मिलियन रिकॉर्ड की इनो डीडी तालिका के साथ MySQL माप के आधार पर @ एड्रियन कार्नेरो के उत्तर (सबक्वायरी, बाएंजॉइन) में समाधानों की तुलना निम्नलिखित है, समूह का आकार: 1-3।

जबकि पूर्ण तालिका स्कैन के लिए सबक्वायरी / बाएंजॉइन / सहसंबंधित समय एक-दूसरे से 6/8/9 के रूप में संबंधित होते हैं, जब प्रत्यक्ष लुकअप या बैच ( id in (1,2,3) ) की बात आती है, तो सबक्वायरी बहुत धीमी होती है और अन्य ( Subquery rerunning के कारण)। हालांकि मैं गति में बाएंजोइन और सहसंबंधित समाधानों के बीच अंतर नहीं कर सका।

एक अंतिम नोट, चूंकि बाएंजॉइन समूह में एन * (एन + 1) / 2 जुड़ता है, इसका प्रदर्शन समूहों के आकार से भारी प्रभावित हो सकता है ...


मुझे कुछ कॉलम द्वारा रिकॉर्ड रैंकिंग करके ऐसा करना पसंद है। इस मामले में, id द्वारा समूहित रैंक rev मान। उच्च rev लोगों की रैंकिंग कम होगी। तो उच्चतम rev में 1 रैंकिंग होगी।

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

सुनिश्चित नहीं है कि चर शुरू करने से पूरी चीज धीमी हो जाती है। लेकिन कम से कम मैं दो बार YOURTABLE क्वेरी से पूछताछ नहीं कर रहा हूं।


मुझे लगता है कि यह सबसे आसान समाधान है:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • चुनें *: सभी फ़ील्ड लौटें।
  • कर्मचारी से: तालिका की खोज की गई।
  • (चयन करें * ...) सबक्वायरी: वेतन द्वारा क्रमबद्ध सभी लोगों को लौटाएं।
  • कर्मचारियों द्वारा समूह। सारारी:: शीर्ष-क्रमबद्ध बल, प्रत्येक कर्मचारी की वेतन पंक्ति लौटा परिणाम होने के लिए मजबूर करें।

यदि आपको केवल एक पंक्ति की आवश्यकता होती है, तो यह भी आसान है:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

मुझे यह भी लगता है कि अन्य उद्देश्यों को तोड़ना, समझना और संशोधित करना सबसे आसान है:

  • कर्मचारी द्वारा आदेश। सैलरी डीईएससी: सबसे ज्यादा वेतन के साथ वेतन से परिणाम आदेश दें।
  • सीमा 1: केवल एक परिणाम लौटें।

इस दृष्टिकोण को समझना, इन समान समस्याओं में से किसी को हल करना तुच्छ हो जाता है: कर्मचारी को सबसे कम वेतन (एएससी में डीईएससी बदलें) प्राप्त करें, शीर्ष दस कमाई करने वाले कर्मचारियों (LIMIT 1 को LIMIT 10 में बदलें) प्राप्त करें, किसी अन्य क्षेत्र के माध्यम से क्रमबद्ध करें (BYDER द्वारा बदलें कर्मचारी द्वारा आदेश देने के लिए कर्मचारी। आयोग), आदि ..


मैं flabbergasted है कि कोई जवाब एसक्यूएल खिड़की समारोह समाधान की पेशकश की:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

एसक्यूएल मानक एएनएसआई / आईएसओ मानक एसक्यूएल में जोड़ा गया: 2003 और बाद में एएनएसआई / आईएसओ मानक एसक्यूएल: 2008 के साथ विस्तारित, विंडो (या विंडोिंग) फ़ंक्शंस अब सभी प्रमुख विक्रेताओं के साथ उपलब्ध हैं। टाई समस्या से निपटने के लिए कई प्रकार के रैंक फ़ंक्शंस उपलब्ध हैं: RANK, DENSE_RANK, PERSENT_RANK


मैं इस समस्या के लिए पूर्व-आधारित समाधान का उपयोग करना पसंद NOT EXIST हूं:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

मैं प्रदर्शन के लिए झुकाव नहीं कर सकता, लेकिन यहां माइक्रोसॉफ्ट एक्सेल की सीमाओं से प्रेरित एक चाल है। इसमें कुछ अच्छी विशेषताएं हैं

अच्छी चीज़

  • यह टाई होने पर भी एक "अधिकतम रिकॉर्ड" की वापसी को मजबूर होना चाहिए (कभी-कभी उपयोगी)
  • इसमें शामिल होने की आवश्यकता नहीं है

दृष्टिकोण

यह थोड़ा बदसूरत है और यह आवश्यक है कि आप rev कॉलम के मान्य मानों की सीमा के बारे में कुछ जानते हों। आइए मान लें कि हम जानते हैं कि रेव कॉलम 0.00 और 99 9 के बीच दशमलव के बीच एक संख्या है, लेकिन दशमलव बिंदु के दाईं ओर केवल दो अंक होंगे (उदाहरण के लिए 34.17 मान्य मान होगा)।

चीज का अर्थ यह है कि आप स्ट्रिंग द्वारा एक सिंथेटिक कॉलम बनाते हैं जो आपके इच्छित डेटा के साथ प्राथमिक तुलना फ़ील्ड को पैक / पैकिंग करते हैं। इस तरह, आप सभी डेटा को वापस करने के लिए एसक्यूएल के MAX () कुल फ़ंक्शन को मजबूर कर सकते हैं (क्योंकि इसे एक कॉलम में पैक किया गया है)। फिर आपको डेटा को अनपैक करना होगा।

एसक्यूएल में लिखे गए उपरोक्त उदाहरण के साथ यह कैसा दिखता है

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

पैकिंग पुनरावृत्ति के मूल्य के बावजूद रेव कॉलम को कई ज्ञात वर्ण लंबाई के लिए मजबूर कर देती है ताकि उदाहरण के लिए

  • 3.2 1003.201 बन जाता है
  • 57 1057.001 बन जाता है
  • 923.88 1 9 23.881 बन गया

यदि आप इसे सही करते हैं, तो दो संख्याओं की स्ट्रिंग तुलना को "अधिकतम" को दो संख्याओं की संख्यात्मक तुलना के रूप में उपज करना चाहिए और सबस्ट्रिंग फ़ंक्शन का उपयोग करके मूल संख्या में वापस परिवर्तित करना आसान है (जो एक रूप में उपलब्ध है या किसी अन्य रूप में बहुत अधिक है हर जगह)।


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

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

मैं फिर इन अधिकतम मानों (# temp1) में सभी संभावित आईडी / सामग्री संयोजनों में शामिल हो गया। ऐसा करने से, मैं स्वाभाविक रूप से गैर-अधिकतम आईडी / सामग्री संयोजनों को फ़िल्टर करता हूं, और प्रत्येक के लिए केवल अधिकतम संशोधन मूल्यों के साथ छोड़ दिया जाता है।

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

यह sqlite3 में मेरे लिए काम करता है:

SELECT *, MAX(rev) FROM t1 GROUP BY id

* के साथ, आपको डुप्लिकेट रेव कॉलम मिलता है, लेकिन यह कोई समस्या नहीं है।


यह समाधान आपकेटेबल से केवल एक चयन करता है, इसलिए यह तेज़ है। यह केवल MySQL और SQLite (SQLite को डीईएससी हटाने के लिए) के लिए काम करता है sqlfiddle.com पर परीक्षण के अनुसार। हो सकता है कि इसे अन्य भाषाओं पर काम करने के लिए tweaked किया जा सकता है जो मैं परिचित नहीं हूँ।

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id

रिकॉर्ड्स को केवल उस फ़ील्ड के साथ पुनर्प्राप्त करने का एक और समाधान है जिसमें उस फ़ील्ड के लिए अधिकतम मूल्य है। यह SQL400 के लिए काम करता है जो प्लेटफॉर्म पर काम करता है। इस उदाहरण में, FIELD5 फ़ील्ड में अधिकतम मान वाले रिकॉर्ड निम्न SQL कथन द्वारा पुनर्प्राप्त किए जाएंगे।

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)

संशोधित क्षेत्र को रिवर्स ऑर्डर में क्रमबद्ध करें और उसके बाद आईडी द्वारा समूहित किया गया है जो प्रत्येक समूह की पहली पंक्ति देता है जो उच्चतम संशोधन मान वाला है।

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

निम्नलिखित डेटा के साथ http://sqlfiddle.com/ में परीक्षण किया गया

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

इसने निम्नलिखित परिणाम MySQL 5.5 और 5.6 में दिया

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

select * from yourtable
group by id
having rev=max(rev);




greatest-n-per-group