You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by GitBox <gi...@apache.org> on 2018/11/13 07:12:35 UTC
[GitHub] jeesim2 opened a new issue #6610: IN sub-query not working if
granularity is not same.
jeesim2 opened a new issue #6610: IN sub-query not working if granularity is not same.
URL: https://github.com/apache/incubator-druid/issues/6610
I want to do some SQL like...
## 1. first get top 30 countries across all time
```
SELECT country
FROM
( SELECT country,
count(DISTINCT device_id_count) cnt
FROM "jpl_20181107_ minute_userkey_20g-unique-lite"
GROUP BY country
ORDER BY cnt DESC
LIMIT 30)
```
this returns proper result.
## 2. then use 1's result to in sub-query
## 3. group by hourly
```
SELECT floor(__time TO hour),
country,
count(DISTINCT device_id_count)
FROM "jpl_20181107_ minute_userkey_20g-unique-lite"
WHERE country IN
(SELECT country
FROM
(SELECT country,
count(DISTINCT device_id_count) cnt
FROM "jpl_20181107_ minute_userkey_20g-unique-lite"
GROUP country
ORDER BY cnt DESC
LIMIT 30))
GROUP BY floor(__time TO hour),
country
```
## But, I got error.
> Bad Request: Unknown exception: Cannot build plan for query: select * from (SELECT floor(__time to hour), country, count(DISTINCT device_id_count) FROM ( select * from "jpl_20181107_ minute_userkey_20g-unique-lite" where country in ( select country from ( SELECT country, count(DISTINCT device_id_count) cnt FROM "jpl_20181107_ minute_userkey_20g-unique-lite" GROUP BY country ORDER BY cnt DESC limit 30 ) ) ) GROUP BY floor(__time TO hour), country) limit 5000
It seems like error occur when if subquery's __time granulrity is not to the upper query. is it right?
then How can I do this? or any work around?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org