SQL Aggregation Research Articles

Towards an optimized GROUP by abstraction for large-scale machine learning

Many applications that use large-scale machine learning (ML) increasingly prefer different models for subgroups (e.g., countries) to improve accuracy, fairness, or other desiderata. We call this emerging popular practice learning over groups , analogizing to GROUP BY in SQL, albeit for ML training instead of SQL aggregates. From the systems standpoint, this practice compounds the already data-intensive workload of ML model selection (e.g., hyperparameter tuning). Often, thousands of models may need to be trained, necessitating high-throughput parallel execution. Alas, most ML systems today focus on training one model at a time or at best, parallelizing hyperparameter tuning. This status quo leads to resource wastage, low throughput, and high runtimes. In this work, we take the first step towards enabling and optimizing learning over groups from the data systems standpoint for three popular classes of ML: linear models, neural networks, and gradient-boosted decision trees. Analytically and empirically, we compare standard approaches to execute this workload today: task-parallelism and data-parallelism. We find neither is universally dominant. We put forth a novel hybrid approach we call grouped learning that avoids redundancy in communications and I/O using a novel form of parallel gradient descent we call Gradient Accumulation Parallelism (GAP). We prototype our ideas into a system we call Kingpin built on top of existing ML tools and the flexible massively-parallel runtime Ray. An extensive empirical evaluation on large ML benchmark datasets shows that Kingpin matches or is 4x to 14x faster than state-of-the-art ML systems, including Ray's native execution and PyTorch DDP.

Workload Optimization by Horizontal Aggregation in SQL for Data Mining Analysis

Preparing a data set for analysis is generally the most time consuming task in a data mining project, requiring many complex SQL queries, joining tables, and aggregating columns. Existing SQL aggregations have limitations to prepare data sets because they return one column per aggregated group. In general, a significant manual effort is required to build data sets, where a horizontal layout is required. We propose simple, yet powerful, methods to generate SQL code to return aggregated columns in a horizontal tabular layout, returning a set of numbers instead of one number per row. This new class of functions is called horizontal aggregations. Horizontal aggregations build data sets with a horizontal denormalized layout (e.g., point-dimension, observation variable, instance-feature), which is the standard layout required by most data mining algorithms. We propose three fundamental methods to evaluate horizontal aggregations: ? CASE: Exploiting the programming CASE construct; ? SPJ: Based on standard relational algebra operators (SPJ queries); ? PIVOT: Using the PIVOT operator, which is offered by some DBMSs. Experiments with large tables compare the proposed query evaluation methods. Our CASE method has similar speed to the PIVOT operator and it is much faster than the SPJ method. In general, the CASE and PIVOT methods exhibit linear scalability, whereas the SPJ method does not. For query optimization the distance computation and nearest cluster in the k-means are based on SQL. Workload balancing is the assignment of work to processors in a way that maximizes application performance. The process of load balancing can be generalized into four basic steps: 1. Monitoring processor load and state; 2. Exchanging workload and state information between processors; 3. Decision making; 4. Data migration. The decision phase is triggered when the load imbalance is detected to calculate optimal data redistribution. In the fourth and last phase, data migrates from overloaded processors to under-loaded ones.

A linear programming-based framework for handling missing data in multi-granular data warehouses

Data Warehouse (DW) and OLAP systems are first citizens of Business Intelligence tools. They are widely used in the academic and industrial communities for numerous different fields of application. Despite the maturity of DW and OLAP systems, with the advent of Big Data, more and more sources of data are available, and warehousing this data can lead to important quality issues. In this work, we focus on missing numerical and categorical in presence of aggregated facts. Motivated by the lack of a formal approach for the imputation of this kind of data taking into account all type of aggregation functions (distributive, algebraic and holistic), we propose an new methodology based on linear programming. Our methodology allows dealing with the relaxed constraints over classical SQL aggregation functions. The proposed approach is tested on two well-known datasets. Experiments show the effectiveness of the proposed approach.

Aggregate Searchable Encryption With Result Privacy

