FAQ > 金融建模 > 建模问题 > 语法相关

Q：group by 常用场景使用说明    

  • 语法
    [GROUP BY <expression[1,2,..,n]> [HAVING <expression>]]
    说明
      group by 语句是通过列的引用对数组进行分组后汇总数据。

    注意：带group by语句的select语句中，select 后面返回的字段只能是被group by后的字段或聚集函数产生的结果，或其它单一值，即一个组中只有一个值的结果才可以放在最外层进行返回，表示该组的整体产生的结果。
    GROUP BY 语句可以与以下语法搭配使用：
    1、thisgroup：分组后产生的各子数组，必须与group by搭配使用，不可单独使用。即上层select语句中group by分组后的当前组的记录，是一个表，对它访问需要嵌套select子语句。
     例如select子句按照字段A进行分组，thisgroup在运行时指定的是当前行的字段A的值按照A分组后的行集，相当于select * from table where [‘A’]=thisrow[‘A’] end；
    2、聚集函数：分组后，对各组的简单统计应用，参考：FAQ：聚集函数；
    3、having：用于分组后组内数据的筛选，常用用聚集函数作为条件，与分组前对数据进行筛选的where不同；
    4、多个值分组：将用于分组的多个值在Group By后用逗号分隔即可；
    5、自定义分组模型：可以对分组列的值自定义模型筛选后分组；
    6、refsof：访问上级表的行集或者表达式。

    使用范例
    范例1：thisgroup
    访问组内详情
     //分组后，并返回各组具体详情。是一个表，访问需要用select
      Table1 := Array(
      ("A":2,"B":2),
      ("A":2,"B":5),
      ("A":2,"B":3),
      ("A":8,"B":3),
      ("A":4,"B":41),
      ("A":4,"B":54));
      return select ['A'], //返回分组字段
         Avgof(['B']) as 'AvgB', //聚集函数，返回该组内B列的平均值
          select * from thisgroup end as 'group'//分组后当前组内的详细记录
          from Table1 group by ['A'] order by ['A'] end;//按A列分组，且最终结果按A列排序

    结果：

    点开第一个group展开如下：


    范例2：聚集函数的使用
    聚集函数:分组后，对各组的简单统计应用
     //将行按["A"]进行分类，然后在每个分类中进行sumof(["B"])操作。
      Table1 := Array(
      ("A":2,"B":2),
      ("A":2,"B":5),
      ("A":2,"B":3),
      ("A":8,"B":3),
      ("A":4,"B":41),
      ("A":4,"B":54) );
      Return Select ["A"],
             sumof(["B"]) as "SumB"
          from Table1 GROUP BY ["A"] end;

    结果：


    范例3：HAVING的应用
    HAVING：分组后对组的筛选
     //指定结果集中，将行按["A"]进行分组，然后在每个分类中进行sumof(["B"])操作。
     //返回得出的结果中Sumof(["B"])>4的分组。
      Table1:= Array(
      ("A":2,"B":2),
      ("A":2,"B":5),
      ("A":2,"B":3),
      ("A":8,"B":3),
      ("A":4,"B":41),
      ("A":4,"B":54) );
      Return Select ["A"],
          sumof(["B"]) as "SumB"
          from Table1 GROUP BY ["A"] HAVING SUMOF(["B"])>4 end;

    结果：


    范例4：多个值分组
    将用于分组的多个值在Group By后用逗号分隔即可
     //按['A'],["B"]列的值分组后对['C']列的值进行聚类分析
      Table1 := Array(
      ("A":0,"B":0,"C":10),
      ("A":0,"B":1,"C":15),
      ("A":0,"B":0,"C":15),
      ("A":0,"B":1,"C":20),
      ("A":1,"B":0,"C":15),
      ("A":1,"B":1,"C":15),
      ("A":1,"B":0,"C":20),
      ("A":1,"B":1,"C":10));
      return select ['A'],["B"],
             Avgof(['C']) as 'AvgB',
             Sumof(['C']) as 'SumB'
          from Table1 group by ['A'],["B"] end;

    结果：


    范例5：自定义条件分组
     //对 A 股股价进行聚类，返回聚类中股票个数大于 10 的聚类，得到每个聚类的股票个数以及平均价格:
    return SELECT GetStockPriceType(["close"]) AS "价格分类",
         AvgOf(["close"]) AS "平均价格",
         COUNTOF(['StockID']) AS "个数"
         FROM MarketTable
         DateKey today() to today()
         OF GetBK("深证A股;上证A股")
         GROUP BY GetStockPriceType(["close"]) Having COUNTOF(['StockID'])>10
         END;

    //GetStockPriceType函数的定义如下：
    function GetStockPriceType(c);
    begin
      return
      ifthen(c>15,"高价股",ifthen(c>5,"中价股","低价股"));
    end;

    结果：
    价格分类平均价格个数
    中价股8.861672939
    高价股44.972328567
    低价股3.378094509


    范例6：RefsOf访问上级结果集
     ////表M按[2]分组后求[0]的和，同时返回分组后行集中[0]的最大值所在行对应[1]的值。
      m := array(
      (0.86,0.08,'A'),
      (0.74,0.89,'A'),
      (0.17,0.82,'A'),
      (0.33,0.99,'B'),
      (0.09,0.45,'B'),
      (0.92,0.26,'C'),
      (0.57,0.26,'C'));
      return select sumof([0]) as 'total-0',
             (vselect [1] from thisgroup where [0]=refsof(maxof([0]),1) end) as 'max-0-1'
          from m group by [2] end;

    结果：


    相关说明：
    FAQ：Group By支持对多个值进行分组
    FAQ：Group by 进行分组与Having进行分组后的结果筛选
    FAQ：ThisGroup访问分组内的内容
    FAQ：RefsOf访问上级结果集
    FAQ：TS-SQL访问分组后组内的内容
    FAQ：ThisGroup访问分组内的内容