总计总数 [英] Aggregate Total count

问题描述

我想合并两列(发送方"和接收方")并获取交易类型"计数.

I want to merge two columns(Sender and Receiver) and get the Transaction Type count.

Sender      Receiver  Type  Amount  Date
773787639   777611388   1   300    2/1/2019
773631898   776806843   4   450    8/20/2019
773761571   777019819   6   369    2/11/2019
774295511   777084440   34  1000   1/22/2019
774263079   776816905   45  678    6/27/2019
774386894   777202863   12  2678   2/10/2019
773671537   777545555   14  38934  9/29/2019
774288117   777035194   18  21     4/22/2019
774242382   777132939   21  1275    9/30/2019
774144715   777049859   30  6309    7/4/2019
773911674   776938987   10  3528    5/1/2019
773397863   777548054   15  35892   7/6/2019
776816905   772345091   6   1234    7/7/2019
777035194   775623065   4   453454  7/20/2019

我试图得到像这样的桌子

I am try to get like this kind of table

Sender/Receiver    Type_1    Type_4    Type_12...... Type_45
773787639            3          2         0            0
773631898            1          0         1            2
773397863            2          2         0            0 
772345091            1          1         0            3

推荐答案

您正在寻找数据透视查询.唯一的问题是，我们首先需要对表进行合并，以将发送方/接收方数据合并到单个列中.

You are looking for a pivot query. The only twist here is that we need to first take a union of the table to combine the sender/receiver data into a single column.

SELECT
    SenderReceiver,
    COUNT(CASE WHEN Type = 1 THEN 1 END) AS Type_1,
    COUNT(CASE WHEN Type = 2 THEN 1 END) AS Type_2,
    COUNT(CASE WHEN Type = 3 THEN 1 END) AS Type_3,
    ...
    COUNT(CASE WHEN Type = 45 THEN 1 END) AS Type_45
FROM
(
    SELECT Sender AS SenderReceiver, Type FROM yourTable
    UNION ALL
    SELECT Receiver, Type FROM yourTable
) t
GROUP BY
    SenderReceiver;

如果您不想键入45个单独的CASE表达式，则可以使用Python在某种程度上将其自动化.

If you don't want to type out 45 separate CASE expressions, you could probably automate it to some degree using Python.

这篇关于总计总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！