With searchable encryption (SE), the user is allowed to extract partial data from stored ciphertexts from the storage server, based on a chosen query of keywords. A majority of the existing SE schemes support SQL search query, i.e. 'Select * where (list of keywords).' However, applications for encrypted data analysis often need to count data matched with a query, instead of data extraction. For such applications, the execution of SQL aggregate query, i.e. 'Count * where (list of keywords)' at server is essential. Additionally, in case of semi-honest server, privacy of aggregate result is of primary concern. In this article, the authors propose an aggregate searchable encryption with result privacy (ASE-RP) that includes ASearch() algorithm. The proposed ASearch() performs aggregate operation (i.e. Count *) on the implicitly searched ciphertexts (for the conjunctive query) and outputs an encrypted result. The server, due to encrypted form of aggregate result, would not be able to get actual count unless having a decryption key and hence ASearch() offers result privacy.

Bounded Approximate Query Processing

OLAP is a core functionality in database systems and the performance is crucial to enable on-time decisions. However, OLAP queries are rather time consuming, especially on large datasets, and traditional exact solutions usually cannot meet the high-performance requirement. Recently, approximate query processing (AQP) has been proposed to enable approximate OLAP. However, existing AQP methods have some limitations. First, they may involve unacceptable errors on skewed data (e.g., long-tail distribution). Second, they require to store large amount of data and have no significant performance improvement. Third, they only support a small subset of SQL aggregation queries. To overcome these limitations, we propose a bounded approximate query processing framework ${\mathtt {BAQ}}$ BAQ . Given a predefined error bound and a set of queries, ${\mathtt {BAQ}}$ BAQ judiciously selects high-quality samples from the data to generate a unified synopsis offline, and then uses the synopsis to answer online queries. Compared with existing methods, ${\mathtt {BAQ}}$ BAQ has the following salient features. (1) ${\mathtt {BAQ}}$ BAQ does not need to generate a synopsis for each query while it only generates a unified synopsis, and thus ${\mathtt {BAQ}}$ BAQ has much smaller synopsis. (2) ${\mathtt {BAQ}}$ BAQ achieves much smaller error than existing studies. Specifically, ${\mathtt {BAQ}}$ BAQ can provide deterministic approximate results (i.e., the estimated query results must be within the error bound with 100 percent confidence) for SQL aggregation queries that do not contain selection conditions on numerical columns. For queries with selection conditions on numerical columns, we propose effective grouping-based techniques and the estimated results are also within the error bound in practice. Experimental results on both real and synthetic datasets show that ${\mathtt {BAQ}}$ BAQ significantly outperforms state-of-the-art approaches. For example, on a Microsoft production dataset (a real dataset with synthetic queries), ${\mathtt {BAQ}}$ BAQ has 10-100× improvement on synopsis size and 10-100× improvement on the error compared with state-of-the-art algorithms.

Detecting measurement issues in SQL arithmetic expressions and aggregations

Research on user errors in retrieving information from SQL databases has focused on erroneous syntax in the query language and erroneous semantics concerning the data model. In the present paper, we investigate a third source of error, namely erroneous aggregations that break the limitations imposed by the numerical properties of the data. An erroneous aggregation might arise because of the SQL programmer’s misunderstanding concerning those numerical properties, or because of a simple mistake. We show that for database queries in the SQL language, significant classes of erroneous aggregations can be detected by non-intrusive, off-line checking, using only a simple set of metadata rules that can be supplied by the data provider. We have implemented software that performs static checks on users’ SQL queries, looking for evidence of misunderstandings concerning the measurement properties of the numerical data.

Fixpoint semantics and optimization of recursive Datalog programs with aggregates

