我得到了一些只有秒分辨率的财务刻度数据。数据本身是按时间顺序排列的。
Date Time Bid Ask
06/07/2015 19:09:29 0.7623 0.76262
06/07/2015 19:09:29 0.7623 0.76271
06/07/2015 19:09:29 0.7623 0.76276
我想做一些分析,要求我能够知道次二阶。所以我最初的想法是"伪造"毫秒。当在同一秒内有多个tick(数据点)时,我只假设它们在该秒内均匀分布。因此,由于在同一秒内有3个刻度,我假设第一个发生在第二个刻度的开始,第二个在.333,第三个在.666。
两个问题
这种方法(伪造毫秒)是存储实际订单?
我有相当多的数据要导入,所以我想我会在两个阶段进行操作,上传原始数据,然后用一个进行毫秒计算的select插入到我的目标表中。因此,如果您对以下查询/方法有任何帮助,我们将不胜感激。
CREATE TEMPORARY TABLE dataload (
id serial
, dt date
, tm time
, bid numeric(10,5)
, ask numeric(10,5)
);
COPY dataload (dt, tm, bid, ask) FROM '/path/to/data.csv' WITH CSV HEADER;
-- INSERT INTO actual_table
SELECT
dt
, tm
, (dt||tm)::timestamp -- Need to hack the milliseconds here
from dataload
group by dt, tm;
我通常不喜欢伪造数据。更准确的做法是准确地存储所给的内容,并使用第二列来指示顺序。它将花费一些额外的存储空间,但没有混乱可能会弥补这一点。
我也不会把时间和日期分开存储,除非你有充分的理由。
最好使用file_fdw而不是中间表,以避免额外的复制步骤。问题是file_fdw没有为您提供行号或任何其他排序方式,从而允许您使用row_number()创建次秒排序。所以我们需要使用一个函数。
CREATE TABLE tick(
tick_timestamp timestamptz NOT NULL
, tick_sequence smallint NOT NULL
, CONSTRAINT tick__pk_tick_timestamp__tick_sequence PRIMARY KEY( tick_timestamp, tick_sequence )
, bid numeric(10,5) NOT NULL
, ask numeric(10,5) NOT NULL
);
CREATE FOREIGN TABLE tick_csv( tick_date date, tick_time time, bid numeric, ask numeric ) ...;
CREATE FUNCTION get_tick() RETURNS SETOF tick_csv LANGUAGE sql AS $$SELECT * FROM tick_csv$$;
COMMENT ON FUNCTION get_tick() IS $$This function is necessary because file_fdw provides no means of obtaining row numbers, which we need for safe ordering. This function allows use of WITH ORDINALITY to get a row number.$$;
INSERT INTO tick( tick_timestamp, tick_sequence, bid, ask )
SELECT tick_timestamp
-- Generate a series that resets for each second
, row_number() OVER( PARTITION BY tick_timestamp ORDER BY ordinality )
, bid
, ask
FROM (
SELECT *, ( tick_date + tick_time ) AT TIME ZONE 'America/New_York' AS tick_timestamp -- CHANGE TO CORRECT TIMEZONE
FROM get_ticks() WITH ordinality
) raw
;
您可能会想省略函数位,并使用类似临时序列的东西来生成行号。问题是,它不能防止规划者创建一个重新排序外部表输出的计划。这种情况不应该发生在只进行顺序扫描的简单情况下,但如果您决定加入其他表,那么您可能很容易得到无序的行。这个SQL函数之所以安全,是因为它不会被内联,因为我们使用的是WITH ORDINALITY(而且它没有被定义为STABLE,你可能可以这样做)。
如果您无法获得改进的数据,最好以符合其背后现实的方式对其进行建模。
因此,添加假秒可能不是一个好主意,因为你会混淆真实情况。
相反,您可以在一秒内添加一个具有订单等级的字段。使用CCD_ 1可以很容易地做到这一点。
如果这有助于减小数据的大小,您可以随时将该数字转换为秒的一部分。