Create and populate table t2
Make sure that you read the section The data sets used by the code examples before running the script to create table "t2". In particular, it's essential that you have installed the pgcrypto extension.
The rows in table "t2" are inserted in random order. It is used for demonstrating these window functions:
row_number(),
rank(),
dense_rank(),
percent_rank(),
cume_dist(),
and ntile().
It contains eighteen rows. It has primary key "k" (int) whose values are the dense sequence from 1 through 18. The column "class" (int) has two distinct values, 1 and 2, and there are nine rows with each "class" value.
The values for the column "score", for "class = 1", are the dense sequence from 1 through 9.
And for "class = 2", there are deliberate duplicates so the 2 and 8 each occur twice and 1 and 9 are missing. This scheme allows the difference between rank() and dense_rank() to be seen.
For maximum pedagogic effect, it uses the same technique that table t1 uses to ensure that, with no window ORDER BY clause, a SELECT will return rows in a random order.
This ysqlsh script creates and populates able "t2". Save it as t2.sql.
-- Suppress the spurious warning that is raised
-- when the to-be-deleted table doesn't yet exist.
set client_min_messages = warning;
drop type if exists rt cascade;
drop table if exists t2;
create table t2(
surrogate_pk uuid primary key,
class int not null,
k int not null,
score int not null,
constraint t2_pk unique(class, k),
constraint t2_k_positive check(k > 0),
constraint t2_class_positive check(class > 0));
insert into t2(surrogate_pk, class, k, score)
with
v1 as (
values
(1, 1, 1),
(1, 2, 2),
(1, 3, 3),
(1, 4, 4),
(1, 5, 5),
(1, 6, 6),
(1, 7, 7),
(1, 8, 8),
(1, 9, 9),
(2, 10, 2),
(2, 11, 2),
(2, 12, 2),
(2, 13, 4),
(2, 14, 5),
(2, 15, 6),
(2, 16, 7),
(2, 17, 7),
(2, 18, 9)),
v2 as (
select
gen_random_uuid() as r,
column1,
column2,
column3
from v1)
select
r,
column1,
column2,
column3
from v2
order by r;
Now inspect its contents:
-- Notice the absence of "ORDER BY".
select class, k, score
from t2;
select class, k, score
from t2
order by class, k;
Here is the result of the second SELECT. To make it easier to see the pattern, several blank lines have been manually inserted here between each successive set of rows with the same value for "class". And in the second set, which has ties, one blank line has been inserted between each tie group.
class | k | score
-------+----+-------
1 | 1 | 1
1 | 2 | 2
1 | 3 | 3
1 | 4 | 4
1 | 5 | 5
1 | 6 | 6
1 | 7 | 7
1 | 8 | 8
1 | 9 | 9
2 | 10 | 2
2 | 11 | 2
2 | 12 | 2
2 | 13 | 4
2 | 14 | 5
2 | 15 | 6
2 | 16 | 7
2 | 17 | 7
2 | 18 | 9