This shows you the differences between two versions of the page.
— |
part-count-by [2015/09/23 20:27] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== PART_COUNT_BY Function ====== | ||
+ | PART_COUNT_BY function returns the number partitions grouped by specified partition columns in the table. | ||
+ | |||
+ | **Syntax**: | ||
+ | |||
+ | <code language="sql"> | ||
+ | PART_COUNT_BY([db_name.]table_name, [part_col, ...]); | ||
+ | </code> | ||
+ | |||
+ | **Parameters:** | ||
+ | |||
+ | | [dbname.]table_name | Identifier, variable or expression | | ||
+ | | part_col | One or more partition columns used for aggregation | | ||
+ | |||
+ | **Return Value:** | ||
+ | |||
+ | * The number of top-level partitions if //part_col// is not specified | ||
+ | * Partition value and total number of existing partitions with the same value if //part_col// is not specified | ||
+ | |||
+ | **Examples:** | ||
+ | |||
+ | Table //db.orders// is partitioned by //dt// and //region// columns and has the following partitions: | ||
+ | |||
+ | | dt=2015-09-01/region=1 | | ||
+ | | dt=2015-09-01/region=2 | | ||
+ | | dt=2015-09-02/region=1 | | ||
+ | | dt=2015-09-03/region=3 | | ||
+ | | dt=2015-09-03/region=2 | | ||
+ | |||
+ | Get the total number of distinct top-level partitions (dt=2015-09-01, dt=2015-09-02 and dt=2015-09-03): | ||
+ | |||
+ | <code language="sql"> | ||
+ | part_count_by(db.orders); | ||
+ | -- | ||
+ | 3 | ||
+ | </code> | ||
+ | |||
+ | Get the top level partitions and the number of its sub-partitions: | ||
+ | |||
+ | <code language="sql"> | ||
+ | part_count_by(db.orders, dt); | ||
+ | -- | ||
+ | dt=2015-09-01 2 | ||
+ | dt=2015-09-02 1 | ||
+ | dt=2015-09-03 2 | ||
+ | </code> | ||
+ | |||
+ | Get the number of specified partitions: | ||
+ | |||
+ | <code language="sql"> | ||
+ | part_count_by(db.orders, region); | ||
+ | -- | ||
+ | region=1 2 | ||
+ | region=2 2 | ||
+ | region=3 1 | ||
+ | </code> | ||
+ | |||
+ | **Compatibility**: HPL/SQL extension. | ||
+ | |||
+ | **Version:** HPL/SQL 0.3.13 | ||
+ | |||
+ | See also: | ||
+ | * [[part-count|PART_COUNT]] - Get the number of partitions | ||
+ | * [[part-loc|PART_LOC]] - Get the location of a partition |