Query generation based on merger of subqueries
Systems and methods for query generation based on merger of subqueries are described. For example, methods may include accessing a first join graph representing tables in a database, wherein the first join graph has vertices corresponding to respective tables in the database and directed edges corresponding to join relationships; receiving a first query specification that references data in two or more of the tables of the database to specify multiple subqueries in a set of subqueries; checking that two or more subqueries from the set of subqueries have the same join graph; checking that the two or more subqueries have the same set of grouping columns; responsive, at least in part, to the two or more subqueries having the same join graph and the same set of grouping columns, merging the two or more subqueries to obtain a consolidated query.
1. A method comprising:
outputting, by a database analysis system, results data responsive to a request for data, wherein outputting the results data includes:
obtaining data expressing the request for data in a first form that is incompatible with a defined structured data access language of a database;
in response to obtaining the data expressing the request for data, generating a query specification expressing the request for data in a second form that is implemented by the database analysis system and is incompatible with the defined structured data access language;
generating a transformed query specification by transforming the query specification, wherein transforming the query specification includes:
determining that a first subquery portion of the query specification is mergeable with a second subquery portion of the query specification; and
in response to determining that the first subquery portion of the query specification is mergeable with the second subquery portion of the query specification, generating a merged subquery portion of the transformed query specification by merging the first subquery portion with the second subquery portion;
generating one or more database queries, in a form that is compatible with the defined structured data access language, in accordance with the transformed query specification; and
obtaining the results data using data output by the database in response to execution of the one or more database queries.
2. The method of claim 1 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that a filter on an aggregate expression is absent from the first subquery portion; and
determining that a filter on an aggregate expression is absent from the second subquery portion.
3. The method of claim 1 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that a windowing function is absent from the first subquery portion; and
determining that a windowing function is absent from the second subquery portion.
4. The method of claim 1 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that an aggregate distinct function is absent from the first subquery portion; and
determining that an aggregate distinct function is absent from the second subquery portion.
5. The method of claim 1 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that the first subquery portion includes one or more grouping columns; and
determining that the second subquery portion includes the one or more grouping columns and omits other grouping columns.
6. The method of claim 1 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that data associating the first subquery portion of the query specification with a results visualization is absent from the query specification; and
determining that data associating the second subquery portion of the query specification with a results visualization is absent from the query specification.
7. The method of claim 1 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that the first subquery portion includes a first join clause that indicates a first type of join relationship between a first table and a second table; and
determining that the second subquery portion includes a second join clause that indicates the first type of join relationship between the first table and the second table.
8. The method of claim 7 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that a join clause indicating a type of join relationship that is absent from the second subquery portion is absent from the first subquery portion; and
determining that a join clause indicating a type of join relationship that is absent from the first subquery portion is absent from the second subquery portion.
9. The method of claim 7 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that the first table is stored in the database; and
determining that the second table is stored in the database.
10. A database analysis system, comprising:
a memory, wherein the memory stores instructions for query generation based on merger of subqueries;
a processor that executes the instruction to output results data responsive to a request for data, wherein to output the results data the processor executes the instruction to:
obtain data expressing the request for data in a first form that is incompatible with a defined structured data access language of a database;
in response to obtaining the data expressing the request for data, generate a query specification expressing the request for data in a second form that is implemented by the database analysis system and is incompatible with the defined structured data access language;
generate a transformed query specification, wherein, to generate the transformed query specification, the processor executes the instructions to transform the query specification, wherein to transform the query specification the processor executes the instructions to:
determine that a first subquery portion of the query specification is mergeable with a second subquery portion of the query specification; and
in response to determining that the first subquery portion of the query specification is mergeable with the second subquery portion of the query specification, generate a merged subquery portion of the transformed query specification, wherein, to generate the merged subquery portion, the processor executes the instructions to merge the first subquery portion with the second subquery portion;
generate one or more database queries, in a form that is compatible with the defined structured data access language, in accordance with the transformed query specification; and
obtain the results data using data output by the database in response to execution of the one or more database queries.
11. The system of claim 10 , wherein to determine that the first subquery portion is mergeable with the second subquery portion, the processor executes the instruction to:
determine that a filter on an aggregate expression is absent from the first subquery portion; and
determine that a filter on an aggregate expression is absent from the second subquery portion.
12. The system of claim 10 , wherein to determine that the first subquery portion is mergeable with the second subquery portion, the processor executes the instruction to:
determine that a windowing function is absent from the first subquery portion;
determine that a windowing function is absent from the second subquery portion;
determine that an aggregate distinct function is absent from the first subquery portion;
determine that an aggregate distinct function is absent from the second subquery portion;
determine that the first subquery portion includes one or more grouping columns;
determine that the second subquery portion includes the one or more grouping columns and omits other grouping columns;
determining that data associating the first subquery portion of the query specification with a results visualization is absent from the query specification; and
determining that data associating the second subquery portion of the query specification with a results visualization is absent from the query specification.
13. The system of claim 10 , wherein to determine that the first subquery portion is mergeable with the second subquery portion, the processor executes the instruction to:
determine that the first subquery portion includes a first join clause that indicates a first type of join relationship between a first table and a second table; and
determine that the second subquery portion includes a second join clause that indicates the first type of join relationship between the first table and the second table.
14. The system of claim 13 , wherein to determine that the first subquery portion is mergeable with the second subquery portion, the processor executes the instruction to:
determine that a join clause indicating a type of join relationship that is absent from the second subquery portion is absent from the first subquery portion; and
determine that a join clause indicating a type of join relationship that is absent from the first subquery portion is absent from the second subquery portion.
15. The system of claim 13 , wherein to determine that the first subquery portion is mergeable with the second subquery portion, the processor executes the instruction to:
determine that the first table is stored in the database; and
determine that the second table is stored in the database.
16. A non-transitory computer-readable storage medium that includes instructions that, when executed by a processor, facilitate performance of operations comprising:
outputting, by a database analysis system, results data responsive to a request for data, wherein outputting the results data includes:
obtaining data expressing the request for data in a first form that is incompatible with a defined structured data access language of a database;
in response to obtaining the data expressing the request for data, generating a query specification expressing the request for data in a second form that is implemented by the database analysis system and is incompatible with the defined structured data access language;
generating a transformed query specification by transforming the query specification, wherein transforming the query specification includes:
determining that a first subquery portion of the query specification is mergeable with a second subquery portion of the query specification; and
in response to determining that the first subquery portion of the query specification is mergeable with the second subquery portion of the query specification, generating a merged subquery portion of the transformed query specification by merging the first subquery portion with the second subquery portion;
generating one or more database queries, in a form that is compatible with the defined structured data access language, in accordance with the transformed query specification; and
obtaining the results data using data output by the database in response to execution of the one or more database queries.
17. The non-transitory computer-readable storage medium of claim 16 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that a filter on an aggregate expression is absent from the first subquery portion;
determining that a filter on an aggregate expression is absent from the second subquery portion;
determining that a windowing function is absent from the first subquery portion;
determining that a windowing function is absent from the second subquery portion;
determining that an aggregate distinct function is absent from the first subquery portion;
determining that an aggregate distinct function is absent from the second subquery portion;
determining that the first subquery portion includes one or more grouping columns;
determining that the second subquery portion includes the one or more grouping columns and omits other grouping columns;
determining that data associating the first subquery portion of the query specification with a results visualization is absent from the query specification; and
determining that data associating the second subquery portion of the query specification with a results visualization is absent from the query specification.
18. The non-transitory computer-readable storage medium of claim 17 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that the first subquery portion includes a first join clause that indicates a first type of join relationship between a first table and a second table; and
determining that the second subquery portion includes a second join clause that indicates the first type of join relationship between the first table and the second table.
19. The non-transitory computer-readable storage medium of claim 18 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that a join clause indicating a type of join relationship that is absent from the second subquery portion is absent from the first subquery portion; and
determining that a join clause indicating a type of join relationship that is absent from the first subquery portion is absent from the second subquery portion.
20. The non-transitory computer-readable storage medium of claim 19 , wherein determining that the first subquery portion is mergeable with the second subquery portion includes:
determining that the first table is stored in the database; and
determining that the second table is stored in the database.