MySQL में कई कॉलम पर आधारित रैंक निर्धारित करें




sql-rank (3)

मेरे पास एक तालिका है जिसमें 3 फ़ील्ड हैं, मैं user_id और game_id के आधार पर कॉलम रैंक करना चाहता हूं।

यहाँ SQL Fiddle: http://sqlfiddle.com/#!9/883e9d/1 -9 http://sqlfiddle.com/#!9/883e9d/1 883e9d http://sqlfiddle.com/#!9/883e9d/1

मेरे पास पहले से ही टेबल है: 

 user_id | game_id |   game_detial_sum  |
 --------|---------|--------------------|
 6       | 10      |  1000              |   
 6       | 11      |  260               |
 7       | 10      |  1200              |
 7       | 11      |  500               |
 7       | 12      |  360               |
 7       | 13      |  50                |

अपेक्षित उत्पादन : 

user_id  | game_id |   game_detial_sum  |  user_game_rank  |
 --------|---------|--------------------|------------------|
 6       | 10      |  1000              |   1              |
 6       | 11      |  260               |   2              |
 7       | 10      |  1200              |   1              |
 7       | 11      |  500               |   2              |
 7       | 12      |  360               |   3              |
 7       | 13      |  50                |   4              |

अब तक के मेरे प्रयास: 

SET @s := 0; 
SELECT user_id,game_id,game_detail, 
       CASE WHEN user_id = user_id THEN (@s:[email protected]s+1) 
            ELSE @s = 0 
       END As user_game_rank 
FROM game_logs

संपादित करें: (ओपी Comments ): ऑर्डरिंग game_detail के अवरोही क्रम पर आधारित है

game_detail का क्रम

आप एक बहुत ही सरल सह-संबद्ध उप क्वेरी का उपयोग कर सकते हैं: 

SELECT *, (
    SELECT COUNT(DISTINCT game_detail) + 1
    FROM game_logs AS x
    WHERE user_id = t.user_id AND game_detail > t.game_detail
) AS user_game_rank
FROM game_logs AS t
ORDER BY user_id, user_game_rank

डीबी फ़िडल

यह धीमी लेकिन उपयोगकर्ता चर की तुलना में अधिक विश्वसनीय है। यह सब उन्हें तोड़ने के लिए एक JOIN है।

एक व्युत्पन्न तालिका में ( FROM क्लॉज के अंदर उपकुंजी), हम अपने डेटा को ऐसे क्रमबद्ध करते हैं कि सभी user_id मानों वाली सभी पंक्तियाँ एक साथ आती हैं, और आगे अवरोही क्रम में game_detail आधार पर उनके बीच छँटाई होती है।

अब, हम इस परिणाम-सेट का उपयोग करते हैं और सशर्त CASE..WHEN उपयोग करते हैं। पंक्ति क्रमांकन का मूल्यांकन करने के लिए CASE..WHEN भाव हैं। यह एक लूपिंग तकनीक की तरह होगा (जिसे हम एप्लिकेशन कोड में उपयोग करते हैं, जैसे: PHP)। हम पिछली पंक्ति के मानों को उपयोगकर्ता-परिभाषित चर में संग्रहीत करेंगे, और फिर पिछली पंक्ति के विरुद्ध वर्तमान पंक्ति के मान की जाँच करेंगे। आखिरकार, हम तदनुसार पंक्ति संख्या निर्दिष्ट करेंगे।

संपादित करें: MySQL docs और @ गॉर्डन लिनोफ़ के अवलोकन के आधार पर:

उपयोगकर्ता चर को शामिल करने वाले अभिव्यक्तियों के मूल्यांकन का क्रम अपरिभाषित है। उदाहरण के लिए, इस बात की कोई गारंटी नहीं है कि Select @a, @a: = @ a + 1 पहले @a का मूल्यांकन करता है और फिर असाइनमेंट करता है।

हमें पंक्ति संख्या का मूल्यांकन करने और उसी अभिव्यक्ति के भीतर @u चर के लिए user_id मान निर्दिष्ट करने की आवश्यकता होगी। 

SET @r := 0, @u := 0; 
SELECT
  @r := CASE WHEN @u = dt.user_id 
                  THEN @r + 1
             WHEN @u := dt.user_id /* Notice := instead of = */
                  THEN 1 
        END AS user_game_rank, 
  dt.user_id, 
  dt.game_detail, 
  dt.game_id 

FROM 
( SELECT user_id, game_id, game_detail
  FROM game_logs 
  ORDER BY user_id, game_detail DESC 
) AS dt

परिणाम 

| user_game_rank | user_id | game_detail | game_id |
| -------------- | ------- | ----------- | ------- |
| 1              | 6       | 260         | 11      |
| 2              | 6       | 100         | 10      |
| 1              | 7       | 1200        | 10      |
| 2              | 7       | 500         | 11      |
| 3              | 7       | 260         | 12      |
| 4              | 7       | 50          | 13      |

DB Fiddle पर देखें

MySQL docs एक दिलचस्प नोट, जिसे मैंने हाल ही में खोजा है:

MySQL के पिछले रिलीज ने SET के अलावा अन्य स्टेटमेंट्स में एक यूजर वेरिएबल के लिए एक वैल्यू असाइन करना संभव बना दिया। यह कार्यक्षमता बैकवर्ड संगतता के लिए MySQL 8.0 में समर्थित है, लेकिन MySQL के भविष्य के रिलीज में हटाने के अधीन है।

इसके अलावा, एक साथी SO सदस्य के लिए धन्यवाद, MySQL टीम द्वारा इस ब्लॉग पर आया: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

सामान्य अवलोकन यह है कि एक ही क्वेरी ब्लॉक में उपयोगकर्ता चर के मूल्यांकन के साथ ORDER BY का उपयोग करना, यह सुनिश्चित नहीं करता है कि मान हमेशा सही होंगे। जैसा कि, MySQL ऑप्टिमाइज़र जगह में आ सकता है और मूल्यांकन के हमारे निर्धारित क्रम को बदल सकता है।

इस समस्या के लिए सबसे अच्छा तरीका MySQL 8+ में अपग्रेड करना होगा और Row_Number() कार्यक्षमता का उपयोग करना Row_Number() :

स्कीमा (MySQL v8.0) 

SELECT user_id, 
       game_id, 
       game_detail, 
       ROW_NUMBER() OVER (PARTITION BY user_id 
                          ORDER BY game_detail DESC) AS user_game_rank 
FROM game_logs 
ORDER BY user_id, user_game_rank;

परिणाम 

| user_id | game_id | game_detail | user_game_rank |
| ------- | ------- | ----------- | -------------- |
| 6       | 11      | 260         | 1              |
| 6       | 10      | 100         | 2              |
| 7       | 10      | 1200        | 1              |
| 7       | 11      | 500         | 2              |
| 7       | 12      | 260         | 3              |
| 7       | 13      | 50          | 4              |

DB Fiddle पर देखें 

SELECT user_id, game_id, game_detail, 
       CASE WHEN user_id = @lastUserId 
            THEN @rank := @rank + 1 
            ELSE @rank := 1 
       END As user_game_rank,
       @lastUserId := user_id
FROM game_logs
cross join (select @rank := 0, @lastUserId := 0) r
order by user_id, game_detail desc

SQLFiddle डेमो




mysql   sql   sql-rank  


sql-rank