Skip to content

Latest commit

 

History

History
276 lines (215 loc) · 7.03 KB

query.md

File metadata and controls

276 lines (215 loc) · 7.03 KB
Raw

Query

Protocol

Overview

Query is a golang structure as OLAP input, defining at github.com/awatercolorpen/olap-sql/api/types.

type Query struct {
	DataSetName  string        `json:"data_set_name"`
	TimeInterval *TimeInterval `json:"time_interval"`
	Metrics      []string      `json:"metrics"`
	Dimensions   []string      `json:"dimensions"`
	Filters      []*Filter     `json:"filters"`
	Orders       []*OrderBy    `json:"orders"`
	Limit        *Limit        `json:"limit"`
	Sql          string        `json:"Sql"`
}

Supported json protocol now.

Example:

{
  "data_set_name": "",
  "time_interval": {},
  "metrics": [],
  "dimensions": [],
  "filters": [],
  "orders": [],
  "limit": {},
  "sql": ""
}

Data Set Name

data_set_name (required) is the name of one business, also the name of sets.

Example:

{
  "data_set_name": "wikistat"
}

Time Interval

time_interval (optional) is a special structure for time interval filter condition.

It will auto translate to one filters.

WHERE (`name` >= `start` AND `name` < `end`)
  1. name (required) is the dimension name for time interval.
  2. start (required) is a string that is valid for golang time.Time type.
  3. end (required) is a string that is valid for golang time.Time type.

Example:

{
  "time_interval": {
    "name": "date",
    "start": "2021-05-06",
    "end": "2021-05-08"
  }
}

Metrics

metrics (optional) is a list of metrics name.

At least one of metrics or dimensions is required.

Example:

{
  "metrics": [
    "hits",
    "hits_avg"
  ]
}

Dimensions

dimensions (optional) is a list of dimensions name.

At least one of metrics or dimensions is required.

Example:

{
  "dimensions": [
    "date"
  ]
}

Filters

filters (optional) is a list of WHERE statement object.

  1. table (don't fill) is an autofilled property by name.
  2. name (required) is a valid dimension name or metrics name.
  3. field_property (don't fill) is an autofilled property by name.
  4. operator_type (required) is operator type of filter. For detail.
  5. value (required) is array for values. For detail.
  6. value_type (don't fill) is an autofilled property by name.
  7. children (optional) is a list of Filter. It is used for FILTER_OPERATOR_AND or FILTER_OPERATOR_OR case.

Example:

{
  "filters": [
    {
      "operator_type":"FILTER_OPERATOR_IN",
      "name":"date",
      "value": [
        "2021-05-06",
        "2021-05-07"
      ]
    }
  ]
}

Supported filter operator type and value

type description required sql example
FILTER_OPERATOR_EQUALS = condition. value size == 1. name = value[0]
FILTER_OPERATOR_IN IN condition. value size > 0. name IN (value)
FILTER_OPERATOR_NOT_IN NOT IN condition. value size > 0. name NOT IN (value)
FILTER_OPERATOR_LESS_EQUALS <= condition. value size == 1. name <= value[0]
FILTER_OPERATOR_LESS < condition. value size == 1. name < value[0]
FILTER_OPERATOR_GREATER_EQUALS >= condition. value size == 1. name >= value[0]
FILTER_OPERATOR_GREATER > condition. value size == 1. name > value[0]
FILTER_OPERATOR_LIKE like condition. value size == 1. name like value[0]
FILTER_OPERATOR_HAS has condition. value size == 1. hash(name, value[0])
FILTER_OPERATOR_EXTENSION expression as condition value size == 1. value[0]
FILTER_OPERATOR_AND AND multi children children size > 0. (children[0] AND children[1])
FILTER_OPERATOR_OR OR multi children children size > 0. (children[0] OR children[1])

Supported value type

type description
VALUE_STRING string
VALUE_INTEGER int64
VALUE_FLOAT float

Order By

orders (optional) is list of ORDER BY statement object.

  1. table (don't fill) is an autofilled property by name.
  2. name (required) is a valid dimension name or metrics name.
  3. field_property (don't fill) is an autofilled property by name.
  4. direction (optional) is direction type for ORDER BY.

Example:

{
  "orders": [
    {
      "name": "date",
      "direction": "ORDER_DIRECTION_DESCENDING"
    }
  ]
}

Supported direction type:

type description
ASC
ORDER_DIRECTION_ASCENDING ASC
ORDER_DIRECTION_DESCENDING DESC

Limit

limit (optional) is a structure for setting LIMIT and OFFSET.

LIMIT 100 OFFSET 20
  1. limit (optional) is uint64 for setting LIMIT.
  2. offset (optional) is uint64 for setting OFFSET.

Example:

{
  "limit": {
    "limit": 100,
    "offset": 20
  }
}

SQL

sql (optional) is a special property to override other metrics, dimensions, filters, orders and limit properties.

It will not generate SQL.

Example:

{
  "sql": "SELECT VERSION()"
}

Generate SQL from query

Query Example:

{
  "data_set_name": "wikistat",
  "time_interval": {
    "name": "date",
    "start": "2021-05-06",
    "end": "2021-05-08"
  },
  "metrics": [
    "hits",
    "hits_avg"
  ],
  "dimensions": [
    "date"
  ]
}

Auto SQL Example:

SELECT
    wikistat.date AS date,
    1.0 * SUM(wikistat.hits) AS hits,
    ( ( 1.0 * SUM(wikistat.hits) ) /  NULLIF(( COUNT(*) ), 0) ) AS hits_avg
FROM wikistat AS wikistat
WHERE
    ( wikistat.date >= '2021-05-06' AND wikistat.date < '2021-05-08' )
GROUP BY
    wikistat.date