This topic describes how to use Realtime Compute to obtain the latest transaction records.
Background
The order transaction table of an e-commerce merchant may contain operation records about the same order at different times. For example, the order transaction table records a user's operations such as modifying the quantity of the product to be ordered and returning the product. The merchant only wants to obtain valid transaction records. The following section describes how to use Realtime Compute to obtain the latest transaction records.
Procedure
- Syntax
SELECT col1, col2, col3 FROM ( SELECT col1, col2, col3 ROW_NUMBER() OVER ([PARTITION BY col1[, col2..]] ORDER BY col1 [asc|desc][, col2 [asc|desc]...]) AS rownum FROM table_name) WHERE rownum <= N [AND conditions]
Parameter Description ROW_NUMBER()
The OVER window function used to compute the number of a row. The value starts from 1. PARTITION BY col1[, col2..]
The columns by which you want to partition the table. This parameter is optional. ORDER BY col1 [asc|desc][, col2 [asc|desc]...]
The columns by which you want to sort your data. You can specify different orders for multiple columns. - Examples
- Test data
id (BIGINT) TIME (VARCHAR) VALUE (BIGINT) 1 1517898096 5 1 1517887296 44 1 1517872896 32 2 1517872896 10 - Test statements
create table source_table ( id BIGINT, `TIME` VARCHAR, `VALUE` BIGINT )with( type='datahub', endPoint='yourEndpointURL', project='yourProjectName', topic='yourTableName', accessId='yourAccessId', accessKey='yourAccessSecret' ); CREATE TABLE result_table ( id BIGINT, `TIME` VARCHAR, `VALUE` BIGINT ) WITH ( type= 'rds', url = 'yourRDSDatabaseURL', userName = 'yourDatabaseName', password = 'yourDatabasePassword', tableName = 'yourTableName' ); INSERT INTO result_table SELECT id,`TIME`,`VALUE` FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY `TIME` desc) AS rownume FROM source_table ) WHERE rownume <= 1 ;
- Test results
id (BIGINT) TIME (VARCHAR) VALUE (BIGINT) 1 1517898096 5 2 1517872896 10
- Test data
- Key points
SELECT id,`TIME`,`VALUE` FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY `TIME` desc) AS rownume FROM source_table ) WHERE rownume <= 1
Some orders in the order table have multiple records generated at different times. To obtain only the last generated record for each order, use
row_number() OVER (PARTITION BY id ORDER BY TIME DESC)
to group records by order ID and sort the records in each group in descending order of time. The computing result lists the sequential numbers (which are continuous and unique in a group) of sorted records in each group. The first record in each group is the last generated record of the corresponding order.