Returns the first non-null value in a data stream. Use FIRST_VALUE as a window function in an OVER clause, or as an aggregate function with GROUP BY.
Syntax
FIRST_VALUE( T value )
FIRST_VALUE( T value, BIGINT order )Arguments
| Argument | Data type | Description |
|---|---|---|
value | Any | The column or expression to evaluate. Returns NULL if all input values are NULL. All arguments must be of the same data type. |
order | BIGINT | A numeric key that determines which record is considered first. The non-null record with the smallest order value is returned. |
Examples
The following examples use the table T1:
| user_id (VARCHAR) | product_id (BIGINT) | create_time (TIMESTAMP) |
|---|---|---|
| user_001 | 1 | 2023-02-15 10:40:56 |
| user_002 | 2 | 2023-02-15 11:40:56 |
| user_003 | 3 | 2023-02-15 12:40:56 |
| user_001 | 4 | 2023-02-15 13:40:55 |
| user_002 | 5 | 2023-02-15 14:40:56 |
| user_001 | 1 | 2023-02-15 15:40:56 |
| user_003 | 4 | 2023-02-15 16:40:56 |
Find the first product each user purchased
Groups rows by user_id, sorts each group by create_time, and returns the product_id of the earliest row per user.
SELECT `user_id`, create_time, product_id,
FIRST_VALUE(product_id)
OVER (PARTITION BY `user_id` ORDER BY create_time) AS first_product
FROM T1;Result:
| user_id (VARCHAR) | create_time (TIMESTAMP) | product_id (BIGINT) | first_product (BIGINT) |
|---|---|---|---|
| user_001 | 2023-02-15 10:40:56 | 1 | 1 |
| user_001 | 2023-02-15 13:40:56 | 4 | 1 |
| user_001 | 2023-02-15 15:40:56 | 1 | 1 |
| user_002 | 2023-02-15 11:40:56 | 2 | 2 |
| user_002 | 2023-02-15 14:40:56 | 5 | 2 |
| user_003 | 2023-02-15 12:40:56 | 3 | 3 |
| user_003 | 2023-02-15 16:40:56 | 4 | 3 |
Find the first user who purchased each product
Groups rows by product_id and uses UNIX_TIMESTAMP(create_time) as the ordering key to return the user_id of the earliest purchase per product.
SELECT product_id,
FIRST_VALUE(`user_id`, UNIX_TIMESTAMP(create_time)) AS first_user
FROM T1
GROUP BY product_id;Result:
| product_id | first_user |
|---|---|
| 1 | user_001 |
| 2 | user_002 |
| 3 | user_003 |
| 4 | user_001 |
| 5 | user_002 |