Spark SQL----GROUP BY子句

一、描述

GROUP BY子句用于根据一组指定的分组表达式对行进行分组，并根据一个或多个指定的聚合函数计算行组上的聚合。Spark还支持高级聚合，通过GROUPING SETS、CUBE、ROLLUP子句对同一输入记录集进行多个聚合。分组表达式和高级聚合可以混合在GROUP BY子句中，也可以嵌套在GROUPING SETS子句中。请参阅Mixed/Nested Grouping Analytics部分中的更多详细信息。当FILTER子句附加到聚合函数时，只有匹配的行被传递给该函数。

二、语法

GROUPBY group_expression [, group_expression [,...]][WITH { ROLLUP | CUBE } ]GROUPBY { group_expression | { ROLLUP | CUBE | GROUPING SETS } (grouping_set [,...]) } [,...]

而聚合函数定义为

aggregate_name ([DISTINCT] expression [,...])[ FILTER (WHERE boolean_expression )]

三、参数

group_expression 指定将行分组在一起所依据的条件。行的分组是基于分组表达式的结果值来执行的。分组表达式可以是类似GROUP BY A的列名、类似GROUP BY 0的列位置或类似GROUP BY a + b的表达式。
grouping_set grouping set由括号中的零个或多个逗号分隔的表达式指定。当分组集只有一个元素时，可以省略括号。例如，分组集((a), (b))与分组集(a, b)相同。语法：{ ( [ expression [ , … ] ] ) | expression }
GROUPING SETS 对GROUPING SETS之后指定的每个分组集的行进行分组。例如，GROUP BY GROUPING SETS ((warehouse), (product))在语义上等效于GROUP BY warehouse 和 GROUP BY product的结果的并集。此子句是UNION ALL的简写，其中UNION ALL运算符的每个分支执行GROUPING SETS子句中指定的每个分组集的聚合。类似地，GROUP BY GROUPING SETS ((warehouse, product), (product), ()) 在语义上等价于GROUP BY warehouse, product, GROUP BY product 和 global aggregate的结果的并集。注意：为了Hive兼容性，Spark允许GROUP BY … GROUPING SETS (…)。GROUP BY表达式通常被忽略，但如果它包含比GROUPING SETS表达式更多的表达式，则这些额外的表达式将包含在分组表达式中，并且值始终为null。例如，SELECT a, b, c FROM … GROUP BY a, b, c GROUPING SETS (a, b)，列c的输出始终为null。
ROLLUP 在单个语句中指定多个级别的聚合。此子句用于基于多个grouping sets计算聚合。ROLLUP是GROUPING SETS的简写。例如，GROUP BY warehouse, product WITH ROLLUP 或者GROUP BY ROLLUP(warehouse, product) 等效于GROUP BY GROUPING SETS((warehouse, product), (warehouse), ())。GROUP BY ROLLUP(warehouse, product, (warehouse, location))等效于GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ())。ROLLUP specification的N个元素产生N+1个GROUPING集合。
CUBE CUBE子句用于根据GROUP BY子句中指定的分组列的组合执行聚合。CUBE是GROUPING SETS的简写。例如，GROUP BY warehouse, product WITH CUBE 或 GROUP BY CUBE(warehouse, product)等效于GROUP BY GROUPING SETS((warehouse, product), (warehouse), (product), ())。GROUP BY CUBE(warehouse, product, (warehouse, location)) 等效于GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())。CUBE specification的N个元素产生2^N个分组集。
Mixed/Nested Grouping Analytics GROUP BY子句可以包括多个group_expressions和多个 CUBE|ROLLUP|GROUPING SETSs。GROUPING SETS也可以具有嵌套的CUBE|ROLLUP|GROUPING SETS子句，例如GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location)), GROUPING SETS(warehouse, GROUPING SETS(location, GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location))))。CUBE|ROLLUP只是GROUPING SETS的语法糖，请参阅上面的部分，了解如何将CUBE|ROLLUP转换为GROUPING SETS。在此上下文中，group_expression可以被视为单个组GROUPING SETS。对于GROUP BY子句中的多个GROUPING SETS，我们通过对原始GROUP集进行cross-product来生成单个GROUPING SETS。对于GROUPING SETS子句中嵌套的GROUPING SETS，我们只需取其分组集并将其剥离即可。例如，GROUP BY warehouse, GROUPING SETS((product), ()), GROUPING SETS((location, size), (location), (size), ()) 和 GROUP BY warehouse, ROLLUP(product), CUBE(location, size) 等价于GROUP BY GROUPING SETS( (warehouse, product, location, size), (warehouse, product, location), (warehouse, product, size), (warehouse, product), (warehouse, location, size), (warehouse, location), (warehouse, size), (warehouse))。 GROUP BY GROUPING SETS(GROUPING SETS(warehouse), GROUPING SETS((warehouse, product)))等价于GROUP BY GROUPING SETS((warehouse), (warehouse, product))。
aggregate_name 指定聚合函数名称（MIN、MAX、COUNT、SUM、AVG等）。
DISTINCT 在将输入行中的重复项传递给聚合函数之前，移除这些行。
FILTER 过滤WHERE子句中boolean_expression计算结果为true并传递给聚合函数的输入行；其他行被丢弃。

四、例子

