group-by - अलग-अलग मानों को कैसे गिनाना है जो कि MySQL में एक शर्त को संतुष्ट करते हैं?




count distinct (8)

मैं किसी विशेष क्षेत्र में अलग-अलग मानों को खोजने के लिए एक क्वेरी लिखने की कोशिश कर रहा हूं, उस मान की घटनाओं की संख्या की गणना करें जहां उस विशेष मान के सभी उदाहरणों के लिए एक और कॉलम मान संतुष्ट है, और फिर परिणाम निम्नानुसार प्रदर्शित करें (अधिक स्पष्टीकरण का पालन करें):

उदाहरण डीबी:

RowId    Status       MemberIdentifier
-----    ------       ----------------
1       In Progress   111111111
2       Complete      123456789
3       Not Started   146782452
4       Complete      111111111
5       Complete      123456789
6       Not Started   146782452
7       Complete      111111111

वांछित परिणाम:

Status         MemberIdentifierCount 
------         ---------------------- 
Not Started    1
In Progress    1
Complete       1

उपरोक्त प्रश्न में, किसी दिए गए स्थिति के साथ अलग-अलग सदस्य पहचानकर्ताओं की संख्या की गणना की जाती है और प्रदर्शित की जाती है। यदि कोई सदस्य पहचानकर्ता की स्थिति 'पूर्ण' के साथ दो पंक्तियां हैं, लेकिन 'प्रगति में' स्थिति वाला एक है, तो उसे समूहीकृत और प्रगति के रूप में गिना जाता है (अर्थात, सदस्य पहचानकर्ता = 111111111)। सदस्य आयडेंटिफायर के लिए समूहीकृत और गिनती के अनुसार, उसकी सभी पंक्तियों में 'पूर्ण' की स्थिति होना चाहिए (यानी, सदस्य पहचानकर्ता = 123456789)। किसी अंतर्दृष्टि की सराहना की जाएगी (MySQL न्यूबी)


Answers

आदेश को कॉन्फ़िगर करने के लिए एक विशिष्ट तालिका का उपयोग करने वाला दूसरा तरीका (दो पूर्णांक की शक्ति के लिए मानचित्र)

इस मैपिंग को डेटा को स्थानांतरित करने के लिए bit_or समेकित की अनुमति है

http://rextester.com/edit/ZSG98543

-- Table bit_progression to determine priority

CREATE TABLE bit_progression (bit_status int PRIMARY KEY, Status VARCHAR(255));
INSERT INTO bit_progression (bit_status, Status)
VALUES
(1,       'Not Started'),  
(2,       'Complete'   ),      
(4,       'In Progress');

select
    Status,
    count(*)
from
    (
    select
         MemberIdentifier,max(bit_status) bit_status
    from
        tbl natural join bit_progression
    group by
        MemberIdentifier
    ) Maxi natural join bit_progression
group by
    Status
;

उत्पादित करें

Status  count(*)

1   Complete    1
2   In Progress 1
3   Not Started 1

अतिरिक्त:

select
    MemberIdentifier,
    bit_or(bit_status) bits_status,
    case when bit_or(bit_status) & 4 = 4 then true end as withStatusInProgress,
    case when bit_or(bit_status) & 2 = 2 then true end as withStatusComplete,
    case when bit_or(bit_status) & 1 = 1 then true end as withStatusNotStarted
from
    tbl natural join bit_progression
group by
    MemberIdentifier
;

इसे उत्पादन:

MemberIdentifier bits_status    withStatusInProgress    withStatusComplete  withStatusNotStarted

111111111   6   1       1       NULL
123456789   2   NULL    1       NULL
146782452   1   NULL    NULL    1

प्रति सदस्य आयडेंटिफायर आपके द्वारा उपयुक्त उपयुक्त स्थिति को ढूंढता है, जैसे 'In Progress' 'Complete' और 'Not Started' 'Complete' से अधिक जीतता है। 'Not Started' पर 'Complete' जीतता है इस के लिए सशर्त एकत्रीकरण का उपयोग करें

select status, count(*)
from
(
  select 
    case when sum(status = 'In Progress') > 0 then 'In Progress'
         when sum(status = 'Not Started') > 0 then 'Not Started'
         else 'Complete'
    end as status
  from mytable
  group by memberidentifier
) statuses
group by status;

मुझे लगता है कि आपके पास नीचे 2 टेबल हैं

CREATE TABLE table1 (RowId INT PRIMARY KEY, MemberIdentifier VARCHAR(255));
INSERT INTO table1 (RowId, MemberIdentifier)
VALUES
(1,'111111111'), (2, '123456789'), (3, '146782452'), (4, '111111111'),(5,'123456789'), (6,'146782452'), (7,'111111111');


CREATE TABLE table2 (RowId INT PRIMARY KEY, Status VARCHAR(255));
INSERT INTO table2 (RowId, Status)
VALUES
(1,'In Progress'), (2,'Complete'   ), (3,'Not Started'), (4,'Complete'   ), (5,'Complete'   ), (6,'Not Started'), (7,'Complete'   );

