add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE my_table (
country VARCHAR(32),
value FLOAT,
date date
);
INSERT INTO
my_table
VALUES
('a', 1.4, '2010-02-03'),
('a', 1.4, '2010-02-04'),
('a', 1.3, '2010-02-05'),
('a', 1.4, '2010-02-06'),
('a', 1.2, '2010-02-07'),
('a', 1.4, '2010-02-08'),
('a', 1.5, '2010-02-09'),
('a', 1.7, '2010-02-10'),
('a', 1.4, '2010-02-11'),
('a', 1.6, '2010-02-12'),
('a', 1.4, '2010-02-13'),
('a', 1.5, '2010-02-14'),
('a', 1.3, '2010-02-15'),
('a', 1.2, '2010-02-16'),
('b', 1.3, '2010-02-03'),
('b', 1.3, '2010-02-04'),
('b', 1.4, '2010-02-05'),
('b', 1.6, '2010-02-06'),
('b', 1.9, '2010-02-07'),
('b', 1.3, '2010-02-08'),
('b', 1.3, '2010-02-09'),
('b', 1.2, '2010-02-10'),
('b', 1.3, '2010-02-11'),
('b', 1.5, '2010-02-12'),
('b', 1.3, '2010-02-13'),
('b', 1.3, '2010-02-14'),
('b', 1.5, '2010-02-15'),
('c', 1.4, '2010-02-03'),
('c', 1.4, '2010-02-04'),
('c', 1.3, '2010-02-05'),
('c', 1.4, '2010-02-06'),
('c', 1.2, '2010-02-07'),
('c', 1.4, '2010-02-08'),
Records: 105  Duplicates: 0  Warnings: 0
WITH
parameters AS
(
SELECT 5 AS target_size
),
sorted AS
(
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY country
ORDER BY date
) - 1
AS row_id,
COUNT(*) OVER (
PARTITION BY country
)
AS original_size
FROM
my_table
CROSS JOIN
parameters
),
partitioned AS
(
SELECT
country,
value,
date,
row_id,
original_size,
CASE WHEN original_size <= target_size THEN 1 ELSE original_size - 1 END AS step,
CASE WHEN original_size <= target_size THEN 1 ELSE target_size - 1 END AS target_size
FROM
sorted
)
country value date row_id original_size sample_size
a 1.4 2010-02-03 0 14 5
a 1.2 2010-02-07 4 14 5
a 1.7 2010-02-10 7 14 5
a 1.4 2010-02-13 10 14 5
a 1.2 2010-02-16 13 14 5
b 1.3 2010-02-03 0 13 5
b 1.6 2010-02-06 3 13 5
b 1.3 2010-02-09 6 13 5
b 1.5 2010-02-12 9 13 5
b 1.5 2010-02-15 12 13 5
c 1.4 2010-02-03 0 12 5
c 1.4 2010-02-06 3 12 5
c 1.5 2010-02-09 6 12 5
c 1.6 2010-02-12 9 12 5
c 1.5 2010-02-14 11 12 5
d 1.3 2010-02-03 0 11 5
d 1.6 2010-02-06 3 11 5
d 1.3 2010-02-08 5 11 5
d 1.3 2010-02-11 8 11 5
d 1.3 2010-02-13 10 11 5
e 1.4 2010-02-03 0 10 5
e 1.4 2010-02-06 3 10 5
e 1.4 2010-02-08 5 10 5
e 1.7 2010-02-10 7 10 5
e 1.6 2010-02-12 9 10 5
f 1.3 2010-02-03 0 9 5
f 1.4 2010-02-05 2 9 5
f 1.9 2010-02-07 4 9 5
f 1.3 2010-02-09 6 9 5
f 1.3 2010-02-11 8 9 5
g 1.3 2010-02-03 0 8 5
g 1.4 2010-02-05 2 8 5
g 1.9 2010-02-07 4 8 5
g 1.3 2010-02-09 6 8 5
g 1.2 2010-02-10 7 8 5
h 1.4 2010-02-03 0 7 5
h 1.3 2010-02-05 2 7 5
h 1.4 2010-02-06 3 7 5
h 1.4 2010-02-08 5 7 5
h 1.5 2010-02-09 6 7 5
i 1.3 2010-02-03 0 6 5
i 1.4 2010-02-05 2 6 5
i 1.6 2010-02-06 3 6 5
i 1.9 2010-02-07 4 6 5
i 1.3 2010-02-08 5 6 5
j 1.3 2010-02-03 0 5 5
j 1.3 2010-02-04 1 5 5
j 1.4 2010-02-05 2 5 5
j 1.6 2010-02-06 3 5 5
j 1.9 2010-02-07 4 5 5
k 1.3 2010-02-03 0 4 4
k 1.3 2010-02-04 1 4 4
k 1.4 2010-02-05 2 4 4
k 1.6 2010-02-06 3 4 4
l 1.3 2010-02-03 0 3 3
l 1.3 2010-02-04 1 3 3
l 1.4 2010-02-05 2 3 3
m 1.3 2010-02-03 0 2 2
m 1.3 2010-02-04 1 2 2
n 1.3 2010-02-03 0 1 1
WITH
parameters AS
(
SELECT 5 AS target_size
),
sorted AS
(
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY country
ORDER BY date
) - 1
AS row_id,
COUNT(*) OVER (
PARTITION BY country
)
AS original_size
FROM
my_table
CROSS JOIN
parameters
)
SELECT
*,
COUNT(*) OVER (PARTITION BY country) AS sample_size
FROM
sorted
WHERE
(
mod( row_id * (target_size-1), (original_size-1))
<=
mod((row_id + (original_size-1) - 1) * (target_size-1), (original_size-1))
)
OR
(
country value date target_size row_id original_size sample_size
a 1.4 2010-02-03 5 0 14 5
a 1.2 2010-02-07 5 4 14 5
a 1.7 2010-02-10 5 7 14 5
a 1.4 2010-02-13 5 10 14 5
a 1.2 2010-02-16 5 13 14 5
b 1.3 2010-02-03 5 0 13 5
b 1.6 2010-02-06 5 3 13 5
b 1.3 2010-02-09 5 6 13 5
b 1.5 2010-02-12 5 9 13 5
b 1.5 2010-02-15 5 12 13 5
c 1.4 2010-02-03 5 0 12 5
c 1.4 2010-02-06 5 3 12 5
c 1.5 2010-02-09 5 6 12 5
c 1.6 2010-02-12 5 9 12 5
c 1.5 2010-02-14 5 11 12 5
d 1.3 2010-02-03 5 0 11 5
d 1.6 2010-02-06 5 3 11 5
d 1.3 2010-02-08 5 5 11 5
d 1.3 2010-02-11 5 8 11 5
d 1.3 2010-02-13 5 10 11 5
e 1.4 2010-02-03 5 0 10 5
e 1.4 2010-02-06 5 3 10 5
e 1.4 2010-02-08 5 5 10 5
e 1.7 2010-02-10 5 7 10 5
e 1.6 2010-02-12 5 9 10 5
f 1.3 2010-02-03 5 0 9 5
f 1.4 2010-02-05 5 2 9 5
f 1.9 2010-02-07 5 4 9 5
f 1.3 2010-02-09 5 6 9 5
f 1.3 2010-02-11 5 8 9 5
g 1.3 2010-02-03 5 0 8 5
g 1.4 2010-02-05 5 2 8 5
g 1.9 2010-02-07 5 4 8 5
g 1.3 2010-02-09 5 6 8 5
g 1.2 2010-02-10 5 7 8 5
h 1.4 2010-02-03 5 0 7 5
h 1.3 2010-02-05 5 2 7 5
h 1.4 2010-02-06 5 3 7 5
h 1.4 2010-02-08 5 5 7 5
h 1.5 2010-02-09 5 6 7 5
i 1.3 2010-02-03 5 0 6 5
i 1.4 2010-02-05 5 2 6 5
i 1.6 2010-02-06 5 3 6 5
i 1.9 2010-02-07 5 4 6 5
i 1.3 2010-02-08 5 5 6 5
j 1.3 2010-02-03 5 0 5 5
j 1.3 2010-02-04 5 1 5 5
j 1.4 2010-02-05 5 2 5 5
j 1.6 2010-02-06 5 3 5 5
j 1.9 2010-02-07 5 4 5 5
k 1.3 2010-02-03 5 0 4 4
k 1.3 2010-02-04 5 1 4 4
k 1.4 2010-02-05 5 2 4 4
k 1.6 2010-02-06 5 3 4 4
l 1.3 2010-02-03 5 0 3 3
l 1.3 2010-02-04 5 1 3 3
l 1.4 2010-02-05 5 2 3 3
m 1.3 2010-02-03 5 0 2 2
m 1.3 2010-02-04 5 1 2 2
n 1.3 2010-02-03 5 0 1 1