mysql - 如何在 MySql 中使用 Group By 实现以下结果？

Question

考虑下表:

    +------------+-----------+-------------+---------+------------+--------+-------------+
    | client_id  | TradeDate | servicetype | SEGMENT | OrdChannel | orders | OrderAmount |
    +------------+-----------+-------------+---------+------------+--------+-------------+
    |      1     |  20140611 | Type_1      | CASH    | TT         |      1 |       39275 |
    |      2     |  20150119 | Type_1      | CASH    | DNT        |      2 |     11856.9 |
    |      3     |  20150922 | Type_1      | FNO     | OTHER      |      1 |      854750 |
    |      4     |  20151223 | Type_1      | CASH    | TT         |      5 |       71075 |
    |      5     |  20140529 | Type_1      | Offline | FNO        |      1 |           0 |
    |      6     |  20160310 | Type_2      | CASH    | WEB        |      2 |      8009.6 |
    |      7     |  20150318 | Type_1      | Offline | FNO        |      2 |      432900 |
    |      8     |  20150914 | Type_2      | CASH    | WEB        |      2 |       15612 |
    |      9     |  20160317 | Type_2      | FNO     | MINI       |      1 |        9000 |
    |      10    |  20140421 | Type_1      | CASH    | TT         |      8 |     17112.5 |
    +------------+-----------+-------------+---------+------------+--------+-------------+

我正在尝试按 client_id 对这些数据进行分组和 TradeDate .因此最终数据集将包含每个 <client-id, TradeDate> 的每一行一对。我想根据这些数据计算以下特征:

1. 对于每对中的每种类型的 SEGMENT，我想计算订单总和和 OrderAmount

2. 同样，对于每对中的每种类型的 OrdChannel，我想计算订单总和和 OrderAmount

3. 最后，对每个 servicetype 进行计数即 Type_1和 Type_2 .

因此最终数据集将包含类似于以下所示的列:

    +------------+-----------+-------------+---------+------------+--------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
    | client_id  | TradeDate | SEGMENT_CASH_orders_sum | SEGMENT_CASH_OrderAmount_sum.... | OrdChannel_TT_orders_sum | OrdChannel_TT_OrderAmount_sum.... | servicetype_Type_1_count | servicetype_Type_2_count |
    +------------+-----------+-------------+---------+------------+--------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+

到目前为止，我已经尝试过:

select clientsrno, TradeDate, SEGMENT, COUNT(orders) from orders group by clientsrno, TradeDate, SEGMENT;

但它没有为 SEGMENT_CASH-orders_sum 提供单独的列等

相反，我得到的输出是:

    +------------+-----------+---------+---------------+
    | clientsrno | TradeDate | SEGMENT | COUNT(orders) |
    +------------+-----------+---------+---------------+
    |         44 |  20141209 | CASH    |            23 |
    |         44 |  20141211 | FNO     |            10 |
    +------------+-----------+---------+---------------+

Answer 1

你可以做很多事情(CASE .... END)

这是对第一列的建议

 select client_di, TradeDate
   sum (case SEGMENT 
        when 'CASH' then orders ELSE 0 END) as SEGMENT_CASH_orders_sum, 
   sum (case SEGMENT 
        when 'CASH' then OrderAmount ELSE 0 END) as SEGMENT_CASH_ordersAmount_sum, 
   sum (case SEGMENT 
        when 'FNO' then orders ELSE 0 END) as SEGMENT_FNO_orders, 
   sum (case SEGMENT 
        when 'FNO' then OrderAmount ELSE 0 END) as SEGMENT_FNO_ordersAmount_sum
 from my_table 
 group by  client_id, TradeDate