pivot
Executes one or more aggregation on each row or column group.
Syntax
pivot [parallel=BOOL] AGGR_FUNC [as ALIAS], ... [by|rows GRP_FIELD, ...] [for|cols GRP_FIELD, ...]
Required Parameter
AGGR_FUNC [as ALIAS], ...
- Pairs of a group function (
AGGR_FUNC
) and optional alias (ALIAS
) as a field name, separated by a comma (,
). It is recommended that you specify anALIAS
. If no alias provided, the function name is labeled as the field name, such ascount()
andsum(sent_pkts)
.
Optional Requirements
parallel=BOOL
- Option to enable parallel processing (default:
f
).t
: Enables processing the query in parallel. The processing speed increases but the order of data is not guaranteed. Avoid using this option in query commands where the order of the data matters.f
: Disables processing the query in parallel.
by|rows GRP_FIELD, ...
- Grouping fields with
by
orrows
directive, separated by a comma(,
). for|cols GRP_FIELD, ...
- Grouping fields with
for
orcols
directive, separated by a comma(,
).
Note
If the 'by|rows' or 'for|cols' clause is not specified, the entire data that comes from the previous query command is aggregated into one group. There is a side effect that is sorted out based on the group field.
Usage
-
Return the number of rows.
pivot count
-
Return the number of rows by each
src_ip
value.pivot count by src_ip
-
Count the number of rows for each protocol field value (e.g., TCP, UDP, ICMP and the like) for the src_ip and dst_ip fields.
pivot count by src_ip, dst_ip for protocol
-
Count the number of rows (count()) and traffic (sum(bytes)) for each protocol field value (e.g., TCP, UDP, and ICMP) for the src_ip and dst_ip fields.
pivot sum(bytes) as bytes, count rows src_ip, dst_ip cols protocol