在選擇中獲取當前行的先前值-有解無憂

我正在根據前一行的值計算值。我用于LAG從前一行獲取值，它適用于前兩行但不適用于下一行。如果行是第一個，那么讓我詳細說明我的場景，然后我將Open_HA與O列相同。但是對于下一行，我將上一行Open_HA添加到上一行Close_HA以計算下一行Open_HA并將其除以 2，依此類推。我所做的如下。

DECLARE @StockIndicator AS TABLE
(
Sr INT IDENTITY,
StartDate DATE,
O decimal(18,3),
H decimal(18,3),    
L decimal(18,3),    
C decimal(18,3),
Close_HA AS (O H L C)/4
)
                                                                                                   
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-15',  93.25   ,93.30  ,93.30  ,93.25 )
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-16',  98.55   ,98.55  ,98.55  ,98.55 )
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-17',  100.98  ,99.99  ,100.98 ,99.99 )
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-18',  102.05  ,102.05 ,102.05 ,102.05)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-19',  103.00  ,103.90 ,103.90 ,103.00)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-20',  104.08  ,104.23 ,104.23 ,104.08)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-21',  104.90  ,104.60 ,105.00 ,104.00)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-22',  104.60  ,104.60 ,104.60 ,104.60)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-23',  105.90  ,105.90 ,105.90 ,105.90)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-24',  104.40  ,104.40 ,105.00 ,103.51)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-25',  105.18  ,105.18 ,105.18 ,105.18)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-26',  103.00  ,102.60 ,103.52 ,102.60)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-27',  100.00  ,100.00 ,100.00 ,100.00)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-28',  99.40   ,98.95  ,99.78  ,98.95 )
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-29',  99.00   ,99.00  ,99.00  ,99.00 )
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-30',  100.01  ,100.90 ,101.00 ,100.01)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-10-01',  102.00  ,102.70 ,102.70 ,102.00)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-10-02',  102.70  ,102.00 ,102.70 ,102.00)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-10-03',  103.30  ,103.30 ,103.30 ,103.30)


SELECT Sr,
       O,
       H,
       L,
       C,
       CASE
            WHEN OuterDetail.Sr > 1 THEN ((LAG(OuterDetail.Open_HA) OVER (ORDER BY OuterDetail.Sr))   (LAG(OuterDetail.[Close_HA]) OVER (ORDER BY OuterDetail.Sr))) / 2
            ELSE OuterDetail.Open_HA
       END AS Open_HA,
       [Close_HA]
FROM (SELECT Sr,
             O,
             H,
             L,
             C,
             CASE WHEN Detail.Sr = 1 THEN Detail.O ELSE 0 END AS Open_HA,
             [Close_HA]
      FROM (SELECT Sr, O, H, L, C, [Close_HA] FROM @StockIndicator) Detail ) OuterDetail;

預期結果應如下圖所示

在選擇中獲取當前行的先前值

如需更多說明，請使用我正在嘗試使用上述查詢進行準確計算的excel 鏈接。

uj5u.com熱心網友回復：

由于您正在計算的行使用最后一行的公式的結果，它不是在結果集上運行，而是逐行動態創建，因此滯后函式在這里不起作用。您可以做的是使用游標來填充該列：

DECLARE @StockIndicator AS TABLE
(
Sr INT IDENTITY,
StartDate DATE,
O decimal(18,3),
H decimal(18,3),    
L decimal(18,3),    
C decimal(18,3),
Open_HA decimal(18,3), -- I've added the column to the temp variable
Close_HA AS (O H L C)/4
)

declare @sr int, @close_ha decimal(18,3), @O decimal(18,3)
declare @last_close_ha decimal(18,3), @last_open_ha decimal(18,3)

declare cur cursor local FOR
    select Sr, Close_HA, O
    from @StockIndicator
    order by Sr asc
for update of Open_HA

open cur
fetch next from cur into @sr, @close_ha, @o
while @@fetch_status = 0
BEGIN
    set @last_open_ha = case @Sr
        when 1 then @O
        else (@last_open_ha @last_close_ha)/2
    end
    set @last_close_ha = @close_ha
    update @StockIndicator set
    Open_HA = @last_open_ha
    where Sr=@Sr
    fetch next from cur into @sr, @close_ha, @o
end
close cur
deallocate cur
select *
from @StockIndicator

你可以在這里看到它在 DB Fiddle 上作業。

uj5u.com熱心網友回復：

這可以通過遞回來完成，以執行與前一行相關的非平凡運行計算。

SQL Server 的小提琴

首先我們計算Close_HA第一個 CTE 項，然后我們遞回地使用該結果來計算Open_HA。

如果您已經計算過Close_HA（它已經在表格中），我們可以跳過第一個 CTE 術語。

SQL：

WITH cte AS (
        SELECT *
             , (O   H   L   C)/4 AS Close_HA
          FROM test
     )
   , calc AS (
        SELECT t1.*, C AS Open_HA FROM cte AS t1 WHERE Sr = 1 UNION ALL
        SELECT t1.*
             , CAST((t2.Close_HA   t2.Open_HA) / 2.0 AS DECIMAL(8,2))
          FROM cte  AS t1
          JOIN calc AS t2
            ON t1.Sr = t2.Sr   1
     )
