如何使用SQL查询股票交易表中各股票的总盈亏

需求背景 #

现有一张存储股票投资交易记录的SQL表，字段如下：

需要编写SQL语句，统计每只股票的总盈亏。

已知前提 #

• 每个STOCK_ID恰好对应1笔买入交易和1笔卖出交易
• 盈亏计算逻辑：对每个股票名称，汇总所有对应STOCK_ID的「卖出金额 - 买入金额」的总和

举个例子：

4|ITC|7292|6865|buy
6|ITC|7292|7313|sell

ITC的盈亏 = 7313 - 6865 = 448

样例数据 #

1|REL|2624|6653|sell
2|INFY|7660|3064|buy
3|INFY|4361|3117|buy
4|ITC|7292|6865|buy
5|INFY|2187|2618|buy
6|ITC|7292|7313|sell
7|INFY|3478|3196|sell
8|INFY|3472|3105|sell
9|REL|4472|7398|sell
10|INFY|7660|3351|sell
11|REL|4472|7350|buy
12|REL|5833|8240|buy
13|REL|5833|7937|sell
14|INFY|2187|2487|sell
15|REL|7198|7998|sell
16|INFY|3478|2944|buy
17|REL|2624|6985|buy
18|REL|7198|7645|buy
19|INFY|3472|3046|buy
20|INFY|4361|3384|sell

预期输出 #

ITC|448
INFY| 734
REL| -234

测试数据构造SQL #

BEGIN TRANSACTION;
CREATE TABLE stocks(id integer PRIMARY KEY, symbol text, stock_id integer, value integer, typ text);
INSERT INTO stocks VALUES(1,'REL', 2624, 6653, 'sell');
INSERT INTO stocks VALUES(2,'INFY', 7660, 3064, 'buy');
INSERT INTO stocks VALUES(3,'INFY', 4361, 3117, 'buy');
INSERT INTO stocks VALUES(4,'ITC', 7292, 6865, 'buy');
INSERT INTO stocks VALUES(5,'INFY', 2187, 2618, 'buy');
INSERT INTO stocks VALUES(6, 'ITC', 7292, 7313, 'sell');
INSERT INTO stocks VALUES(7, 'INFY', 3478, 3196, 'sell');
INSERT INTO stocks VALUES(8, 'INFY', 3472, 3105, 'sell');
INSERT INTO stocks VALUES(9, 'REL', 4472, 7398, 'sell');
INSERT INTO stocks VALUES(10, 'INFY', 7660, 3351, 'sell');
INSERT INTO stocks VALUES(11, 'REL', 4472, 7350, 'buy');
INSERT INTO stocks VALUES(12, 'REL', 5833, 8240, 'buy');
INSERT INTO stocks VALUES(13, 'REL', 5833, 7937, 'sell');
INSERT INTO stocks VALUES(14, 'INFY', 2187, 2487, 'sell');
INSERT INTO stocks VALUES(15, 'REL', 7198, 7998, 'sell');
INSERT INTO stocks VALUES(16, 'INFY', 3478, 2944, 'buy');
INSERT INTO stocks VALUES(17, 'REL', 2624, 6985, 'buy');
INSERT INTO stocks VALUES(18, 'REL', 7198, 7645, 'buy');
INSERT INTO stocks VALUES(19, 'INFY', 3472, 3046, 'buy');
INSERT INTO stocks VALUES(20, 'INFY', 4361, 3384, 'sell');
COMMIT;

实现SQL #

使用条件聚合即可实现需求，语句如下：

SELECT 
  symbol AS stock_name,
  SUM(CASE WHEN typ = 'sell' THEN value ELSE -value END) AS profit_loss
FROM stocks
GROUP BY symbol
ORDER BY stock_name;

如果需要和预期输出完全一致的竖线分隔格式，可以调整输出拼接逻辑：

SELECT 
  symbol || '|' || SUM(CASE WHEN typ = 'sell' THEN value ELSE -value END) AS result
FROM stocks
GROUP BY symbol
ORDER BY symbol;

内容的提问来源于stack exchange，提问作者Joshy Joy