计算每只股票买卖操作间的平均时长

我们有一张shares表，用于存储特定股票的买卖订单，相关规则与计算需求如下：

• 所有买入的股票会在当日卖出，反之亦然
• 存在单独买入的股票批量卖出、或批量买入后分笔卖出的情况（例如2023-03-31，GOOGLE股票分别于10:03:34和10:03:42各卖出1股，随后于11:18:11批量买入2股）
• time_in_seconds字段由time列的hh:mm:ss格式转换为秒数得到
• 记录已按order_execution_time排序
• 需要计算每只股票买卖操作间的平均时长

以GOOGLE在2023-03-31的交易为例：

• 10:03:34卖出的1股与10:03:42卖出的1股，均在11:18:11被批量买回
• 总时间差：(40691 - 36214) + (40691 - 36222) = 8946秒
• 总交易数：2笔
• 平均时长：8946 / 2 = 4473秒

表结构 #

CREATE TABLE shares (
  symbol TEXT NOT NULL,
  trade_type TEXT NOT NULL,
  quantity integer,
  price integer,
  order_execution_time TEXT,
  time TEXT,
  time_in_seconds integer
);

示例数据 #

INSERT INTO shares VALUES ('APPLE','sell',1, 539.2, '2023-02-17T09:38:59', '09:38:59', 34739); -- 第一笔APPLE股票卖出订单，当前存在1笔未平仓交易
INSERT INTO shares VALUES ('APPLE','buy',1, 536.7, '2023-02-17T10:40:05','10:40:05', 38405); -- 买回此前卖出的APPLE股票，无未平仓交易
INSERT INTO shares VALUES ('GOOGLE','buy',1, 862.5, '2023-02-17T10:59:45','10:59:45', 39585); -- 买入1股GOOGLE股票
INSERT INTO shares VALUES ('GOOGLE','sell',1, 861.05, '2023-02-17T12:55:22','12:55:22', 46522); -- 卖出1股GOOGLE股票，无未平仓交易
INSERT INTO shares VALUES ('GOOGLE','sell',1, 876.05, '2023-03-31T10:03:34','10:03:34', 36214); -- 卖出1股GOOGLE股票
INSERT INTO shares VALUES ('GOOGLE','sell',1, 876.25, '2023-03-31T10:03:42','10:03:42', 36222); -- 卖出1股GOOGLE股票（当前GOOGLE累计卖出2股）
INSERT INTO shares VALUES ('GOOGLE','buy',2, 876.15, '2023-03-31T11:18:11','11:18:11', 40691); -- 批量买入2股GOOGLE股票，无未平仓交易

内容的提问来源于stack exchange，提问作者Govind Yadav