You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "nrao57 (via GitHub)" <gi...@apache.org> on 2024/04/12 18:41:02 UTC
[PR] Adds Druid SQL query examples for the Stats aggregator Native Queries (druid)
nrao57 opened a new pull request, #16277:
URL: https://github.com/apache/druid/pull/16277
Adds Druid SQL query examples for the Stats aggregator Native Queries
<!-- Thanks for trying to help us make Apache Druid be the best it can be! Please fill out as much of the following information as is possible (where relevant, and remove it when irrelevant) to help make the intention and scope of this PR clear in order to ease review. -->
<!-- Please read the doc for contribution (https://github.com/apache/druid/blob/master/CONTRIBUTING.md) before making this PR. Also, once you open a PR, please _avoid using force pushes and rebasing_ since these make it difficult for reviewers to see what you've changed in response to their reviews. See [the 'If your pull request shows conflicts with master' section](https://github.com/apache/druid/blob/master/CONTRIBUTING.md#if-your-pull-request-shows-conflicts-with-master) for more details. -->
Fixes #13148.
<!-- Replace XXXX with the id of the issue fixed in this PR. Remove this section if there is no corresponding issue. Don't reference the issue in the title of this pull-request. -->
<!-- If you are a committer, follow the PR action item checklist for committers:
https://github.com/apache/druid/blob/master/dev/committer-instructions.md#pr-and-issue-action-item-checklist-for-committers. -->
### Description
<!-- Describe the goal of this PR, what problem are you fixing. If there is a corresponding issue (referenced above), it's not necessary to repeat the description here, however, you may choose to keep one summary sentence. -->
This PR adds Druid SQL query examples for the Stats aggregator documents page. Currently, there are only examples for the Native queries.
<!-- Describe your patch: what did you change in code? How did you fix the problem? -->
This PR adds SQL code examples to fix this issue.
<!-- If there are several relatively logically separate changes in this PR, create a mini-section for each of them. For example: -->
<!-- Check the items by putting "x" in the brackets for the done things. Not all of these items apply to every PR. Remove the items which are not done or not relevant to the PR. None of the items from the checklist below are strictly necessary, but it would be very helpful if you at least self-review the PR. -->
This PR has:
- [x] been self-reviewed.
- [x] added comments explaining the "why" and the intent of the code wherever would not be obvious for an unfamiliar reader.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] Adds Druid SQL query examples for the Stats aggregator Native Queries (druid)
Posted by "abhishekrb19 (via GitHub)" <gi...@apache.org>.
abhishekrb19 commented on code in PR #16277:
URL: https://github.com/apache/druid/pull/16277#discussion_r1563058032
##########
docs/development/extensions-core/stats.md:
##########
@@ -125,8 +126,24 @@ To acquire standard deviation from variance, user can use "stddev" post aggregat
}
```
+#### Druid SQL
Review Comment:
It would be preferable to move the SQL query section before the native query section since SQL is more widely used and familiar to users. Same suggestion for all the SQL sections in this page.
##########
docs/development/extensions-core/stats.md:
##########
@@ -181,3 +206,14 @@ To acquire standard deviation from variance, user can use "stddev" post aggregat
]
}
```
+
+#### Druid SQL
+
+```SQL
+SELECT
+ alias,
+ VARIANCE("index") AS index_var
+FROM "testing"
+WHERE TIME_IN_INTERVAL(__time, '2016-03-06T00:00:00/2016-03-06T23:59:59')
Review Comment:
End date is exclusive, so I would think this should be start of the next day? Same for the native query:
```suggestion
WHERE TIME_IN_INTERVAL(__time, '2016-03-06/2016-03-07')
```
##########
docs/development/extensions-core/stats.md:
##########
@@ -154,8 +171,16 @@ To acquire standard deviation from variance, user can use "stddev" post aggregat
}
```
+#### Druid SQL
+
+```SQL
+There is no equivalent SQL for this query.
Review Comment:
Perhaps move this text outside the sql codeblock?
##########
docs/development/extensions-core/stats.md:
##########
@@ -125,8 +126,24 @@ To acquire standard deviation from variance, user can use "stddev" post aggregat
}
```
+#### Druid SQL
+
+```SQL
+SELECT
+ DATE_TRUNC('day', __time),
+ VARIANCE("index_var")
+FROM
+ "testing"
+WHERE
+ TIME_IN_INTERVAL(__time, '2013-03-01T00:00:00.000/2016-03-20T00:00:00.000')
+GROUP BY
+ DATE_TRUNC('day', __time)
Review Comment:
Small simplification:
```suggestion
SELECT
DATE_TRUNC('day', __time),
VARIANCE("index_var") AS index_var
FROM "testing"
WHERE TIME_IN_INTERVAL(__time, '2013-03-01/2016-03-20')
GROUP BY 1
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] Adds Druid SQL query examples for the Stats aggregator Native Queries (druid)
Posted by "abhishekrb19 (via GitHub)" <gi...@apache.org>.
abhishekrb19 commented on code in PR #16277:
URL: https://github.com/apache/druid/pull/16277#discussion_r1563322067
##########
docs/development/extensions-core/stats.md:
##########
@@ -120,13 +132,19 @@ To acquire standard deviation from variance, user can use "stddev" post aggregat
}
],
"intervals": [
- "2016-03-01T00:00:00.000/2013-03-20T00:00:00.000"
+ "2016-03-01/2013-03-20"
]
}
```
### TopN query
+#### Druid SQL
+
+There is no equivalent SQL for this query.
Review Comment:
Looking at this, it's possible to have a topN query -- the groupBy query below with an `ORDER BY` on one dimension and a `LIMIT` clause should plan the query as a `topN`. You could verify this by running `EXPLAIN PLAN FOR`
##########
docs/development/extensions-core/stats.md:
##########
@@ -107,6 +107,18 @@ To acquire standard deviation from variance, user can use "stddev" post aggregat
### Timeseries query
+#### Druid SQL
+
+```SQL
Review Comment:
It will also be nice to include a mention of the SQL functions `VARIANCE` and `STDDEV` in the above section where it provides the native JSON syntaxes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] Adds Druid SQL query examples for the Stats aggregator Native Queries (druid)
Posted by "nrao57 (via GitHub)" <gi...@apache.org>.
nrao57 commented on PR #16277:
URL: https://github.com/apache/druid/pull/16277#issuecomment-2052514001
Hi @abhishekrb19!
I have updated the Pull Request to reflect your suggestions. Please let me know if there is anything else.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] Adds Druid SQL query examples for the Stats aggregator Native Queries (druid)
Posted by "abhishekrb19 (via GitHub)" <gi...@apache.org>.
abhishekrb19 commented on code in PR #16277:
URL: https://github.com/apache/druid/pull/16277#discussion_r1563192124
##########
docs/development/extensions-core/stats.md:
##########
@@ -120,13 +132,33 @@ To acquire standard deviation from variance, user can use "stddev" post aggregat
}
],
"intervals": [
- "2016-03-01T00:00:00.000/2013-03-20T00:00:00.000"
+ "2016-03-01/2013-03-20"
]
}
```
+#### Druid SQL
+
+```SQL
+SELECT
+ DATE_TRUNC('day', __time),
Review Comment:
Should be removed now that it's moved up?
##########
docs/development/extensions-core/stats.md:
##########
@@ -177,7 +228,18 @@ To acquire standard deviation from variance, user can use "stddev" post aggregat
}
],
"intervals": [
- "2016-03-06T00:00:00/2016-03-06T23:59:59"
+ "2016-03-06/2016-03-07"
]
}
```
+
+#### Druid SQL
+
+```SQL
+SELECT
Review Comment:
Same here
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] Adds Druid SQL query examples for the Stats aggregator Native Queries (druid)
Posted by "abhishekrb19 (via GitHub)" <gi...@apache.org>.
abhishekrb19 commented on code in PR #16277:
URL: https://github.com/apache/druid/pull/16277#discussion_r1565107060
##########
docs/development/extensions-core/stats.md:
##########
@@ -56,6 +56,19 @@ Numer. Math, 58 (1991) pp. 583--590
precisely the same across query runs.
:::
+### Variance Aggregator in Druid SQL
Review Comment:
```suggestion
### Variance and Standard Deviation SQL Aggregators
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] Adds Druid SQL query examples for the Stats aggregator Native Queries (druid)
Posted by "nrao57 (via GitHub)" <gi...@apache.org>.
nrao57 commented on PR #16277:
URL: https://github.com/apache/druid/pull/16277#issuecomment-2053762232
I added the TopN Druid SQL query and a table describing the sql variance and stdev functions.
Let me know if anything needs to change.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] Adds Druid SQL query examples for the Stats aggregator Native Queries (druid)
Posted by "nrao57 (via GitHub)" <gi...@apache.org>.
nrao57 commented on PR #16277:
URL: https://github.com/apache/druid/pull/16277#issuecomment-2052539764
My mistake 😅
I removed the old duplicate sections
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] Adds Druid SQL query examples for the Stats aggregator Native Queries (druid)
Posted by "abhishekrb19 (via GitHub)" <gi...@apache.org>.
abhishekrb19 merged PR #16277:
URL: https://github.com/apache/druid/pull/16277
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org