Skip to content

chart.md

Latest commit

 

History

History
200 lines (158 loc) · 7.67 KB

chart.md

File metadata and controls

200 lines (158 loc) · 7.67 KB

chart

Description

The chart command transforms search results by applying a statistical aggregation function and optionally grouping the data by one or two fields. The results are suitable for visualization as a two-dimension chart when grouping by two fields, where unique values in the second group key can be pivoted to column names.

Syntax

chart [limit=(top|bottom) <number>] [useother=<boolean>] [usenull=<boolean>] [nullstr=<string>] [otherstr=<string>] <aggregation_function> [ by <row_split> <column_split> ] | [over <row_split> ] [ by <column_split>]

  • limit: optional. Specifies the number of categories to display when using column split. Each unique value in the column split field represents a category. Default: top10.
    • Syntax: limit=(top|bottom)<number> or limit=<number> (defaults to top)
    • When limit=K is set, the top or bottom K categories from the column split field are retained; the remaining categories are grouped into an "OTHER" category if useother is not set to false.
    • Set limit to 0 to show all categories without any limit.
    • Use limit=topK or limit=bottomK to specify whether to retain the top or bottom K column categories. The ranking is based on the sum of aggregated values for each column category. For example, chart limit=top3 count() by region, product keeps the 3 products with the highest total counts across all regions. If not specified, top is used by default.
    • Only applies when column split is present (by 2 fields or over...by... coexists).
  • useother: optional. Controls whether to create an "OTHER" category for categories beyond the limit. Default: true
    • When set to false, only the top/bottom N categories (based on limit) are shown without an "OTHER" category.
    • When set to true, categories beyond the limit are grouped into an "OTHER" category.
    • Only applies when using column split and when there are more categories than the limit.
  • usenull: optional. Controls whether to group events without a column split (i.e. whose column split is null) into a separate "NULL" category. Default: true
    • usenull only applies to column split.
    • Row split should always be non-null value. Documents with null values in row split will be ignored.
    • When usenull=false, events with a null column split are excluded from results.
    • When usenull=true, events with a null column split are grouped into a separate "NULL" category.
  • nullstr: optional. Specifies the category name for rows that do not contain the column split value. Default: "NULL"
    • Only applies when usenull is set to true.
  • otherstr: optional. Specifies the category name for the "OTHER" category. Default: "OTHER"
    • Only applies when useother is set to true and there are values beyond the limit.
  • aggregation_function: mandatory. The aggregation function to apply to the data.
    • Currently, only a single aggregation function is supported.
    • Available functions: aggregation functions supported by the stats command.
  • by: optional. Groups the results by either one field (row split) or two fields (row split and column split)
    • limit, useother, and usenull apply to the column split
    • Results are returned as individual rows for each combination.
    • If not specified, the aggregation is performed across all documents.
  • over...by...: optional. Alternative syntax for grouping by multiple fields.
    • over <row_split> by <column_split> groups the results by both fields.
    • Using over alone on one field is equivalent to by <row-split>

Notes

  • The fields generated by column splitting are converted to strings so that they are compatible with nullstr and otherstr and can be used as column names once pivoted.
  • Documents with null values in fields used by the aggregation function are excluded from aggregation. For example, in chart avg(balance) over deptno, group, documents where balance is null are excluded from the average calculation.
  • The aggregation metric appears as the last column in the result. Result columns are ordered as: [row-split] [column-split] [aggregation-metrics].

Example 1: Basic aggregation without grouping

This example calculates the average balance across all accounts.

source=accounts
| chart avg(balance)

Expected output:

fetched rows / total rows = 1/1
+--------------+
| avg(balance) |
|--------------|
| 20482.25     |
+--------------+

Example 2: Group by single field

This example calculates the count of accounts grouped by gender.

source=accounts
| chart count() by gender

Expected output:

fetched rows / total rows = 2/2
+--------+---------+
| gender | count() |
|--------+---------|
| F      | 1       |
| M      | 3       |
+--------+---------+

Example 3: Using over and by for multiple field grouping

This example shows average balance grouped by both gender and age fields. Note that the age column in the result is converted to string type.

source=accounts
| chart avg(balance) over gender by age

Expected output:

fetched rows / total rows = 4/4
+--------+-----+--------------+
| gender | age | avg(balance) |
|--------+-----+--------------|
| F      | 28  | 32838.0      |
| M      | 32  | 39225.0      |
| M      | 33  | 4180.0       |
| M      | 36  | 5686.0       |
+--------+-----+--------------+

Example 4: Using basic limit functionality

This example limits the results to show only the top 1 age group. Note that the age column in the result is converted to string type.

source=accounts
| chart limit=1 count() over gender by age

Expected output:

fetched rows / total rows = 3/3
+--------+-------+---------+
| gender | age   | count() |
|--------+-------+---------|
| F      | OTHER | 1       |
| M      | 33    | 1       |
| M      | OTHER | 2       |
+--------+-------+---------+

Example 5: Using limit with other parameters

This example shows using limit with useother and custom otherstr parameters.

source=accounts
| chart limit=top1 useother=true otherstr='minor_gender' count() over state by gender

Expected output:

fetched rows / total rows = 4/4
+-------+--------------+---------+
| state | gender       | count() |
|-------+--------------+---------|
| IL    | M            | 1       |
| MD    | M            | 1       |
| TN    | M            | 1       |
| VA    | minor_gender | 1       |
+-------+--------------+---------+

Example 6: Using null parameters

This example shows using limit with usenull and custom nullstr parameters.

source=accounts
| chart usenull=true nullstr='employer not specified' count() over firstname by employer

Expected output:

fetched rows / total rows = 4/4
+-----------+------------------------+---------+
| firstname | employer               | count() |
|-----------+------------------------+---------|
| Amber     | Pyrami                 | 1       |
| Dale      | employer not specified | 1       |
| Hattie    | Netagy                 | 1       |
| Nanette   | Quility                | 1       |
+-----------+------------------------+---------+

Example 7: Using chart command with span

This example demonstrates using span for grouping age ranges.

source=accounts
| chart max(balance) by age span=10, gender

Expected output:

fetched rows / total rows = 2/2
+-----+--------+--------------+
| age | gender | max(balance) |
|-----+--------+--------------|
| 20  | F      | 32838        |
| 30  | M      | 39225        |
+-----+--------+--------------+

Limitations

  • Only a single aggregation function is supported per chart command.