Analytic View Enhancements to SQL and PL/SQL复制
Parent topic: Big Data and Data Warehousing Solutions
Analytic View Enhancements to SQL and PL/SQL
SQL and PL/SQL enhancements for analytic views are the following:
Star views can be created on fact and dimension tables Analytic views are created automatically for queries of the tables and views Level group caches are created and maintained autonomously Query-scoped base measures Support for remote sources Support for aggregate tables Star support for dimension caches复制
With these enhancements:
Star view and star table queries can use the benefits of analytic views without any code changes Front-end tools can use analytic view calculations by inserting a new expression Level group caches can become more efficient over time based on usage patterns复制
Related Topics
Oracle® Database Data Warehousing Guide复制
Parent topic: Analytic Views
Analytical SQL and Statistical Functions
Bitwise Aggregate Functions Enhanced Analytic Functions New Analytical and Statistical Aggregate Functions复制
Parent topic: Big Data and Data Warehousing Solutions
Bitwise Aggregate Functions
New aggregate functions BIT_AND_AGG, BIT_OR_AGG, and BIT_XOR_AGG enable bitwise aggregation of integer columns and columns that can be converted or rounded to integer values.
Bitwise aggregation functions enable bitwise type processing directly in SQL. Use of these new functions improves overall query performance by eliminating unnecessary data movement and by taking full advantage of other database capabilities such as parallel processing.
Related Topics
Oracle® Database SQL Language Reference Use Bitwise Aggregate Functions lab in the Database 21c New Features workshop in LiveLabs复制
Parent topic: Analytical SQL and Statistical Functions
Enhanced Analytic Functions
Window functions now support the EXCLUDE options of the SQL standard window frame clause. The query_block clause of a SELECT statement now supports the window_clause, which implements the window clause of the SQL standard table expression as defined in the SQL:2011 standard.
Supporting the full ANSI standard enables easier migration of applications that were developed against other standard-compliant database systems.
Related Topics
Oracle® Database Data Warehousing Guide Use Enhanced Analytic Functions lab in the Database 21c New Features workshop in LiveLabs复制
Parent topic: Analytical SQL and Statistical Functions
New Analytical and Statistical Aggregate Functions
New analytical and statistical aggregate functions are available in SQL:
CHECKSUM computes the checksum of the input values or expression.
KURTOSIS functions KURTOSIS_POP and KURTOSIS_SAMP measure the tailedness of a data set where a higher value means more of the variance within the data set is the result of infrequent extreme deviations as opposed to frequent modestly sized deviations. Note that a normal distribution has a kurtosis of zero.
SKEWNESS functions SKEWNESS_POP and SKEWNESS_SAMP are measures of asymmetry in data. A positive skewness is means the data skews to the right of the center point. A negative skewness means the data skews to the left.
All of these new aggregate functions support the keywords ALL, DISTINCT, and UNIQUE.
ANY_VALUE, a function to simplify and optimize the performance of GROUP BY statements, returns a random value in a group and is optimized to return the first value in the group. It ensures that there are no comparisons for any incoming row and eliminates the necessity to specify every column as part of the GROUP BY clause.
With these additional SQL aggregation functions, you can write more efficient code and benefit from faster in-database processing.
Related Topics
Oracle® Database SQL Language Reference Detect Data Tampering with the CHECKSUM Function, Measure Asymmetry in Data with the SKEWNESS Functions, and Measure Tailedness of Data with the KURTOSIS Functions labs in the Database 21c New Features workshop in LiveLabs复制
Parent topic: Analytical SQL and Statistical Functions
Machine Learning for SQL
Adam Optimization Solver for the Neural Network Algorithm Oracle Machine Learning MSET-SPRT Algorithm Oracle Machine Learning XGBoost Algorithm复制
Parent topic: Big Data and Data Warehousing Solutions
Adam Optimization Solver for the Neural Network Algorithm
Adam is an optimization solver for the Neural Network algorithm that is computationally efficient, requires little memory, and is well suited for problems that are large in terms of data or parameters or both.
Adam is a popular extension to stochastic gradient descent. It uses mini-batch optimization and can make progress faster while seeing less data than the other Neural Network optimization solver, Limited-memory Broyden–Fletcher–Goldfarb–Shanno (L-BFGS) with line search.
Related Topics
Oracle® Machine Learning for SQL Concepts复制
Parent topic: Machine Learning for SQL
Oracle Machine Learning MSET-SPRT Algorithm
The Multivariate State Estimation Technique-Sequential Probability Ratio Test (MSET-SPRT) algorithm is a nonlinear, nonparametric anomaly detection technique for monitoring critical processes.
The DBMS_DATA_MINING.ALGO_MSET_SPRT algorithm detects subtle anomalies while producing minimal false alarms. It calibrates expected behavior from available, historical data of the normal operational sequence of monitored signals. It incorporates the learned behavior of the system into a persistent MSET-SPRT model. You can apply the model to new records to detect anomalous behavior.
Related Topics
Oracle® Machine Learning for SQL Concepts复制
Parent topic: Machine Learning for SQL
Oracle Machine Learning XGBoost Algorithm
XGBoost is a highly-efficient, scalable gradient tree boosting machine learning algorithm for regression and classification.
The DBMS_DATA_MINING.ALGO_XBGOOST algorithm prepares training data, builds and persists a model, and applies the model for prediction. You can use it as a stand-alone predictor or incorporate it into real-world production pipelines for a wide range of problems such as ad click-through rate prediction, hazard risk prediction, web text classification, and so on.
Related Topics
Oracle® Machine Learning for SQL Concepts复制
Parent topic: Machine Learning for SQL
Machine Learning for Python
Oracle Machine Learning for Python (OML4Py) Oracle Machine Learning for Python Configuration in DBCA复制
Parent topic: Big Data and Data Warehousing Solutions