SELECT * FROM calc ORDER BY Sr
;

結果：

 ------ ------------ -------- -------- -------- -------- ------------ --------- 
| Sr   | Date       | O      | H      | L      | C      | Close_HA   | Open_HA |
 ------ ------------ -------- -------- -------- -------- ------------ --------- 
|    1 | 2020-09-15 |  93.25 |  93.30 |  93.30 |  93.25 |  93.275000 |   93.25 |
|    2 | 2020-09-16 |  98.55 |  98.55 |  98.55 |  98.55 |  98.550000 |   93.26 |
|    3 | 2020-09-17 | 100.98 |  99.99 | 100.98 |  99.99 | 100.485000 |   95.91 |
|    4 | 2020-09-18 | 102.05 | 102.05 | 102.05 | 102.05 | 102.050000 |   98.20 |
|    5 | 2020-09-19 | 103.00 | 103.90 | 103.90 | 103.00 | 103.450000 |  100.13 |
|    6 | 2020-09-20 | 104.08 | 104.23 | 104.23 | 104.08 | 104.155000 |  101.79 |
|    7 | 2020-09-21 | 104.90 | 104.60 | 105.00 | 104.00 | 104.625000 |  102.97 |
|    8 | 2020-09-22 | 104.60 | 104.60 | 104.60 | 104.60 | 104.600000 |  103.80 |
|    9 | 2020-09-23 | 105.90 | 105.90 | 105.90 | 105.90 | 105.900000 |  104.20 |
|   10 | 2020-09-24 | 104.40 | 104.40 | 105.00 | 103.51 | 104.327500 |  105.05 |
|   11 | 2020-09-25 | 105.18 | 105.18 | 105.18 | 105.18 | 105.180000 |  104.69 |
|   12 | 2020-09-26 | 103.00 | 102.60 | 103.52 | 102.60 | 102.930000 |  104.94 |
|   13 | 2020-09-27 | 100.00 | 100.00 | 100.00 | 100.00 | 100.000000 |  103.94 |
|   14 | 2020-09-28 |  99.40 |  98.95 |  99.78 |  98.95 |  99.270000 |  101.97 |
|   15 | 2020-09-29 |  99.00 |  99.00 |  99.00 |  99.00 |  99.000000 |  100.62 |
|   16 | 2020-09-30 | 100.01 | 100.90 | 101.00 | 100.01 | 100.480000 |   99.81 |
|   17 | 2020-10-01 | 102.00 | 102.70 | 102.70 | 102.00 | 102.350000 |  100.15 |
|   18 | 2020-10-02 | 102.70 | 102.00 | 102.70 | 102.00 | 102.350000 |  101.25 |
|   19 | 2020-10-03 | 103.30 | 103.30 | 103.30 | 103.30 | 103.300000 |  101.80 |
 ------ ------------ -------- -------- -------- -------- ------------ ---------

測驗用例的設定：

CREATE TABLE test (
    Sr     int
  , Date   Date
  , O      DECIMAL(8,2)
  , H      DECIMAL(8,2)
  , L      DECIMAL(8,2)
  , C      DECIMAL(8,2)
);

INSERT INTO test VALUES
  ( 1,'2020-09-15', 93.25, 93.30, 93.30, 93.25)
, ( 2,'2020-09-16', 98.55, 98.55, 98.55, 98.55)
, ( 3,'2020-09-17',100.98, 99.99,100.98, 99.99)
, ( 4,'2020-09-18',102.05,102.05,102.05,102.05)
, ( 5,'2020-09-19',103.00,103.90,103.90,103.00)
, ( 6,'2020-09-20',104.08,104.23,104.23,104.08)
, ( 7,'2020-09-21',104.90,104.60,105.00,104.00)
, ( 8,'2020-09-22',104.60,104.60,104.60,104.60)
, ( 9,'2020-09-23',105.90,105.90,105.90,105.90)
, (10,'2020-09-24',104.40,104.40,105.00,103.51)
, (11,'2020-09-25',105.18,105.18,105.18,105.18)
, (12,'2020-09-26',103.00,102.60,103.52,102.60)
, (13,'2020-09-27',100.00,100.00,100.00,100.00)
, (14,'2020-09-28', 99.40, 98.95, 99.78, 98.95)
, (15,'2020-09-29', 99.00, 99.00, 99.00, 99.00)
, (16,'2020-09-30',100.01,100.90,101.00,100.01)
, (17,'2020-10-01',102.00,102.70,102.70,102.00)
, (18,'2020-10-02',102.70,102.00,102.70,102.00)
, (19,'2020-10-03',103.30,103.30,103.30,103.30)
;

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/380040.html

標籤：sql sql-server

上一篇：列重復時如何從表中選擇行？

下一篇：搜索一組SQLServer視圖以供表使用