從Snowflake查詢中分組的第一個和最后一個條目中獲取列

我正在嘗試運行一個查詢，從股票交易中獲取 OHLC 統計資訊。為此，我創建了以下查詢：

SELECT 
    SUM(T.size), 
    SUM(T.size) / SUM(T.price), 
    FIRST_VALUE(T.price), 
    LAST_VALUE(T.price),
    MAX(T.price),
    MIN(T.price),
    FLOOR(T.sip_timestamp / 3600000000000)
FROM (
    SELECT *
    FROM trades AS T
    WHERE 
        T.symbol = 'NTAP' AND 
        T.sip_timestamp >= 1640995200000000000 AND
        T.sip_timestamp < 1672531200000000000
    ORDER BY T.sip_timestamp) AS T
GROUP BY FLOOR(T.sip_timestamp / 3600000000000)

這應該通過將交易資料過濾到時間戳視窗，按時間段（等于一小時）對其進行分組，然后據此計算統計資料來作業。成交量、加權值、最高價、最低價和開盤時間值很簡單，但我無法生成開盤價和收盤價。我正在使用FIRST_VALUE和LAST_VALUE視窗函式，但我不確定如何使它們在分組背景關系中作業。有誰知道如何在這里獲得正確的結果？

uj5u.com熱心網友回復：

所以FIRST_VALUE和 LAST_VALUE 是視窗框架函式而不是聚合函式，因此不在 GROUP BY 的域中操作

因此，您要做的是通過磁區和 order by 子句定義 FIRST_VALUE 的范圍，然后在 GROUP BY 中使用ANY_VALUE

因此：

with trades(symbol, sip_timestamp, size, price) as (
    select * from values
        ('NTAP', 1640995200000000000, 100, 1.234),
        ('NTAP', 1640995200000000001, 100, 1.111)
)
SELECT 
    bucket,
    SUM(T.size), 
    SUM(T.size) / SUM(T.price), 
    ANY_VALUE(b_fv) as bucket_first_value, 
    ANY_VALUE(b_lv) as bucket_last_value, 
    MAX(T.price) as max_price,
    MIN(T.price) as min_price
FROM (
    SELECT *
        ,FLOOR(T.sip_timestamp / 3600000000000) as bucket
        ,FIRST_VALUE(T.price) over (partition by bucket order by T.sip_timestamp) as b_fv
        ,LAST_VALUE(T.price) over (partition by bucket order by T.sip_timestamp) as b_lv 
    FROM trades AS T
    WHERE 
        T.symbol = 'NTAP' AND 
        T.sip_timestamp >= 1640995200000000000 AND
        T.sip_timestamp < 1672531200000000000
    ) AS T
GROUP BY bucket

現在給定你有一個子選擇，我把 FIRST_VALUES/LAST_VALUE 放在那里，因為嵌套的視窗函式不能一起玩。

我也把 也放在bucket那層。

如果你有很多值，你可以使用 ROW_NUMBER 和 IFF 得到一個值，然后使用 MAX，所以這會得到相同的結果：

SELECT 
    bucket,
    SUM(T.size), 
    SUM(T.size) / SUM(T.price), 
    max(b_fv) as bucket_first_value, 
    max(b_lv) as bucket_last_value, 
    MAX(T.price) as max_price,
    MIN(T.price) as min_price
FROM (
    SELECT *
        ,FLOOR(T.sip_timestamp / 3600000000000) as bucket
        ,IFF(row_number() over (partition by bucket order by T.sip_timestamp) = 1 , T.price, null) as b_fv
        ,IFF(row_number() over (partition by bucket order by T.sip_timestamp desc) = 1 , T.price, null) as b_lv 
    FROM trades AS T
    WHERE 
        T.symbol = 'NTAP' AND 
        T.sip_timestamp >= 1640995200000000000 AND
        T.sip_timestamp < 1672531200000000000
    ) AS T
GROUP BY bucket

現在如果你有很多你想要的相同順序的東西，你可以把它們分開，比如：

