Grouping data in a database

• US 10,025,803 B2
• Filed: 09/19/2016
• Issued: 07/17/2018
• Est. Priority Date: 05/30/2014
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bester, Karla, Chandler, Allan T., Shewell, Mark A., Yates, Stephen J.
• Primary Examiner(s): LE, HUNG D
• Application Number:
  US15/268,688
• Publication Number:
  US 20170004149A1
• Time in Patent Office: 666 Days
• Field of Search: None
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/285 Clustering or classification

Abstract

According to embodiments of the present invention, two or more attributes that are included in a plurality of attributes are aggregated into a group defined by a first data definition language syntax. The first data definition language syntax defines the group as having a groupID and one or more of an attribute definition defined in a comma-separated list and a group definition. The attribute definition is defined by a second data definition syntax. The first data definition language syntax includes the second data definition language syntax. The first data definition language syntax is structured in a manner to allow a database operation associated with the group to be applied to all attributes and/or groups included therein.

Claims

1. A method comprising:

receiving an extended SQL (structured query language) data set defining an extended SQL, with the extended SQL including;

table definition syntax for defining tables including a plurality of columns,

group definition syntax for defining groups of multiple columns, with each group of columns including a subset of the plurality of columns from a table, and

group function syntax for applying a function to all of the data, and only the data, in the group of columns;

defining a first table using the table definition of the extended SQL, with the first table being stored on data storage hardware as a relational database that is structured according to a data definition language (DDL), with the first table being made up of a plurality of tuples, with each tuple respectively including a plurality of attribute values, and with each attribute value respectively corresponding to a column of a plurality of columns of the first table that are defined under the DDL;

defining a first group of columns within the first table using the group definition syntax of the extended SQL, with the first group of columns including at least two columns of the plurality of columns, and with the first group of columns including less than all columns of the plurality of columns; and

performing a first group function to all of the data, and only the data, in the first group of columns of the first table using the group function syntax of the extended SQL;

wherein the method makes a specific improvement to a way that computers operate by facilitating selective application of a database function to only a partial sub-set of columns in a table.

2. The method of claim 1 wherein:

the group definition syntax provides for assignment of a group ID to each defined group;

the group IDs can be used in SQL functions as an argument in SQL functions in place of a column name;

the defining of the first group of columns includes assignment of a first group ID to the first group of columns; and

the performance of the first group function includes placing the first group ID into an argument portion of an SQL function.

3. The method of claim 2 wherein the first group function is one of the following types of SQL functions:

data retrieval function, or data manipulation function.

4. The method of claim 2 further comprising:

outputting a result of the first group function in human understandable form and format.

5. The method of claim 2 wherein:

the table definition syntax includes listing an ordered list of column name assignments; and

the group definition syntax includes placing delimiting characters within the ordered list of column names to delimit the groups.

6. The method of claim 5 wherein the group definition syntax further includes placing group IDs within the ordered list of column names.

7. The method of claim 2 wherein the group definition syntax includes:

providing for definition of an alphanumeric character string; and

automatically placing into a group any columns of a table that have names sufficiently matching the alphanumeric character string.

8. A computer program product comprising:

a machine readable storage device; and

computer code stored on the machine readable storage device, with the computer code including instructions for causing a processor(s) set to perform operations including the following;

receiving an extended SQL (structured query language) data set defining an extended SQL, with the extended SQL including;

table definition syntax for defining tables including a plurality of columns,

group definition syntax for defining groups of multiple columns, with each group of columns including a subset of the plurality of columns from a table, and

group function syntax for applying a function to all of the data, and only the data, in the group of columns;

defining a first table using the table definition of the extended SQL, with the first table being stored on data storage hardware as a relational database that is structured according to a data definition language (DDL), with the first table being made up of a plurality of tuples, with each tuple respectively including a plurality of attribute values, and with each attribute value of each tuple respectively corresponding to a column of a plurality of columns of the first table that are defined under the DDL;

