Methods and systems for selectively retrieving data to provide a limited dataset for incorporation into a pivot table

• US 10,114,867 B2
• Filed: 05/29/2015
• Issued: 10/30/2018
• Est. Priority Date: 05/29/2015
• Status: Active Grant

• Current Assignee: Google LLC (Alphabet Inc.)
• Original Assignee: Looker Data Sciences, Inc. (Alphabet Inc.)
• Inventors: Marcotte, Daniel, Tabb, Lloyd
• Primary Examiner(s): Beausoliel, Jr., Robert W
• Assistant Examiner(s): Santos, Pedro J
• Application Number:
  US14/726,403
• Publication Number:
  US 20160350308A1
• Time in Patent Office: 1,250 Days
• Field of Search: 707737, 707748, 707723, 707E17089
• CPC Class Codes: G06F 16/00 Information retrieval; Database structures therefor; File system structures therefor
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/24578 using ranking
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: G06F 16/285 Clustering or classification
• CPC Class Codes: G06F 16/35 Clustering; Classification

Abstract

The present disclosure provides inclusive and exclusive approaches, including computer-implemented methods for selectively retrieving data from an initial dataset in a relational database to provide a limited dataset for incorporation into a pivot table. Also provided are related systems and non-transitory computer-readable recording media.

Claims

1. A computer-implemented method for selectively retrieving data from an initial dataset in a relational database to provide a limited dataset for incorporation into a pivot table, the method comprising the following steps performed by one or more computer processors of a computer:

a. receiving in a user interface to a database application program executing in the computer, a designation of a grouping column in the initial dataset, wherein the grouping column comprises one or more groups of grouping values;

b. receiving in the user interface a designation of a partition column in the initial dataset, wherein the partition column comprises one or more partition values;

c. receiving in the user interface a designation of an ordering column in the initial dataset, wherein the ordering column comprises one or more ordering values, and wherein the initial dataset comprises one or more rows, wherein each row comprises a corresponding grouping value, a corresponding partition value, and a corresponding ordering value;

d. receiving in the user interface a designation of a specific partition value;

e. identifying rows in the initial dataset which comprise the specific partition value;

f. selecting the rows that include the specific partition value, and limiting the selected rows based on a ranking of the corresponding ordering value of each selected row to provide a limited set of rows;

g. expanding the limited set of rows to include all rows that share grouping values in common with the grouping values of the limited set of rows to provide the limited dataset;

h. retrieving from memory, a designated row limit, and on condition that a number of rows in the expanded ones of the limited ones of the selected rows exceed the row limit, reducing a number of rows in the expanded ones of the limited ones of the selected rows to cause the number of rows to meet the designated row limit, but on condition that the number of rows in expanded ones of the limited ones of the selected rows falls short of the row limit, inserting additional rows of a different partition value into the expanded ones of the limited ones of the selected rows until the number of rows is equivalent to the designated row limit;

i. inserting the expanded ones of the limited ones of the selected rows into a pivot table comprising a row for each distinct grouping value and a column for each distinct partition value in the limited data set, wherein each distinct grouping value and each distinct partition value forms a distinct grouping value-partition value pair that defines a unique cell in the pivot table and corresponds to a distinct row of the limited dataset, by populating each cell defined by each distinct grouping value-partition value pair with the corresponding ordering value from each distinct row in the limited dataset, and,

j. displaying the pivot table in the user interface to the database application program.

2. The method of claim 1, wherein the ranking of the corresponding ordering value of each selected row is based on either an ascending order or a descending order.

3. The method of claim 1, wherein the steps of receiving a designation of a grouping column, a partition column, and an ordering column in the initial dataset are repeated for additional initial datasets in the relational database.

4. The method of claim 1, wherein a JOIN SQL expression is executed to perform one or more of the steps performed by the one or more computer processors.

5. The method of claim 1, comprising executing an OFFSET SQL expression to sort for other partition values included in the partition column.

6. The method of claim 1, comprising executing a LIMIT SQL expression to limit the partition values included in the partition column.

7. The method of claim 1, further comprising transmitting the limited dataset to an application program included on a client device.

8. The method of claim 1, wherein the step of limiting the selected rows based on a ranking of the corresponding ordering value of each selected row comprises executing a RIGHT JOIN SQL expression to selectively retrieve additional rows that do not comprise the specific partition value to provide the limited dataset.

9. he method of claim 1, wherein the step of limiting the selected rows based on a ranking of the corresponding ordering value of each selected row comprises executing a LEFT JOIN SQL expression to selectively retrieve additional rows that do not comprise the specific partition value to provide the limited dataset.

10. A non-transitory computer-readable recording medium for selectively retrieving data from an initial dataset in a relational database to provide a limited dataset for incorporation into a pivot table comprising instructions, which, when executed by one or more processors, cause the one or more processors of a computer to:

a. receive a designation of a grouping column in the initial dataset, wherein the grouping column comprises one or more groups of grouping values;

b. receive a designation of a partition column in the initial dataset, wherein the partition column comprises one or more partition values;

c. receive a designation of an ordering column in the initial dataset, wherein the ordering column comprises one or more ordering values, and wherein the initial dataset comprises one or more rows, wherein each row comprises a corresponding grouping value, a corresponding partition value, and a corresponding ordering value;

d. receive a designation of a specific partition value;

e. identify rows in the initial dataset which comprise the specific partition value;

f. select the rows that include the specific partition value, and limit the selected rows based on a ranking of the corresponding ordering value of each selected row to provide a limited set of rows;

g. expand the limited set of rows to include all rows that share grouping values in common with the grouping values of the limited set of rows to provide the limited dataset;

