26800


１対多の関係にある２つのテーブルを結合して、その集計結果を表示する方法はどうすればいいのでしょうか？

clientsとinvestmentsのテーブルがあり、それぞれに以下のデータを挿入しているとします。

[code]

    create table clients (
      client_id int not null auto_increment primary key,
      email varchar(255)
    );
    
    create table investments (
      investment_id int not null auto_increment primary key,
      amount numeric(8,2),
      client_id int,
      foreign key (client_id) references clients(client_id)
    );
    
    mysql> select * from clients;
    +-----------+-------------+
    | client_id | email       |
    +-----------+-------------+
    |         1 | 111@aaa.com |
    |         2 | 222@aaa.com |
    |         3 | 333@aaa.com |
    |         4 | 444@aaa.com |
    |         5 | 555@aaa.com |
    +-----------+-------------+
    5 rows in set (0.00 sec)
    
    mysql> select * from investments;
    +---------------+--------+-----------+
    | investment_id | amount | client_id |
    +---------------+--------+-----------+
    |             1 | 101.00 |         1 |
    |             2 | 102.00 |         1 |
    |             3 | 103.00 |         1 |
    |             4 | 201.00 |         2 |
    |             5 | 202.00 |         2 |
    |             6 | 301.00 |         3 |
    +---------------+--------+-----------+
    
[/code]

これらのテーブルを結合して、client毎にメールアドレスとinvestmentsの数、investmentsの合計金額を出したいと考えています。

[code]

    mysql> select client_id, count(*), sum(amount) from investments group by client_id;
    +-----------+----------+-------------+
    | client_id | count(*) | sum(amount) |
    +-----------+----------+-------------+
    |         1 |        3 |      306.00 |
    |         2 |        2 |      403.00 |
    |         3 |        1 |      301.00 |
    +-----------+----------+-------------+
    
[/code]

この結果にメールアドレスを足すイメージです。

[code]

    +-----------+----------+-------------+-------------+
    | client_id | email    |    count(*) | sum(amount) |
    +-----------+----------+-------------+-------------+
    
[/code]

どうやってもうまくemailが入りません。どうすればいいのでしょうか？


CC BY-SA 3.0
2016-05-30T08:34:26.123
25296
2016-06-16T16:22:59.993
14900
question
mysql sql postgresql
１対多のテーブルで集計結果を表示する方法は？


以下はサブクエリを使う場合です。

[code]

    select client_id, 
           ( select email from clients where
               client_id = investments.client_id ) as email,
           count(*), sum(amount) from investments group by client_id;
    
    +-----------+-------------+----------+-------------+
    | client_id | email       | count(*) | sum(amount) |
    +-----------+-------------+----------+-------------+
    |         1 | 111@aaa.com |        3 |      306.00 |
    |         2 | 222@aaa.com |        2 |      403.00 |
    |         3 | 333@aaa.com |        1 |      301.00 |
    +-----------+-------------+----------+-------------+
    
[/code]


CC BY-SA 3.0
2016-05-30T08:55:10.660
25297
2016-05-30T08:55:10.660
25296
answer


結合してから潰すやり方です。

[code]

    mysql> select client_id, email, count(*),sum(amount) from clients join investments using (client_id) group by client_id;
    +-----------+-------------+----------+-------------+
    | client_id | email       | count(*) | sum(amount) |
    +-----------+-------------+----------+-------------+
    |         1 | 111@aaa.com |        3 |      306.00 |
    |         2 | 222@aaa.com |        2 |      403.00 |
    |         3 | 333@aaa.com |        1 |      301.00 |
    +-----------+-------------+----------+-------------+
    3 rows in set (0.00 sec)
    
[/code]


CC BY-SA 3.0
2016-05-30T10:00:33.993
25302
2016-05-30T10:00:33.993
13199
25296
answer


window関数を使ったやり方です。  
<http://lets.postgresql.jp/documents/technical/window_functions>

実行結果  
<http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/9304>

[code]

    WITH clients AS (
    SELECT * FROM (
        VALUES
            (1, '111@aaa.com'),
            (2, '222@aaa.com'),
            (3, '333@aaa.com'),
            (4, '444@aaa.com'),
            (5, '555@aaa.com')
    ) AS clients(client_id, email)
    ),
    investments AS (
    SELECT * FROM (
        VALUES
            (1, 101.00, 1),
            (2, 102.00, 1),
            (3, 103.00, 1),
            (4, 201.00, 2),
            (5, 202.00, 2),
            (6, 301.00, 3)
    ) AS investments(investment_id, amount, client_id)
    )
    SELECT
        T.client_id,
        count(*) AS count,
        -- 集約される行の値はすべて同じなので、max/minなどの集約関数でよい
        max(T.sum_of_amount) AS sum_of_amount,
        max(T.email) AS email
    FROM (
        SELECT
            T.client_id,
            sum(T.amount) over (partition by T.client_id) AS sum_of_amount,
            S.email
        FROM
            investments T
        LEFT JOIN
            clients S USING(client_id)
    ) T
    GROUP BY
        T.client_id
    ;
    
[/code]


CC BY-SA 3.0
2016-06-16T16:22:59.993
26800
2016-06-16T16:22:59.993
9796
25296
answer