Data Lake Insight
- Spark SQL Syntax Reference
- Common Configuration Items of Batch SQL Jobs
- SQL Syntax Overview of Batch Jobs
- Databases
- Creating an OBS Table
- Creating a DLI Table
- Deleting a Table
- Viewing Tables
- Modifying a Table
- Syntax for Partitioning a Table
- Importing Data to the Table
- Inserting Data
- Clearing Data
- Exporting Search Results
- Creating a Datasource Connection with an HBase Table
- Creating a Datasource Connection with an OpenTSDB Table
- Creating a Datasource Connection with a DWS table
- Creating a Datasource Connection with an RDS Table
- Creating a Datasource Connection with a CSS Table
- Creating a Datasource Connection with a DCS Table
- Creating a Datasource Connection with a DDS Table
- Views
- Viewing the Execution Plan
- Data Permissions Management
- Data Types
- User-Defined Functions
- Built-in Functions
- Basic SELECT Statements
- Filtering
- Sorting
- Grouping
- JOIN
- Subquery
- Alias
- Set Operations
- WITH...AS
- CASE...WHEN
- OVER Clause
- Flink SQL Syntax
- Identifiers
- Operators
- Change History
- Help Center
- Data Lake Insight
- SQL Syntax Reference
- Spark SQL Syntax Reference
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Common Configuration Items of Batch SQL Jobs
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a DLI Table
- Creating a DLI Table Using the DataSource Syntax
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Filtering
- WHERE Filtering Clause
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Syntax for Partitioning a Table
- Adding Partition Data (Only OBS Tables Supported)
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Grouping
- Column-Based GROUP BY
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Subquery
- Subquery Nested by WHERE
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- CASE...WHEN
- Basic CASE Statement
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- SQL Syntax Constraints and Definitions
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Source Stream
- CloudTable HBase Source Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- CloudTable HBase Sink Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Dimension Table
- Creating a Redis Table
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Custom Stream Ecosystem
- Custom Source Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Built-In Functions
- Mathematical Operation Functions
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- StreamingML
- Anomaly Detection
- Help Center
- Data Lake Insight
- Identifiers
- aggregate_func
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with an HBase Table
- Creating a DLI Table and Associating It with HBase
- Help Center
- Data Lake Insight
- Operators
- Relational Operators
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Databases
- Creating a Database
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with an OpenTSDB Table
- Creating a DLI Table and Associating It with OpenTSDB
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a DWS table
- Creating a DLI Table and Associating It with DWS
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with an RDS Table
- Creating a DLI Table and Associating It with RDS
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a CSS Table
- Creating a DLI Table and Associating It with CSS
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a DCS Table
- Creating a DLI Table and Associating It with DCS
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a DDS Table
- Creating a DLI Table and Associating It with DDS
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Data Permissions Management
- Data Permissions List
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- User-Defined Functions
- Creating a Function
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating an OBS Table
- Creating an OBS Table Using the DataSource Syntax
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- SQL Syntax Overview of Batch Jobs
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a DLI Table
- Creating a DLI Table Using the Hive Syntax
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Viewing Tables
- Viewing Table Creation Statements
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Filtering
- HAVING Filtering Clause
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Syntax for Partitioning a Table
- Renaming a Partition (Only OBS Tables Supported)
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Grouping
- Expression-Based GROUP BY
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Subquery
- Subquery Nested by FROM
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- CASE...WHEN
- CASE Query Statement
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- SQL Syntax Overview of Stream Jobs
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- CloudTable OpenTSDB Sink Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Dimension Table
- Creating an RDS Table
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- StreamingML
- Time Series Forecasting
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with an HBase Table
- Inserting Data to an HBase Table
- Help Center
- Data Lake Insight
- Operators
- Arithmetic Operators
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Databases
- Deleting a Database
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with an OpenTSDB Table
- Inserting Data to the OpenTSDB Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a DWS table
- Inserting Data to the DWS Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with an RDS Table
- Inserting Data to the RDS Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a CSS Table
- Inserting Data to the CSS Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a DCS Table
- Inserting Data to a DCS Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a DDS Table
- Inserting Data to the DDS Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Data Types
- Primitive Data Types
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- User-Defined Functions
- Deleting a Function
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating an OBS Table
- Creating an OBS Table Using the Hive Syntax
- Help Center
- Data Lake Insight
- Identifiers
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Viewing Tables
- Viewing Table Properties
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Grouping
- GROUP BY Using HAVING
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Subquery
- Subquery Nested by HAVING
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Source Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- MRS OpenTSDB Sink Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Built-In Functions
- Temporal Functions
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- StreamingML
- Real-Time Clustering
- Help Center
- Data Lake Insight
- Operators
- Logical Operators
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Databases
- Viewing a Specified Database
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Data Types
- Complex Data Types
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- User-Defined Functions
- Displaying Function Details
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Built-in Functions
- Mathematical Functions
- Help Center
- Data Lake Insight
- Operators
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating an OBS Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Viewing Tables
- Viewing All Columns in a Specified Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Syntax for Partitioning a Table
- Deleting Partitions by Specifying Filter Criteria (Only OBS Tables Supported)
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Subquery
- Multi-Layer Nested Subquery
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Source Stream
- MRS Kafka Source Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- CSS Elasticsearch Sink Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Built-In Functions
- Type Conversion Functions
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- StreamingML
- Deep Learning Model Prediction
- Help Center
- Data Lake Insight
- Identifiers
- attr_expr_list
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Databases
- Viewing All Databases
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- User-Defined Functions
- Displaying All Functions
- Help Center
- Data Lake Insight
- Change History
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a DLI Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Viewing Tables
- Viewing All Partitions in a Specified Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Syntax for Partitioning a Table
- Altering the Partition Location of a Table (Only OBS Tables Supported)
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Temporary Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Source Stream
- Open-Source Kafka Source Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Built-In Functions
- Aggregate Functions
- Help Center
- Data Lake Insight
- Identifiers
- attrs_value_set_expr
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Deleting a Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Viewing Tables
- Viewing Table Statistics
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Syntax for Partitioning a Table
- Updating Partitioned Table Data (Only OBS Tables Supported)
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Dimension Table
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Built-In Functions
- Table-Valued Functions
- Help Center
- Data Lake Insight
- Identifiers
- boolean_expression
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Syntax for Partitioning a Table
- Updating Table Metadata with REFRESH TABLE
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Custom Stream Ecosystem
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Data Permissions Management
- Granting a Permission
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Modifying a Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Data Permissions Management
- Revoking a Permission
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Syntax for Partitioning a Table
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Built-In Functions
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- DWS Sink Stream (JDBC Mode)
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Data Permissions Management
- Showing Granted Permissions
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Importing Data to the Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Built-in Functions
- Aggregate Functions
- percentile_approx
percentile_approx
This function is used to approximate the pth percentile (including floating-point numbers) of a numeric column within a group.
Syntax
percentile_approx(DOUBLE col, p [, B])
Parameters
Parameter |
Mandatory |
Description |
---|---|---|
col |
Yes |
Columns with a data type of numeric. If the values are of any other type, NULL is returned. |
p |
Yes |
The value should be between 0 and 1. Otherwise, NULL is returned. |
B |
Yes |
The parameter B controls the accuracy of the approximation, with a higher value of B resulting in a higher level of approximation. The default value is 10000. If the number of non-repeating values in the column is less than B, an exact percentile is returned. |
Return Values
The return value is of the DOUBLE type.
Example Code
- Calculates the 0.5 percentile of all offering inventories (items), with an accuracy of 100. An example command is as follows:
select stddev_samp(items,0.5, 100) from warehouse;
The command output is as follows:
_c0 500
- When used with group by, it groups all offerings by warehouse (warehourseId) and returns the 0.5 percentile of the offering inventory (items) in the same group, with an accuracy of 100. An example command is as follows:
select warehourseId, stddev_samp(items, 0.5, 100) from warehourse group by warehourseId;
The command output is as follows:
warehouseId _c1 city1 499 city2 354 city3 565
Parent topic: Aggregate Functions
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- User-Defined Functions
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- DWS Sink Stream (OBS-based Dumping)
- Help Center
- Data Lake Insight
- Identifiers
- col_name_list
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Data Permissions Management
- Displaying the Binding Relationship Between All Roles and Users
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Geographical Functions
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- MRS HBase Sink Stream
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- MRS Kafka Sink Stream
- Help Center
- Data Lake Insight
- Identifiers
- condition_list
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Exporting Search Results
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Condition Expression
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- Open-Source Kafka Sink Stream
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with an HBase Table
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Creating a Sink Stream
- File System Sink Stream (Recommended)
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with an OpenTSDB Table
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- JOIN Between Stream Data and Table Data
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a DWS table
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Configuring Time Models
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with an RDS Table
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Pattern Matching
- Help Center
- Data Lake Insight
- Identifiers
- else_result_expression
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a CSS Table
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a DCS Table
- Help Center
- Data Lake Insight
- Flink SQL Syntax
- Reserved Keywords
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Creating a Datasource Connection with a DDS Table
- Help Center
- Data Lake Insight
- Identifiers
- function_name
- Help Center
- Data Lake Insight
- Identifiers
- groupby_expression
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Viewing the Execution Plan
- Help Center
- Data Lake Insight
- Identifiers
- having_condition
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Data Permissions Management
- Help Center
- Data Lake Insight
- Identifiers
- input_expression
- Help Center
- Data Lake Insight
- Identifiers
- join_condition
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- User-Defined Functions
- Help Center
- Data Lake Insight
- Identifiers
- non_equi_join_condition
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Built-in Functions
- Help Center
- Data Lake Insight
- Spark SQL Syntax Reference
- Basic SELECT Statements
- Help Center
- Data Lake Insight
- Identifiers
- partition_col_name
- Help Center
- Data Lake Insight
- Identifiers
- partition_col_value
- Help Center
- Data Lake Insight
- Identifiers
- partition_specs
- Help Center
- Data Lake Insight
- Identifiers
- property_name
- Help Center
- Data Lake Insight
- Identifiers
- property_value
- Help Center
- Data Lake Insight
- Identifiers
- regex_expression
- Help Center
- Data Lake Insight
- Identifiers
- result_expression
- Help Center
- Data Lake Insight
- Identifiers
- select_statement
- Help Center
- Data Lake Insight
- Identifiers
- sql_containing_cte_name
- Help Center
- Data Lake Insight
- Identifiers
- table_comment
- Help Center
- Data Lake Insight
- Identifiers
- table_properties
- Help Center
- Data Lake Insight
- Identifiers
- table_reference
- Help Center
- Data Lake Insight
- Identifiers
- when_expression
- Help Center
- Data Lake Insight
- Identifiers
- where_condition
- Help Center
- Data Lake Insight
- Identifiers
- window_function