1 số công thức dùng trong evenhouse và pineline

vào

 * bài đăng với mục đích lưu trữ

1. Pineline



Connection:source table

Query:  Lọc những data có sự thay đổi về giá trong ngày

T_stock

    | where startofday(Timestamp_UTC7) == startofday(

        toscalar(T_stock | summarize max(Timestamp_UTC7)) - 1d

      ) 

    | order by symbol, Timestamp_UTC7 asc

    | extend prev_price = iff(symbol == prev(symbol), prev(price), price)  

    | extend price_change = price - prev_price  

    | project symbol, price, prev_price, price_change, volume, RSI, MACD, EMA_50, EMA_200, SMA_20, 

              high, low, ADX, Formatted_Timestamp = format_datetime(Timestamp_UTC7, 'yyyy-MM-dd HH:mm:ss')

    | where price_change != 0




chọn bảng destination

2. Evenhouse Kquery



.set-or-append daily_stock <|
T_stock
| order by symbol, Timestamp_UTC7 asc
| extend prev_price = iff(symbol == prev(symbol), prev(price), price)  
| extend price_change = price - prev_price  
| project symbol, price, prev_price, price_change, volume, RSI, MACD, EMA_50, EMA_200, SMA_20,
          high, low, ADX, Formatted_Timestamp = format_datetime(Timestamp_UTC7, 'yyyy-MM-dd HH:mm:ss')
|where price_change != 0
|take 0


.set-or-append price_change <|
T_stock
| order by symbol, Timestamp_UTC7 asc
| extend prev_price = iff(symbol == prev(symbol), prev(price), price)  
| extend price_change = price - prev_price  
| project symbol, price, prev_price, price_change, volume, RSI, MACD, EMA_50, EMA_200, SMA_20,
          high, low, ADX, Formatted_Timestamp = format_datetime(Timestamp_UTC7, 'yyyy-MM-dd HH:mm:ss')
| where price_change > 0

.set-or-append daily_stock <|
T_stock
| where startofday(Timestamp_UTC7) == startofday(
    toscalar(T_stock | summarize max(Timestamp_UTC7)) - 1d
  )
| order by symbol, Timestamp_UTC7 asc
| extend prev_price = iff(symbol == prev(symbol), prev(price), price)
| extend price_change = price - prev_price
| project symbol, price, prev_price, price_change, volume, RSI, MACD, EMA_50, EMA_200, SMA_20,
          high, low, ADX, Formatted_Timestamp = format_datetime(Timestamp_UTC7, 'yyyy-MM-dd HH:mm:ss')
|where price_change != 0


T_stock
| where Timestamp_UTC7 > now() + 7h - 1h
| order by symbol, Timestamp_UTC7 asc
| extend prev_price = iff(symbol == prev(symbol), prev(price), price)  // Chỉ lấy giá trước đó nếu cùng mã
| extend price_change = price - prev_price  // Tính toán chênh lệch
| project symbol, price, prev_price, price_change, volume, RSI, MACD, EMA_50, EMA_200, SMA_20,
          high, low, ADX, Formatted_Timestamp = format_datetime(Timestamp_UTC7, 'yyyy-MM-dd HH:mm:ss')

T_stock
| order by symbol, Timestamp_UTC7 asc
| extend prev_price = iff(symbol == prev(symbol), prev(price), price)  // Chỉ lấy giá trước đó nếu cùng mã
| extend price_change = price - prev_price  // Tính toán chênh lệch
| project symbol, price, prev_price, price_change, volume, RSI, MACD, EMA_50, EMA_200, SMA_20,
          high, low, ADX, Formatted_Timestamp = format_datetime(Timestamp_UTC7, 'yyyy-MM-dd HH:mm:ss')
| where price_change !=0
| extend Formatted_Timestamp = todatetime(Formatted_Timestamp)
|project symbol,price_change,Formatted_Timestamp

daily_stock

stock_his
|extend Year = datetime_part('year', ['time']),
         Month = datetime_part('month', ['time']),
         Day = datetime_part('day', ['time'])



.alter-merge table Stock_1 policy retention softdelete = 30d recoverability = disabled

daily_stock
| getschema

.clear table daily_stock data

.show table daily_stock policy mirroring

.alter table daily_stock policy mirroring delete

.truncate table daily_stock


Nhận xét

Đăng nhận xét