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




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

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

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

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

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

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

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


मैं 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


SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

MySQL नहीं , लेकिन अन्य लोगों के लिए इस प्रश्न को ढूंढने और एसक्यूएल का उपयोग करने के लिए, greatest-n-per-group समस्या को हल करने का एक और तरीका एमएस एसक्यूएल में Cross Apply का उपयोग कर रहा है

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

SqlFiddle में एक उदाहरण दिया गया है


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

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

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


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

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

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

आपको 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 साथ दो पंक्तियां हैं, तो दोनों पंक्तियों के परिणामस्वरूप दोनों पंक्तियां परिणामस्वरूप होंगी।

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

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


मेरी प्राथमिकता जितनी संभव हो सके छोटे कोड का उपयोग करना है ...

आप इसे आज़माकर इसे कर सकते हैं:

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

मेरे दिमाग में यह कम जटिल है ... पढ़ने और बनाए रखने में आसान है।


मैं इस समस्या के लिए पूर्व-आधारित समाधान का उपयोग करना पसंद 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 बन गया

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


यदि आपके पास चुनिंदा कथन में कई फ़ील्ड हैं और आप उन सभी फ़ील्ड के लिए अनुकूलित कोड के माध्यम से नवीनतम मान चाहते हैं:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

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

रिकॉर्ड्स को केवल उस फ़ील्ड के साथ पुनर्प्राप्त करने का एक और समाधान है जिसमें उस फ़ील्ड के लिए अधिकतम मूल्य है। यह 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)

यह समाधान आपकेटेबल से केवल एक चयन करता है, इसलिए यह तेज़ है। यह केवल 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

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

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 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)

इन उत्तरों में से कोई भी मेरे लिए काम नहीं किया है।

इसी से मेरा काम बना है।

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

मुझे कुछ कॉलम द्वारा रिकॉर्ड रैंकिंग करके ऐसा करना पसंद है। इस मामले में, 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 क्वेरी से पूछताछ नहीं कर रहा हूं।


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

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

यहां एक और समाधान है उम्मीद है कि यह किसी की मदद करेगा

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev

बहुत से, यदि सभी नहीं, तो यहां अन्य उत्तरों के छोटे डेटासेट के लिए ठीक है। स्केलिंग के लिए, अधिक देखभाल की आवश्यकता है। here देखें

यह प्रति समूह समूह अधिकतम और शीर्ष-एन समूह करने के कई तेज़ तरीकों पर चर्चा करता है।


एक तीसरा समाधान जिसे मैंने शायद ही कभी देखा है, 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 सीमा में भी तेजी से भाग लेंगे।


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

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) );
}

चूंकि यह इस समस्या के संबंध में सबसे लोकप्रिय सवाल है, इसलिए मैं इसे यहां एक और उत्तर दोबारा पोस्ट करूंगा:

ऐसा लगता है कि ऐसा करने का एक आसान तरीका है (लेकिन केवल MySQL में ):

select *
from (select * from mytable order by id, rev desc ) x
group by id

इस समस्या के लिए संक्षिप्त और सुरुचिपूर्ण उत्तर प्रदान करने के लिए कृपया इस प्रश्न में उपयोगकर्ता बोहेमियन का क्रेडिट उत्तर दें

संपादित करें: हालांकि यह समाधान कई लोगों के लिए काम करता है, यह लंबे समय तक स्थिर नहीं हो सकता है, क्योंकि MySQL गारंटी नहीं देता है कि ग्रुप बाय स्टेटमेंट ग्रुप बाय सूची में कॉलम के लिए सार्थक मूल्य वापस नहीं करेगा। तो इस समाधान का अपने जोखिम पर उपयोग करें


नौकरी करने का एक और तरीका ओवर पार्टिशन क्लॉज में 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 जी पर अच्छी तरह से काम करें।





greatest-n-per-group