Home » Other » General » Just Pondering (all)
| Just Pondering [message #320945] |
Fri, 16 May 2008 17:11  |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
So I was sitting at work after a few pints at the pub.
I was creating a load of group by statements, and it hit me, why does Oracle need the GROUP BY statement?
select col1, col2, count(1)
from table
group by col1, col2;
Surely Oracle could work out the group by clause. Its always the same as the select clause without the aggregate functions isnt it??
Anyway, just a thought. Any comments?
|
|
|
|
|
|
| Re: Just Pondering [message #321003 is a reply to message #320945] |
Sat, 17 May 2008 12:22  |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
You are missing the point. which it is why its a "general" topic, and not a question in the main topics.
I know its the syntax. But its certainly not the full stop. Syntaxes evolve, thats why we have generations of programming languages, and we are not all writing 1's and 0's.
For example, "decode" was the syntax until someone decided that a case statement would be better. Someone came up with the idea of analytic functions because they didnt want to use group by to get an aggregate.
Some people like to look forward for improvements, not backwards because thats the way its always been done.
The question was just a ponderance on whether the oracle SQL parsing engine could do without it, or whether there is a magic case that means you couldnt work it out (if you were parsing the query).
|
|
|
|
|
|
| Re: Just Pondering [message #321005 is a reply to message #321003] |
Sat, 17 May 2008 12:50 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
YOU miss the point of what is a standard language.
SQL syntax is driven by standard.
"group by" is part of the standard, Oracle follows the standard (when it is defined) and so there is "group by". Full stop. No RDBMS will implement grouping without "group by".
DECODE is NOT part of the standard, it is Oracle proprietary.
CASE is in standard, but was not in the first standard, this is why Oracle introduce DECODE until CASE was defined.
By the way, "group by" is part of SQL syntax, DECODE is a function, functions are not part of the standard, each RDBMS can implement the ones they want.
Regards
Michel
|
|
|
|
| Re: Just Pondering [message #321011 is a reply to message #320945] |
Sat, 17 May 2008 15:40 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
| coleing wrote on Fri, 16 May 2008 15:11 | So I was sitting at work after a few pints at the pub.
I was creating a load of group by statements, and it hit me, why does Oracle need the GROUP BY statement?
select col1, col2, count(1)
from table
group by col1, col2;
Surely Oracle could work out the group by clause. Its always the same as the select clause without the aggregate functions isnt it??
Anyway, just a thought. Any comments?
|
What sort of place do you work where you can have a few pints at the pub (at lunch?) then return to work? Is this standard practice in other countries? Most American businesses expert you to be completely sober while at work.
Interesting ponderings. I have had only caffeinated beverages, but it makes sense to me. It would be a nice enhancement and save a little coding and decrease the likelihood for miscoding if Oracle would just figure the group by clause for you and execute the select statement without it, as if it had been included. However, it would not be a simple task to include that in the parsing. Let us assume that it already divides each select statement and sub-select and so forth into the select clause, from clause, where clause, group by clause, and order by clause. So, it should be a matter of extracting the appropriate columns from the select clause and creating a group by clause from them.
There are exceptions, such as analytic functions. You could have a COUNT (...) OVER (PARTITION BY ... ORDER BY ...) without a group by clause. I started to play with it just a little and it would have to do something like the following for each select statement. I only included two aggregate functions and assumed that the aggregate function would be at the end of the columns list. Obviously, this is just a start and there would be a lot more that would need to be done.
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION add_group_by
2 (p_sql IN VARCHAR2)
3 RETURN VARCHAR2
4 AS
5 v_select VARCHAR2 (32767);
6 v_from VARCHAR2 (32767);
7 v_order_by VARCHAR2 (32767);
8 v_group_by VARCHAR2 (32767);
9 v_sql VARCHAR2 (32767);
10 BEGIN
11 v_select := SUBSTR (p_sql, 1, INSTR (UPPER (p_sql), ' FROM ') - 1);
12 v_from := SUBSTR (p_sql, INSTR (UPPER (p_sql), ' FROM '));
13 IF INSTR (UPPER (v_from), ' ORDER BY ') > 0 THEN
14 v_order_by := SUBSTR (v_from, INSTR (UPPER (v_from), ' ORDER BY '));
15 v_from := SUBSTR (v_from, 1, INSTR (UPPER (v_from), ' ORDER BY '));
16 END IF;
17 IF (INSTR (UPPER (v_select), 'COUNT') > 0 OR INSTR (UPPER (v_select), 'SUM') > 0)
18 AND INSTR (UPPER (v_select), ' OVER ') = 0 THEN
19 v_group_by := SUBSTR (v_select, 8);
20 IF INSTR (UPPER (v_group_by), 'COUNT') > 0 THEN
21 v_group_by := ' GROUP BY ' || SUBSTR (v_group_by, 1, INSTR (UPPER (v_group_by), 'COUNT') - 1);
22 ELSIF INSTR (UPPER (v_group_by), 'SUM') > 0 THEN
23 v_group_by := ' GROUP BY ' || SUBSTR (v_group_by, 1, INSTR (UPPER (v_group_by), 'SUM') - 1);
24 END IF;
25 v_group_by := RTRIM (v_group_by, ', ');
26 END IF;
27 v_sql := v_select || v_from || v_group_by || v_order_by;
28 RETURN v_sql;
29 END add_group_by;
30 /
Function created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT add_group_by ('SELECT deptno, job, COUNT (*) FROM emp WHERE 1 = 1 ORDER BY deptno, job') FROM DUAL
2 /
ADD_GROUP_BY('SELECTDEPTNO,JOB,COUNT(*)FROMEMPWHERE1=1ORDERBYDEPTNO,JOB')
----------------------------------------------------------------------------------------------------
SELECT deptno, job, COUNT (*) FROM emp WHERE 1 = 1 GROUP BY deptno, job ORDER BY deptno, job
SCOTT@orcl_11g>
I don't know what the proper route is to submit such a suggestion as either an ANSI SQL enhancement or Oracle enhancement, but if you are really interested you might pursue it.
|
|
|
|
| Re: Just Pondering [message #321016 is a reply to message #320945] |
Sat, 17 May 2008 17:48 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
"What sort of place do you work where you can have a few pints at the pub (at lunch?) then return to work?"
lol. Its the UK. pubs are very busy on friday lunchtimes over here. Safe to say that in some companies over here also it is not acceptable. Although in quite a lot of places it is encouraged for team building.
Anyway, I could have meant pints of water 
Many thanks for your responses.
Barbara, I think you have proved it could be done with that function. I know its not all singing or all dancing, but the pricipal is there.
For the record, im not on a crusade to get the standard changed, im just wondering why you would put a "group by" clause in a standard if it was not actually required. I was seeing if I was missing some reason where you really need it or you cant work out the logic of the query without it. Considering some the sql ive seen and just how insanely complex it can get, I think working out the group by clause would be within the realms of possibility.
And Michael, Just because soemthing is defined in a "standard", doesnt mean we cant call it into question does it? Thats where updated standards come from.
You did get me on the decode point though 
Ive just been reading through the ANSI-92 syntax, and the <group by> and something I dont quite understand:-
3) For every grouping column, if <collate clause> is specified,
then the data type of the <column reference> shall be character
string. The column descriptor of the corresponding column in the
result has the collating sequence specified in <collate clause>
and the coercibility attribute Explicit.
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Whats a <collate clause> in reference to a group by clause?
[Updated on: Sat, 17 May 2008 17:51] Report message to a moderator |
|
|
|
|
|
| Re: Just Pondering [message #321295 is a reply to message #320945] |
Mon, 19 May 2008 16:20 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
I knew it must have a point to it, otherwise why would it be there, I just couldnt figure out why. Thanks.
Although whether you actually ever get useful output from a query like that is debatable.
But it got me to realise this would work to prove its needed also:-
select col1, col2, col3
from tab1
group by col1, col2, col3
having count(*) > 1;
++ EDIT
Although looking at it again, the having clause could imply the group by in that case.
[Updated on: Mon, 19 May 2008 16:24] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Fri Nov 29 15:27:51 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