यह मानते हुए कि आपके पास इन तालिकाओं में लाखों रिकॉर्ड नहीं हैं, तो आप जो चाहें हासिल करने के लिए नीचे दिए गए क्वेरी का उपयोग कर सकते हैं।

SELECT CASE WHEN not_started.Status = 'Not Started' 
            THEN 'Not Started' 
            WHEN in_progress.Status = 'In Progress' 
            THEN 'In Progress' 
            WHEN complete.Status = 'Complete' 
            THEN 'Complete' 
       END AS over_all_status,
       COUNT(*) AS MemberIdentifierCount
  FROM  (SELECT DISTINCT t1.MemberIdentifier
          FROM table1 t1) main
        LEFT OUTER JOIN   
            (SELECT DISTINCT t1.MemberIdentifier, t2.Status
              FROM table1 t1,
                   table2 t2 
             WHERE t1.RowId = t2.RowId
               AND t2.Status = 'In Progress') in_progress
            ON (main.MemberIdentifier = in_progress.MemberIdentifier)
        LEFT OUTER JOIN
            (SELECT DISTINCT t1.MemberIdentifier, t2.Status
              FROM table1 t1,
                   table2 t2 
             WHERE t1.RowId = t2.RowId
               AND t2.Status = 'Not Started') not_started
        ON (main.MemberIdentifier = not_started.MemberIdentifier)
        LEFT OUTER JOIN
            (SELECT DISTINCT t1.MemberIdentifier, t2.Status
              FROM table1 t1,
                   table2 t2 
             WHERE t1.RowId = t2.RowId
               AND t2.Status = 'Complete') complete
        ON (main.MemberIdentifier = complete.MemberIdentifier)
GROUP BY over_all_status;

मूल रूप से क्वेरी में प्रति सदस्य एक पहचान बनाता है जिसमें सभी तीन स्थितियां संभावित हैं। यह तब समग्र स्थिति के आधार पर परिणाम को समूह करता है और गिनती को आउटपुट करता है

क्वेरी से आउटपुट है


सदस्य आयडेंटिफायर की स्थिति पाने के लिए निम्न कोड का उपयोग करें

select MemberIdentifier
,case 
when total = cn then 'Complete' 
when total < cn then 'In Progress' 
when total is null then 'Not Started' END as Fstatus
 from 
(
select sum(stat) total,MemberIdentifier,(select count(MemberIdentifier) as cnt from tbldata t1
     where t1.MemberIdentifier = C.MemberIdentifier
     group by MemberIdentifier) as cn
from (
select MemberIdentifier,case status when 'In Progress' then -1 
                                    when 'Complete' Then 1 
                                    when 'Not Started' then null End as Stat from tbldata 
 ) C
 group by MemberIdentifier

 ) as f1

विशेष स्थिति में सदस्य पहचानकर्ताओं की संख्या प्राप्त करने के लिए निम्न कोड का उपयोग करें

Select count(fstatus) counts,fstatus from (
select MemberIdentifier
,case when total = cn then 'Complete' 
      when total < cn then 'In Progress' 
      when total is null then 'Not Started' END as Fstatus
 from 
(
select sum(stat) total,MemberIdentifier,(select count(MemberIdentifier) as cnt from tbldata t1
     where t1.MemberIdentifier = C.MemberIdentifier
     group by MemberIdentifier) as cn
from (
select MemberIdentifier
,case status when 'In Progress' then -1 when 'Complete' Then 1 when 'Not Started' then null End as Stat from tbldata 
 ) C
 group by MemberIdentifier

 ) as f1

 ) f2 group by fstatus
आउटपुट:
counts  fstatus
1       Complete
1       In Progress
1       Not Started

यदि status लिए प्राथमिकता का क्रम है

 Not Started
 In Progress
 Complete

हम एक शॉर्टकट का उपयोग कर सकते हैं ...

   SELECT t.memberIdentifier
        , MAX(t.status) AS status
     FROM mytable t
    GROUP BY t.MemberIdentifier

यह हमें अलग memberIdentifier

यदि किसी ऐसे सदस्य के लिए कोई पंक्तियां हों जो 'In Progress' और 'Complete' स्थिति में पंक्तियां हैं, तो क्वेरी 'In Progress' स्थिति के रूप में लौटाएगी।

हम एक सदस्य के लिए स्थिति 'Complete' लौटाएंगे, यदि उस सदस्य की 'Complete' से अधिक की स्थिति के साथ कोई पंक्ति नहीं है

उस परिणाम से गिना जाने के लिए, हम उस क्वेरी को इनलाइन दृश्य के रूप में संदर्भ दे सकते हैं:

 SELECT q.status
      , COUNT(q.memberIdentifier) 
   FROM ( 
          SELECT t.memberIdentifier
               , MAX(t.status) AS status
            FROM mytable t
           GROUP BY t.MemberIdentifier
        ) q
  ORDER BY q.status

यदि इस तरह से सोचें ... MySQL पहले माता-पिता के बीच की क्वेरी को चलाता है (MySQL यह एक "व्युत्पन्न तालिका" कहता है।) क्वेरी से परिणाम पंक्तियों का एक सेट है जिसे तालिका की तरह पूछताछ की जा सकती है

