gustavodemorais commented on code in PR #29967:
URL: https://github.com/apache/flink/pull/29967#discussion_r4627297575


##########
docs/content/docs/sql/built-in-functions/process-table-functions.md:
##########
@@ -31,6 +31,7 @@ The built-in process table functions (PTFs) take a table as 
input and return a t
 
|---------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
 | `FROM_CHANGELOG(input => TABLE t [, ...])`              | Converts an 
append-only table with an explicit operation column into a dynamic table. See 
[Changelog Conversion]({{< ref "docs/sql/reference/queries/changelog" 
>}}#from_changelog) for the full list of arguments, semantics, and usage.       
                                                                  |
 | `TO_CHANGELOG(input => TABLE t [, ...])`                | Converts a dynamic 
table into an append-only table with an explicit operation column. See 
[Changelog Conversion]({{< ref "docs/sql/reference/queries/changelog" 
>}}#to_changelog) for the full list of arguments, semantics, and usage.         
                                                                  |
+| `DEDUPLICATE(input => TABLE t PARTITION BY k [, ...])` | Keeps the first or 
the last row per key of an append-only table. See [Deduplication]({{< ref 
"docs/sql/reference/queries/deduplication" >}}#deduplicate) for the full list 
of arguments, semantics, and usage. |

Review Comment:
   Fine for this PR. Let's just remember to drop "of an append-only table" here 
once updating input is supported.



##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
 WHERE row_num = 1
 ```
 
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row 
per key of an append-only table, as long as the state of the key is retained. 
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are 
ordered by arrival or by `sequence_by` columns.

Review Comment:
   I'd drop "Unlike the `ROW_NUMBER()` pattern above, it needs no time 
attribute." to keep the intro short:
   ```suggestion
   The `DEDUPLICATE` process table function (PTF) keeps the first or the last 
row per key of an append-only table, as long as the state of the key is 
retained. Rows are ordered by arrival or by `sequence_by` columns.
   ```



##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
 WHERE row_num = 1
 ```
 
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row 
per key of an append-only table, as long as the state of the key is retained. 
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are 
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+  input => TABLE source_table PARTITION BY key_col[, ...],
+  [keep => 'first' | 'last',]
+  [sequence_by => DESCRIPTOR(col[, ...]),]
+  [state_ttl => INTERVAL '1' HOUR,]
+  [reset_ttl_on_duplicate => true | false,]
+  [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and 
its columns are the key of the output. `ORDER BY` is not supported, use 
`sequence_by` instead. |

Review Comment:
   Let's keep the parameter descriptions lean. Users get a clear error if they 
use `ORDER BY`, so we don't need to mention it here.



##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
 WHERE row_num = 1
 ```
 
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row 
per key of an append-only table, as long as the state of the key is retained. 
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are 
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+  input => TABLE source_table PARTITION BY key_col[, ...],
+  [keep => 'first' | 'last',]
+  [sequence_by => DESCRIPTOR(col[, ...]),]
+  [state_ttl => INTERVAL '1' HOUR,]
+  [reset_ttl_on_duplicate => true | false,]
+  [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and 
its columns are the key of the output. `ORDER BY` is not supported, use 
`sequence_by` instead. |
+| `keep` | No | `'first'` (default) keeps the earliest arrival or the smallest 
`sequence_by` value, `'last'` keeps the latest arrival or the greatest. Must be 
a literal. Arrival order is not deterministic across reruns, use `sequence_by` 
for a repeatable result. |
+| `sequence_by` | No | One or more columns that order the records of a key 
instead of their arrival. The first column decides, later columns break ties, 
and `NULL` orders below every other value. On a tie, the later arrival wins for 
`'last'` and the earlier one for `'first'`. Columns must be numeric, string, 
binary, boolean or date-time, and not part of `PARTITION BY`. |
+| `state_ttl` | No | The minimum time the state of an idle key is retained, in 
processing time, as a non-negative `INTERVAL` literal. If omitted, 
`table.exec.state.ttl` applies, including later changes to it. `INTERVAL '0'` 
retains state indefinitely; the TTL is then left out of the state, so one 
cannot be added later without losing the state. When the state of a key 
expires, its next record is treated as the first one and emits `+I` again, so a 
sink without an upsert key can hold several rows for that key. |

Review Comment:
   This is a lot of detail for one parameter. Could we keep only what a user 
needs to choose a value, e.g. what it does, the default, and that an expired 
key is emitted again?



##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
 WHERE row_num = 1
 ```
 
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row 
per key of an append-only table, as long as the state of the key is retained. 
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are 
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+  input => TABLE source_table PARTITION BY key_col[, ...],
+  [keep => 'first' | 'last',]
+  [sequence_by => DESCRIPTOR(col[, ...]),]
+  [state_ttl => INTERVAL '1' HOUR,]
+  [reset_ttl_on_duplicate => true | false,]
+  [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and 
its columns are the key of the output. `ORDER BY` is not supported, use 
`sequence_by` instead. |
+| `keep` | No | `'first'` (default) keeps the earliest arrival or the smallest 
`sequence_by` value, `'last'` keeps the latest arrival or the greatest. Must be 
a literal. Arrival order is not deterministic across reruns, use `sequence_by` 
for a repeatable result. |
+| `sequence_by` | No | One or more columns that order the records of a key 
instead of their arrival. The first column decides, later columns break ties, 
and `NULL` orders below every other value. On a tie, the later arrival wins for 
`'last'` and the earlier one for `'first'`. Columns must be numeric, string, 
binary, boolean or date-time, and not part of `PARTITION BY`. |
+| `state_ttl` | No | The minimum time the state of an idle key is retained, in 
processing time, as a non-negative `INTERVAL` literal. If omitted, 
`table.exec.state.ttl` applies, including later changes to it. `INTERVAL '0'` 
retains state indefinitely; the TTL is then left out of the state, so one 
cannot be added later without losing the state. When the state of a key 
expires, its next record is treated as the first one and emits `+I` again, so a 
sink without an upsert key can hold several rows for that key. |
+| `reset_ttl_on_duplicate` | No | Whether a record that is dropped refreshes 
the TTL of its key. Defaults to `true`. Has no effect without a TTL. |
+| `uid` | No | A unique identifier for the PTF invocation. Defaults to 
`DEDUPLICATE`. See [Multiple Invocations]({{< ref 
"docs/sql/reference/queries/changelog" >}}#multiple-invocations). |
+
+System arguments such as `uid` come last.

Review Comment:
   Can we drop this? It's a general PTF rule, not specific to `DEDUPLICATE`.



##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
 WHERE row_num = 1
 ```
 
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row 
per key of an append-only table, as long as the state of the key is retained. 
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are 
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+  input => TABLE source_table PARTITION BY key_col[, ...],
+  [keep => 'first' | 'last',]
+  [sequence_by => DESCRIPTOR(col[, ...]),]
+  [state_ttl => INTERVAL '1' HOUR,]
+  [reset_ttl_on_duplicate => true | false,]
+  [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and 
its columns are the key of the output. `ORDER BY` is not supported, use 
`sequence_by` instead. |
+| `keep` | No | `'first'` (default) keeps the earliest arrival or the smallest 
`sequence_by` value, `'last'` keeps the latest arrival or the greatest. Must be 
a literal. Arrival order is not deterministic across reruns, use `sequence_by` 
for a repeatable result. |
+| `sequence_by` | No | One or more columns that order the records of a key 
instead of their arrival. The first column decides, later columns break ties, 
and `NULL` orders below every other value. On a tie, the later arrival wins for 
`'last'` and the earlier one for `'first'`. Columns must be numeric, string, 
binary, boolean or date-time, and not part of `PARTITION BY`. |
+| `state_ttl` | No | The minimum time the state of an idle key is retained, in 
processing time, as a non-negative `INTERVAL` literal. If omitted, 
`table.exec.state.ttl` applies, including later changes to it. `INTERVAL '0'` 
retains state indefinitely; the TTL is then left out of the state, so one 
cannot be added later without losing the state. When the state of a key 
expires, its next record is treated as the first one and emits `+I` again, so a 
sink without an upsert key can hold several rows for that key. |
+| `reset_ttl_on_duplicate` | No | Whether a record that is dropped refreshes 
the TTL of its key. Defaults to `true`. Has no effect without a TTL. |
+| `uid` | No | A unique identifier for the PTF invocation. Defaults to 
`DEDUPLICATE`. See [Multiple Invocations]({{< ref 
"docs/sql/reference/queries/changelog" >}}#multiple-invocations). |
+
+System arguments such as `uid` come last.
+
+### Output
+
+The output columns are the `PARTITION BY` columns, in the order listed, 
followed by the remaining input columns. Time attributes are emitted as regular 
timestamps.
+
+The result is an updating table whose key is the `PARTITION BY` columns. The 
first record of a key emits `+I`. A record that replaces the kept one emits 
`+U`, preceded by `-U` if the consumer needs retractions. A record that equals 
the kept one, or that does not replace it, emits nothing. With `keep => 
'first'` and no `sequence_by`, only `+I` is emitted. The output is currently 
declared as updating in every case, so a sink that cannot consume updates is 
rejected at planning time. See [Updates and Changelogs]({{< ref 
"docs/dev/table/functions/ptfs" >}}#updates-and-changelogs) for the meaning of 
the change flags.
+
+### Examples
+
+#### Keep the first or the last record
+
+```sql
+CREATE TABLE user_events (user_id STRING, event STRING) WITH (...);
+-- (u1, login), (u2, login), (u1, click)
+
+SELECT * FROM DEDUPLICATE(
+  input => TABLE user_events PARTITION BY user_id);
+
+-- +I[u1, login]
+-- +I[u2, login]
+
+SELECT * FROM DEDUPLICATE(
+  input => TABLE user_events PARTITION BY user_id,
+  keep => 'last');
+
+-- +I[u1, login]
+-- +I[u2, login]
+-- +U[u1, click]     (an upsert consumer; a retract consumer also gets -U[u1, 
login] before it)

Review Comment:
   Same idea for the examples: showing the resulting table (or the final rows) 
is easier to follow than the change flags, and we can drop the upsert/retract 
consumer note.



##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
 WHERE row_num = 1
 ```
 
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row 
per key of an append-only table, as long as the state of the key is retained. 
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are 
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+  input => TABLE source_table PARTITION BY key_col[, ...],
+  [keep => 'first' | 'last',]
+  [sequence_by => DESCRIPTOR(col[, ...]),]
+  [state_ttl => INTERVAL '1' HOUR,]
+  [reset_ttl_on_duplicate => true | false,]
+  [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and 
its columns are the key of the output. `ORDER BY` is not supported, use 
`sequence_by` instead. |
+| `keep` | No | `'first'` (default) keeps the earliest arrival or the smallest 
`sequence_by` value, `'last'` keeps the latest arrival or the greatest. Must be 
a literal. Arrival order is not deterministic across reruns, use `sequence_by` 
for a repeatable result. |
+| `sequence_by` | No | One or more columns that order the records of a key 
instead of their arrival. The first column decides, later columns break ties, 
and `NULL` orders below every other value. On a tie, the later arrival wins for 
`'last'` and the earlier one for `'first'`. Columns must be numeric, string, 
binary, boolean or date-time, and not part of `PARTITION BY`. |
+| `state_ttl` | No | The minimum time the state of an idle key is retained, in 
processing time, as a non-negative `INTERVAL` literal. If omitted, 
`table.exec.state.ttl` applies, including later changes to it. `INTERVAL '0'` 
retains state indefinitely; the TTL is then left out of the state, so one 
cannot be added later without losing the state. When the state of a key 
expires, its next record is treated as the first one and emits `+I` again, so a 
sink without an upsert key can hold several rows for that key. |
+| `reset_ttl_on_duplicate` | No | Whether a record that is dropped refreshes 
the TTL of its key. Defaults to `true`. Has no effect without a TTL. |
+| `uid` | No | A unique identifier for the PTF invocation. Defaults to 
`DEDUPLICATE`. See [Multiple Invocations]({{< ref 
"docs/sql/reference/queries/changelog" >}}#multiple-invocations). |
+
+System arguments such as `uid` come last.
+
+### Output
+
+The output columns are the `PARTITION BY` columns, in the order listed, 
followed by the remaining input columns. Time attributes are emitted as regular 
timestamps.
+
+The result is an updating table whose key is the `PARTITION BY` columns. The 
first record of a key emits `+I`. A record that replaces the kept one emits 
`+U`, preceded by `-U` if the consumer needs retractions. A record that equals 
the kept one, or that does not replace it, emits nothing. With `keep => 
'first'` and no `sequence_by`, only `+I` is emitted. The output is currently 
declared as updating in every case, so a sink that cannot consume updates is 
rejected at planning time. See [Updates and Changelogs]({{< ref 
"docs/dev/table/functions/ptfs" >}}#updates-and-changelogs) for the meaning of 
the change flags.

Review Comment:
   I'd like to propose an idea: I'd describe the result in table terms and keep 
the change flags out of the main text, like in the TO_APPEND docs (#30016). For 
example: "The result has one row per key. When a later row replaces the kept 
one, the row of that key is updated, so the result must be written to a table 
that accepts updates, e.g. one with a primary key on the `PARTITION BY` 
columns." The `+I`/`+U` details could go into a short hint box, if at all.



##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
 WHERE row_num = 1
 ```
 
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row 
per key of an append-only table, as long as the state of the key is retained. 
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are 
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+  input => TABLE source_table PARTITION BY key_col[, ...],
+  [keep => 'first' | 'last',]
+  [sequence_by => DESCRIPTOR(col[, ...]),]
+  [state_ttl => INTERVAL '1' HOUR,]
+  [reset_ttl_on_duplicate => true | false,]
+  [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and 
its columns are the key of the output. `ORDER BY` is not supported, use 
`sequence_by` instead. |
+| `keep` | No | `'first'` (default) keeps the earliest arrival or the smallest 
`sequence_by` value, `'last'` keeps the latest arrival or the greatest. Must be 
a literal. Arrival order is not deterministic across reruns, use `sequence_by` 
for a repeatable result. |
+| `sequence_by` | No | One or more columns that order the records of a key 
instead of their arrival. The first column decides, later columns break ties, 
and `NULL` orders below every other value. On a tie, the later arrival wins for 
`'last'` and the earlier one for `'first'`. Columns must be numeric, string, 
binary, boolean or date-time, and not part of `PARTITION BY`. |
+| `state_ttl` | No | The minimum time the state of an idle key is retained, in 
processing time, as a non-negative `INTERVAL` literal. If omitted, 
`table.exec.state.ttl` applies, including later changes to it. `INTERVAL '0'` 
retains state indefinitely; the TTL is then left out of the state, so one 
cannot be added later without losing the state. When the state of a key 
expires, its next record is treated as the first one and emits `+I` again, so a 
sink without an upsert key can hold several rows for that key. |
+| `reset_ttl_on_duplicate` | No | Whether a record that is dropped refreshes 
the TTL of its key. Defaults to `true`. Has no effect without a TTL. |
+| `uid` | No | A unique identifier for the PTF invocation. Defaults to 
`DEDUPLICATE`. See [Multiple Invocations]({{< ref 
"docs/sql/reference/queries/changelog" >}}#multiple-invocations). |
+
+System arguments such as `uid` come last.
+
+### Output
+
+The output columns are the `PARTITION BY` columns, in the order listed, 
followed by the remaining input columns. Time attributes are emitted as regular 
timestamps.
+
+The result is an updating table whose key is the `PARTITION BY` columns. The 
first record of a key emits `+I`. A record that replaces the kept one emits 
`+U`, preceded by `-U` if the consumer needs retractions. A record that equals 
the kept one, or that does not replace it, emits nothing. With `keep => 
'first'` and no `sequence_by`, only `+I` is emitted. The output is currently 
declared as updating in every case, so a sink that cannot consume updates is 
rejected at planning time. See [Updates and Changelogs]({{< ref 
"docs/dev/table/functions/ptfs" >}}#updates-and-changelogs) for the meaning of 
the change flags.
+
+### Examples
+
+#### Keep the first or the last record
+
+```sql
+CREATE TABLE user_events (user_id STRING, event STRING) WITH (...);
+-- (u1, login), (u2, login), (u1, click)
+
+SELECT * FROM DEDUPLICATE(
+  input => TABLE user_events PARTITION BY user_id);
+
+-- +I[u1, login]
+-- +I[u2, login]
+
+SELECT * FROM DEDUPLICATE(
+  input => TABLE user_events PARTITION BY user_id,
+  keep => 'last');
+
+-- +I[u1, login]
+-- +I[u2, login]
+-- +U[u1, click]     (an upsert consumer; a retract consumer also gets -U[u1, 
login] before it)
+```
+
+#### Order by a column
+
+```sql
+CREATE TABLE customer_changes (customer_id STRING, city STRING, lsn BIGINT) 
WITH (...);
+-- (c1, Berlin, 100), (c1, Munich, 120), (c1, Hamburg, 110)
+
+SELECT * FROM DEDUPLICATE(
+  input => TABLE customer_changes PARTITION BY customer_id,
+  keep => 'last',
+  sequence_by => DESCRIPTOR(lsn),
+  state_ttl => INTERVAL '1' DAY);
+
+-- +I[c1, Berlin, 100]
+-- +U[c1, Munich, 120]     (The entry (c1, Hamburg, 110) is older than the 
kept, row -> dropped)

Review Comment:
   Small wording fix:
   ```suggestion
   -- +U[c1, Munich, 120]     (Hamburg has a smaller lsn than the kept row, so 
it is dropped)
   ```



-- 
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]

Reply via email to