Differences

This shows you the differences between two versions of the page.

--- part-count-by [2015/09/23 20:27]
+++ 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);
+--
+</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

HPL/SQL - Procedural SQL on Hadoop, NoSQL and RDBMS

User Tools

Site Tools

Differences

Page Tools