网格聚合函数

概述

所有网格聚合函数都接受一个整数间隔参数，并生成一个结果序列，其中包含每个间隔的计算聚合值。

函数	说明
seq_grid_agg_max(input, interval)	返回每个元素区间内具有最大值的序列
seq_grid_agg_min(input, interval)	返回每个元素区间内具有最小值的序列
seq_grid_agg_sum(input, interval)	返回每个元素区间内具有总和的序列
seq_grid_agg_avg(input, interval)	返回每个元素区间内具有平均值的序列
seq_grid_agg_var(input, interval)	返回每个元素区间内具有方差的序列
seq_grid_agg_var_samp(input, interval)	返回每个元素区间内具有样本方差的序列
seq_grid_agg_dev(input, interval)	返回每个元素区间内具有标准差的序列
seq_grid_agg_dev_samp(input, interval)	返回每个元素区间内具有样本标准差的序列

最大值和最小值函数

seq_grid_agg_max() 和 seq_grid_agg_min() 函数返回包含两个 7 元素块的序列，其元素与样本体积块中的值相对应。 以下是一个演示 seq_grid_agg_max() 和 seq_grid_agg_min() 函数的示例脚本：

    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13",
        seq_grid_agg_max(volume@Q1_13, 7) as "max_Q1_13",
        seq_grid_agg_min(volume@Q1_13, 7) as "min_Q1_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol
    Q1_13{}
    Volume_Q1_13{}
    max_Q1_13{}
    min_Q1_13{}
    ------------------------------------------------------------------------------
    SYM0
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129,
    0130213, 20130214, 20130216, 20130311, 20130326}
    {335, 808, 45, 402, 732, 48, 805,
    450, 385, 420, 367, 69}
    {808, 450}
    {45, 69}

要使用 xSQL 演示此选择语句，可从 samples/xsql/scripts/financial 目录中运行以下命令来执行示例脚本：

    g 7

求和与平均值函数

seq_grid_agg_sum() 和 seq_grid_agg_avg() 函数返回包含两个 7 元素块的序列，其元素与样本体积块中的值相对应。 以下是一个示例脚本，演示了 seq_grid_agg_sum() 和 seq_grid_agg_avg() 函数：

    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13",
        seq_grid_agg_sum(volume@Q1_13, 7) as "sum_Q1_13",
        seq_grid_agg_avg(volume@Q1_13, 7) as "avg_Q1_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol
    Q1_13{}
    Volume_Q1_13{}
    sum_Q1_13{}
    avg_Q1_13{}
    ------------------------------------------------------------------------------
    SYM0
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129,
    20130213, 20130214, 20130216, 20130311, 20130326}
    {335, 808, 45, 402, 732, 48, 805,
    450, 385, 420, 367, 69}
    {3175, 1691}
    {453.571429, 338.200000}

为了验证：

    seq_grid_agg_sum:
        block 1: 335 + 808 + 45 + 402 + 732 + 48 + 805 = 3175

        block 2: 450 + 385 + 420 + 367 + 69 = 1691

    seq_grid_agg_avg:
        block 1: 3175 / 7 = 453.571429

        block 2: 1691 / 5 = 338.200000

要使用 xSQL 演示此选择语句，可从 samples/xsql/scripts/financial 目录中运行以下命令来执行示例脚本：

    g 7

方差和标准差函数

seq_grid_agg_var() 和 seq_grid_agg_dev() 函数用于计算样本体积块的方差和标准差。 以下是一个示例脚本，演示了 seq_grid_agg_var() 和 seq_grid_agg_dev() 函数的用法：

    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13",
        seq_grid_agg_var(volume@Q1_13, 7) as "var_Q1_13",
        seq_grid_agg_dev(volume@Q1_13, 7) as "dev_Q1_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol
    Q1_13{}
    Volume_Q1_13{}
    var_Q1_13{}
    dev_Q1_13{}
    ------------------------------------------------------------------------------
    SYM0
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129,
      20130213, 20130214, 20130216, 20130311, 20130326}
    {335, 808, 45, 402, 732, 48, 805,
      450, 385, 420, 367, 69}
    {96397.387755, 18935.760000}
    {310.479287, 137.607267}

请注意，使用以下表格（用 Microsoft Excel 2010 版本构建）可以验证为样本体积块计算出的方差和标准差值：

要使用 xSQL 演示此选择语句，可从 samples/xsql/scripts/financial 目录中运行以下命令来执行示例脚本：

    g 7

示例

以下是一个示例代码片段，演示了二进制函数：

    -- seq_add, seq_sub, seq_mul, seq_div
            
     
    INSERT INTO SimpleSequence(testNumber,dVal1,dVal2)
    VALUES(1,'{1,2}','{3,4}');
     
    SELECT dVal1,dVal2,seq_add(dVal1,dVal2) As "add" FROM SimpleSequence where testNumber=1;
    SELECT dVal1,dVal2,seq_sub(dVal1,dVal2) As "sub" FROM SimpleSequence where testNumber=1;
    SELECT dVal1,dVal2,seq_mul(dVal1,dVal2) As "mul" FROM SimpleSequence where testNumber=1;
    SELECT dVal1,dVal2,seq_div(dVal1,dVal2) As "div" FROM SimpleSequence where testNumber=1;
     
    dVal1{}
    dVal2{}
    add{}
    --------------------------------------------------------------------
    {1.000000, 2.000000}
    {3.000000, 4.000000}
    {4.000000, 6.000000}
     
    dVal1{}
    dVal2{}
    sub{}
    --------------------------------------------------------------------
    {1.000000, 2.000000}
    {3.000000, 4.000000}
    {-2.000000, -2.00000}
     
    dVal1{}
    dVal2{}
    mul{}
    --------------------------------------------------------------------
    {1.000000, 2.000000}
    {3.000000, 4.000000}
    {3.000000, 8.000000}
     
    dVal1{}
    dVal2{}
    div{}
    --------------------------------------------------------------------
    {1.000000, 2.000000}
    {3.000000, 4.000000}
    {0.333333, 0.500000}
     
    -- seq_mod
            
     
    INSERT INTO SimpleSequence(testNumber,dVal1,dVal2)
     
    VALUES(2,'{6,7}','{3,4}');
    SELECT dVal1,dVal2,seq_mod(dVal1,dVal2) As "mod" FROM SimpleSequence where testNumber=2;
     
    dVal1{}
    dVal2{}
    mod{}
    --------------------------------------------------------------------
    {6.000000, 7.000000}
    {3.000000, 4.000000}
    {0.000000, 3.000000}