数据湖探索 DLI-GROUPING SETS:关键字

时间:2024-11-11 14:10:59

关键字

GROUPING SETS:为对GROUP BY的扩展,例如

  • SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS((a,b));
    将转换为以下一条查询:
    1
    2
    SELECT a, b, sum(expression) FROM table
      GROUP BY a, b;
    
  • SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS(a,b);

    将转换为以下两条查询:

    1
    2
    3
    SELECT a, NULL, sum(expression) FROM table GROUP BY a;
    UNION
    SELECT NULL, b, sum(expression) FROM table GROUP BY b;
    
  • SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS((a,b), a);
    将转换为以下两条查询:
    1
    2
    3
    SELECT a, b, sum(expression) FROM table GROUP BY a, b;
    UNION
    SELECT a, NULL, sum(expression) FROM table GROUP BY a;
    
  • SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS((a,b), a, b, ());
    将转换为以下四条查询:
    1
    2
    3
    4
    5
    6
    7
    SELECT a, b, sum(expression) FROM table GROUP BY a, b;
    UNION
    SELECT a, NULL, sum(expression) FROM table GROUP BY a, NULL;
    UNION
    SELECT NULL, b, sum(expression) FROM table GROUP BY NULL, b;
    UNION
    SELECT NULL, NULL, sum(expression) FROM table;
    
support.huaweicloud.com/sqlref-spark-dli/dli_08_0164.html