pandas 计算每股买入/卖出之间的平均持续时间

nom7f22z 于 2023-05-05 发布在其他

关注(0)|答案(1)|浏览(88)

我们有一张table共享。它包含特定股票的买入和卖出订单。所有买入的股票将在同一天卖出，反之亦然。在某些情况下，单独购买的股票可以一起出售，反之亦然。例如，在2023-03-31上，数量为1的GOOGLE股票以10:03:34和10:03:42的价格出售，数量为2的股票以11:18:11的价格购买。相反的情况也有可能发生，当股票被大量购买并在分销中出售时。
time_in_seconds通过将hh：mm：ss转换为秒从time列派生。
保证记录按order_execution_time排序。
我们需要计算每笔股票买入/卖出之间的平均持续时间。例如，在2023-03-31上，以10:03:34卖出的1股GOOGLE股票以11:18:11买回，以10:03:42卖出的另一股GOOGLE股票以11:18:11买回（注：2股一起买）
总时间差：（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); -- first order of selling share of APPLE. One trade active now.
INSERT INTO shares VALUES ('APPLE','buy',1, 536.7, '2023-02-17T10:40:05','10:40:05', 38405); -- sold share previously for APPLE bought back, No shares left to trade.
INSERT INTO shares VALUES ('GOOGLE','buy',1, 862.5, '2023-02-17T10:59:45','10:59:45', 39585); -- GOOGLE 1 share bought
INSERT INTO shares VALUES ('GOOGLE','sell',1, 861.05, '2023-02-17T12:55:22','12:55:22', 46522); -- GOOGLE 1 share sold. No shares left to trade.
INSERT INTO shares VALUES ('GOOGLE','sell',1, 876.05, '2023-03-31T10:03:34','10:03:34', 36214); -- GOOGLE 1 share sold
INSERT INTO shares VALUES ('GOOGLE','sell',1, 876.25, '2023-03-31T10:03:42','10:03:42', 36222); -- GOOGLE 1 shares sold (Now total 2 shares sold GOOGLE)
INSERT INTO shares VALUES ('GOOGLE','buy',2, 876.15, '2023-03-31T11:18:11','11:18:11', 40691); -- GOOGLE 2 shares bought together at same time. No shares left to trade.

pandas

来源：https://stackoverflow.com/questions/76083851/calculate-the-average-time-duration-between-buying-selling-of-each-share

1条答案

按热度按时间

ccrfmcuu1#

这个表格定义解决了原始帖子中存在的一些设计问题：

CREATE TABLE shares(
  symbol text NOT NULL,
  trade_type text NOT NULL,
  quantity integer NOT NULL,
  price integer NOT NULL,
  order_execution_time timestamptz NOT NULL
);

下面插入与原始帖子相同的信息：

INSERT INTO shares(symbol, trade_type, quantity, price, order_execution_time)
  VALUES ('APPLE', 'sell', 1, 539.2, '2023-02-17T09:38:59'),
         ('APPLE', 'buy', 1, 536.7, '2023-02-17T10:40:05'),
         ('GOOGLE', 'buy', 1, 862.5, '2023-02-17T10:59:45'),
         ('GOOGLE', 'sell', 1, 861.05, '2023-02-17T12:55:22'),
         ('GOOGLE', 'sell', 1, 876.05, '2023-03-31T10:03:34'),
         ('GOOGLE', 'sell', 1, 876.25, '2023-03-31T10:03:42'),
         ('GOOGLE', 'buy', 2, 876.15, '2023-03-31T11:18:11');

此查询返回每个交易日期的补充交易之间的平均时间（以秒为单位）：

SELECT symbol,
       DATE_TRUNC('day', order_execution_time) AS trade_date,
       ABS(  SUM(EXTRACT(EPOCH FROM order_execution_time) * quantity)
                FILTER (WHERE trade_type = 'buy')
           - SUM(EXTRACT(EPOCH FROM order_execution_time) * quantity)
                FILTER (WHERE trade_type = 'sell')) / (SUM(quantity) / 2) AS mean_time_seconds,
       SUM(quantity) FILTER (WHERE trade_type = 'buy') = SUM(quantity) FILTER (WHERE trade_type = 'sell') IS TRUE AS is_balanced
  FROM shares
 GROUP BY symbol,
          trade_date
 ORDER BY symbol,
          trade_date;

此查询取决于每天是否有平衡的买入和卖出数量。检查列is_balanced表示满足此要求的每一天的TRUE。
当使用示例数据运行时，查询将生成：
| 符号|交易日期|平均时间秒|平衡|
| --------------|--------------|--------------|--------------|
| 苹果|2019 -02-17 00：00：00|3666.00000000000000000|TRUE|
| 谷歌|2019 -02-17 00：00：00|6937.00000000000000000|TRUE|
| 谷歌|2023-03-31 00：00：00|4473.00000000000000000|TRUE|
以下查询通过忽略不匹配的数量来处理不平衡的每日交易：

WITH daily_trades AS (
SELECT symbol,
       DATE_TRUNC('day', order_execution_time) AS trade_date,
       order_execution_time,
       trade_type,
       quantity
  FROM shares),
daily_quantities AS (
SELECT symbol,
       trade_type,
       trade_date,
       order_execution_time,
       quantity,
       COALESCE(LEAST(SUM(quantity) FILTER (WHERE trade_type = 'buy')
                                    OVER (PARTITION BY symbol, trade_date),
                      SUM(quantity) FILTER (WHERE trade_type = 'sell')
                                    OVER (PARTITION BY symbol, trade_date)), 0) AS daily_quantity_limit,
       COALESCE(SUM(quantity) FILTER (WHERE trade_type = 'buy')
                              OVER (PARTITION BY symbol, trade_date
                                    ORDER BY order_execution_time
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS purchased_quantity,
       COALESCE(SUM(quantity) FILTER (WHERE trade_type = 'sell')
                              OVER (PARTITION BY symbol, trade_date
                                    ORDER BY order_execution_time
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS sold_quantity
  FROM daily_trades)
SELECT symbol,
       trade_date,
       ABS(  SUM(EXTRACT(EPOCH FROM order_execution_time) * LEAST(quantity, daily_quantity_limit - purchased_quantity))
                FILTER (WHERE trade_type = 'buy')
           - SUM(EXTRACT(EPOCH FROM order_execution_time) * LEAST(quantity, daily_quantity_limit - sold_quantity))
                FILTER (WHERE trade_type = 'sell'))
       / MIN(daily_quantity_limit) AS mean_time_seconds,
       SUM(quantity) FILTER (WHERE trade_type = 'buy') = SUM(quantity) FILTER (WHERE trade_type = 'sell') IS TRUE AS is_balanced
  FROM daily_quantities
 GROUP BY symbol,
          trade_date
 ORDER BY symbol,
          trade_date;

赞(0）回复(0）举报 2023-05-05

我来回答

pandas 计算每股买入/卖出之间的平均持续时间

1条答案

相关问题

热门标签

最新问答