AbstractA very desirable Datalog extension investigated by many researchers in the last 30 years consists in allowing the use of the basic SQL aggregates min, max, count and sum in recursive rules. In this paper, we propose a simple comprehensive solution that extends the declarative least-fixpoint semantics of Horn Clauses, along with the optimization techniques used in the bottom-up implementation approach adopted by many Datalog systems. We start by identifying a large class of programs of great practical interest in which the use of min or max in recursive rules does not compromise the declarative fixpoint semantics of the programs using those rules. Then, we revisit the monotonic versions of count and sum aggregates proposed by Mazuran et al. (2013b, The VLDB Journal 22, 4, 471–493) and named, respectively, mcount and msum. Since mcount, and also msum on positive numbers, are monotonic in the lattice of set-containment, they preserve the fixpoint semantics of Horn Clauses. However, in many applications of practical interest, their use can lead to inefficiencies, that can be eliminated by combining them with max, whereby mcount and msum become the standard count and sum. Therefore, the semantics and optimization techniques of Datalog are extended to recursive programs with min, max, count and sum, making possible the advanced applications of superior performance and scalability demonstrated by BigDatalog (Shkapsky et al. 2016. In SIGMOD. ACM, 1135–1149) and Datalog-MC (Yang et al. 2017. The VLDB Journal 26, 2, 229–248).

A Survey on Preparing Data Sets for Data Mining Analysis using Horizontal Aggregations in SQL

A Survey on Preparing Data Sets for Data Mining Analysis using Horizontal Aggregations in SQL

Preparing Data Sets by Using Horizontal Aggregations in SQL for Data Mining Analysis

Preparing Data Sets by Using Horizontal Aggregations in SQL for Data Mining Analysis

Database Transformation to Build Dataset for Generation of Decision Tree and Extended ER Model

In Data mining project most of the time consuming task is to prepare a required data set for data mining analysis because in general the relational database has collection of tables and views that must be joined, aggregated and transformed in order to build the required data set. As result, most of the complex SQL queries are written multiple times independently from each other and in a disorganized manner. Therefore, the database grows with many tables and views that are not present as entities in the ER model. Similarly existing SQL aggregations having some limitations to prepare normalized data sets because they return only one column per aggregated group. To address this issue, we propose simple methods to generate SQL code to return aggregated columns in a horizontal tabular layout, where every row corresponds to an observation and every column is associated to a one variable. This new class of functions is called horizontal aggregations. Horizontal aggregations is extension of standard SQL aggregation for building data sets with a horizontal denormalized layout, which is input for most of the data mining algorithms. By providing these standard normalized data-set as an input to the Decision tree generation algorithm for generating Decision tree, similarly we can generate extended ER model.

Preparing Data Sets for the Data Mining Analysis using the Most Efficient Horizontal Aggregation Method in SQL

A huge amount of time is needed for making the dataset for the data mining analysis because data mining practitioners required to write complex SQL queries and many tables are to be joined to get the aggregated result. The traditional SQL aggregations prepare the data sets in vertical layout that is; they return result on one column per aggregated group. But for the data mining project, the data set to be required in horizontal layout. In order to transform the data into suitable form the existing three horizontal aggregation methods are used. The existing method for evaluating horizontal aggregation are SPJ (select, project, join) method, CASE method and PIVOT method. The analysis become more efficient if the dataset obtained is in the horizontal form. The main aim is to identify the most efficient method from these three methods in terms of time and space complexity. So these methods are compared using large tables and identified that the CASE method is more efficient than SPJ and PIVOT method.

Exploiting compression and approximation paradigms for effective and efficient online analytical processing over sensor network readings in data grid environments

SUMMARYAggregate queries are useful tools in the context of sensor network‐based systems as they retrieve knowledge from huge amounts of summarized readings to be exploited for knowledge discovery purposes. Actually, data representation and query models are problematic issues for managing sensor network data, because streams produced by sensors are theoretically unbounded. In this paper, we present a Grid framework, called SensorGrid, on the basis of data compression and approximation paradigms, which allows us to provide approximate answers to aggregate queries on summarized sensor network data. These queries are the basis for achieving Online Analytical Processing (OLAP) over sensor network readings in Data Grid environments, with both effectiveness and efficiency. We also present our experience in the context of a real‐life system focused on the management of environmental sensor network data. Another contribution of our research is represented by the extensive experimental evaluation and analysis of SensorGrid, which, in more details, focuses on two main classes of aggregate range queries over sensor readings, namely, (i) the window queries, which apply an SQL aggregation operator over a fixed window over the reading stream produced by the sensor network, and (ii) the continuous queries, which instead consider a ‘moving’ window and produce as output a stream of answers. Both classes of queries are extremely useful to extract summarized knowledge to be exploited by OLAP‐like analysis tools over sensor network data. The experimental results, conducted on both synthetic and real‐life data sets, clearly confirm the benefits deriving from embedding data compression and approximation paradigms into Grid‐based sensor network data‐intensive management systems.Copyright © 2013 John Wiley & Sons, Ltd.

