mysql - 結果 - sql count 複数テーブル




どのようにMySQLの条件を満たす個別の値を数える? (6)

特定のフィールドに別の値を見つけるためのクエリを作成しようとしていて、その特定の値のすべてのインスタンスに対して別の列の値が満たされているその値の出現回数を数え、次のように結果を表示します続く):

例db:

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

上記のクエリでは、特定のステータスを持つ別個のMemberIdentifierの数がカウントされ、表示されます。 MemberIdentifierにステータス 'Complete'があり、ステータスが 'In Progress'である2つの行がある場合、グループ化され、進行状況(MemberIdentifier = 111111111)としてカウントされます。 MemberIdentifierをグループ化して完全とカウントするには、その行のすべてのステータスが「Complete」(つまり、MemberIdentifier = 123456789)である必要があります。 どんな洞察も認められるだろう(MySQL初心者)。


MemberIdentifierごとに適切と思われるステータスを見つけます。たとえば、 '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;

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()関数は、ステータスが「Complete」であるかどうかをチェックし、同じMemberIdentifierは同じMemberIdentifier持つ「In Progress」のステータスを持つ別のレコードの存在をチェックします:これはIF(EXISTS(SELECT...))) 。 見つかった場合は、「進行中」のステータスがAdjustedStatusフィールドに割り当てられます。そうでない場合、 AdjustedStatusは(調整されていない) Status値から設定されます。

これらの2つのフィールド値のすべての固有の組み合わせを取得するために、 AdjustedStatusおよびMemberIdentifier GROUP BYの表の行ごとに調整したステータスが得られました。 これはサブクエリとしてエイリアスされsubq 。 その後、 AdjustedStatusを集計( GROUP BY )して、発生数、つまりそれぞれの一意のMemberIdentifierの数をMemberIdentifierます。


私はあなたの下に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;

基本的に、クエリは、可能な3つのステータスすべてを含むMemberIdentifierごとに1つのレコードを作成します。 その後、全体のステータスに基づいて結果をグループ化し、カウントを出力します。

クエリからの出力は


次のコードを使用してMemberIdentifierのステータスを取得します

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

次のコードを使用して、特定のステータスのMemberIdentifierの数を取得します。

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

特定の表を使用して順序を構成するもう1つの方法(2整数のべき乗にマップ)。

このマッピングにより、 bit_or aggregateは単にデータを転置することができます。

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

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;

これは、これらの文字列がどのように比較されるかを利用します。「進行中」>「完了」 そうすることで、複数のステータスを持つ他のメンバーにランダムなことが起こります。





distinct