|
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)
|
