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 timetable
(
person_id INT,
timetable_id INT
);


INSERT INTO
timetable
VALUES
(5215, 57), (5215, 67), (5215, 77), (5215, 87), (5215, 97),
(18943, 221), (18943, 230), (18943, 238),
(21488, 257), (21488, 270)
;
10 rows affected
WITH
summary (person_id, timetable_id, person_rows, person_row_ix)
AS
(
SELECT
person_id,
timetable_id,
COUNT(*) OVER (PARTITION BY person_id),
CAST(ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY timetable_id) AS INT)
FROM
timetable
),
mapping
AS
(
SELECT
*,
-- PRODUCT AGGREGATE, in this case, 5*1*1*1*1 * 3*1*1 * 2*1 == 30
CAST(
ROUND(
EXP(
SUM(CASE WHEN person_row_ix = 1 THEN LOG(person_rows) ELSE 0 END)
OVER ()
),
0
)
AS INT
)
AS total_combinations
FROM
summary
)
SELECT
value AS combination_id,
mapping.*
FROM
combination_id person_id timetable_id person_rows person_row_ix total_combinations
1 5215 57 5 1 30
1 18943 221 3 1 30
1 21488 257 2 1 30
2 5215 67 5 2 30
2 18943 230 3 2 30
2 21488 270 2 2 30
3 5215 77 5 3 30
3 18943 238 3 3 30
3 21488 257 2 1 30
4 5215 87 5 4 30
4 18943 221 3 1 30
4 21488 270 2 2 30
5 5215 97 5 5 30
5 18943 230 3 2 30
5 21488 257 2 1 30
6 5215 57 5 1 30
6 18943 238 3 3 30
6 21488 270 2 2 30
7 5215 67 5 2 30
7 18943 221 3 1 30
7 21488 257 2 1 30
8 5215 77 5 3 30
8 18943 230 3 2 30
8 21488 270 2 2 30
9 5215 87 5 4 30
9 18943 238 3 3 30
9 21488 257 2 1 30
10 5215 97 5 5 30
10 18943 221 3 1 30
10 21488 270 2 2 30
11 5215 57 5 1 30
11 18943 230 3 2 30
11 21488 257 2 1 30
12 5215 67 5 2 30
12 18943 238 3 3 30
12 21488 270 2 2 30
13 5215 77 5 3 30
13 18943 221 3 1 30
13 21488 257 2 1 30
14 5215 87 5 4 30
14 18943 230 3 2 30
14 21488 270 2 2 30
15 5215 97 5 5 30
15 18943 238 3 3 30
15 21488 257 2 1 30
16 5215 57 5 1 30
16 18943 221 3 1 30
16 21488 270 2 2 30
17 5215 67 5 2 30
17 18943 230 3 2 30
17 21488 257 2 1 30
18 5215 77 5 3 30
18 18943 238 3 3 30
18 21488 270 2 2 30
19 5215 87 5 4 30
19 18943 221 3 1 30
19 21488 257 2 1 30
20 5215 97 5 5 30
20 18943 230 3 2 30
20 21488 270 2 2 30
21 5215 57 5 1 30
21 18943 238 3 3 30
21 21488 257 2 1 30
22 5215 67 5 2 30
22 18943 221 3 1 30
22 21488 270 2 2 30
23 5215 77 5 3 30
23 18943 230 3 2 30
23 21488 257 2 1 30
24 5215 87 5 4 30
24 18943 238 3 3 30
24 21488 270 2 2 30
25 5215 97 5 5 30
25 18943 221 3 1 30
25 21488 257 2 1 30
26 5215 57 5 1 30
26 18943 230 3 2 30
26 21488 270 2 2 30
27 5215 67 5 2 30
27 18943 238 3 3 30
27 21488 257 2 1 30
28 5215 77 5 3 30
28 18943 221 3 1 30
28 21488 270 2 2 30
29 5215 87 5 4 30
29 18943 230 3 2 30
29 21488 257 2 1 30
30 5215 97 5 5 30
30 18943 238 3 3 30
30 21488 270 2 2 30