CREATETABLE dealer (id INT, city STRING, car_model STRING, quantity INT);INSERTINTO dealer VALUES(100,'Fremont','Honda Civic',10),(100,'Fremont','Honda Accord',15),(100,'Fremont','Honda CRV',7),(200,'Dublin','Honda Civic',20),(200,'Dublin','Honda Accord',10),(200,'Dublin','Honda CRV',3),(300,'San Jose','Honda Civic',5),(300,'San Jose','Honda Accord',8);-- Sum of quantity per dealership. Group by `id`.SELECT id,sum(quantity)FROM dealer GROUPBY id ORDERBY id;+---+-------------+| id|sum(quantity)|+---+-------------+|100|32||200|33||300|13|+---+-------------+-- Use column position in GROUP by clause.SELECT id,sum(quantity)FROM dealer GROUPBY1ORDERBY1;+---+-------------+| id|sum(quantity)|+---+-------------+|100|32||200|33||300|13|+---+-------------+-- Multiple aggregations.-- 1. Sum of quantity per dealership.-- 2. Max quantity per dealership.SELECT id,sum(quantity)AS sum,max(quantity)AS max FROM dealer GROUPBY id ORDERBY id;+---+---+---+| id|sum|max|+---+---+---+|100|32|15||200|33|20||300|13|8|+---+---+---+-- Count the number of distinct dealer cities per car_model.SELECT car_model,count(DISTINCT city)AS count FROM dealer GROUPBY car_model;+------------+-----+|   car_model|count|+------------+-----+| Honda Civic|3||   Honda CRV|2||Honda Accord|3|+------------+-----+-- Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership.SELECT id,sum(quantity) FILTER (WHERE car_model IN('Honda Civic','Honda CRV'))AS`sum(quantity)`FROM dealer
    GROUPBY id ORDERBY id;+---+-------------+| id|sum(quantity)|+---+-------------+|100|17||200|23||300|5|+---+-------------+-- Aggregations using multiple sets of grouping columns in a single statement.-- Following performs aggregations based on four sets of grouping columns.-- 1. city, car_model-- 2. city-- 3. car_model-- 4. Empty grouping set. Returns quantities for all city and car models.SELECT city, car_model,sum(quantity)AS sum FROM dealer
    GROUPBY GROUPING SETS ((city, car_model),(city),(car_model),())ORDERBY city;+---------+------------+---+|     city|   car_model|sum|+---------+------------+---+|null|null|78||null| HondaAccord|33||null|    HondaCRV|10||null|  HondaCivic|35||   Dublin|null|33||   Dublin| HondaAccord|10||   Dublin|    HondaCRV|3||   Dublin|  HondaCivic|20||  Fremont|null|32||  Fremont| HondaAccord|15||  Fremont|    HondaCRV|7||  Fremont|  HondaCivic|10|| San Jose|null|13|| San Jose| HondaAccord|8|| San Jose|  HondaCivic|5|+---------+------------+---+-- Group by processing with `ROLLUP` clause.-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ())SELECT city, car_model,sum(quantity)AS sum FROM dealer
    GROUPBY city, car_model WITH ROLLUPORDERBY city, car_model;+---------+------------+---+|     city|   car_model|sum|+---------+------------+---+|null|null|78||   Dublin|null|33||   Dublin| HondaAccord|10||   Dublin|    HondaCRV|3||   Dublin|  HondaCivic|20||  Fremont|null|32||  Fremont| HondaAccord|15||  Fremont|    HondaCRV|7||  Fremont|  HondaCivic|10|| San Jose|null|13|| San Jose| HondaAccord|8|| San Jose|  HondaCivic|5|+---------+------------+---+-- Group by processing with `CUBE` clause.-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())SELECT city, car_model,sum(quantity)AS sum FROM dealer
    GROUPBY city, car_model WITH CUBE
    ORDERBY city, car_model;+---------+------------+---+|     city|   car_model|sum|+---------+------------+---+|null|null|78||null| HondaAccord|33||null|    HondaCRV|10||null|  HondaCivic|35||   Dublin|null|33||   Dublin| HondaAccord|10||   Dublin|    HondaCRV|3||   Dublin|  HondaCivic|20||  Fremont|null|32||  Fremont| HondaAccord|15||  Fremont|    HondaCRV|7||  Fremont|  HondaCivic|10|| San Jose|null|13|| San Jose| HondaAccord|8|| San Jose|  HondaCivic|5|+---------+------------+---+--Prepare data for ignore nulls exampleCREATETABLE person (id INT, name STRING, age INT);INSERTINTO person VALUES(100,'Mary',NULL),(200,'John',30),(300,'Mike',80),(400,'Dan',50);--Select the first row in column ageSELECTFIRST(age)FROM person;+--------------------+|first(age,false)|+--------------------+|NULL|+--------------------+--Get the first row in column `age` ignore nulls,last row in column `id` and sum of column `id`.SELECTFIRST(age IGNORE NULLS),LAST(id),SUM(id)FROM person;+-------------------+------------------+----------+|first(age,true)|last(id,false)|sum(id)|+-------------------+------------------+----------+|30|400|1000|+-------------------+------------------+----------+

标签： spark sql 大数据

本文转载自: https://blog.csdn.net/gabriel_wang_sh/article/details/137514755
版权归原作者 85程序员老王 所有，如有侵权，请联系我们删除。

Spark SQL----GROUP BY子句

Spark SQL----GROUP BY子句

一、描述

二、语法

三、参数

四、例子

发表评论

“Spark SQL----GROUP BY子句”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航