You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "vtlim (via GitHub)" <gi...@apache.org> on 2025/01/28 21:46:10 UTC
Re: [PR] [Docs] Adds examples for window functions for the SQL Reference (batch23) (druid)
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: 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