h. retrieve from memory of the computer a designated row limit, and on condition that a number of rows in the expanded ones of the limited ones of the selected rows exceed the row limit, reduce a number of rows in the expanded ones of the limited ones of the selected rows to cause the number of rows to meet the designated row limit, but on condition that the number of rows in expanded ones of the limited ones of the selected rows falls short of the row limit, insert additional rows of a different partition value into the expanded ones of the limited ones of the selected rows until the number of rows is equivalent to the designated row limit;

i. insert the expanded ones of the limited ones of the selected rows into a pivot table comprising a row for each distinct grouping value and a column for each distinct partition value in the limited data set, wherein each distinct grouping value and each distinct partition value forms a distinct grouping value-partition value pair that defines a unique cell in the pivot table and corresponds to a distinct row of the limited dataset, by populating each cell defined by each distinct grouping value-partition value pair with the corresponding ordering value from each distinct row in the limited dataset, and,

j. display the pivot table in a user interface to a database application program.

11. A computer-implemented method for selectively retrieving data from an initial dataset in a relational database to provide a limited dataset for incorporation into a pivot table, the method comprising the following steps performed by one or more computer processors:

a. receiving a designation of a grouping column in the initial dataset, wherein the grouping column comprises one or more groups of grouping values;

b. receiving a designation of a partition column in the initial dataset, wherein the partition column comprises a plurality of partition values comprising one or more partition values;

c. receiving a designation of an ordering column in the initial dataset, wherein the ordering column comprises one or more ordering values, and wherein the initial dataset comprises one or more rows, wherein each row comprises a corresponding grouping value, a corresponding partition value, and a corresponding ordering value;

d. partitioning rows of the one or more rows, which share a common partition value, into separate partitions according to the shared common partition value;

e. establishing a ranking value of each of the rows within each separate partition according to the corresponding ordering value of each row;

f. calculating a minimum ranking value for each separate group of grouping values which corresponds to the row within each group having the lowest ranking value;

g. applying a minimum ranking value limit to each of the separate groups of grouping values to exclude groups of grouping values that do not meet the minimum ranking value limit, wherein the group of grouping values not excluded by the minimum ranking value limit provide the limited dataset;

h. retrieving from memory, a designated row limit, and on condition that a number of rows in the expanded ones of the limited ones of the selected rows exceed the row limit, reducing a number of rows in the expanded ones of the limited ones of the selected rows to cause the number of rows to meet the designated row limit, but on condition that the number of rows in expanded ones of the limited ones of the selected rows falls short of the row limit, inserting additional rows of a different partition value into the expanded ones of the limited ones of the selected rows until the number of rows is equivalent to the designated row limit;

i. inserting the expanded ones of the limited ones of the selected rows into a pivot table comprising a row for each distinct grouping value and a column for each distinct partition value in the limited data set, wherein each distinct grouping value and each distinct partition value forms a distinct grouping value-partition value pair that defines a unique cell in the pivot table and corresponds to a distinct row of the limited dataset, by populating each cell defined by each distinct grouping value-partition value pair with the corresponding ordering value from each distinct row in the limited dataset, and,

j. displaying the pivot table in the user interface to the database application program.

12. The method of claim 11, wherein the steps of receiving a designation of a grouping column, a partition column, and an ordering column in the initial dataset are repeated for additional initial datasets in the relational database.

13. The method of claim 11, further comprising using a LIMIT SQL expression to limit the values of interest included in the partition column.

14. The method of claim 11, further comprising transmitting the limited dataset from the relational database to an application program included on a client device.

15. A non-transitory computer-readable recording medium for selectively retrieving data from an initial dataset in a relational database to provide a limited dataset for incorporation into a pivot table comprising instructions, which, when executed by one or more processors, cause the one or more processors to:

a. receive a designation of a grouping column in the initial dataset, wherein the grouping column comprises one or more groups of grouping values;

b. receive a designation of a partition column in the initial dataset, wherein the partition column comprises one or more partition values;

c. receive a designation of an ordering column in the initial dataset, wherein the ordering column comprises one or more ordering values, and wherein the initial dataset comprises one or more rows, wherein each row comprises a corresponding grouping value, a corresponding partition value, and a corresponding ordering value;

d. partition rows of the one or more rows, which share a common partition value, into separate partitions according to the shared common partition value;

e. establish a ranking value of each of the rows within each separate partition according to the corresponding ordering value of each row;

f. calculate a minimum ranking value for each separate group of grouping values which corresponds to the row within each group having the lowest ranking value;

g. apply a minimum ranking value limit to each of the separate groups of grouping values to exclude groups of grouping values that do not meet the minimum ranking value limit, wherein the group of grouping values not excluded by the minimum ranking value limit provide the limited dataset;

h. retrieve from memory of the computer a designated row limit, and on condition that a number of rows in the expanded ones of the limited ones of the selected rows exceed the row limit, reduce a number of rows in the expanded ones of the limited ones of the selected rows to cause the number of rows to meet the designated row limit, but on condition that the number of rows in expanded ones of the limited ones of the selected rows falls short of the row limit, insert additional rows of a different partition value into the expanded ones of the limited ones of the selected rows until the number of rows is equivalent to the designated row limit;

i. insert the expanded ones of the limited ones of the selected rows into a pivot table comprising a row for each distinct grouping value and a column for each distinct partition value in the limited data set, wherein each distinct grouping value and each distinct partition value forms a distinct grouping value-partition value pair that defines a unique cell in the pivot table and corresponds to a distinct row of the limited dataset, by populating each cell defined by each distinct grouping value-partition value pair with the corresponding ordering value from each distinct row in the limited dataset, and,

j. display the pivot table in a user interface to a database application program.