Efficient Tabular Dataset Preparations by the Aggregations in SQL: A Survey

Many data mining algorithms require the result to be transformed into tabular format. Tabular datasets are the suitable input for many data mining approaches. But the existing SQL aggregations cannot produce results in tabular form with more summarized details especially in horizontal tabular form. Here discuss several approaches to produce data sets in tabular format and also present an efficient method to produce results in horizontal tabular format. Alternative methods for the evaluation of new format are also shown here.

Computing Structural Statistics by Keywords in Databases

Keyword search in RDBs has been extensively studied in recent years. The existing studies focused on finding all or top-k interconnected tuple-structures that contain keywords. In reality, the number of such interconnected tuple-structures for a keyword query can be large. It becomes very difficult for users to obtain any valuable information more than individual interconnected tuple-structures. Also, it becomes challenging to provide a similar mechanism like group-&-aggregate for those interconnected tuple-structures. In this paper, we study computing structural statistics keyword queries by extending the group-&-aggregate framework. We consider an RDB as a large directed graph where nodes represent tuples, and edges represent the links among tuples. Instead of using tuples as a member in a group, we consider rooted subgraphs. Such a rooted subgraph represents an interconnected tuple-structure among tuples and some of the tuples contain keywords. The dimensions of the rooted subgraphs are determined by dimensional keywords in a data driven fashion. Two rooted subgraphs are grouped into the same group if they are isomorphic based on the dimensions or in other words the dimensional keywords. The scores of the rooted subgraphs are computed by a user-given score function if the rooted subgraphs contain some of general keywords. Here, the general keywords are used to compute scores rather than determining dimensions. The aggregates are computed using an sql aggregate function for every group based on the scores computed. We give our motivation using a real data set. We propose new approaches to compute structural statistics keyword queries, perform extensive performance studies using two large real data sets and a large synthetic data set, and confirm the effectiveness and efficiency of our approach.

Horizontal Aggregations in SQL to Prepare Data Sets for Data Mining Analysis

Preparing a data set for analysis is generally the most time consuming task in a data mining project, requiring many complex SQL queries, joining tables, and aggregating columns. Existing SQL aggregations have limitations to prepare data sets because they return one column per aggregated group. In general, a significant manual effort is required to build data sets, where a horizontal layout is required. We propose simple, yet powerful, methods to generate SQL code to return aggregated columns in a horizontal tabular layout, returning a set of numbers instead of one number per row. This new class of functions is called horizontal aggregations. Horizontal aggregations build data sets with a horizontal denormalized layout (e.g., point-dimension, observation-variable, instance-feature), which is the standard layout required by most data mining algorithms. We propose three fundamental methods to evaluate horizontal aggregations: CASE: Exploiting the programming CASE construct; SPJ: Based on standard relational algebra operators (SPJ queries); PIVOT: Using the PIVOT operator, which is offered by some DBMSs. Experiments with large tables compare the proposed query evaluation methods. Our CASE method has similar speed to the PIVOT operator and it is much faster than the SPJ method. In general, the CASE and PIVOT methods exhibit linear scalability, whereas the SPJ method does not.

Percentage Aggregation Functions by Extending SQL

Current SQL aggregation functions have evident limitations for computing percentages, for which this paper proposes two SQL aggregation functions. The two novel aggregation functions are easy to use, which have wide applicability and can be efficiently evaluated. They may be used as a framework to study percentage queries and to generate efficient SQL code. Experiments compare our proposed percentage aggregations against queries using OLAP aggregations. The results show that both proposed aggregations are significantly faster than existing OLAP aggregate functions.

