Movatterモバイル変換


[0]ホーム

URL:


You don’t need a vector database - just use Postgres for everything. Read the case study on switching from Pinecone to Neon
PostgreSQL Tutorial
PostgreSQL Tutorial

PostgreSQL CUBE

Summary: in this tutorial, you will learn how to use the PostgreSQLCUBE to generate multiple grouping sets.

Introduction to the PostgreSQL CUBE

PostgreSQLCUBE is a subclause of theGROUP BY clause. TheCUBE allows you to generate multiple grouping sets.

A grouping set is a set of columns to which you want to group. For more information on the grouping sets, check it out theGROUPING SETS tutorial.

The following illustrates the syntax of theCUBE subclause:

SELECT    c1,    c2,    c3,    aggregate (c4)FROM    table_nameGROUP BY    CUBE (c1, c2, c3);

In this syntax:

  • First, specify theCUBE subclause in theGROUP BY clause of the SELECT statement.
  • Second, in the select list, specify the columns (dimensions or dimension columns) which you want to analyze andaggregation function expressions.
  • Third, in theGROUP BY clause, specify the dimension columns within the parentheses of theCUBE subclause.

The query generates all possible grouping sets based on the dimension columns specified inCUBE. TheCUBE subclause is a short way to define multiple grouping sets so the following are equivalent:

CUBE(c1,c2,c3)GROUPINGSETS (    (c1,c2,c3),    (c1,c2),    (c1,c3),    (c2,c3),    (c1),    (c2),    (c3),    () )

In general, if the number of columns specified in theCUBE isn, then you will have 2ncombinations.

PostgreSQL allows you to perform a partial cube to reduce the number of aggregates calculated. The following shows the syntax:

SELECT    c1,    c2,    c3,    aggregate (c4)FROM    table_nameGROUP BY    c1,    CUBE (c1, c2);

PostgreSQL CUBE examples

We will use thesales table created in theGROUPING SETS tutorial for the demonstration.

Sales TableThe following query uses theCUBE subclause to generate multiple grouping sets:

SELECT    brand,    segment,    SUM (quantity)FROM    salesGROUP BY    CUBE (brand, segment)ORDER BY    brand,    segment;

Here is the output:

PostgreSQL CUBE exampleThe following query performs a partial cube:

SELECT    brand,    segment,    SUM (quantity)FROM    salesGROUP BY    brand,    CUBE (segment)ORDER BY    brand,    segment;

PostgreSQL CUBE - partial cube exampleIn this tutorial, you have learned how to use the PostgreSQLCUBE to generate multiple grouping sets.

Last updated on

Was this page helpful?
Thank you for your feedback!

[8]ページ先頭

©2009-2025 Movatter.jp