Find minimum and show the area/values around it-CodePudding

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)

CodePudding user response：

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;