tencent cloud

$0 14-Day TrialExperience EdgeOne for acceleration and security protection!
이 페이지는 현재 영어로만 제공되며 한국어 버전은 곧 제공될 예정입니다. 기다려 주셔서 감사드립니다.
关闭

피드백

Window Functions

마지막 업데이트 시간:2024-01-22 10:52:48
This document introduces the basic syntax and examples of window functions.
A window function calculates the data of multiple rows and returns the calculation result. Unlike GROUP BY, it only appends the calculation result to each row of data and does not merge the rows.

Syntax

window_function (expression) OVER (
[ PARTITION BY part_key ]
[ ORDER BY order_key ]
[ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )

Parameters

Parameter
Description
window_function
Specifies the window value calculation method. Aggregate functions, ranking functions and value functions are supported.
PARTITION BY
Specifies how a window is partitioned.
ORDER BY
Specifies how the rows in each window partition are ordered.
{ ROWS |RANGE } BETWEEN frame_start AND frame_end
Window frames, that is, the data range (rows) used when calculating the value of each row within the window partition. If not specified, it represents all rows within the window partition. Example:
rows between current row and 1 following: The current row and the subsequent row rows between 1 preceding and current row: The current row and the preceding row rows between 1 preceding and 1 following: From the preceding row to the subsequent row (a total of three rows) rows between current row and unbounded following: The current row and all subsequent rows rows between unbounded preceding and current row: The current row and all preceding rows

General Aggregate Functions

All general aggregate functions are supported, such as sum() and avg(), can be used to calculate the statistics of each row of data in window frames.

Ranking Functions

Ranking functions cannot use window frames.
Function
Description
rank()
Returns the rank of each row in a window partition. Rows that have the same field value are assigned the same rank, and therefore ranks may not be consecutive. For example, if two rows have the same rank of 1, the rank of the next row is 3.
dense_rank()
Similar to rank(). The difference is that the ranks in this function are consecutive. For example, if two rows have the same rank of 1, the rank of the next row is 2.
cume_dist()
Returns the cumulative distribution of each value in a window partition, that is, the proportions of rows whose field values are less than or equal to the current field value to the total number of rows in the window partition.
ntile(n)
Divides the rows for a window partition into n groups. If the number of rows in the partition is not divided evenly into n groups, the remaining values are distributed one per group, starting with the first group. For example, if there are 6 rows of data, and they need to be divided into 4 groups, the numbers of each row of data are: 1, 1, 2, 2, 3, 4.
percent_rank()
Calculates the percentage ranking of each row in a window partition. The calculation formula is: (r - 1) / (n - 1), where r is the rank value obtained via rank() and n is the total number of rows in the window partition.
row_number()
Calculates the rank of each row (after ranking based on ranking rules) in a window partition. The ranks are unique and start from 1.

Value Functions

Function
Description
first_value(key)
Returns the first value of key of the window partition.
last_value(key)
Returns the last value of key of the window partition.
nth_value(key, offset)
Returns the value of key in the row at the specified offset of the window partition. Offsets start from 1 and cannot be 0 or negative. If offset is null or exceeds the number of rows in the window partition, null is returned.
lead(key[, offset[, default_value]])
Returns the value of key in the row that is at the specified offset after the current row of the window partition. Offsets start from 0, indicating the current row. offset is 1 by default. If offset is null, null is returned. If the offset row exceeds the window partition, default_value is returned. If default_value is not specified, null is returned.<br />When using this function, you must specify the ranking rule (ORDER BY) within the window partition and cannot use window frames.
lag(key[, offset[, default_value]])
Similar to lead(key[, offset[, default_value]]). The only difference is that this function returns the value at offset rows before the current row.

Example

Example 1. Query the 5 slowest requests and their IDs of each API in the last hour

Select the last hour as the time range and run the following query and analysis statement, where action indicates the API name, timeCost indicates the API response time, and seqId indicates the request ID.
Query and analysis statement
* | select * from (select action,timeCost,seqId,rank() over (partition by action order by timeCost desc) as ranking order by action,ranking,seqId) where ranking<=5 limit 10000
Query and analysis result
action
timeCost
seqId
ranking
ModifyXXX
151
d75427b3-c562-6d7a-354f-469963aab689
1
ModifyXXX
104
add0d353-1099-2c73-e9c9-19ad02480474
2
CreateXXX
1254
c7d591f0-2da6-292c-8abf-98a0716ff8c6
1
CreateXXX
970
d920cf7a-7e7b-524b-68e9-a957c454c328
2
CreateXXX
812
16357f6d-33b3-83ea-0ae3-b1a2233d4858
3
CreateXXX
795
0efdab5e-af5f-4a4a-0618-7961420d17a1
4
CreateXXX
724
fb0481f2-dcfc-9500-cb44-a139b774aceb
5
DescribeXXX
55242
4129dcda-46d7-9213-510e-f58cba29daf5
1
DescribeXXX
17413
e36cdeb0-cbc5-ce2b-dec7-f485818ab6c7
2
DescribeXXX
10171
cd6228f7-4644-ba45-f539-0fce7b09455b
3
DescribeXXX
9475
48b6f6e3-6d08-5a31-cd68-89006a346497
4
DescribeXXX
9337
940b5398-e2ae-9141-801b-b7f0ca548875
5

Example 2. Query the 3-day moving average trend of the application throughput

Select the last 7 days as the query and analysis time range and run the following query and analysis statement, where pv indicates the daily application throughput and avg_pv_3 indicates the application throughput after 3-day moving average.
Query and analysis statement
* | select avg(pv) over(order by analytic_time rows between 2 preceding and current row) as avg_pv_3,pv,analytic_time from (select histogram( cast(__TIMESTAMP__ as timestamp),interval 1 day) as analytic_time, count(*) as pv group by analytic_time order by analytic_time)
Query and analysis result



문의하기

고객의 업무에 전용 서비스를 제공해드립니다.

기술 지원

더 많은 도움이 필요하시면, 티켓을 통해 연락 바랍니다. 티켓 서비스는 연중무휴 24시간 제공됩니다.

연중무휴 24시간 전화 지원
중국 홍콩
+852 800 906 020 (무료)
캐나다
+1 888 605 7930 (무료)
영국
+44 808 196 4551 (무료)
미국
+1 844 606 0804 (무료)
호주
+61 1300 986 386 (무료)
EdgeOne 전화 번호
+852 300 80699
현지 서비스 핫라인은 지속적으로 추가 중입니다