SQL Reference - PARTITION

Example

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT
) PARTITION BY RANGE( YEAR(hired) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

Output

Query OK, 0 rows affected (0.07 sec)

Explanation

This example creates a partitioned table employees. The table is partitioned by the hired column using the range method. Four partitions are defined, where each partition holds rows for which the hired year falls within a given range. For example, partition p1 holds rows with hired years from 1991 to 1995.

Example

CREATE TABLE employees (
    id INT,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    department_id INT
);

INSERT INTO employees
VALUES
    (1, 'John', 'Doe', 1),
    (2, 'Jane', 'Smith', 2),
    (3, 'Alice', 'Johnson', 1),
    (4, 'Bob', 'Brown', 2);

SELECT *
FROM employees
PARTITION BY department_id;

Output

 id | first_name | last_name | department_id
----+------------+-----------+---------------
  1 | John       | Doe       |             1
  3 | Alice      | Johnson   |             1
  2 | Jane       | Smith     |             2
  4 | Bob        | Brown     |             2
(4 rows)

Explanation

In this example, the PARTITION BY clause is used with the SELECT statement to divide the employees table into partitions based on the department_id column. It then returns all data from the employees table, but the rows in each partition are ordered separately.

Example

CREATE PARTITION FUNCTION PartitionRange (INT)
AS RANGE LEFT FOR VALUES (300, 600, 900);

CREATE PARTITION SCHEME PartitionScheme
AS PARTITION PartitionRange TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

CREATE TABLE SampleTable
(
    ID INT IDENTITY,
    Details VARCHAR(20)
)
ON PartitionScheme (ID);

INSERT INTO SampleTable VALUES ('Detail1'), ('Detail2'), ('Detail3'), ('Detail4'), ('Detail5');

Output

SELECT $PARTITION.PartitionRange(ID) AS Partition,
       COUNT(*) AS TotalRows
FROM   SampleTable
GROUP  BY $PARTITION.PartitionRange(ID);

Output:

Partition TotalRows
1         300
2         300
3         300
4         100

Explanation

This code creates a partition of the SampleTable based on the ID field. The data is partitioned into four parts: less than 300, between 300 and 600, between 600 and 900, and greater than 900. The output query shows the count of records that fall into each of the partitions.

Example

SELECT DEPARTMENT_ID,
       LAST_NAME,
       SALARY,
       SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS dept_total
FROM   EMPLOYEES
WHERE  DEPARTMENT_ID IN (10,20,30)
ORDER BY DEPARTMENT_ID, SALARY;

Output

DEPARTMENT_ID  LAST_NAME    SALARY    DEPT_TOTAL
-------------  ----------   -------   -----------
10             Whalen       4400      4400
20             Fay          6000      6000
20             Hartstein    13000     19000
30             Baida        2900      2900
30             Tobias       2800      5700
30             Himuro       2600      8300
30             Colmeres     2500      10800

Explanation

In the SQL query given, the SUM() function is used with the OVER() clause to create a sub-total for each department. The PARTITION BY clause is used to divide the SUM() into partitions based on the DEPARTMENT_ID, and ORDER BY to sort the rows in each partition. This results in a running total of the salaries for each department, ordered by the salary within the department.