mysql تنزيل - كيفية حساب القيم المميزة التي تلبي جميع الشروط في ميسكل؟




تطبيق فتاة (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). أي تقدير سيكون موضع تقدير (ميسكل مبتدئ).


Answers

بير ميمبريندنتيفيه العثور على الحالة التي تراها مناسبة، على سبيل المثال 'In Progress' يفوز على 'Complete' و 'Not Started' . '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;

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;

هذا يستفيد من كيفية مقارنة هذه السلاسل: "في التقدم"> "كاملة". في القيام بذلك، فإنه يفعل أشياء عشوائية إلى أي عضو آخر مع حالات متعددة.


أفترض أن لديك 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;

يقوم الاستعلام أساسا بإنشاء سجل واحد لكل معرف عضو يحتوي على جميع الحالات الثلاث الممكنة. ثم يقوم بجمع النتيجة بناء على الوضع العام والمخرجات العد.

الناتج من الاستعلام هو


طريقة أخرى باستخدام جدول معين لتكوين النظام (خريطة إلى قوة اثنين صحيح).

يسمح هذا التعيين 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

SQL

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...))) . إذا وجدت، يتم تعيين حالة "قيد التقدم" إلى الحقل AdjustedStatus وإلا يتم ضبط أدجوستيدستاتوس من قيمة Status (غير المعدلة).

مع اشتقاق الحالة المعدلة مثل هذا لكل صف من الصفوف في الجدول، GROUP BY MemberIdentifier و MemberIdentifier أجل الحصول على كل تركيبات فريدة من قيم MemberIdentifier . ثم يتم ذلك إلى استعلام فرعي - المستعار باسم subq . ثم التجميع فوق ( GROUP BY ) AdjustedStatus و عدد عدد مرات حدوث، أي عدد MemberIdentifier فريد من نوعه s لكل منهما.


استخدم التعليمات البرمجية التالية للحصول على حالة ميمبريدنتيفيه

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

فكر إذا كان بهذه الطريقة ... ميسكل يدير الاستعلام بين بارينز أولا (ميسكل يدعو هذا "جدول مشتقة" .النتائج من الاستعلام هو مجموعة من الصفوف التي يمكن الاستعلام عنها مثل جدول.

يمكن أن نفعل 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 مع شيء معكوس، لتحويل مرة أخرى إلى سلاسل:

  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 = 'Abracadabra .


إذا كنت ترغب في حساب جميع القيم في وقت واحد يمكنك القيام بذلك بسرعة كبيرة باستخدام المصفوفات bincount و bincount النحو التالي

import numpy as np
a = np.array([1, 2, 3, 4, 1, 4, 1])
np.bincount(a)

الذي يعطي

>>> array([0, 3, 1, 1, 2])




mysql sql group-by count distinct