हम एक COUNT(DISTINCT q.memberIdentifier) कर सकते हैं या, मानते हुए सदस्य ईडेंटिफ़ायर गैर-न्यूलल होने की गारंटी है, हम COUNT(1) या SUM(1) और एक समान परिणाम प्राप्त कर सकते हैं। (इनलाइन दृश्य में ग्रुप द्वारा हमें यह गारंटी देता है कि सदस्य पहचानकर्ता अद्वितीय होगा।)

अधिक सामान्य मामले में, जहां हमारे पास स्थिति के प्रावधान के लिए वर्णमाला क्रम का एक सुविधाजनक शॉर्टकट नहीं है ... हम एक ऐसी अभिव्यक्ति का उपयोग कर सकते हैं जो "क्रम" वाले मान लौटाते हैं। इससे क्वेरी को कुछ और जटिल बना देता है, लेकिन यह एक ही काम करेगा।

हम इस तरह से कुछ के साथ t.status जगह ले सकता है:

  CASE t.status
  WHEN 'Complete'    THEN 1
  WHEN 'In Progress' THEN 2
  WHEN 'Not Started' THEN 3
  ELSE 4
  END AS `status_priority`

और q.status वापस कन्वर्ट करने के लिए, व्युत्क्रम के साथ q.status जगह:

  CASE q.status_priority
  WHEN 1 THEN 'Complete'
  WHEN 2 THEN 'In Progress'
  WHEN 3 THEN 'Not Started'
  ELSE NULL
  END AS `status`

हमें यह तय करने की आवश्यकता होगी कि हम उन स्थिति के मूल्यों को कैसे संभाल लेंगे जो तीनों में से एक नहीं हैं ... जो उन लोगों को नजरअंदाज करने वाले हैं, दूसरों की तुलना में उच्च या निचले प्राथमिकता के रूप में संभाला जा रहे हैं। (एक परीक्षण का मामला status = 'Unknown' साथ पंक्तियाँ होगा status = 'Unknown' और status = 'Abracadabra साथ पंक्तियों status = 'Abracadabra


SELECT max_status AS Status
     , COUNT(*) AS ct
    FROM (
        SELECT MAX(Status) AS max_status
            FROM tbl
            GROUP BY MemberIdentifier
         ) AS a
    GROUP BY max_status;

यह कैसे इन तारों की तुलना का लाभ लेता है: "प्रगति में"> "पूर्ण" ऐसा करने में, यह कई स्थितियों के साथ किसी अन्य सदस्य को यादृच्छिक चीजें करता है।


एसक्यूएल

SELECT AdjustedStatus AS Status,
       COUNT(*) AS MemberIdentifierCount
FROM
(SELECT IF(Status='Complete',
           IF(EXISTS(SELECT Status
                     FROM tbl t2
                     WHERE t2.Status = 'In Progress'
                       AND t2.MemberIdentifier = t1.MemberIdentifier),
              'In Progress',
              'Complete'),
           Status) AS AdjustedStatus,
        MemberIdentifier
 FROM tbl t1
 GROUP BY AdjustedStatus, MemberIdentifier) subq
GROUP BY AdjustedStatus;

ऑनलाइन डेमो

http://rextester.com/FFGM6300

व्याख्या

पहले IF() फ़ंक्शन की जांच करता है कि क्या स्थिति "पूर्ण" है और यदि हां, तो उसी MemberIdentifier साथ एक और रिकॉर्ड के अस्तित्व की जांच करता है, लेकिन "प्रगति में" की स्थिति के साथ: यह IF(EXISTS(SELECT...))) माध्यम से किया जाता है IF(EXISTS(SELECT...))) । यदि मिले, तो "प्रगति में" स्थिति को AdjustedStatus फ़ील्ड में निर्दिष्ट किया गया है, अन्यथा AdjustedStatus को (बिना AdjustedStatus ) Status मान से सेट किया गया है।

इन दो फ़ील्ड वैल्यू के सभी अनूठे संयोजनों को प्राप्त करने के लिए समायोजित स्थिति के साथ तालिका में प्रत्येक पंक्तियों के लिए इस तरह से प्राप्त किया गया है, ताकि समायोजित स्थिति और MemberIdentifier GROUP BY सके। यह तब एक subquery में बनाया गया है - subq रूप में aliased फिर समेकित ( GROUP BY ) AdjustedStatus और घटनाओं की संख्या को गिनें, अर्थात् प्रत्येक के लिए अद्वितीय MemberIdentifier की संख्या।


यदि आप पायथन 2.7 या 3 का उपयोग कर रहे हैं और आप प्रत्येक तत्व के लिए घटनाओं की संख्या चाहते हैं:

>>> from collections import Counter
>>> z = ['blue', 'red', 'blue', 'yellow', 'blue', 'red']
>>> Counter(z)
Counter({'blue': 3, 'red': 2, 'yellow': 1})




mysql sql group-by count distinct