列的顺序在一个子句组中是否重要？

如果我有两列，一列具有很高的基数，另一列具有很低的基数(值的唯一 #) ，按照哪个顺序分组有关系吗？

这里有一个例子:

select
dimensionName,
dimensionCategory,
sum(someFact)
from SomeFact f
join SomeDim d on f.dimensionKey = d.dimensionKey
group by
d.dimensionName,  -- large number of unique values
d.dimensionCategory -- small number of unique values

有没有重要的情况？

45878 次浏览
小开
最佳答案

No, the order doesn't matter for the GROUP BY clause.

MySQL and SQLite are the only databases I'm aware of that allow you to select columns which are omitted from the group by (non-standard, not portable) but the order doesn't matter there either.

小开

SQL is declarative.

In this case, you have told the optimiser how you want the data grouped and it works out how to do it.

It won't evaluate line by line (procedural) and look at one column first

The main place column order matters is for indexes. col1, col2 is not the same as col2, col1. At all.

小开

There's a legacy, non-standard feature of Microsoft SQL Server called ROLLUP. ROLLUP is an extension to the GROUP BY syntax and when it is used the order of the GROUP BY columns determines which columns should be grouped in the result. ROLLUP is deprecated however. The standard SQL alternative is to use grouping sets, which is supported by SQL Server 2008 and later versions.

小开

Since this has not been mentioned here. The answers above are correct i.e. the order of the columns after the "group by" clause will not affect the correctness of the query (i.e. the sum amount).

However, the order of the rows being retrieved will vary based on the order of the columns specified after the "group by" clause. For example consider Table A with the following rows:

Col1 Col2 Col3
1   xyz 100
2   abc 200
3   xyz 300
3   xyz 400

SELECT *, SUM(Col3) FROM A GROUP BY Col2, Col1 will retrieve rows ordered by the Col2 in ascending order.

Col1 Col2 Col3 sum(Col3)
2   abc 200 200
1   xyz 100 100
3   xyz 300 700

Now change the ordering of column in group by to Col1, Col2. The retrieved rows are ordered asc by Col1.

i.e. select *, sum(Col3) from A group by Col1, Col2

Col1 Col2 Col3 sum(Col3)
1   xyz 100 100
2   abc 200 200
3   xyz 300 700

Note: The the summation amount (i.e. the correctness of the query) remains exactly the same.

小开

If I have two columns, one with very high cardinality and one with very low cardinality (unique # of values), does it matter in which order I group by?

Query-1

SELECT spec_id, catid, spec_display_value, COUNT(*) AS cnt  FROM tbl_product_spec
GROUP BY spec_id, catid, spec_display_value ;

Query-2

SELECT spec_id, catid, spec_display_value, COUNT(*) AS cnt  FROM tbl_product_spec FORCE INDEX(idx_comp_spec_cnt)
GROUP BY catid, spec_id,spec_display_value;

Both are equal , order doesn't work in group by clause.


提交答案