Hi,
I have two tables and I want to get summary information from the second table for each row of the first table, I can see two ways to do this one is with the SQL below but since the first table is very big the group by takes a long time and there is no need since it is unique. The second way is with a function which loops through each row in the first table and does the aggregate function for that row. Does anyone know of a way to do this with SQL or will I have to use a function?
Thanks in advance
Matthew
EXPLAIN select ID, MIN( AA.ALLOCATION - AA.BOOKING_LEVEL ), COUNT(1)
FROM package_rules_expanded PRE, ACCOMMODATION_AVAILABILITY AA
WHERE AA.CODE = ACCOMM_CODE AND AA.CODE_TYPE = ACCOMM_CODE_TYPE
AND AA.ROOM_TYPE = PRE.ROOM_TYPE
AND AA.DATE BETWEEN OUTWARD_DATE AND ( RETURN_DATE - 1 )
GROUP BY ID;
Aggregate (cost=23229579.28..23641565.44 rows=4119862 width=78)
-> Group (cost=23229579.28..23435572.36 rows=41198616 width=78)
-> Sort (cost=23229579.28..23332575.82 rows=41198616 width=78)
Sort Key: pre.id
-> Merge Join (cost=893507.72..10179309.28 rows=41198616 width=78)
Merge Cond: (("outer".accomm_code = "inner".code) AND ("outer".room_type = "inner".room_type) AND ("outer".accomm_code_type = "inner".code_type))
Join Filter: (("inner".date >= "outer".outward_date) AND ("inner".date <= ("outer".return_date - 1)))
-> Sort (cost=80147.71..81166.21 rows=407400 width=38)
Sort Key: pre.accomm_code, pre.room_type, pre.accomm_code_type
-> Seq Scan on package_rules_expanded pre (cost=0.00..28271.00 rows=407400 width=38)
-> Sort (cost=813360.01..823216.61 rows=3942640 width=40)
Sort Key: aa.code, aa.room_type, aa.code_type
-> Seq Scan on accommodation_availability aa (cost=0.00..77409.40 rows=3942640 width=40)