This is my data
| id | day | op | hi | lo | cl | per_chng | gt |
|---|---|---|---|---|---|---|---|
| 1 | 2007-01-01 | 1.95805 | 1.96417 | 1.95765 | 1.96332 | -0.26842287553 | 0 |
| 2 | 2007-01-02 | 1.9633 | 1.97418 | 1.9604 | 1.97295 | -0.48911528421 | 0 |
| 3 | 2007-01-03 | 1.97301 | 1.9749 | 194814 | 195169 | 1.0923865982814 | 1 |
| 4 | 2007-01-04 | 1.95165 | 1.95296 | 1.9398 | 1.94258 | 0.466904837896 | 1 |
| 5 | 2007-01-05 | 1.94251 | 1.94316 | 192633 | 1929 | 0.700362882322442 | 1 |
| 6 | 2007-01-07 | 1.93033 | 1.93057 | 1.92907 | 1.92932 | 0.05235005079 | 0 |
| 7 | 2007-01-08 | 1.92938 | 1.94023 | 1.92608 | 1.93087 | -0.5407578858 | 0 |
| 8 | 2007-01-09 | 1.93985 | 1.94527 | 1.93852 | 1.9388 | 0.054157210645 | 0 |
| 9 | 2007-01-10 | 1.93881 | 1.94205 | 1.93148 | 1.93187 | 0.3592374228 | 0 |
| 10 | 2007-01-11 | 1.932 | 1.95336 | 1.93169 | 1.94305 | -0.61472774505 | 0 |
| 11 | 2007-01-12 | 1.94392 | 1.96036 | 1.94268 | 1.95893 | -0.766234628 | 0 |
| 12 | 2007-01-14 | 1.95938 | 1.96019 | 1.95791 | 1.95992 | -0.027552144 | 0 |
| 13 | 2007-01-15 | 1.9598 | 1.96676 | 1.95697 | 1.96309 | -0.2133412084 | 0 |
| 14 | 2007-01-16 | 1.96413 | 19702 | 1.959 | 1.96069 | 0.17544843906001 | 0 |
| 15 | 2007-01-17 | 1.96068 | 19722 | 1.96035 | 1.97015 | -0.48067406035 | 0 |
| 16 | 2007-01-18 | 1.97022 | 1.97776 | 1.96364 | 1.97388 | -0.185421606 | 0 |
| 17 | 2007-01-19 | 1.97382 | 1.97662 | 1.9696 | 1.97335 | 0.0238173664 | 0 |
| 18 | 2007-01-21 | 1.9738 | 1.97444 | 1.97288 | 1.97365 | 0.007600131735 | 0 |
| 19 | 2007-01-22 | 1.97367 | 197857 | 1.97169 | 1.97647 | -0.1416667088 | 0 |
| 20 | 2007-01-23 | 1.97655 | 1.99145 | 1.97652 | 1.98179 | -0.264407429 | 0 |
| 21 | 2007-01-24 | 1.98181 | 1.98299 | 1.96464 | 19677 | 0.717080855821 | 1 |
| 22 | 2007-01-25 | 1.96757 | 1.97307 | 1.96245 | 1.96495 | 0.1333367261 | 0 |
| 23 | 2007-01-26 | 1.96485 | 1.96771 | 1.95576 | 1.95005 | 0.2060618667 | 0 |
| 24 | 2007-01-28 | 1.95969 | 1.96137 | 1.95824 | 1.96096 | -0.064764197 | 0 |
| 25 | 2007-01-29 | 1.96095 | 1.96123 | 1.95482 | 1.96108 | -0.006629 | 0 |
I want to find multiple highest values of High column.
For example I want to find maximum value of High column index 1-14, 2-15, 3-16, 4-17, 5-18, 6-19.
How to make different column of this values also?
You can use analytical / windowed functions for this.
SELECT
*,
MAX(high) OVER (
ORDER BY time
ROWS BETWEEN CURRENT ROW AND 13 FOLLOWING
)
AS rolling_14_max_high
FROM
your_data
This will take the max of the current row and its 13 following rows (when sorted by time).