我有一个交易表,并且想要为每个产品计算包括该产品在内的交易的总交易价值.
+---------------+-----------+----------+-----------+ | TransactionId | ProductId | Quantity | UnitPrice | +---------------+-----------+----------+-----------+ | 1 | A | 2 | 3.00 | | 1 | B | 1 | 6.00 | | 2 | B | 1 | 6.00 | | 3 | A | 6 | 3.00 | | 3 | C | 2 | 8.00 | +---------------+-----------+----------+-----------+
例如,产品A有两个交易:交易1和3.交易1的总收入是产品A的6.00和产品B的6.00,总共12.00.交易3的总收入为产品A的18.00,产品C的16.00,总计34.00.因此,包括产品A在内的交易的总收入为12.00 + 34.00 = 46.00.
我想选择以下内容(如上表所示):
+-----------+----------------+ | ProductId | TotalBasketRev | +-----------+----------------+ | A | 46.00 | | B | 18.00 | | C | 34.00 | +-----------+----------------+
我还需要能够为Redshift执行此操作.
感谢您的任何见解.
首先,您需要计算每笔交易的总额.
然后加入回原始表格并分组每个产品,以总计每个参与交易的总和.
SQL小提琴演示
SELECT T."ProductId", SUM(TTotal.total) FROM Transactions T JOIN ( SELECT "TransactionId", SUM ("Quantity" * "UnitPrice") total FROM Transactions GROUP BY "TransactionId" ) Ttotal ON T."TransactionId" = TTotal."TransactionId" GROUP BY T."ProductId" ORDER BY T."ProductId"
OUTPUT
| ProductId | sum | |-----------|-----| | A | 46 | | B | 18 | | C | 34 |