雪花:“SQL 编译错误:...不是有效的按表达式分组" [英] Snowflake: "SQL compilation error:... is not a valid group by expression"

问题描述

不借助 CTE 或子查询，是否有任何方法可以使用具有与 GROUP BY 不同的汇总级别的 Window 功能?COUNT(*) 有效，但如果在 COUNT 中指定了列名或使用了 SUM 函数，则会出现不是有效的表达式组"的查询错误.即使 PARTITION BY 列与 GROUP BY 列相同，错误结果也是如此.

Without resorting to CTEs or a sub-query is there any way to use Window functionality with a different summary level than the GROUP BY? COUNT(*) works, but if a column name is specified in the COUNT or the SUM function is used, the query errors with "is not a valid group by expression". Even if the PARTITION BY columns are the same as the GROUP BY the error results.

注释掉的行将导致查询失败.正是针对这些类型的事情，人们首先想要使用 Window 功能.

The commented out lines will cause the query to fail. It's precisely for these types of things that one would want to use Window functionality in the first place.

create table sales (product_id integer, retail_price real, quantity integer, city varchar, state varchar);
insert into sales (product_id, retail_price, quantity, city, state) values 
(1, 2.00,  1, 'SF', 'CA'),
(1, 2.00,  2, 'SJ', 'CA'),
(2, 5.00,  4, 'SF', 'CA'),
(2, 5.00,  8, 'SJ', 'CA'),
(2, 5.00, 16, 'Miami', 'FL'),
(2, 5.00, 32, 'Orlando', 'FL'),
(2, 5.00, 64, 'SJ', 'PR');

select  city, state
,   count(*) as city_sale_cnt
,   count(*) over ( partition by state) as state_sale_cnt
--  ,   count(product_id) over ( partition by state) as state_sale_cnt2
,   sum(retail_price) as city_price
--  ,   sum(retail_price) over ( partition by state) as state_price

from sales
group by 1,2;

docs 表明 Window 功能可能会导致问题，包括模糊的警告PARTITION BY 并不总是与 GROUP BY 兼容.":错误消息 SQL 编译错误: ... is not a valid group by expression 通常表示 SELECT 语句的project"子句中的不同列的分区方式不同，因此可能产生不同数量的行.

The docs indicate Window functionality might cause problems, including the vague warning "PARTITION BY is not always compatible with GROUP BY.": The error message SQL compilation error: ... is not a valid group by expression is often a sign that different columns in the SELECT statement’s "project" clauses are not partitioned the same way and therefore might produce different numbers of rows.

推荐答案

注释掉的代码不正确.原因是窗口函数是在group by后"解析的，group后没有product_id或retail_price通过.

The commented out code is not correct. The reason is that the window function is parsed "after" the group by, and there is no product_id or retail_price after the group by.

这很容易解决:

select city, state,
       count(*) as city_sale_cnt,
       count(*) over (partition by state) as state_sale_cnt,
       sum(count(product_id)) over (partition by state) as ,
       sum(retail_price) as city_price,
       sum(sum(retail_price)) over ( partition by state) as state_price
from sales
group by 1, 2;

起初，在聚合查询中使用窗口函数看起来有点混乱——嵌套聚合函数看起来很尴尬.我发现，尽管使用过几次语法后很容易习惯它.

At first, using window functions in an aggregation query looks a bit confusing -- the nested aggregation functions look awkward. I find, though that it is pretty easy to get used to the syntax, once you have used it a few times.

这篇关于雪花:“SQL 编译错误:...不是有效的按表达式分组"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！