英文:
Snowflake SQL group-by behaving differently depending whether columns are referenced by position or alias
问题
以下是翻译好的部分：
我正在尝试理解为什么在Snowflake中使用group by函数时，根据我如何引用group-by字段，会产生不同的结果。以下是两个查询，我相信它们应该产生相同的结果，但实际上却没有：
使用显式字段别名引用的查询：
select
hash('SHA2_256', CONCAT(field1,field2,field3,field4)) as hash
,field1
,field2
,field3
,field4
,count(*) as count
from <table>
where
<一些筛选条件>
group by hash, field1, field2, field3, field4;
使用位置引用字段的查询：
select
hash('SHA2_256', CONCAT(field1,field2,field3,field4)) as hash
,field1
,field2
,field3
,field4
,count(*) as count
from <table>
where
<与上述相同的筛选条件>
group by 1,2,3,4,5;
第一个查询产生了显著更多的记录，这可能表明它没有应用在第二个查询中应用的某个分组字段，但根据snowflake文档，我真的相信它们应该是相同的。这两者有何不同？
英文:
I am trying to understand why the group by function is yielding different results in snowflake depending on how I reference the group-by fields. Here are two Queries that I believe should yield the same result, but do NOT:
Query using explicit field alias references:
select
hash('SHA2_256', CONCAT(field1,field2,field3,field4)) as hash
,field1
,field2
,field3
,field4
,count(*) as count
from <table>
where
<some filters>
group by hash, field1, field2, field3, field4;
Query using positional references to fields:
select
hash('SHA2_256', CONCAT(field1,field2,field3,field4)) as hash
,field1
,field2
,field3
,field4
,count(*) as count
from <table>
where
<same filters as above>
group by 1,2,3,4,5;
The first query yields significantly more records, suggesting maybe it isn't applying a grouping field that is being applied in the second query, but based on the snowflake docs I really believe they should be the same. How are these two different?
答案1
得分: 2
以下是翻译的部分：
-
The clue is that the aliased expression
hashdoes not overshadow existing columns so:
提示是，别名为hash的表达式不会遮盖现有的列，因此： -
is
是 -
which is different than:
与之不同的是：
英文:
The clue is that the aliased expression hash does not overshadow existing columns so:
select
hash('SHA2_256', CONCAT(field1,field2,field3,field4)) as hash
,field1
,field2
,field3
,field4
,count(*) as count
from <table>
where <some filters>
group by hash, field1, field2, field3, field4;
is
select
hash('SHA2_256', CONCAT(field1,field2,field3,field4)) as hash
,field1
,field2
,field3
,field4
,count(*) as count
from <table>
where <some filters>
group by <table>.hash, field1, field2, field3, field4;
which is different than:
select
hash('SHA2_256', CONCAT(field1,field2,field3,field4)) as hash
,field1
,field2
,field3
,field4
,count(*) as count
from <table>
where <same filters as above>
group by 1,2,3,4,5;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论