這是我的資料
| ID | 日 | 操作 | 你好 | 羅 | CL | per_chng | 總噸 |
|---|---|---|---|---|---|---|---|
| 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 |
我想找到High列的多個最高值。
例如，我想找到High列索引 1-14、2-15、3-16、4-17、5-18、6-19 的最大值。
如何制作此值的不同列？
uj5u.com熱心網友回復：
您可以為此使用分析/視窗函式。
SELECT
*,
MAX(high) OVER (
ORDER BY time
ROWS BETWEEN CURRENT ROW AND 13 FOLLOWING
)
AS rolling_14_max_high
FROM
your_data
這將取當前行及其后 13 行的最大值（按時間排序時）。
uj5u.com熱心網友回復：
您可以為此使用視窗函式。一個OFFSET子句將跳過前 13 行
SELECT *,
MaxHigh = MAX(High) OVER (ORDER BY Time ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)
FROM YourTable t
OFFSET 13 ROWS FETCH 10000000 ROWS ONLY;
uj5u.com熱心網友回復：
您可以在要搜索最大值的范圍內使用起始索引：-
DECLARE @startIndex INT = 5 -- Starting Index
DECLARE @recods INT = 15 -- Number of next rows (Range)
SELECT MAX([Time]) FROM (SELECT [Time]
FROM YouTable
ORDER BY [Time]
OFFSET @startIndex ROWS FETCH NEXT @recods ROWS ONLY) T
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/323662.html
標籤：sql sql-server 聚合函数 窗函数
上一篇：合并陳述句中的洗掉陳述句問題