Query Metric Data
1. Overview
Query metric results.
With the metric query API, you can:
-
Query result values for metrics that have already been defined.
-
During the query, temporarily add time constraints, business filters, quick derivations such as same-period comparison, proportion, and ranking, and control which query dimensions affect each metric result.
2. API URL
POSTHttp://{semantic_layer_service_address}/semantic/api/v1.1/metrics/query
Tip
How to obtain the semantic layer service address
In the metric platform UI, go to Metric Application > API Integration. The environment information is the semantic layer service address of the metric platform.
3.1 Header
| Parameter | Required | Description | Type | Max Length |
|---|---|---|---|---|
| tenant-id | Required |
Tenant ID. The metric platform is multi-tenant, so queries must specify the tenant that contains the assets. | String | 32 |
| auth-type | Required |
Authentication type. Supported values: UID, TOKEN, ACCOUNT, APIKEY. |
String | 32 |
| auth-value | Required |
Authentication value corresponding to auth-type. |
String | 32 |
| query-user-account | Optional |
User account used for authorization. If empty, authorization uses the user corresponding to auth-value. |
String | 32 |
How to obtain tenant-id and UID
In the metric platform UI, go to Metric Application > API Integration. tenant-id is the tenant ID of the current account, and user-id is the UID of the current account.
How to obtain TOKEN
To obtain a TOKEN, see the help manual section about obtaining access credentials.
How to obtain query-user-account?
This is the user's username, also known as the login name. You can view your username in Personal Center.
You can also view each user's username in Management Settings.
3.2 Body Params
| Parameter | Required | Description | Type |
|---|---|---|---|
| metrics | Required |
Metrics to query. Supports metrics already defined in the metric platform, temporary metrics defined in metricDefinitions, and metrics extended by quick calculations. |
Array[ String ] |
| metricDefinitions | Optional |
Temporary metrics generated during the query based on existing metrics. | Map< Sting,Stirng > |
| dimensions | Optional |
Query dimensions. Supports defined dimensions and quick granularity switching for date dimensions. | Array[ String ] |
| filters | Optional |
Global filters that apply dimension-level data filtering to all metrics in the current query. | Array[ String ] |
| specialMvConfig | Optional |
Configures whether to use specified materialized views for acceleration and how to handle materialized-view misses. | Map |
| resultFilters | Optional |
Filters the query result. You can filter by metric values or dimension values. | Array[String] |
| timeConstraint | Optional |
Metric date range used to query metric data. | String |
| orders | Optional |
Sorts returned query results in the specified order. Sort fields must be included in metrics or dimensions. |
Map |
| limit | Optional |
Number of records to return. Defaults to 100. | int |
| offset | Optional |
Result offset. Defaults to 1. For example, when offset=100, data is returned starting from the 100th record. |
int |
| queryResultType | Optional |
Returned result content. Defaults to SQL_AND_DATA. SQL_AND_DATA: returns both query results and query SQL. SQL: returns only query SQL. DATA: returns only data results. |
String |
| source | Optional |
Custom source identifier added by the user for the data query. | String |
| isQueryTotalCount | Optional |
Whether the query returns the data count. | Boolean |
3.3 Request Example
{
"metrics": [ /* List of metrics to query */
"orderCount", /* orderCount metric */
"orderCount_total" /* Temporary metric orderCount_total */
],
"metricDefinitions":{ /* Definition of the temporary metric orderCount_total */
"orderCount_total":{
"refMetric":"orderCount",
"specifyDimension":{
"type":"INCLUDE",
"dimensions":"metric_time__day,province"
},
}
},
"dimensions": [ /* List of dimensions to query */
"metric_time__day", /* metric_time dimension at day granularity */
"province",/* province dimension */
"city"/* city dimension */
],
"filters": [],
"timeConstraint": "([metric_time__month]= DateTrunc(Today(),\"MONTH\"))",
"limit": 30,
"offset": 1,
"queryResultType":"SQL_AND_DATA"
}
4. Response Parameters
4.1 Response Parameters
| Parameter | Required | Description | Type |
|---|---|---|---|
| data | Required |
Data returned by the query. | |
| success | Required |
Query status. true: query succeeded. false: query failed and the request parameters should be checked. |
|
| code | Required |
API response code. | String |
| message | Optional |
Error message. | String |
| traceid | Required |
Trace ID for this request. | String |
4.2 Response Example
{
"data": {
"queryId": "f8d14bba-918a-410b-b01c-87233fc70940",
"warning": null,
"sql": "SELECT `t4`.`f0` AS `metric_time__day`, `t8`.`f1` AS `flOrderCount`\nFROM (SELECT `t1`.`f0`, COALESCE(`t1`.`f0`, CAST('1970-01-01 00:00:00' AS DATETIME)) AS `f1`, (`t1`.`f0`) IS NOT NULL AS `f2`\nFROM (SELECT CAST(DATE_TRUNC('DAY', `tb_order`.`order_date`) AS DATETIME) AS `f0`\nFROM `anymetrics`.`tb_order` AS `tb_order`\nGROUP BY CAST(DATE_TRUNC('DAY', `tb_order`.`order_date`) AS DATETIME)) AS `t1`\nWHERE `t1`.`f0` >= DATE_ADD(DATE_TRUNC('DAY', CURRENT_TIMESTAMP ()), INTERVAL - 365 DAY) AND `t1`.`f0` < DATE_ADD(DATE_TRUNC('DAY', CURRENT_TIMESTAMP ()), INTERVAL 1 DAY)\nORDER BY (`t1`.`f0`) IS NULL, `t1`.`f0`\nLIMIT 10) AS `t4`\nLEFT JOIN (SELECT COUNT(`tb_order0`.`order_id`) AS `f1`, COALESCE(CAST(DATE_TRUNC('DAY', `tb_order0`.`order_date`) AS DATETIME), CAST('1970-01-01 00:00:00' AS DATETIME)) AS `f2`, (CAST(DATE_TRUNC('DAY', `tb_order0`.`order_date`) AS DATETIME)) IS NOT NULL AS `f3`\nFROM `anymetrics`.`tb_order` AS `tb_order0`\nWHERE CAST(DATE_TRUNC('DAY', `tb_order0`.`order_date`) AS DATETIME) >= DATE_ADD(DATE_TRUNC('DAY', CURRENT_TIMESTAMP ()), INTERVAL - 365 DAY) AND CAST(DATE_TRUNC('DAY', `tb_order0`.`order_date`) AS DATETIME) < DATE_ADD(DATE_TRUNC('DAY', CURRENT_TIMESTAMP ()), INTERVAL 1 DAY) AND (`tb_order0`.`product_id` = CAST(22 AS BIGINT) OR `tb_order0`.`product_id` = CAST(27 AS BIGINT) OR `tb_order0`.`product_id` = CAST(19 AS BIGINT))\nGROUP BY CAST(DATE_TRUNC('DAY', `tb_order0`.`order_date`) AS DATETIME)) AS `t8` ON `t4`.`f1` = `t8`.`f2` AND `t4`.`f2` = `t8`.`f3`\nORDER BY (`t4`.`f0`) IS NULL, `t4`.`f0`\nLIMIT 10",
"table": {
"columns": {
"metric_time__day": [
{
"value": "2022-09-09 00:00:00",
"flag": null,
"count": 1
},
{
"value": "2022-09-10 00:00:00",
"flag": null,
"count": 1
}
],
"flOrderCount": [
{
"value": null,
"flag": null,
"count": 1
},
{
"value": null,
"flag": null,
"count": 1
}
]
}
},
"metas": [
{
"name": "metric_time__day",
"dataType": null,
"dataTypeName": "DATETIME",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "flOrderCount",
"dataType": null,
"dataTypeName": "BIGINT",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
}
]
},
"success": true,
"code": null,
"message": null,
"traceId": "add22b18f1524199941fc83ce26dda33.576.16940932763920003"
}
5. Request Parameter Description
5.1 metrics
In API queries, use either the metric English name or code. The choice depends on your current metric platform configuration. This document uses the name as the unique identifier.
Tip
How to configure English-name or code mode
In Management Settings > Platform Settings, you can configure whether metrics and dimensions use code or name as the identifier.
Wrap each metric in double quotes (""). The following usage patterns are supported:
5.1.1 Reference Metrics Directly
Use a single metric:
Use multiple metrics:
5.1.2 Use Quick Calculations
The metric platform query API supports temporary metric definitions. To make temporary metrics easier to create, it also provides shortcut syntax. The quick calculation configuration is logically equivalent to defining temporary metrics through metricDefinitions. In the query API, quick calculations can define the following scenarios:
-
Same-period comparison for a metric: order amount -> order amount for the same period last year.
-
Proportion for a metric: order amount -> each city's share of order amount within its province.
-
Ranking for a metric: order amount -> ranking of each city's order amount within its province.
-
Time constraint for a metric: order amount -> order amount in the last 7 days.
5.1.2.1 Same-Period Comparison
/* Syntax: metric_name__sameperiod__offset_granularity__date_identifier__comparison_method */
"order_count__sameperiod__-2_dod__workdays__growth" means "growth rate compared with order count two workdays earlier"
When using a same-period quick calculation, make sure that:
The metric_time dimension is used in dimensions or timeConstraint. If it is used in timeConstraint, it must be a single-value filter, for example:
{
"metrics": [
"orderCount__sameperiod__yoy__value"
],
"timeConstraint": "DateTrunc([metric_time],\"MONTH\")= DateTrunc(Today(),\"MONTH\"))",/* Single-value filter on metric_time */
...
}
The same-period offset granularity cannot be smaller than the date granularity of metric_time, for example:
{
"metrics": [
"orderCount__sameperiod__yoy__value" /* Year-level same-period offset */
],
"dimensions": [
"metric_time__month" /* Metric date at month granularity */
],
...
}
| Parameter | Description | Notes |
|---|---|---|
| Offset granularity | Date used for comparison. Supported keywords include:{N}_dod: {N}-day offset. For day-over-day comparison, dod and -1_dod are equivalent. To compare with 14 days earlier, use -14_dod.{N}_wow: {N}-week offset{N}_mom: {N}-month offset{N}_qoq: {N}-quarter offset{N}_yoy: {N}-year offset{N}_woeow: {N}-previous-weekend offset. For previous weekend, woeow and -1_woeow are equivalent. To compare with the weekend two weeks earlier, use -2_woeow.{N}_moeom: {N}-previous-month-end offset{N}_qoeoq: {N}-previous-quarter-end offset{N}_yoeoy: {N}-previous-year-end offset{N}_wosow: {N}-previous-week-start offset. For previous week start, wosow and -1_wosow are equivalent. To compare with the week start two weeks earlier, use -2_wosow.{N}_mosom: {N}-previous-month-start offset{N}_qosoq: {N}-previous-quarter-start offset{N}_yosoy: {N}-previous-year-start offsetCustom calendar offset format: {N}{custom_calendar_name}_{offset_granularity}. Examples:{N}_FY_wow: {N}-fiscal-week offset{N}_FY_mom: {N}-fiscal-month offset{N}_FY_qoq: {N}-fiscal-quarter offset{N}_FY_yoy: {N}-fiscal-year offset{N}_FY_woeow: {N}-previous-fiscal-weekend offset. For previous fiscal-week weekend, FY_woeow and -1_FY_woeow are equivalent. To compare with the weekend two fiscal weeks earlier, use -2_FY_woeow.{N}_FY_moeom: {N}-previous-fiscal-month-end offset{N}_FY_qoeoq: {N}-previous-fiscal-quarter-end offset{N}_FY_yoeoy: {N}-previous-fiscal-year-end offset{N}_FY_wosow: {N}-previous-fiscal-week-start offset. For previous fiscal-week start, FY_wosow and -1_FY_wosow are equivalent. To compare with the week start two fiscal weeks earlier, use -2_FY_wosow.{N}_FY_mosom: {N}-previous-fiscal-month-start offset{N}_FY_qosoq: {N}-previous-fiscal-quarter-start offset{N}_FY_yosoy: {N}-previous-fiscal-year-start offset |
How to obtain a custom calendar name In Management Settings > Custom Calendar, view the maintained custom calendar names.  |
| Date identifier | The metric platform supports date identifiers, such as workday indicators and trading-day indicators in securities scenarios. Analysis often needs period-over-period comparison with the previous trading day. | How to obtain a date identifier In Management Settings > Time Constraint, view the maintained date identifier names.  |
| Comparison method | Same-period comparison method. Supported keywords: - value: comparison value- growthvalue: growth value (current date - comparison date)- growth: growth rate- decrease: decrease value (comparison date - current date)- decreaserate: decrease rate |
Examples
Same period last year value: {metric_name}__sameperiod__yoy__value
Growth value compared with the previous trading day: {metric_name}__sameperiod__-2_dod__{trading_day_identifier}__growthvalue
Value from 52 weeks earlier: {metric_name}__sameperiod__-52_wow__value
Value from six months earlier: {metric_name}__sameperiod__-6_mom__value
Growth value compared with the end of last year: {metric_name}__sameperiod__yoeoy__growthvalue
Growth value compared with the beginning of this year: {metric_name}__sameperiod__0_yosoy__growthvalue
Value compared with two fiscal months earlier: {metric_name}__sameperiod__-2_{custom_calendar_name}_mom__value
5.1.2.2 Proportion
/* Syntax: metric_name__proportion__proportion_scope_dimension_1,proportion_scope_dimension_2 */
"order_count__proportion__province" means "calculate the proportion value of order_count within the province window"
Note
When using a proportion quick calculation, make sure that the proportion scope dimensions are used in the dimensions structure.
{
"metrics": [
"order_count__proportion__province" /* Calculate proportion within province */
],
"dimensions": [
"province",/* province must be used in dimensions */
"city"
],
...
}
When calculating a global proportion, you can omit the proportion dimension after proportion__, for example:
{
"metrics": [
"order_count__proportion" /* Global proportion */
],
"dimensions": [
"province",
"city"
],
...
}
Example
Syntax: metric_name__proportion__proportion_scope_dimension_1,proportion_scope_dimension_2 "order_count__proportion__province" means "calculate the proportion value of order_count within the province window"
5.1.2.3 Ranking
/* Syntax: metric_name__ranking_method__ranking_order__ranking_scope_dimension,ranking_scope_dimension */
"order_count__rank__desc__province" means "calculate the rank value of order_count within the province window by using rank (sparse ranking) and desc (descending order)"
Note
When using a ranking quick calculation, make sure that:
The ranking scope dimensions are used in the dimensions structure.
{
"metrics": [
"order_count__rank__desc__province" /* Calculate ranking within province */
],
"dimensions": [
"province",/* province must be used in dimensions */
"city"
],
...
}
When calculating a global ranking, you can omit the ranking dimension after rank__desc__, for example:
{
"metrics": [
"order_count__rank__desc" /* Global ranking */
],
"dimensions": [
"province",
"city"
],
...
}
| Parameter | Description |
|---|---|
| Ranking method | Ranking method. Supported keywords: - rank: sparse ranking, for example 1,1,3,4,5.- rankDense: dense ranking, for example 1,1,2,3,4.- rowNumber: row-number ranking, for example 1,2,3,4,5. |
| Ranking order | Ranking order. Supported keywords: - asc: ascending ranking. Sorts values from small to large; smaller values rank higher.- desc: descending ranking. Sorts values from large to small; larger values rank higher. |
Examples
Descending ranking value within province: {metric_name}__rank__desc__province
Ascending ranking value within province: {metric_name}__rank__asc__province
Global descending value: {metric_name}__rank__desc or {metric_name}__rank__desc
Descending ranking within province where equal values receive increasing ranks: {metric_name}__rowNumber__desc__province
5.1.2.4 Time Constraint
/* Syntax: metric_name__period__time_constraint */
"order_count__period__7d" means "sum of order_count over the last 7 days"
Note
When using a time-constraint quick calculation, make sure that:
The referenced metric does not already use a time constraint in its metric definition. For example, for a base metric order amount, you can use a last-7-days quick time constraint to calculate order amount over the last 7 days, but you cannot apply another time constraint to a metric that is already defined as last-7-days order amount.
| Parameter | Description |
|---|---|
| Time constraint | Supported keywords: Last N minutes/hours/days/weeks/months/quarters/years: {N}min: last N minutes. For example, orderCount__period__10min indicates order amount over the last 10 minutes.{N}h: last N hours{N}d: last N days{N}w: last N weeks{N}m: last N months{N}q: last N quarters{N}y: last N yearsYear/month/etc. to date: ytd: year to dateqtd: quarter to datemtd: month to dateytm: year to monthwtd: week to datedt{N}h: day to current N-hour interval. This splits the current day into N-hour steps. For example, orderCount__period__dt2h uses 2-hour intervals. If the current time is 11:00, period__dt2h means 00:00 to 12:00 today.dt{N}min: day to current N-minute intervalCurrent day/month/etc.: cy: current yearcq: current quartercm: current monthcw: current weekcd: current dayc{N}h: current N-hour interval. For example, orderCount__period__c3h uses 3-hour intervals. If the current time is 08:00, period__c3h means 07:00 to 09:00 today.c{N}min: current N-minute interval |
Examples
Metric value over the last 7 days: {metric_name}__period__7d
Year-to-date metric value: {metric_name}__period__ytd
Year-to-month metric value: {metric_name}__period__ytm
Day to current 10-minute interval: {metric_name}__period__dt10min
Current month: {metric_name}__period__cm
Current 4-hour interval: {metric_name}__period__c4h
5.2 metircDefinitions
In API queries, you can define temporary metrics through metricDefinitions. The following parameters are used to define a temporary metric.
{
...
"metricDefinitions":{
"orderCount_total":{ /* Name of the temporary metric */
"refMetric":"orderCount", /* Referenced metric */
"specifyDimension":{ /* Available dimension control */
"type":"INCLUDE",
"dimensions":"metric_time__day,province"
},
"period": " ",/* Time constraint */
"preAggs": [ /* Time average constraint */
{
"granularity": "",
"calculateType": ""
}
],
"filters": [], /* Business filters */
"indirections": [] /* Derivation methods */
}
"agvsa":{
"expr": "[order_count]+[visit_count]" /* Temporary composite expression */
}
}
...
}
| Parameter | Description |
|---|---|
| Temporary metric name | Name of the temporary metric. It must be different from defined metrics. If it has the same name as an existing metric, the system uses the existing metric first. |
| refMetric Referenced metric | Metric referenced by the temporary metric. It must be a metric already defined in the platform. |
| expr | Defines a temporary composite metric expression. |
| specifyDimension Available dimension control | Controls the available dimensions for the temporary metric. - type- INCLUDE: query dimensions that affect the metric. For example, if the query uses province and city, and the temporary metric defines INCLUDE province, the temporary metric is affected only by province.- EXCLUDE: query dimensions excluded from affecting the metric. For example, if the query uses province and city, and the temporary metric defines EXCLUDE city, the temporary metric is not affected by city and is affected only by province.- dimensions: list of dimensions to exclude or retain. Separate multiple dimensions with commas. |
| period Time constraint | Provides four types of time constraints: - Last N days ( to_date)Syntax: "period":"to_date offset offset_granularity of 0 time_granularity "Example: "period":"to_date -6 day of 0 day " means "from 6 days ago to today, 7 days in total"offset_granularity and time_granularity support year, quarter, month, week, day, {N}hour, and {N}minute.- Year/month/etc. to date ( grain_to_date)Syntax: "period":"grain_to_date offset offset_granularity of 0 time_granularity "Example: "period":"grain_to_date 0 year of 0 day " means "year to date"offset_granularity and time_granularity support year, quarter, month, week, day, {N}hour, and {N}minute.- Start or end of period ( specify_date)Syntax: "period":"SPECIFY_DATE start\|end time_granularity of offset offset_granularity "Example: "period":"SPECIFY_DATE end day of -1 month " means "the last day of the previous month"- Single date Syntax: "period":"relative_date offset offset_granularity of 0 time_granularity"Example: "period":"relative_date 0 month of 0 day" means "current month" |
| metricGrain Metric time granularity | { "metricDefinitions":{ "{temporary_metric_name}":{ "refMetric":"{referenced_metric_name}", "period": "TO_DATE -10 day of -3 day" "metricGrain":"DAY" } } }Metric time granularity applies only to controlling the date granularity of a time constraint and cannot be used alone. metricGrain supports DAY, WEEK, MONTH, QUARTER, and YEAR. |
| preAggs Time average constraint | Time average constraints apply only to the calculation method of a time constraint and cannot be used alone. For example, they can calculate the average over the last 7 days or the overall value over the last 7 days. - If preAggs is not used, the time constraint is treated as calculating the overall result.- preAggs contains two parameters: granularity and calculateType, which define the calculation method for the time constraint.- granularity supports DAY, WEEK, MONTH, QUARTER, YEAR.- calculateType supports AVG, MAX, MIN. |
| filters Business filters | Same as the filter request parameter. See 5.4 filters. |
| indirections Derivation methods | Defines same-period comparison, proportion, ranking, and multi-level aggregation derivations. Examples: Yesterday day-over-day comparison "indirections": [ "sameperiod__dod__value" ]Proportion within province "indirections": ["proportion__province"]Descending ranking within province "indirections": ["rank__desc__province"]Multi-level aggregation: use the multi_level_agg prefix and join aggregation method plus dimensions with __."indirections": ["multi_level_agg__avg,province,city__max,province"] |
5.3 dimensions
In API queries, use either the dimension English name or code. The choice depends on your current metric platform configuration. This document uses the name as the unique identifier.
When using dimensions, separate multiple dimensions with commas.
For date dimensions, the metric platform supports quick granularity settings. The following granularities can be switched quickly:
-
year: year -
quarter: quarter -
month: month -
week: week -
day: day -
hour: hour -
minute: minute
When the dimension is metric_time (metric date), {N}hour and {N}minute granularities are also supported.
Example: Query metric values at 2-hour granularity
Example: Use fiscal month as a dimension
5.4 filters
filters supports filtering dimensions.
-
Different dimension data types use different filter expression methods. This section covers the most common methods.
-
Multiple conditions can be joined with
AND,OR, and parentheses()to describe logical relationships.
5.4.1 Text Dimensions
| Match Type | Example |
|---|---|
| Equals / does not equal a value | { "filters": [ " [dim_province]= \"Zhejiang\" AND [dim_city]<> \"Hangzhou\" " ]} |
| Equals / does not equal multiple values | { "filters": [ " IN([dim_level],\"Tier 1 city\",\"Tier 2 city\") AND NotIn([dim_city],\"Beijing\",\"Shanghai\",\"Tianjin\",\"Chongqing\") " ] } |
| Text starts with / ends with / contains | { "filters": [ " startWith([dim_name],\"Huiyuan\") AND endWith([dim_name],\"Juice\") AND contains([dim_name],\"Premium\") " ] } |
5.4.2 Numeric Dimensions
| Match Type | Example |
|---|---|
| Equals / does not equal / greater than / less than a value | { "filters": [ " [dim_price]= 2 AND [dim_counts]<> 20 AND [dim_sales]>= 22 " ] } |
| Equals / does not equal multiple values | { "filters": [ " IN([dim_price],100,42,50) AND NotIn([dim_counts],11,12,13" ] } |
5.4.3 Date Dimensions
| Match Type | Example |
|---|---|
| Equals / does not equal / greater than / less than a date | {"filters": [ "['dim_order_date']>=\"2023-08-01\" AND ['dim_order_date']<\"2023-08-08\"" ]} |
| Today, this month, and similar periods | { "filters": [ " [dim_order_date]= Datetrunc(Now(),\"DAY\") OR [dim_order_date]= Datetrunc(Now(),\"MONTH\") " ] } |
| Previous month, previous year, and similar periods | { "filters": [ " [dim_order_date]= Dateadd(Datetrunc(Now(),\"MONTH\"),-1,\"MONTH\") OR [dim_order_date]= Dateadd(Datetrunc(Now(),\"YEAR\"),-1,\"YEAR\") " ] } |
5.4.4 JSON Format
| Match Type | Example |
|---|---|
| Equals / does not equal a value | { "filters": [ " JsonExtract([data],\"province\"= \"Zhejiang\" AND JsonExtract([data],\"city\"= \"Zhejiang\" " ] } |
| Equals / does not equal multiple values | { "filters": [ " IN(JsonExtract([data],\"dim_level\"),\"Tier 1 city\",\"Tier 2 city\") AND NotIn(JsonExtract([data],\"city\"),\"Beijing\",\"Shanghai\",\"Tianjin\",\"Chongqing\") " ] } |
| Text starts with / ends with / contains | { "filters": [ " startWith(JsonExtract([data],\"dim_level\"),\"Tier 1\") AND endWith(JsonExtract([data],\"dim_level\"),\"city\") AND contains(JsonExtract([data],\" city\"),\"Jiang\") " ] } |
5.5 resultFilters
Filters query results. You can retain or exclude specified dimension values or metric values so that only matching records are returned.
- Supports conditional filtering on returned metric values, such as greater than, less than, equals, and does not equal.
- Supports conditional filtering on returned dimension values, such as a specific city, department, or product category.
- Supports multi-condition filtering with
AND/OR. - Filter conditions apply only to the final returned result and do not affect the underlying data calculation logic. This differs from the global filtering performed by
filters.
Examples
- Filter records where sales amount is greater than 1000:
- Filter data where region is Shanghai or Beijing:
- Filter records where sales amount is greater than 1000 and region is Shanghai:
5.6 timeConstraint
Note
timeConstraint supports only the metric_time dimension.
Filtering on metric_time uses the same syntax as date-dimension filters in filter.
Example: Last N days
{
"timeConstraint":"DateTrunc(['metric_time'],\"DAY\") >= DATEADD(DateTrunc(NOW(), \"DAY\"), -{N+1}, \"DAY\") AND ['metric_time__day'] <= DateTrunc(NOW(), \"DAY\")"
}
Example: Current year
Example: Current fiscal year
Example: Previous fiscal month
{
"timeConstraint": "DateTrunc(['metric_time'], \"FY_MONTH\") = DateAdd(DateTrunc(Now(), \"FYY_MONTH\"), -1, \"FY_MONTH\")"
}
Example: Time range from three fiscal months ago to the previous fiscal month
{
"timeConstraint":"DateTrunc(['metric_time'], \"FY_MONTH\") >= DateAdd(DateTrunc(Now(), \"FY_MONT\"), -3, \"FYY_MONTH\") AND DateTrunc(['metric_time'], \"FY_MONTH\") <= DateAdd(DateTrunc(Now(), \"FY_MONTH\"), -1, \"FY_MONTH\")"
}
Example: Start or end of period, first day of the current fiscal month
{
"timeConstraint":"DateTrunc(['metric_time'], \"DAY\") = DateAdd(DateTrunc(DateAdd(Now(), 0, \"FY_MONTH\"), \"FY_MONTH\"), 0, \"DAY\")"
}
5.7 orders
Note
orders supports only dimensions or metrics that appear in metrics or dimensions. Sort priority is evaluated from highest to lowest: in the example below, sort_column_1 has the highest priority and sort_column_3 has the lowest priority.
Sort columns can be metrics or dimensions. asc sorts in ascending order, and desc sorts in descending order.
5.8 source
Identifies the source of the query record.
{
"dimensions": [
"metric_time__quarter",
"brand"
],
"metrics": [
"orderCount"
],
"timeConstraint": "['metric_time__month'] >= dateTrunc(\"2025-04-01\",\"MONTH\")",
"source":"Documentation example"
}
5.9 specialMvConfig
| Parameter | Type | Description |
|---|---|---|
| hints | Array | Materialized views to use for acceleration. Multiple values are supported. |
| penetrateOnMiss | Boolean | T: fall back to the underlying query (default). F: do not fall back. |
specialMvConfig configures whether to use specified materialized views for a query and how to handle materialized-view misses. It includes two important parameters: hints and penetrateOnMiss.
Parameter Description
-
If
hintsis empty,penetrateOnMissdoes not take effect, and filter matching (ignoreMvFilter) does not take effect. -
If
hintsis not empty, this query is forced to use the data specified inhints. Inhintsmode, metric date range conditions are ignored, and the specified materialized views are assumed to satisfy the required date range.-
When
penetrateOnMiss = T-
ignoreMvFilter = T
- If the specified accelerated materialized view satisfies the hit requirements, the query is rewritten. - If it does not satisfy the requirements, the query falls back to the underlying query. -
ignoreMvFilter = F
-
Ignore filter conditions in the materialized view script and match only the query dimensions against the materialized view dimensions.
-
If matched, the query is rewritten.
-
If not matched, the query falls back to the underlying query.
-
-
-
-
When
penetrateOnMiss = F-
ignoreMvFilter = T
-
If the specified accelerated materialized view satisfies the hit requirements, the query is rewritten.
-
If it does not satisfy the requirements, an error is returned.
-
-
ignoreMvFilter = F
-
Ignore filter conditions in the materialized view script and match only the query dimensions against the materialized view dimensions.
-
If matched, the query is rewritten.
-
If not matched, an error is returned.
-
-
-
-
Example
{
"metrics": [
"1015_avg_product_num"
],
"dimensions": [],
"filters": [],
"timeConstraint": "(datetrunc([metric_time], \"day\")) = (DATETRUNC(CAST(\"2024-04-14 00:00:00\", \"TIMESTAMP\"), \"DAY\"))",
"limit": 30,
"offset": 1,
"specialMvConfig": {
"hints": ["split_mv_1015"],
"penetrateOnMiss": false
},
"queryResultType": "SQL_AND_DATA"
}
5.10 Other Parameters
| Parameter | Description |
|---|---|
| limit and offset | Use these together to control the starting point (offset) and number of records (limit) returned. For example, to get 100 rows starting from row 101, use { ... "limit": 100, "offset": 101, ... }. |
| queryResultType | Controls returned result data. Defaults to SQL_AND_DATA:SQL: returns SQL only, without the result array.DATA: returns the result array and metadata, without SQL.SQL_AND_DATA: returns both. |
6. Response Parameter Description
In the query response, data is the most important returned object. It contains the query result data and other information for this query.
| Parameter | Required | Description | Type | Max Length |
|---|---|---|---|---|
| queryid | Required |
Query identifier. | String | 32 |
| warning | Required |
Warning information. | string | |
| sql | Required |
SQL query statement generated from the request data. | string | |
| table | Required |
Query result array: "table": { "columns": { "metric_time__day": [ { "value": "2022-09-09 00:00:00", "flag": null, "count": 1 }, { "value": "2022-09-10 00:00:00", "flag": null, "count": 1 } ], "flOrderCount": [ { "value": null, "flag": null, "count": 1 }, { "value": null, "flag": null, "count": 1 } ] } }. Data is returned by column. Each column represents the result values for one metric or dimension, and value is the value in that column. |
array | |
| metas | Required |
Metadata for the returned result. | array | |
| hitMvType | Required |
Whether a materialized view is hit. Enumerated values: FULL_REWRITE: fully rewritten based on a materialized view.HALF_REWRITE: partially rewritten based on a materialized view.NOT_REWRITE: not rewritten based on a materialized view. |
enum |
7. Request Examples
Example 1: Query daily order count for product IDs 13 and 18 over the past year
- Request
{
"queryResultType":"DATA",
"metrics": [
"orderCount"
],
"dimensions": [
"metric_time__day"
],
"filters": [
"IN(['product_id1'], 13, 18)"
],
"timeConstraint": "DateTrunc(['metric_time'],\"YEAR\") = DATEADD(DateTrunc(NOW(), \"YEAR\"), -1, \"YEAR\") ",
"limit": 10,
"offset": 1,
"orders": [
{
"metric_time__day": "asc"
}
]
}
- Response
{
"data": {
"queryId": "f7ea0af0c0294412be144c9a698617d3",
"warning": null,
"sql": null,
"table": {
"columns": {
"orderCount": [
{
"value": 11,
"flag": null,
"count": 1
},
{
"value": 9,
"flag": null,
"count": 1
},
{
"value": 13,
"flag": null,
"count": 1
},
{
"value": 7,
"flag": null,
"count": 1
},
{
"value": 2,
"flag": null,
"count": 1
},
{
"value": 0,
"flag": null,
"count": 1
},
{
"value": 2,
"flag": null,
"count": 1
},
{
"value": 8,
"flag": null,
"count": 1
},
{
"value": 1,
"flag": null,
"count": 1
},
{
"value": 10,
"flag": null,
"count": 1
}
],
"metric_time__day": [
{
"value": "2024-01-01",
"flag": null,
"count": 1
},
{
"value": "2024-01-03",
"flag": null,
"count": 1
},
{
"value": "2024-01-09",
"flag": null,
"count": 1
},
{
"value": "2024-01-10",
"flag": null,
"count": 1
},
{
"value": "2024-01-11",
"flag": null,
"count": 1
},
{
"value": "2024-01-14",
"flag": null,
"count": 1
},
{
"value": "2024-01-17",
"flag": null,
"count": 1
},
{
"value": "2024-01-23",
"flag": null,
"count": 1
},
{
"value": "2024-01-24",
"flag": null,
"count": 1
},
{
"value": "2024-01-26",
"flag": null,
"count": 1
}
]
}
},
"metas": [
{
"id": null,
"uuid": null,
"name": "metric_time__day",
"dataType": null,
"dataTypeName": "DATE",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null,
"type": null
},
{
"id": null,
"uuid": null,
"name": "orderCount",
"dataType": null,
"dataTypeName": "BIGINT",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null,
"type": null
}
],
"hitMvType": "NOT_REWRITE",
"containsErrorColumn": false
},
"success": true,
"code": "200",
"message": null,
"traceId": "4308407d40f14298a4fd4f1ab1d0a9a4.252.17446849964940005"
}
Example 2: Query today's order count and day-over-day order count growth value
- Request
{
"metrics": [
"order_count",
"orderCount__sameperiod__dod__growthvalue"
],
"dimensions": [
],
"timeConstraint": "(['metric_time']=TODAY())",
"filters": [],
"limit": 30,
"offset": 1
}
- Response
{
"data": {
"queryId": "8393e005bf2c4174ada3918b1116b2a7",
"warning": null,
"sql": "SELECT CASE WHEN (`t3`.`f0`) IS NOT NULL OR (`t8`.`f0`) IS NOT NULL THEN COALESCE(`t3`.`f0`, 0) - COALESCE(`t8`.`f0`, 0) ELSE NULL END AS `order_count__dod__growthvalue`\nFROM (SELECT COUNT(`t`.`order_id`) AS `f0`\nFROM (SELECT `tn_1578931_fl_sr__default__t_order`.`order_id`, `tn_1578931_fl_sr__default__t_order`.`order_date`, COALESCE(DATE_TRUNC('DAY', `tn_1578931_fl_sr__default__t_order`.`order_date`), CAST('1969-01-01' AS DATE)) AS `f2`, (DATE_TRUNC('DAY', `tn_1578931_fl_sr__default__t_order`.`order_date`)) IS NOT NULL AS `f3`\nFROM `default_catalog`.`aloudatacan`.`tn_1578931_fl_sr__default__t_order` AS `tn_1578931_fl_sr__default__t_order`) AS `t`\nLEFT JOIN (SELECT `smart_date_table__20230101a925000000`.`day_col`, COALESCE(`smart_date_table__20230101a925000000`.`day_col`, CAST('1969-01-01' AS DATE)) AS `f1`, (`smart_date_table__20230101a925000000`.`day_col`) IS NOT NULL AS `f2`\nFROM `aloudatacan`.`smart_date_table__20230101a925000000` AS `smart_date_table__20230101a925000000`) AS `t0` ON `t`.`f2` = `t0`.`f1` AND `t`.`f3` = `t0`.`f2`\nWHERE `t0`.`day_col` = CURRENT_DATE ()AND DATE_TRUNC('DAY', `t`.`order_date`) = CURRENT_DATE ()) AS `t3`\nCROSS JOIN (SELECT COUNT(`t4`.`order_id`) AS `f0`\nFROM (SELECT `tn_1578931_fl_sr__default__t_order0`.`order_id`, `tn_1578931_fl_sr__default__t_order0`.`order_date`, COALESCE(DATE_TRUNC('DAY', `tn_1578931_fl_sr__default__t_order0`.`order_date`), CAST('1969-01-01' AS DATE)) AS `f2`, (DATE_TRUNC('DAY', `tn_1578931_fl_sr__default__t_order0`.`order_date`)) IS NOT NULL AS `f3`\nFROM `default_catalog`.`aloudatacan`.`tn_1578931_fl_sr__default__t_order` AS `tn_1578931_fl_sr__default__t_order0`) AS `t4`\nLEFT JOIN (SELECT `smart_date_table__20230101a9250000000`.`day_col`, COALESCE(`smart_date_table__20230101a9250000000`.`day_col`, CAST('1969-01-01' AS DATE)) AS `f1`, (`smart_date_table__20230101a9250000000`.`day_col`) IS NOT NULL AS `f2`\nFROM `aloudatacan`.`smart_date_table__20230101a925000000` AS `smart_date_table__20230101a9250000000`) AS `t5` ON `t4`.`f2` = `t5`.`f1` AND `t4`.`f3` = `t5`.`f2`\nWHERE DATE_ADD(`t5`.`day_col`, INTERVAL 1 DAY) = CURRENT_DATE ()AND DATE_ADD(DATE_TRUNC('DAY', `t4`.`order_date`), INTERVAL 1 DAY) = CURRENT_DATE ()) AS `t8`\nLIMIT 30",
"table": {
"columns": {
"order_count__dod__growthvalue": [
{
"value": 0,
"flag": null,
"count": 1
}
]
}
},
"metas": [
{
"name": "order_count__dod__growthvalue",
"dataType": null,
"dataTypeName": "BIGINT",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
}
]
},
"success": true,
"code": null,
"message": null,
"traceId": "f48660e183134e848c0a76008cde36a9.520.17171223773600003"
}
Example 3: Query year-over-year order count growth value
- Request
{
"metrics": [
"order_count",
"order_count__yoygrowth"
],
"metricDefinitions":{
"order_count__yoygrowth":{
"refMetric":"order_count",
"indirections": [
"sameperiod__yoy__growthvalue"
]
}
},
"dimensions": [
"metric_time__month"
],
"timeConstraint": "(DateTrunc(['metric_time'], \"MONTH\")= DateTrunc(Today(),\"MONTH\"))",
"filters": [],
"limit": 30,
"offset": 1
}
- Response
{
"data": {
"queryId": "2299e1ec92b94681b0d9f2a43610cd80",
"warning": null,
"sql": "SELECT CASE WHEN (`t4`.`f1`) IS NOT NULL OR (`t10`.`f1`) IS NOT NULL THEN COALESCE(`t4`.`f1`, 0) - COALESCE(`t10`.`f1`, 0) ELSE NULL END AS `order_count__yoy__growthvalue`, CASE WHEN (`t4`.`month_col`) IS NOT NULL THEN `t4`.`month_col` WHEN (`t10`.`f2`) IS NOT NULL THEN `t10`.`f2` ELSE NULL END AS `metric_time__month`\nFROM (SELECT COUNT(`t`.`order_id`) AS `f1`, `t0`.`month_col`, COALESCE(`t0`.`month_col`, CAST('1969-01-01' AS DATE)) AS `f2`, (`t0`.`month_col`) IS NOT NULL AS `f3`\nFROM (SELECT `tn_1578931_fl_sr__default__t_order`.`order_id`, COALESCE(DATE_TRUNC('DAY', `tn_1578931_fl_sr__default__t_order`.`order_date`), CAST('1969-01-01' AS DATE)) AS `f1`, (DATE_TRUNC('DAY', `tn_1578931_fl_sr__default__t_order`.`order_date`)) IS NOT NULL AS `f2`\nFROM `default_catalog`.`aloudatacan`.`tn_1578931_fl_sr__default__t_order` AS `tn_1578931_fl_sr__default__t_order`) AS `t`\nLEFT JOIN (SELECT `smart_date_table__20230101a925000000`.`month_col`, COALESCE(`smart_date_table__20230101a925000000`.`day_col`, CAST('1969-01-01' AS DATE)) AS `f1`, (`smart_date_table__20230101a925000000`.`day_col`) IS NOT NULL AS `f2`\nFROM `aloudatacan`.`smart_date_table__20230101a925000000` AS `smart_date_table__20230101a925000000`) AS `t0` ON `t`.`f1` = `t0`.`f1` AND `t`.`f2` = `t0`.`f2`\nWHERE DATE_TRUNC('MONTH', CURRENT_TIMESTAMP ()) <= `t0`.`month_col` AND DATE_ADD(DATE_TRUNC('MONTH', CURRENT_TIMESTAMP ()), INTERVAL 1 MONTH) > `t0`.`month_col`\nGROUP BY `t0`.`month_col`) AS `t4`\nFULL JOIN (SELECT COUNT(`t5`.`order_id`) AS `f1`, DATE_ADD(`t6`.`month_col`, INTERVAL 1 YEAR) AS `f2`, COALESCE(DATE_ADD(`t6`.`month_col`, INTERVAL 1 YEAR), CAST('1969-01-01 00:00:00' AS DATETIME)) AS `f3`, (DATE_ADD(`t6`.`month_col`, INTERVAL 1 YEAR)) IS NOT NULL AS `f4`\nFROM (SELECT `tn_1578931_fl_sr__default__t_order0`.`order_id`, COALESCE(DATE_TRUNC('DAY', `tn_1578931_fl_sr__default__t_order0`.`order_date`), CAST('1969-01-01' AS DATE)) AS `f1`, (DATE_TRUNC('DAY', `tn_1578931_fl_sr__default__t_order0`.`order_date`)) IS NOT NULL AS `f2`\nFROM `default_catalog`.`aloudatacan`.`tn_1578931_fl_sr__default__t_order` AS `tn_1578931_fl_sr__default__t_order0`) AS `t5`\nLEFT JOIN (SELECT `smart_date_table__20230101a9250000000`.`month_col`, COALESCE(`smart_date_table__20230101a9250000000`.`day_col`, CAST('1969-01-01' AS DATE)) AS `f1`, (`smart_date_table__20230101a9250000000`.`day_col`) IS NOT NULL AS `f2`\nFROM `aloudatacan`.`smart_date_table__20230101a925000000` AS `smart_date_table__20230101a9250000000`) AS `t6` ON `t5`.`f1` = `t6`.`f1` AND `t5`.`f2` = `t6`.`f2`\nWHERE DATE_TRUNC('MONTH', CURRENT_TIMESTAMP ()) <= DATE_ADD(`t6`.`month_col`, INTERVAL 1 YEAR) AND DATE_ADD(DATE_TRUNC('MONTH', CURRENT_TIMESTAMP ()), INTERVAL 1 MONTH) > DATE_ADD(`t6`.`month_col`, INTERVAL 1 YEAR) AND DATE_ADD(DATE_TRUNC('MONTH', CURRENT_TIMESTAMP ()), INTERVAL - 1 YEAR) <= `t6`.`month_col` AND DATE_ADD(DATE_ADD(DATE_TRUNC('MONTH', CURRENT_TIMESTAMP ()), INTERVAL 1 MONTH), INTERVAL - 1 YEAR) > `t6`.`month_col`\nGROUP BY `t6`.`month_col`) AS `t10` ON `t4`.`f2` = `t10`.`f3` AND `t4`.`f3` = `t10`.`f4`\nWHERE DATE_TRUNC('MONTH', CURRENT_TIMESTAMP ()) <= CASE WHEN (`t4`.`month_col`) IS NOT NULL THEN `t4`.`month_col` WHEN (`t10`.`f2`) IS NOT NULL THEN `t10`.`f2` ELSE NULL END AND DATE_ADD(DATE_TRUNC('MONTH', CURRENT_TIMESTAMP ()), INTERVAL 1 MONTH) > CASE WHEN (`t4`.`month_col`) IS NOT NULL THEN `t4`.`month_col` WHEN (`t10`.`f2`) IS NOT NULL THEN `t10`.`f2` ELSE NULL END\nORDER BY (CASE WHEN (`t4`.`month_col`) IS NOT NULL THEN `t4`.`month_col` WHEN (`t10`.`f2`) IS NOT NULL THEN `t10`.`f2` ELSE NULL END) DESC\nLIMIT 30",
"table": {
"columns": {
"order_count__yoy__growthvalue": [
{
"value": 7,
"flag": null,
"count": 1
}
],
"metric_time__month": [
{
"value": "2024-05-01 00:00:00",
"flag": null,
"count": 1
}
]
}
},
"metas": [
{
"name": "order_count__yoy__growthvalue",
"dataType": null,
"dataTypeName": "BIGINT",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "metric_time__month",
"dataType": null,
"dataTypeName": "DATETIME",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
}
]
},
"success": true,
"code": null,
"message": null,
"traceId": "f48660e183134e848c0a76008cde36a9.519.17171228778140005"
}
Example 4: Query order count from 2024-03-01 to 2024-03-07 and the month-over-month comparison value for 2024-02-01 to 2024-02-07
{
"metrics": [
"orderCount",
"orderCount_momvalue"
],
"metricDefinitions":{
"orderCount_momvalue":{
"refMetric":"orderCount",
"indirections": [
"sameperiod__mom__value"
]
}
},
"dimensions": [
"metric_time__month"
],
"timeConstraint": "(['metric_time']>=date(\"2024-03-01\")) AND (['metric_time']<=date(\"2024-03-07\"))",
"filters": [],
"limit": 30,
"offset": 1
}
- Response
{
"data": {
"queryId": "a980be9e34d640a6a6afca8842c86b44",
"warning": null,
"sql": "SELECT `t20`.`f0` AS `metric_time__month`, `t20`.`f1` AS `orderCount`, `t20`.`f10` AS `orderCount_momvalue`\nFROM (SELECT CASE WHEN (`t6`.`month_col`) IS NOT NULL THEN `t6`.`month_col` WHEN (`t19`.`month_col`) IS NOT NULL THEN `t19`.`month_col` ELSE NULL END AS `f0`, `t6`.`f1`, `t19`.`f10`\nFROM (SELECT `t2`.`month_col`, SUM(CASE WHEN CAST('2024-03-01' AS DATE) <= `t2`.`day_col` AND CAST('2024-03-07' AS DATE) >= `t2`.`day_col` THEN `t0`.`quantity` ELSE NULL END) AS `f1`, COALESCE(`t2`.`month_col`, CAST('1969-01-01' AS DATE)) AS `f2`, (`t2`.`month_col`) IS NOT NULL AS `f3`\nFROM (SELECT `tn_19487697_jmsr__default__fl_order`.`quantity`, `tn_19487697_jmsr__default__fl_order`.`order_date`\nFROM `default_catalog`.`aloudatacan`.`tn_19487697_jmsr__default__fl_order` AS `tn_19487697_jmsr__default__fl_order`\nWHERE `tn_19487697_jmsr__default__fl_order`.`order_date` >= CAST('2024-03-01' AS DATE) AND `tn_19487697_jmsr__default__fl_order`.`order_date` <= CAST('2024-03-07' AS DATE)) AS `t0`\nLEFT JOIN (SELECT `smart_date_table__20230101a448000000`.`month_col`, `smart_date_table__20230101a448000000`.`day_col`\nFROM `default_catalog`.`aloudatacan`.`smart_date_table__20230101a448000000` AS `smart_date_table__20230101a448000000`\nWHERE `smart_date_table__20230101a448000000`.`day_col` >= CAST('2024-03-01' AS DATE) AND `smart_date_table__20230101a448000000`.`day_col` <= CAST('2024-03-07' AS DATE)) AS `t2` ON `t0`.`order_date` = `t2`.`day_col`\nWHERE `t2`.`day_col` >= CAST('2024-03-01' AS DATE) AND `t2`.`day_col` <= CAST('2024-03-07' AS DATE)\nGROUP BY `t2`.`month_col`) AS `t6`\nFULL JOIN (SELECT `t11`.`month_col`, `t18`.`f1` AS `f10`, COALESCE(`t11`.`month_col`, CAST('1969-01-01' AS DATE)) AS `f2`, (`t11`.`month_col`) IS NOT NULL AS `f3`\nFROM (SELECT `t9`.`month_col`, DATE_ADD(`t9`.`month_col`, INTERVAL - 1 MONTH) AS `f1`\nFROM (SELECT `smart_date_table__20230101a4480000000`.`month_col`\nFROM `default_catalog`.`aloudatacan`.`smart_date_table__20230101a448000000` AS `smart_date_table__20230101a4480000000`\nGROUP BY `smart_date_table__20230101a4480000000`.`month_col`, `smart_date_table__20230101a4480000000`.`year_col`, `smart_date_table__20230101a4480000000`.`quarter_col`) AS `t9`\nWHERE `t9`.`month_col` >= CAST('2024-03-01' AS DATE) AND `t9`.`month_col` <= CAST('2024-03-01' AS DATE)) AS `t11`\nLEFT JOIN (SELECT `t15`.`month_col`, SUM(CASE WHEN DATE_ADD(CAST('2024-03-01' AS DATE), INTERVAL - 1 MONTH) <= `t15`.`day_col` AND DATE_ADD(CAST('2024-03-07' AS DATE), INTERVAL - 1 MONTH) >= `t15`.`day_col` THEN `t13`.`quantity` ELSE NULL END) AS `f1`\nFROM (SELECT `tn_19487697_jmsr__default__fl_order0`.`quantity`, `tn_19487697_jmsr__default__fl_order0`.`order_date`\nFROM `default_catalog`.`aloudatacan`.`tn_19487697_jmsr__default__fl_order` AS `tn_19487697_jmsr__default__fl_order0`\nWHERE `tn_19487697_jmsr__default__fl_order0`.`order_date` >= CAST('2024-02-01' AS DATE) AND `tn_19487697_jmsr__default__fl_order0`.`order_date` <= CAST('2024-02-07' AS DATE)) AS `t13`\nLEFT JOIN (SELECT `smart_date_table__20230101a4480000001`.`month_col`, `smart_date_table__20230101a4480000001`.`day_col`\nFROM `default_catalog`.`aloudatacan`.`smart_date_table__20230101a448000000` AS `smart_date_table__20230101a4480000001`\nWHERE `smart_date_table__20230101a4480000001`.`day_col` >= CAST('2024-02-01' AS DATE) AND `smart_date_table__20230101a4480000001`.`day_col` <= CAST('2024-02-07' AS DATE)) AS `t15` ON `t13`.`order_date` = `t15`.`day_col`\nWHERE `t15`.`day_col` >= CAST('2024-02-01' AS DATE) AND `t15`.`day_col` <= CAST('2024-02-07' AS DATE)\nGROUP BY `t15`.`month_col`) AS `t18` ON `t11`.`f1` = `t18`.`month_col`) AS `t19` ON `t6`.`f2` = `t19`.`f2` AND `t6`.`f3` = `t19`.`f3`) AS `t20`\nWHERE DATE_TRUNC('MONTH', CAST('2024-03-01' AS DATE)) <= `t20`.`f0` AND DATE_TRUNC('MONTH', CAST('2024-03-07' AS DATE)) >= `t20`.`f0` AND ((`t20`.`f1`) IS NOT NULL OR (`t20`.`f10`) IS NOT NULL)\nORDER BY (`t20`.`f0`) DESC\nLIMIT 30",
"table": {
"columns": {
"orderCount_momvalue": [
{
"value": 166,
"flag": null,
"count": 1
}
],
"orderCount": [
{
"value": 124,
"flag": null,
"count": 1
}
],
"metric_time__month": [
{
"value": "2024-03-01",
"flag": null,
"count": 1
}
]
}
},
"metas": [
{
"id": null,
"uuid": null,
"name": "metric_time__month",
"dataType": null,
"dataTypeName": "DATE",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null,
"type": null
},
{
"id": null,
"uuid": null,
"name": "orderCount",
"dataType": null,
"dataTypeName": "BIGINT",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null,
"type": null
},
{
"id": null,
"uuid": null,
"name": "orderCount_momvalue",
"dataType": null,
"dataTypeName": "BIGINT",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null,
"type": null
}
],
"hitMvType": "NOT_REWRITE",
"containsErrorColumn": false
},
"success": true,
"code": "200",
"message": null,
"traceId": "4308407d40f14298a4fd4f1ab1d0a9a4.250.17446854003390003"
}
Example 5: Query order count, within-province order count proportion, and national order count proportion by metric date (day), province, and city
- Request
{
"metrics": [
"orderCount",
"orderCount__proportion__metric_time__day,province",
"orderCount__proportion__metric_time__day"
],
"dimensions": [
"metric_time__day",
"province",
"city"
],
"timeConstraint": "",
"filters": [],
"limit": "",
"offset": ""
}
- Response
{
"data": {
"queryId": "5b2daa156a5a4a8f918032be59555097",
"warning": null,
"sql": "SELECT `t5`.`f0` AS `YH_fl_order_002_province`, `t5`.`f3` AS `sum_quantity`, CASE WHEN (`t8`.`order_date`) IS NOT NULL THEN `t8`.`order_date` WHEN (`t5`.`f2`) IS NOT NULL THEN `t5`.`f2` ELSE NULL END AS `metric_time__day`, `t5`.`f4` AS `sum_quantity__proportion__metric_time__day,YH_fl_order_002_province`, CASE WHEN `t8`.`f1` = 0 THEN NULL ELSE COALESCE(`t5`.`f30`, 0) / `t8`.`f1` END AS `sum_quantity__proportion__metric_time__day`, `t5`.`city` AS `YH_fl_order_002_city`\nFROM (SELECT CASE WHEN (`t1`.`province`) IS NOT NULL THEN `t1`.`province` WHEN (`t4`.`province`) IS NOT NULL THEN `t4`.`province` ELSE NULL END AS `f0`, `t1`.`f3`, CASE WHEN (`t1`.`order_date`) IS NOT NULL THEN `t1`.`order_date` WHEN (`t4`.`order_date`) IS NOT NULL THEN `t4`.`order_date` ELSE NULL END AS `f2`, CASE WHEN `t4`.`f2` = 0 THEN NULL ELSE COALESCE(`t1`.`f30`, 0) / `t4`.`f2` END AS `f4`, `t1`.`f30`, `t1`.`city`, COALESCE(CASE WHEN (`t1`.`order_date`) IS NOT NULL THEN `t1`.`order_date` WHEN (`t4`.`order_date`) IS NOT NULL THEN `t4`.`order_date` ELSE NULL END, CAST('1969-01-01' AS DATE)) AS `f6`, (CASE WHEN (`t1`.`order_date`) IS NOT NULL THEN `t1`.`order_date` WHEN (`t4`.`order_date`) IS NOT NULL THEN `t4`.`order_date` ELSE NULL END) IS NOT NULL AS `f7`\nFROM (SELECT `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`province`, SUM(`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`quantity`) AS `f3`, `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`order_date`, SUM(`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`quantity`) AS `f30`, `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`city`, COALESCE(`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`province`, '') AS `f5`, (`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`province`) IS NOT NULL AS `f6`, COALESCE(`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`order_date`, CAST('1969-01-01' AS DATE)) AS `f7`, (`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`order_date`) IS NOT NULL AS `f8`\nFROM `default_catalog`.`aloudatacan`.`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002` AS `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`\nGROUP BY `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`province`, `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`city`, `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`order_date`) AS `t1`\nFULL JOIN (SELECT `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0020`.`province`, `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0020`.`order_date`, SUM(`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0020`.`quantity`) AS `f2`, COALESCE(`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0020`.`province`, '') AS `f3`, (`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0020`.`province`) IS NOT NULL AS `f4`, COALESCE(`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0020`.`order_date`, CAST('1969-01-01' AS DATE)) AS `f5`, (`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0020`.`order_date`) IS NOT NULL AS `f6`\nFROM `default_catalog`.`aloudatacan`.`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002` AS `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0020`\nGROUP BY `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0020`.`province`, `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0020`.`order_date`) AS `t4` ON `t1`.`f5` = `t4`.`f3` AND `t1`.`f6` = `t4`.`f4` AND `t1`.`f7` = `t4`.`f5` AND `t1`.`f8` = `t4`.`f6`) AS `t5`\nFULL JOIN (SELECT `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0021`.`order_date`, SUM(`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0021`.`quantity`) AS `f1`, COALESCE(`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0021`.`order_date`, CAST('1969-01-01' AS DATE)) AS `f2`, (`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0021`.`order_date`) IS NOT NULL AS `f3`\nFROM `default_catalog`.`aloudatacan`.`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002` AS `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0021`\nGROUP BY `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_0021`.`order_date`) AS `t8` ON `t5`.`f6` = `t8`.`f2` AND `t5`.`f7` = `t8`.`f3`\nORDER BY (CASE WHEN (`t8`.`order_date`) IS NOT NULL THEN `t8`.`order_date` WHEN (`t5`.`f2`) IS NOT NULL THEN `t5`.`f2` ELSE NULL END) DESC\nLIMIT 5",
"table": {
"columns": {
"YH_fl_order_002_province": [
{
"value": "Jiangsu Province",
"flag": null,
"count": 1
},
{
"value": "Anhui Province",
"flag": null,
"count": 1
},
{
"value": "Jiangsu Province",
"flag": null,
"count": 1
},
{
"value": "Zhejiang Province",
"flag": null,
"count": 1
},
{
"value": "Jiangsu Province",
"flag": null,
"count": 1
}
],
"YH_fl_order_002_city": [
{
"value": "Nanjing",
"flag": null,
"count": 1
},
{
"value": "Hefei",
"flag": null,
"count": 1
},
{
"value": "Nanjing",
"flag": null,
"count": 1
},
{
"value": "Hangzhou",
"flag": null,
"count": 1
},
{
"value": "Nanjing",
"flag": null,
"count": 1
}
],
"sum_quantity__proportion__metric_time__day": [
{
"value": 1.0,
"flag": null,
"count": 1
},
{
"value": 0.27586206896551724,
"flag": null,
"count": 1
},
{
"value": 0.1724137931034483,
"flag": null,
"count": 1
},
{
"value": 0.5517241379310345,
"flag": null,
"count": 1
},
{
"value": 0.5714285714285714,
"flag": null,
"count": 1
}
],
"metric_time__day": [
{
"value": "2024-03-18",
"flag": null,
"count": 1
},
{
"value": "2024-03-17",
"flag": null,
"count": 1
},
{
"value": "2024-03-17",
"flag": null,
"count": 1
},
{
"value": "2024-03-17",
"flag": null,
"count": 1
},
{
"value": "2024-03-16",
"flag": null,
"count": 1
}
],
"sum_quantity": [
{
"value": 20,
"flag": null,
"count": 1
},
{
"value": 8,
"flag": null,
"count": 1
},
{
"value": 5,
"flag": null,
"count": 1
},
{
"value": 16,
"flag": null,
"count": 1
},
{
"value": 12,
"flag": null,
"count": 1
}
],
"sum_quantity__proportion__metric_time__day,YH_fl_order_002_province": [
{
"value": 1.0,
"flag": null,
"count": 1
},
{
"value": 1.0,
"flag": null,
"count": 1
},
{
"value": 1.0,
"flag": null,
"count": 1
},
{
"value": 1.0,
"flag": null,
"count": 1
},
{
"value": 0.8,
"flag": null,
"count": 1
}
]
}
},
"metas": [
{
"name": "YH_fl_order_002_province",
"dataType": null,
"dataTypeName": "VARCHAR",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "sum_quantity",
"dataType": null,
"dataTypeName": "BIGINT",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "metric_time__day",
"dataType": null,
"dataTypeName": "DATE",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "sum_quantity__proportion__metric_time__day,YH_fl_order_002_province",
"dataType": null,
"dataTypeName": "DOUBLE",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "sum_quantity__proportion__metric_time__day",
"dataType": null,
"dataTypeName": "DOUBLE",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "YH_fl_order_002_city",
"dataType": null,
"dataTypeName": "VARCHAR",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
}
]
},
"success": true,
"code": "200",
"message": null,
"traceId": "626cb523172b457ba5d6b97911aef76e.251.17187792563050015"
}
Example 6: Query order count, within-province order count ranking, and national order count ranking by metric date (day), province, and city
- Request
{
"metrics": [
"orderCount",
"orderCount__rank__desc__metric_time__day,province",
"orderCount__rank__desc__metric_time__day"
],
"dimensions": [
"metric_time__day",
"province",
"city"
],
"timeConstraint": "(['metric_time']>=date(\"2024-03-04\")) AND (['metric_time']<=date(\"2024-03-19\"))",
"filters": [],
"limit": 5,
"offset": ""
}
- Response
{
"data": {
"queryId": "0355d4b483d44fd4be6ffce03c32a4db",
"warning": null,
"sql": "SELECT `t2`.`province` AS `YH_fl_order_002_province`, `t2`.`f3` AS `sum_quantity`, RANK() OVER (PARTITION BY `t2`.`order_date` ORDER BY ((`t2`.`f3`) IS NULL) DESC, (`t2`.`f3`) DESC) AS `sum_quantity__rank__metric_time__day__desc`, `t2`.`order_date` AS `metric_time__day`, `t2`.`f4` AS `sum_quantity__rank__metric_time__day,YH_fl_order_002_province__desc`, `t2`.`city` AS `YH_fl_order_002_city`\nFROM (SELECT `t1`.`province`, `t1`.`f3`, `t1`.`order_date`, RANK() OVER (PARTITION BY `t1`.`order_date`, `t1`.`province` ORDER BY ((`t1`.`f3`) IS NULL) DESC, (`t1`.`f3`) DESC) AS `f4`, `t1`.`city`\nFROM (SELECT `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`province`, SUM(`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`quantity`) AS `f3`, `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`order_date`, `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`city`\nFROM `default_catalog`.`aloudatacan`.`tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002` AS `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`\nGROUP BY `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`city`, `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`province`, `tn_597771_correctness_test_qq_datasource__default__YH_fl_order_002`.`order_date`) AS `t1`) AS `t2`\nORDER BY (`t2`.`order_date`) DESC\nLIMIT 5",
"table": {
"columns": {
"province": [
{
"value": "Jiangsu Province",
"flag": null,
"count": 1
},
{
"value": "Jiangsu Province",
"flag": null,
"count": 1
},
{
"value": "Anhui Province",
"flag": null,
"count": 1
},
{
"value": "Zhejiang Province",
"flag": null,
"count": 1
},
{
"value": "Jiangsu Province",
"flag": null,
"count": 1
}
],
"orderCount__rank__metric_time__day,province__desc": [
{
"value": 1,
"flag": null,
"count": 1
},
{
"value": 1,
"flag": null,
"count": 1
},
{
"value": 1,
"flag": null,
"count": 1
},
{
"value": 1,
"flag": null,
"count": 1
},
{
"value": 2,
"flag": null,
"count": 1
}
],
"orderCount__rank__metric_time__day__desc": [
{
"value": 1,
"flag": null,
"count": 1
},
{
"value": 3,
"flag": null,
"count": 1
},
{
"value": 2,
"flag": null,
"count": 1
},
{
"value": 1,
"flag": null,
"count": 1
},
{
"value": 3,
"flag": null,
"count": 1
}
],
"city": [
{
"value": "Nanjing",
"flag": null,
"count": 1
},
{
"value": "Nanjing",
"flag": null,
"count": 1
},
{
"value": "Hefei",
"flag": null,
"count": 1
},
{
"value": "Hangzhou",
"flag": null,
"count": 1
},
{
"value": "Suzhou",
"flag": null,
"count": 1
}
],
"metric_time__day": [
{
"value": "2024-03-18",
"flag": null,
"count": 1
},
{
"value": "2024-03-17",
"flag": null,
"count": 1
},
{
"value": "2024-03-17",
"flag": null,
"count": 1
},
{
"value": "2024-03-17",
"flag": null,
"count": 1
},
{
"value": "2024-03-16",
"flag": null,
"count": 1
}
],
"orderCount": [
{
"value": 20,
"flag": null,
"count": 1
},
{
"value": 5,
"flag": null,
"count": 1
},
{
"value": 8,
"flag": null,
"count": 1
},
{
"value": 16,
"flag": null,
"count": 1
},
{
"value": 3,
"flag": null,
"count": 1
}
]
}
},
"metas": [
{
"name": "province",
"dataType": null,
"dataTypeName": "VARCHAR",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "orderCount",
"dataType": null,
"dataTypeName": "BIGINT",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "orderCount__rank__metric_time__day__desc",
"dataType": null,
"dataTypeName": "BIGINT",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "metric_time__day",
"dataType": null,
"dataTypeName": "DATE",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "orderCount__rank__metric_time__day,province__desc",
"dataType": null,
"dataTypeName": "BIGINT",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
},
{
"name": "city",
"dataType": null,
"dataTypeName": "VARCHAR",
"displaySize": null,
"schemaName": null,
"scale": null,
"precision": null,
"tableName": null
}
]
},
"success": true,
"code": "200",
"message": null,
"traceId": "626cb523172b457ba5d6b97911aef76e.243.17187799641780025"
}