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

问题描述

在不诉诸CTE或子查询的情况下，有什么方法可以使用窗口功能而不具有与GROUP BY不同的摘要级别?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;

文档表示窗口功能可能会导致问题，包括模糊的警告"PARTITION BY并不总是与GROUP BY兼容":错误消息SQL编译错误:...不是有效的按表达式分组，通常表示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.

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