defining a first group of columns within the first table using the group definition syntax of the extended SQL, with the first group of columns including at least two columns of the plurality of columns, and with the first group of columns including less than all columns of the plurality of columns; and

performing a first group function to all of the data, and only the data, in the first group of columns of the first table using the group function syntax of the extended SQL;

wherein the method makes a specific improvement to a way that computers operate by facilitating selective application of a database function to only a partial sub-set of columns in a table.

9. The computer program product of claim 8 wherein:

the group definition syntax provides for assignment of a group ID to each defined group;

the group IDs can be used in SQL functions as an argument in SQL functions in place of a column name;

the defining of the first group of columns includes assignment of a first group ID to the first group of columns; and

the performance of the first group function includes placing the first group ID into an argument portion of an SQL function.

10. The computer program product of claim 9 wherein the first group function is one of the following types of SQL functions:

data retrieval function, or data manipulation function.

11. The computer program product of claim 9 further comprising:

outputting a result of the first group function in human understandable form and format.

12. The computer program product of claim 9 wherein:

the table definition syntax includes listing an ordered list of column name assignments; and

the group definition syntax includes placing delimiting characters within the ordered list of column names to delimit the groups.

13. The computer program product of claim 12 wherein the group definition syntax further includes placing group IDs within the ordered list of column names.

14. The computer program product of claim 9 wherein the group definition syntax includes:

providing for definition of an alphanumeric character string; and

automatically placing into a group any columns of a table that have names sufficiently matching the alphanumeric character string.

15. A computer system comprising:

a processor(s) set;

a machine readable storage device; and

computer code stored on the machine readable storage device, with the computer code including instructions for causing the processor(s) set to perform operations including the following;

receiving an extended SQL (structured query language) data set defining an extended SQL, with the extended SQL including;

table definition syntax for defining tables including a plurality of columns,

group definition syntax for defining groups of multiple columns, with each group of columns including a subset of the plurality of columns from a table, and

group function syntax for applying a function to all of the data, and only the data, in the group of columns;

defining a first table using the table definition of the extended SQL, with the first table being stored on data storage hardware as a relational database that is structured according to a data definition language (DDL), with the first table being made up of a plurality of tuples, with each tuple respectively including a plurality of attribute values, and with each attribute value of each tuple respectively corresponding to a column of a plurality of columns of the first table that are defined under the DDL;

defining a first group of columns within the first table using the group definition syntax of the extended SQL, with the first group of columns including at least two columns of the plurality of columns, and with the first group of columns including less than all columns of the plurality of columns; and

performing a first group function to all of the data, and only the data, in the first group of columns of the first table using the group function syntax of the extended SQL;

wherein the method makes a specific improvement to a way that computers operate by facilitating selective application of a database function to only a partial sub-set of columns in a table.

16. The computer system of claim 15 wherein:

the group definition syntax provides for assignment of a group ID to each defined group;

the group IDs can be used in SQL functions as an argument in SQL functions in place of a column name;

the defining of the first group of columns includes assignment of a first group ID to the first group of columns; and

the performance of the first group function includes placing the first group ID into an argument portion of an SQL function.

17. The computer system of claim 16 wherein the first group function is one of the following types of SQL functions:

data retrieval function, or data manipulation function.

18. The computer system of claim 16 further comprising:

outputting a result of the first group function in human understandable form and format.

19. The computer system of claim 16 wherein:

the table definition syntax includes listing an ordered list of column name assignments; and

the group definition syntax includes placing delimiting characters within the ordered list of column names to delimit the groups.

20. The computer system of claim 19 wherein the group definition syntax further includes placing group IDs within the ordered list of column names.

21. The computer system of claim 16 wherein the group definition syntax includes:

providing for definition of an alphanumeric character string; and

automatically placing into a group any columns of a table that have names sufficiently matching the alphanumeric character string.