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 Parameters
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 an ALIAS. If no alias provided, the function name is labeled as the field name, such as count() and sum(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 or rows directive, separated by a comma(,).
for|cols GRP_FIELD, ...
Grouping fields with for or cols 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

  1. Return the number of rows.

    pivot count
    
  2. Return the number of rows by each src_ip value.

    pivot count by src_ip
    
  3. 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
    
  4. 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