I would like to find a minimum in a very long time series, but instead of show the whole series with 2000 points, or only 1 line, I want to show the 20 rows with the profit
value before and after the minimum,
result example :
company date_num, profit min A EPOCH 4 2 A EPOCH 2 2 # show 10 results before and after 2, for A. A EPOCH 16 2 C EPOCH 9 9 C EPOCH 11 9
So, per company, find the minimum, then print the “area” around the minimum, say 10 results before and after.
Goal is to print the area of the minimum with a line that show minimum.
This gives me a single line result:
WITH BS AS ( SELECT date_num, company, ROW_NUMBER() OVER (PARTITION BY company ORDER BY profit desc) as rn FROM history WHERE company in ['a','b','c'] ) SELECT date_num, company FROM BS WHERE rn = 1
EDIT:
To clarify, thanks to the comments here, if minimum 2 was on August 15, i want to show all results between 10-20th August.
date_time
is a number date which show the date in days as int
number in seconds. (epoch)
Advertisement
Answer
You need two steps:
- find the minimum rows
- find their surrounding rows
For this, determine the minimum profit with MIN OVER
and also number the rows with ROW_NUMBER
. Thus you can then select all rows the row number of which is not farther away than 10 from the minimum rows’ row number.
WITH bs AS ( SELECT company, date_num, profit, MIN(profit) OVER (PARTITION BY company) AS min_profit, ROW_NUMBER() OVER (PARTITION BY company ORDER BY date_num) AS rn FROM history WHERE company IN ('A', 'B', 'C') ) SELECT company, date_num, profit FROM bs WHERE EXISTS ( SELECT NULL FROM bs bsmin WHERE bsmin.profit = bsmin.min_profit AND bsmin.company = bs.company AND ABS(bs.rn - bsmin.rn) <= 10 ) ORDER BY company, date_num;