[
https://issues.apache.org/jira/browse/DRILL-1248?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16709563#comment-16709563
]
Bridget Bevens commented on DRILL-1248:
---------------------------------------
Hi [~arina],
For this JIRA, I'm planning to add the following content to this page:
https://drill.apache.org/docs/group-by-clause/
Starting in Drill 1.15, Drill supports aliases in the GROUP BY clause.
Example:
use cp;
+-------+---------------------------------+
| ok | summary |
+-------+---------------------------------+
| true | Default schema changed to [cp] |
+-------+---------------------------------+
1 row selected (0.081 seconds)
SELECT EXTRACT(year FROM hire_date) AS yrs_hired FROM `employee.json` GROUP BY
yrs_hired;
+------------+
| yrs_hired |
+------------+
| 1994 |
| 1998 |
| 1996 |
| 1995 |
| 1997 |
| 1993 |
+------------+
6 rows selected (0.18 seconds)
0: jdbc:drill:zk=local>
Please let me know if I need to change anything.
Thanks,
Bridget
> Add support for using aliases in group by
> -----------------------------------------
>
> Key: DRILL-1248
> URL: https://issues.apache.org/jira/browse/DRILL-1248
> Project: Apache Drill
> Issue Type: Improvement
> Components: SQL Parser
> Reporter: Jim Scott
> Assignee: Arina Ielchiieva
> Priority: Major
> Labels: doc-impacting, ready-to-commit
> Fix For: 1.15.0
>
>
> when I select using a function and alias the resultant function value it
> won't parse properly saying the alias is ambiguous. I know that this is a
> debatable / questionable topic, but with this engine being so flexible it
> seems that in order to support all of the formatting, casting, etc.. that
> will likely occur having the group by support an alias would be a big deal.
> This in my opinion is nothing like an ordinal group by.
> This works:
> select extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a'))
> from BLAH group by extract(year from to_date(crimes.datetime, 'MM/DD/YYYY
> hh:mm:ss a'));
> This doesn't:
> select extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a'))
> as mygroup from BLAH group by mygroup
> *For documentation*
> Positional / named alias are allowed in group by / order by / having clauses:
> {noformat}
> select length(n_name), n_regionkey from cp.`tpch/nation.parquet` group by 1,
> 2;
> select length(n_name) as len, n_regionkey as key from
> cp.`tpch/nation.parquet` group by len, key;
> select length(n_name) as len, count(*) as cnt from cp.`tpch/nation.parquet`
> group by length(n_name) having cnt > 1;
> select n_regionkey, n_name from cp.`tpch/nation.parquet` order by 1, 2;
> select n_regionkey as r, n_name as n from cp.`tpch/nation.parquet` order by
> r, n;
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
