选择价格在一定时间范围内增加5％的所有股票行情 [英] Selecting all tickers where price has increased by 5 percent within a time range

问题描述

这似乎并不容易。

我有一个表格 data ：

String ticker, Double price, Date time
--------------------------------------

如何提供上表：

选择股票价格WHERE的价格在'2014-10-01'和'2014-10-31'之间增加了百分之x和时间

SELECT ticker WHERE price has increased x percent AND time BETWEEN '2014-10-01' AND '2014-10-31' ?

需要做的是为每个自动收报机，确定最后一个值和第一个值，并将它们分开。

What one needs to do is for each ticker, determine the last and first value and divide them.

我已尝试过此操作，但由于显而易见的原因而无效：

I have tried this but it is not working for obvious reasons:

SELECT * FROM (
    SELECT ticker, min(ctid) as min, max(ctid) as max
    FROM data 
    WHERE 
    time BETWEEN '2014-10-01' AND '2014-10-31'  
    GROUP by ticker, time
    ORDER by ticker, time ASC
    ) X
WHERE 
    1.05 < (
        SELECT value
        FROM data 
        WHERE 
        time BETWEEN '2014-10-01' AND '2014-10-31'
        AND
        ticker = X.ticker
        AND 
        ctid = X.min
    )
    / 
    (
        SELECT value
        FROM data 
        WHERE 
        time BETWEEN '2014-10-01' AND '2014-10-31'
        AND
        ticker = X.ticker
        AND 
        ctid = X.max
    )

上述查询按代码和时间分组，最小和最大值应为用于整个数据集。但我确实得到了几个代码作为返回结果，所以我不知道实际上在那里发生了什么。

The above query is grouped by ticker and time, and the min and max are should be for the entire dataset. But I do get several tickers as a returned result, so I am not sure what is actually going on there.

我也调查过这样做通过WINDOW函数，有一个样本也不工作：

I have also investigated doing this through WINDOW functions, there is a sample that's also not working:

SELECT 
ticker,
first_value(price) over W as first, last_value(price) over W as last 
FROM data 
WHERE 
time BETWEEN '2014-10-01' AND '2014-10-31'
WINDOW W as (
    partition by ticker, time
) 
ORDER BY ticker, time ASC

有人知道如何在任何数据库上执行此类查询吗？

Does anyone know how to do this kind of query, on any database?

我使用PostgreSQL，为什么在其他数据库中看到的 ctid 与 ROW_ID 相同。

I am using PostgreSQL, why you are seeing ctid which in other databases is the same as ROW_ID.

但这个问题与PostgreSQL无关。

But this problem is not related to PostgreSQL only.

资料集 / p>

Dataset:

create table data
(
 ticker varchar(5),
 price numeric(5,2), 
 time date);

insert into data (ticker, price, time) values ('ABC',1,'2014-10-01');
insert into data (ticker, price, time) values ('ABC',0.95,'2014-10-02');
insert into data (ticker, price, time) values ('ABC',1,'2014-10-03');
insert into data (ticker, price, time) values ('ABC',1.04,'2014-10-04');
insert into data (ticker, price, time) values ('ABC',1.05,'2014-10-05');
insert into data (ticker, price, time) values ('ABC',1.06,'2014-10-06');
insert into data (ticker, price, time) values ('ABC',1.07,'2014-10-07');
insert into data (ticker, price, time) values ('ABC',1.09,'2014-10-08');
insert into data (ticker, price, time) values ('ABC',2,'2014-10-09');
insert into data (ticker, price, time) values ('ABC',2,'2014-10-10');
insert into data (ticker, price, time) values ('ABC',1.9,'2014-10-11');
insert into data (ticker, price, time) values ('ABC',1.8,'2014-10-12');
insert into data (ticker, price, time) values ('ABC',1.7,'2014-10-13');
insert into data (ticker, price, time) values ('ABC',1.6,'2014-10-14');
insert into data (ticker, price, time) values ('ABC',1.5,'2014-10-15');
insert into data (ticker, price, time) values ('ABC',1.4,'2014-10-16');
insert into data (ticker, price, time) values ('ABC',1.6,'2014-10-17');
insert into data (ticker, price, time) values ('ABC',1.4,'2014-10-18');
insert into data (ticker, price, time) values ('ABC',1.3,'2014-10-19');
insert into data (ticker, price, time) values ('ABC',1.2,'2014-10-31');
insert into data (ticker, price, time) values ('XYZ',.95,'2014-10-01');
insert into data (ticker, price, time) values ('XYZ',1,'2014-10-31');
insert into data (ticker, price, time) values ('PDQ',1.4,'2014-10-01');
insert into data (ticker, price, time) values ('PDQ',1.3,'2014-10-31');
insert into data (ticker, price, time) values ('XKCD',.01,'2014-10-01');
insert into data (ticker, price, time) values ('XKCD',100,'2014-10-31');
insert into data (ticker, price, time) values ('Z8T',1,'2014-10-01');
insert into data (ticker, price, time) values ('Z8T',1.04,'2014-10-31');

推荐答案

在postgresql中，我们可以有效地执行计算查询这是：

In postgresql, we can efficiently perform a computational query as this:

请注意，我们首先从另一个表中选择所有的股票代码，但是在数据表上也可以是DISTINCT（股票代码）。

Note, we are first selecting all the tickers from another table Ticker first, but this could be DISTINCT(ticker) on the data table as well.

SELECT A.name, A.symbol, B.price AS start, C.price AS last, D.percent from ticker A,

lateral ( SELECT B.price AS value from data B where  B.time BETWEEN '2014-12-01' AND '2014-12-10' AND B.ticker=A.id ORDER BY B.time ASC limit 1 ) AS B,

lateral ( SELECT C.price AS value from data C where AND C.time BETWEEN '2014-12-01' AND '2014-12-10' AND C.ticker=A.id ORDER BY C.time DESC limit 1 ) AS C,

lateral ( SELECT C.price / B.price AS percent ) AS D

WHERE D.percent > 1.05

ORDER BY D.percent DESC

这篇关于选择价格在一定时间范围内增加5％的所有股票行情的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！