Horizontal Aggregations in SQL to Prepare Data Sets for Data Mining Analysis

Data mining is widely used domain for extracting trends or patterns from historical data. However, the databases used by enterprises can't be directly used for data mining. It does mean that Data sets are to be prepared from real world database to make them suitable for particular data mining operations. However, preparing datasets for analyzing data is tedious task as it involves many aggregating columns, complex joins, and SQL queries with sub queries. More over the existing aggregations performed through SQL functions such as MIN, MAX, COUNT, SUM, AVG return a single value output which is not suitable for making datasets meant for data mining. In fact these aggregate functions are generating vertical aggregations. This paper presents techniques to support horizontal aggregations through SQL queries. The result of the queries is the data which is suitable for data mining operations. It does mean that this paper achieves horizontal aggregations through some constructs built that includes SQL queries as well. The methods prepared by this paper include CASE, SPJ and PIVOT. We have developed a prototype application and the empirical results reveal that these constructs are capable of generating data sets that can be used for further data mining operations. Index Terms - Aggregations, SQL, data mining, OLAP, and data set generation.s

Divide-and-approximate: a novel constraint push strategy for iceberg cube mining

The iceberg cube mining computes all cells v, corresponding to GROUP BY partitions, that satisfy a given constraint on aggregated behaviors of the tuples in a GROUP BY partition. The number of cells often is so large that the result cannot be realistically searched without pushing the constraint into the search. Previous works have pushed antimonotone and monotone constraints. However, many useful constraints are neither antimonotone nor monotone. We consider a general class of aggregate constraints of the form f(v)/spl theta//spl sigma/, where f is an arithmetic function of SQL-like aggregates and /spl theta/ is one of <, /spl les/, /spl ges/ >. We propose a novel pushing technique, called divide-and-approximate, to push such constraints. The idea is to recursively divide the search space and approximate the given constraint using antimonotone or monotone constraints in subspaces. This technique applies to a class called separable constraints, which properly contains all constraints built by an arithmetic function f of all SQL aggregates.

Foundations of aggregation constraints

We introduce a new constraint domain, aggregation constraints, that is useful in database query languages, and in constraint logic programming languages that incorporate aggregate functions. We formally study the fundamental problem of determining if a conjunction of aggregation constraints is satisfiable, and show that, for many classes of aggregation constraints, the problem is undecidable. We describe a complete and minimal axiomatization of aggregation constraints, for the SQL aggregate functions min, max, sum, count and average, over a non-empty, finite multiset on several domains. This axiomatization helps identify classes of aggregation constraints for which the satisfiability check is efficient. We present a polynomial-time algorithm that directly checks for satisfiability of a conjunction of aggregation range constraints over a single multiset; this is a practically useful class of aggregation constraints. We discuss the relationships between aggregation constraints over a non-empty, finite multiset of reals, and constraints on the elements of the multiset. We show how these relationships can be used to push constraints through aggregate functions to enable compile-time optimization of database queries involving aggregate functions and constraints.

Improved query performance with variant indexes

The read-mostly environment of data warehousing makes it possible to use more complex indexes to speed up queries than in situations where concurrent updates are present. The current paper presents a short review of current indexing technology, including row-set representation by Bitmaps, and then introduces two approaches we call Bit-Sliced indexing and Projection indexing. A Projection index materializes all values of a column in RID order, and a Bit-Sliced index essentially takes an orthogonal bit-by-bit view of the same data. While some of these concepts started with the MODEL 204 product, and both Bit-Sliced and Projection indexing are now fully realized in Sybase IQ, this is the first rigorous examination of such indexing capabilities in the literature. We compare algorithms that become feasible with these variant index types against algorithms using more conventional indexes. The analysis demonstrates important performance advantages for variant indexes in some types of SQL aggregation, predicate evaluation, and grouping. The paper concludes by introducing a new method whereby multi-dimensional group-by queries, reminiscent of OLAP/Datacube queries but with more flexibility, can be very efficiently performed.