Returns the first non-null record of a data stream.
Syntax
FIRST_VALUE( T value )
FIRST_VALUE( T value, BIGINT order )Arguments
Argument | Data type | Description |
value | Any data type | A data stream. Note
|
order | BIGINT | The non-null record that has the smallest order value is considered the first non-null record. |
Example
Test data in the T1 table
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
Test code
Test code 1
The code snippet groups data by user_id, sort each group by create_time, and returns the product_id of the first product in each group.
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;Test code 2
The code snippet groups data by product_id, sort each group by create_time, and returns the first user_id of each product.
SELECT product_id,FIRST_VALUE( `user_id`, UNIX_TIMESTAMP(create_time)) AS first_product FROM T1 group by product_id;Test results
Test result 1
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
Test result 2
product_id
first_user
1
user_001
2
user_002
3
user_003
4
user_001
5
user_002