huaxingao commented on a change in pull request #27284: [SPARK-30575][DOC]
Document HAVING Clause of SELECT statement in SQL Reference
URL: https://github.com/apache/spark/pull/27284#discussion_r368357508
##########
File path: docs/sql-ref-syntax-qry-select-having.md
##########
@@ -18,5 +18,101 @@ license: |
See the License for the specific language governing permissions and
limitations under the License.
---
+The <code>HAVING</code> clause is used to filter the results produced by
+<code>GROUP BY</code> based on the specified condition. It is often used
+in the conjunction with a <code>GROUP BY</code> clause.
-**This page is under construction**
+### Syntax
+{% highlight sql %}
+HAVING boolean_expression
+{% endhighlight %}
+
+### Parameters
+<dl>
+ <dt><code><em>boolean_expression</em></code></dt>
+ <dd>
+ Specifies any expression that evaluates to a result type
<code>boolean</code>. Two or
+ more expressions may be combined together using comparision operators
+ ('>', '>=', ‘>’, ‘>=’, ‘=’, ‘<’ and ‘<=’, '<=>') and logical operators
(AND, NOT, OR).<br><br>
+
+ <b>Note</b><br>
+ The expressions specified in the <code>HAVING</code> clause can only refer
to:
+ <ol>
+ <li>Constants</li>
+ <li>Expressions that appear in GROUP BY</li>
+ <li>Aggregate functions</li>
+ </ol>
+ </dd>
+</dl>
+
+### Examples
+{% highlight sql %}
+CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);
+INSERT INTO dealer VALUES (100, 'Fremont', 'Honda Civic', 10),
+ (100, 'Fremont', 'Honda Accord', 15),
+ (100, 'Fremont', 'Honda CRV', 7),
+ (200, 'Dublin', 'Honda Civic', 20),
+ (200, 'Dublin', 'Honda Accord', 10),
+ (200, 'Dublin', 'Honda CRV', 3),
+ (300, 'San Jose', 'Honda Civic', 5),
+ (300, 'San Jose', 'Honda Accord', 8);
+
+-- `HAVING` clause referring to column in `GROUP BY`.
+SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING city =
'Fremont';
+
+ +-------+---+
+ |city |sum|
+ +-------+---+
+ |Fremont|32 |
+ +-------+---+
+
+-- `HAVING` clause referring to aggregate function.
+SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING
sum(quantity) > 15;
+
+ +-------+---+
+ | city|sum|
+ +-------+---+
+ | Dublin| 33|
+ |Fremont| 32|
+ +-------+---+
+
+-- `HAVING` clause referring to aggregate function by its alias.
+SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum > 15;
+
+ +-------+---+
+ | city|sum|
+ +-------+---+
+ | Dublin| 33|
+ |Fremont| 32|
+ +-------+---+
+
+-- `HAVING` clause referring to a different aggregate function than what is
present in
+-- `SELECT` list.
+SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING
max(quantity) > 15;
+
+ +------+---+
+ |city |sum|
+ +------+---+
+ |Dublin|33 |
+ +------+---+
+
+-- `HAVING` clause referring to constant expression.
+SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING 1 > 0 ORDER
BY city;
+
+ +--------+---+
+ | city|sum|
+ +--------+---+
+ | Dublin| 33|
+ | Fremont| 32|
+ |San Jose| 13|
+ +--------+---+
+
+-- `HAVING` clause without a `GROUP BY` clause.
+SELECT sum(quantity) AS sum FROM dealer HAVING sum(quantity) > 10;
Review comment:
there is an extra space between ```SELECT``` and ```sum```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
