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.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE table2 (
period INT,
col1 INT,
col2 VARCHAR(8),
col3 VARCHAR(8),
col4 INT,
tbl2_amt DECIMAL(18,9)
);
CREATE TABLE
INSERT INTO table2 (period, col1, col2, col3, col4, tbl2_amt) VALUES
(202302, 20110, 'dt', '0000', 1711, 31172.64171),
(202302, 20110, 'dt', '0000', 1290, 470.1353638),
(202302, 20110, 'dt', '0000', 1208, -157.4978849),
(202302, 20110, 'dt', '0000', 8158, 18382.71997),
(202302, 20110, 'dt', '0000', 1200, 388217.7242),
(202302, 20110, 'dt', '0000', 1200, 290454.7377),
(202302, 20110, 'dt', '0000', 1290, 9422.588832),
(202302, 20110, 'dt', '0000', 1711, 505307.5402),
(202302, 20110, 'dt', '0000', 1290, 9980.002115),
(202302, 20110, 'dt', '0000', 1290, 139973.5195),
(202302, 20110, 'dt', '0000', 0000, 513661.3896),
(202302, 20110, 'dt', '0000', 1200, 138179.7272),
(202302, 20110, 'dt', '0000', 1200, 7159.475465),
(202302, 20110, 'dt', '0000', 1236, 268.0203046),
(202302, 20110, 'dt', '0000', 1290, -480.3828257),
(202302, 20110, 'dt', '0000', 1711, 11272.73689),
(202302, 20110, 'dt', '0000', 1200, 1.057529611),
(202302, 20110, 'dt', '0000', 1711, 2084.263959),
(202302, 20110, 'dt', '0000', 1711, 3142.110829),
(202302, 20110, 'dt', '0000', 1712, 23.19162437),
(202302, 20110, 'dt', '0000', 8307, 11835.60702),
(202302, 20110, 'dt', '0000', 1057, 1230.964467),
(202302, 20110, 'dt', '0000', 1903, 271644.247),
(202302, 20110, 'dt', '0000', 1290, 2470.642978),
(202302, 20110, 'dt', '0000', 1057, 46684.64467),
(202302, 20110, 'dt', '0000', 1290, 23518.40102),
(202302, 20110, 'dt', '0000', 1290, 66276.70262),
(202302, 20110, 'dt', '0000', 1290, 244284.5812),
(202302, 20110, 'dt', '0000', 1903, 105582.0749),
(202302, 20110, 'dt', '0000', 1290, 196.4890017),
(202302, 20110, 'dt', '0000', 1208, 891.4974619),
(202302, 20110, 'dt', '0000', 1711, 288288.1557),
(202302, 20110, 'dt', '0000', 1200, 13.21912014),
(202302, 20110, 'dt', '0000', 1200, 2310.7022),
(202302, 20110, 'dt', '0000', 1290, 23006.66244),
INSERT 0 44
select
period, col1, col2, col3, sum(tbl2_amt) as tbl2_amt
from table2
group by
period, col1, col2, col3
period col1 col2 col3 tbl2_amt
202302 20110 dt 0000 4445702.220854321 
SELECT 1
CREATE TABLE table1 (
col1 INT,
col2 VARCHAR(8),
col3 VARCHAR(8),
period INT,
col4 VARCHAR(8),
col5 VARCHAR(8),
col6 VARCHAR(8),
col7 VARCHAR(8),
tbl1_amt DECIMAL(18, 5)
);
CREATE TABLE
INSERT INTO table1 (col1, col2, col3, period, col4, col6, col7, tbl1_amt)
VALUES
(20110, 'dt', '0000', 202302, 'tcp', 'de', 'otfx', 19169.19495),
(20110, 'dt', '0000', 202302, 'tcp', 'de', 'acpy', 416134.8921),
(20110, 'dt', '0000', 202302, 'tcp', 'de', 'forx', -114689.5469);
INSERT 0 3
select
t1.*, t2.tbl2_amt
from (
select
*
, row_number() over(partition by period,col1,col2,col3 order by tbl1_amt desc) as rn
from table1
) AS t1
left join (
select
period, col1, col2, col3, sum(tbl2_amt) as tbl2_amt
from table2
group by
period, col1, col2, col3
) AS t2 ON t1.period = t2.period
AND t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND t1.col3 = t2.col3
AND t1.rn = 1

col1 col2 col3 period col4 col5 col6 col7 tbl1_amt rn tbl2_amt
20110 dt 0000 202302 tcp null de acpy 416134.89210 1 4445702.220854321
20110 dt 0000 202302 tcp null de otfx 19169.19495 2 null
20110 dt 0000 202302 tcp null de forx -114689.54690 3 null 
SELECT 3