SQL Lookup to another table to do aggregation and merge it back to the outer query-CodePudding

Query: Being able to do lookup from Table A to Table B and use an aggregation function based on date criteria referencing date fields from Table A with the date fields from Table B.

Scenario:

I have a car table (contains CAR_ID,Car_START_DT,Car_END_DT) and a car_payments table (contains CAR_ID, Car_Payment_DT, Car_Payment_Amt).

For every car in the car table, I would like to do a lookup into car_payments table using CAR_ID and aggregate by counting the number of Car_Payment_Amt records between the Car_START_DT and Car_END_DT (from car table) using Car_Payment_DT.

For my attempt, I created a subquery to COUNT(Car_Payment_Amt) GROUP BY CAR_ID under car_payments table and JOIN it with Car table based on CAR_ID to get the results but realized that the subquery will be taking longer than expected as the data size grow larger.

How can I do this efficiently using SQL? I did a search and people are saying that using correlated query but it has performance bottleneck. Are there any other options?

CodePudding user response：

Just use a simple join

select
    c.car_id, count(1) as pmt_count
from
    car c 
    join
    car_payment cp on cp.car_id = c.car_id 
        and cp.payment_dt between c.car_start_dt and c.car_end_date
group by
    c.car_id

CodePudding user response：

You can try it without grouping like here:

SELECT  c.CAR_ID, 
        (Select Count(*) From payments 
         Where CAR_ID = c.CAR_ID And PAY_DATE Between c.START_DATE And c.END_DATE) "NO_OF_PAYS"
FROM    cars c

which with following sample data:

WITH
    cars (CAR_ID, START_DATE, END_DATE) AS
        (
            Select 1, To_Date('01.01.2023', 'dd.mm.yyyy'), To_Date('04.01.2023', 'dd.mm.yyyy') From Dual Union All
            Select 2, To_Date('01.01.2023', 'dd.mm.yyyy'), To_Date('06.01.2023', 'dd.mm.yyyy') From Dual Union All
            Select 3, To_Date('03.01.2023', 'dd.mm.yyyy'), To_Date('08.01.2023', 'dd.mm.yyyy') From Dual Union All
            Select 4, To_Date('03.01.2023', 'dd.mm.yyyy'), To_Date('10.01.2023', 'dd.mm.yyyy') From Dual Union All
            Select 5, To_Date('05.01.2023', 'dd.mm.yyyy'), To_Date('12.01.2023', 'dd.mm.yyyy') From Dual Union All
            Select 6, To_Date('07.01.2023', 'dd.mm.yyyy'), To_Date('14.01.2023', 'dd.mm.yyyy') From Dual 
        ),
    payments (CAR_ID, PAY_DATE, AMAUNT) AS
        (
            Select 1, To_Date('01.01.2023', 'dd.mm.yyyy'), 100 From Dual Union All
            Select 1, To_Date('03.01.2023', 'dd.mm.yyyy'), 100 From Dual Union All
            Select 1, To_Date('05.01.2023', 'dd.mm.yyyy'), 100 From Dual Union All
            Select 1, To_Date('06.01.2023', 'dd.mm.yyyy'), 100 From Dual Union All
            Select 3, To_Date('05.01.2023', 'dd.mm.yyyy'), 300 From Dual Union All
            Select 3, To_Date('08.01.2023', 'dd.mm.yyyy'), 300 From Dual Union All
            Select 3, To_Date('11.01.2023', 'dd.mm.yyyy'), 300 From Dual Union All
            Select 5, To_Date('11.01.2023', 'dd.mm.yyyy'), 500 From Dual Union All
            Select 5, To_Date('13.01.2023', 'dd.mm.yyyy'), 500 From Dual Union All
            Select 6, To_Date('06.01.2023', 'dd.mm.yyyy'), 600 From Dual Union All
            Select 6, To_Date('12.01.2023', 'dd.mm.yyyy'), 600 From Dual 
        )

... results as

--  R e s u l t :
--      CAR_ID NO_OF_PAYS
--  ---------- ----------
--           1          2 
--           2          0 
--           3          2 
--           4          0 
--           5          1 
--           6          1