SELECT 
    bucket,
    SUM(T.size), 
    SUM(T.size) / SUM(T.price), 
    max(b_fv) as bucket_first_value, 
    max(b_lv) as bucket_last_value, 
    max(b_fv_2) as bucket_first_value_2, 
    max(b_lv_2) as bucket_last_value_2, 
    MAX(T.price) as max_price,
    MIN(T.price) as min_price
FROM (
    SELECT *
        ,FLOOR(T.sip_timestamp / 3600000000000) as bucket
        ,row_number() over (partition by bucket order by T.sip_timestamp) as rw_f
        ,row_number() over (partition by bucket order by T.sip_timestamp desc) as rw_d
        ,IFF(rw_f = 1 , T.price, null) as b_fv
        ,IFF(rw_f = 1 , T.price, null) as b_fv_2
        ,IFF(rw_d = 1 , T.price, null) as b_lv 
        ,IFF(rw_d = 1 , T.price, null) as b_lv_2
    FROM trades AS T
    WHERE 
        T.symbol = 'NTAP' AND 
        T.sip_timestamp >= 1640995200000000000 AND
        T.sip_timestamp < 1672531200000000000
    ) AS T
GROUP BY bucket

聚合函式：

CREATE OR REPLACE FUNCTION WindowAggregator(bucket string, bucket_time float, size float, price float)
    RETURNS TABLE (bucket string, sum float, avg float, first float, last float, max float, min float)
    LANGUAGE JAVASCRIPT
    AS '{
            initialize: function (argumentInfo, context) {
               this.ccount = 0;
               this.size_sum = 0;
               this.price_sum = 0;
               this.price_min = Number.POSITIVE_INFINITY;
               this.price_max = Number.NEGATIVE_INFINITY;
               this.price_sum = 0;
               this.last_key = Number.NEGATIVE_INFINITY;
               this.last_val = 0;
               this.first_key = Number.POSITIVE_INFINITY;
               this.first_val = 0;
            },
            processRow: function get_params(row, rowWriter, context){
                    this.bucket = row.BUCKET;
                    if(row.PRICE < this.price_min ) { 
                        this.price_min = row.PRICE; 
                    }
                    if(row.PRICE > this.price_max ) { 
                        this.price_max = row.PRICE; 
                    }
                    if(row.PRICE < this.price_min ) { 
                        this.price_min = row.PRICE; 
                    }
                    if(row.BUCKET_TIME > this.last_key ) { 
                        this.last_key = row.BUCKET_TIME; 
                        this.last_val = row.PRICE; 
                    }
                    if(row.BUCKET_TIME < this.first_key ) { 
                        this.first_key = row.BUCKET_TIME;
                        this.first_val = row.PRICE;
                    }
                    this.price_sum  = row.PRICE;
                    this.size_sum  = row.SIZE;
            },
            finalize: function (rowWriter, context) {
                rowWriter.writeRow({
                    BUCKET: this.bucket, 
                    SUM: this.size_sum, 
                    AVG: this.size_sum/this.price_sum, 
                    FIRST: this.first_val, 
                    LAST: this.last_val, 
                    MAX: this.price_max, 
                    MIN: this.price_min});
            },
        }';

with trades(symbol, sip_timestamp, size, price) as (
    select * from values
        ('NTAP', 1640995200000000000, 100, 1.234),
        ('NTAP', 1640995200000000001, 10, 77.7),
        ('NTAP', 1640995200000000002, 100, 1.111)
)
SELECT 
    wa.*
FROM (
    SELECT *
        ,FLOOR(T.sip_timestamp / 3600000000000) as bucket
        ,(T.sip_timestamp % 3600000000000)::float as bucket_time
    FROM trades AS T
    WHERE 
        T.symbol = 'NTAP' AND 
        T.sip_timestamp >= 1640995200000000000 AND
        T.sip_timestamp < 1672531200000000000
    ) AS T
cross join TABLE(WindowAggregator(t.bucket::text, T.bucket_time, T.size::float, T.price::float) over (partition by t.bucket)) as wa;

給出：

標籤：数据库通过...分组雪花云数据平台

上一篇：更新groupby列的答案

下一篇：顯示銷售額最高的員工