vtlim commented on code in PR #17670:
URL: https://github.com/apache/druid/pull/17670#discussion_r1932875061
##########
docs/querying/sql-functions.md:
##########
@@ -1820,6 +1851,36 @@ Returns the rank for a row within a window without gaps.
For example, if two row
* **Syntax**: `DENSE_RANK()`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the dense rank by airline for flights from two
airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ DENSE_RANK() OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "dense_rank"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
Review Comment:
```suggestion
GROUP BY 1, 2, 3
```
##########
docs/querying/sql-functions.md:
##########
@@ -2869,6 +2990,38 @@ Returns the value evaluated for the expression for the
last row within the windo
* **Syntax**: `LAST_VALUE(expr)`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the last airline name in the window for flights
for two airports on a single day.
+Note that the RANGE BETWEEN clause defines the window frame between the
current row and the final row in the window instead of the default of RANGE
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when using ORDER BY.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ LAST_VALUE("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC
+ RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS "last_value"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
Review Comment:
```suggestion
GROUP BY 1, 2, 3
```
##########
docs/querying/sql-functions.md:
##########
@@ -2296,6 +2357,36 @@ Returns the value evaluated for the expression for the
first row within the wind
* **Syntax**: `FIRST_VALUE(expr)`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the name of the first airline in the window of
flights by airline for two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ FIRST_VALUE("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "frist_val"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
Review Comment:
```suggestion
GROUP BY 1, 2, 3
```
##########
docs/querying/sql-functions.md:
##########
@@ -3859,6 +4072,37 @@ Returns the relative rank of the row calculated as a
percentage according to the
* **Syntax**: `PERCENT_RANK()`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the percent rank within the window for flights
by airline from two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ PERCENT_RANK() OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "pct_rank"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
Review Comment:
```suggestion
GROUP BY 1, 2, 3
```
##########
docs/querying/sql-functions.md:
##########
@@ -3754,6 +3937,36 @@ Divides the rows within a window as evenly as possible
into the number of tiles,
* **Syntax**: `NTILE(tiles)`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the results for flights by airline from two
airports on a single day divided into 3 tiles.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ NTILE(3) OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "ntile"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
Review Comment:
```suggestion
GROUP BY 1, 2, 3
```
##########
docs/querying/sql-functions.md:
##########
@@ -2860,6 +2951,36 @@ If you do not supply an `offset`, returns the value
evaluated at the row precedi
* **Syntax**: `LAG(expr[, offset])`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the preceding airline in the window for flights
by airline from two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ LAG("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "lag"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
Review Comment:
```suggestion
GROUP BY 1, 2, 3
```
##########
docs/querying/sql-functions.md:
##########
@@ -3944,6 +4188,36 @@ Returns the rank with gaps for a row within a window.
For example, if two rows t
* **Syntax**: `RANK()`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the rank within the window for flights by
airline from two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ RANK() OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "rank"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
Review Comment:
```suggestion
GROUP BY 1, 2, 3
```
##########
docs/querying/sql-functions.md:
##########
@@ -3944,6 +4188,36 @@ Returns the rank with gaps for a row within a window.
For example, if two rows t
* **Syntax**: `RANK()`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the rank within the window for flights by
airline from two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ RANK() OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "rank"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
+```
+
+Returns the following:
+
+| `flight_day` | `airport` | `airline` | `lead` | `rank` |
Review Comment:
```suggestion
| `flight_day` | `airport` | `airline` | `num_flights` | `rank` |
```
##########
docs/querying/sql-functions.md:
##########
@@ -1820,6 +1851,36 @@ Returns the rank for a row within a window without gaps.
For example, if two row
* **Syntax**: `DENSE_RANK()`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the dense rank by airline for flights from two
airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ DENSE_RANK() OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "dense_rank"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
+```
+
+Returns the following:
+
+| `flight_day` | `airport` | `airline` | `num_flights` | `dense_rank` |
+| --- | --- | --- | --- | ---|
+| `2005-11-01T00:00:00.000Z` | `KOA` | `HA` | `11` | `1` |
+| `2005-11-01T00:00:00.000Z` | `KOA` | `UA` | `4` | `2` |
+| `2005-11-01T00:00:00.000Z` | `KOA` | `AA` | `1` | `3` |
+| `2005-11-01T00:00:00.000Z` | `KOA` | `NW` | `1` | `3` |
+| `2005-11-01T00:00:00.000Z` | `LIH` | `HA` | `15` | `1` |
+| `2005-11-01T00:00:00.000Z` | `LIH` | `AA` | `2` | `2`|
+| `2005-11-01T00:00:00.000Z` | `LIH` | `UA` | `2` | `3` |
Review Comment:
```suggestion
| `2005-11-01T00:00:00.000Z` | `LIH` | `UA` | `2` | `2` |
```
check this result?
##########
docs/querying/sql-functions.md:
##########
@@ -4176,6 +4450,37 @@ Returns the number of the row within the window starting
from 1.
* **Syntax**: `ROW_NUMBER()`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the row number within the window for flights by
airline from two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
Review Comment:
```suggestion
SELECT FLOOR("__time" TO DAY) AS "flight_day",
```
##########
docs/querying/sql-functions.md:
##########
@@ -2942,6 +3095,36 @@ If you do not supply an `offset`, returns the value
evaluated at the row followi
* **Syntax**: `LEAD(expr[, offset])`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the subsequent value for an airline in the
window for flights from two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ LEAD("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "lead"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
+```
+
+Returns the following:
+
+| `flight_day` | `airport` | `airline` | `lead` | `` |
Review Comment:
```suggestion
| `flight_day` | `airport` | `airline` | `num_flights ` | `lead` |
```
##########
docs/querying/sql-functions.md:
##########
@@ -2296,6 +2357,36 @@ Returns the value evaluated for the expression for the
first row within the wind
* **Syntax**: `FIRST_VALUE(expr)`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the name of the first airline in the window of
flights by airline for two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ FIRST_VALUE("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "frist_val"
Review Comment:
```suggestion
FIRST_VALUE("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "first_val"
```
##########
docs/querying/sql-functions.md:
##########
@@ -2942,6 +3095,36 @@ If you do not supply an `offset`, returns the value
evaluated at the row followi
* **Syntax**: `LEAD(expr[, offset])`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the subsequent value for an airline in the
window for flights from two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ LEAD("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "lead"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
Review Comment:
```suggestion
GROUP BY 1, 2, 3
```
##########
docs/querying/sql-functions.md:
##########
@@ -4176,6 +4450,37 @@ Returns the number of the row within the window starting
from 1.
* **Syntax**: `ROW_NUMBER()`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the row number within the window for flights by
airline from two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ ROW_NUMBER() OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "row_num"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
Review Comment:
```suggestion
GROUP BY 1, 2, 3
```
##########
docs/querying/sql-functions.md:
##########
@@ -2869,6 +2990,38 @@ Returns the value evaluated for the expression for the
last row within the windo
* **Syntax**: `LAST_VALUE(expr)`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the last airline name in the window for flights
for two airports on a single day.
+Note that the RANGE BETWEEN clause defines the window frame between the
current row and the final row in the window instead of the default of RANGE
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when using ORDER BY.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ LAST_VALUE("Reporting_Airline") OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC
+ RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS "last_value"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
+```
+
+Returns the following:
+
+| `flight_day` | `airport` | `airline` | `num_flights` | `` |
Review Comment:
```suggestion
| `flight_day` | `airport` | `airline` | `num_flights` | `last_value` |
```
##########
docs/querying/sql-functions.md:
##########
@@ -4176,6 +4450,37 @@ Returns the number of the row within the window starting
from 1.
* **Syntax**: `ROW_NUMBER()`
* **Function type:** Window
+<details><summary>Example</summary>
+
+The following example returns the row number within the window for flights by
airline from two airports on a single day.
+
+```sql
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+SELECT FLOOR("__time" TO DAY) AS "flight_day",
+ "Origin" AS "airport",
+ "Reporting_Airline" as "airline",
+ COUNT("Flight_Number_Reporting_Airline") as "num_flights",
+ ROW_NUMBER() OVER (PARTITION BY "Origin" ORDER BY
COUNT("Flight_Number_Reporting_Airline") DESC) AS "row_num"
+FROM "flight-carriers"
+WHERE FLOOR("__time" TO DAY) = '2005-11-01'
+ AND "Origin" IN ('KOA', 'LIH')
+GROUP BY 2, 3, 1
+```
+
+Returns the following:
+
+| `flight_day` | `airport` | `airline` | `lead` | `row_num` |
Review Comment:
```suggestion
| `flight_day` | `airport` | `airline` | `num_flights` | `row_num` |
```
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
