CODEWITHSUNDEEP
sqloracle-database
Rogue258
Rogue258

Reputation: 135

Iterating through Dynamic variable in Oracle SQL

I have two tables table_Day and table_A

table_Day

I Date
1 05-02-2021
2 05-03-2021
3 05-04-2021
4 05-05-2021
5 05-06-2021

and so on. It goes till 365 days

Table_B

Cust Added_Date Bill No Cust_Setup_Dt
Mark 05-03-2021 1A 05-03-2021
Mark 05-03-2021 1P 05-03-2021
Dave 05-03-2021 35B 05-03-2021
Joan 05-03-2021 40C 05-03-2021
Mark 05-04-2021 2A 05-03-2021
Mark 05-04-2021 54A 05-03-2021
Mark 05-04-2021 88A 05-03-2021
Phil 05-04-2021 3Z 05-04-2021
Joan 05-04-2021 2D 05-03-2021
Joan 05-04-2021 57L 05-03-2021
Mark 05-05-2021 10E 05-03-2021
STUK 05-05-2021 12F 05-05-2021
WILL 05-05-2021 20ER 05-05-2021

and so on. I need to find the cumulative count of bills for each customer for each day, upto that day. The sample output would look something like this

Cust Day Cumulative Count of Bills
Mark 05-03-2021 2
Mark 05-04-2021 5
Mark 05-05-2021 6
Joan 05-03-2021 1
Joan 05-04-2021 3

Here, on day 1 Mark had only 2 bills. On day 2, Mark had 3 bills, so total cumulative count becomes Count of (Day1 + Day2) = 5. On day 3 total count becomes (count(day 1 + day 2 + day 3)) = 6 and so on. Need to do this for all customers. Current code which I have is logically correct, but because of the size of data (table_A has billions of data points across hundreds of rows and columns) and table_Day is for 365 days. Computationally it is extremely time consuming.

with table_Day (I, DT) AS 
(
  SELECT 1, SYSDATE FROM DUAL
    UNION ALL
 SELECT I + 1, DT - INTERVAL '1' DAY FROM Dates_Index WHERE I < 365
)

select A.cust,A.count(distinct (A.Bill_No)) as cul_count,A.Added_Date,A.cust_setup_dt,D.DT
from table_Day D
cross join lateral 
table_A A
WHERE a.ADDED_DATE between (A.Cust_setup_dt) and (D.DT)
group by A.cust,D.DT,A.Added_Date

This code takes too much time that it is not feasible computationally. Since the cumulative count is basically just count of all bills till previous day + current day, iterating through the table for all Added_Date for every Day in table_Day seems doing same processes again and again. Instead I was thinking to store the cumulative count in a temporary variable and update it by adding the count of the current day to the number already stored. I want the logic to be something like this.

for cust = x (where x = 1 to n)

(Declare @temp_cul_count = 0
Declare @temp_added_date


    (for i = 05-02-2021 to (05-06-2021) 
     cul_count = count(distinct(bills)
     temp_cul_count = temp_cul_count + cul_count
     print(cul_count),print(table_day.DAY) /*Basically storing the updated cul_count and day in table*/
     i++
     if i > a.added_date
     break)
)
x++

The above code is basic logic which I want to implement. However, I have no idea how to put it in Oracle SQL.

Upvotes: 0

Views: 81

Answers (1)

MT0
MT0

Reputation: 167867

You can use a PARTITIONed OUTER JOIN to join your calendar for each customer and then find the cumulative total with the SUM analytic function:

WITH calendar (day) AS (
  SELECT TRUNC(SYSDATE, 'YYYY') + LEVEL - 1
  FROM   DUAL
  CONNECT BY TRUNC(SYSDATE, 'YYYY') + LEVEL - 1 < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12)
)
select A.cust,
       C.day,
       SUM(COALESCE(bill_count, 0)) OVER (PARTITION BY a.cust ORDER BY C.day)
         AS cum_bill_count
from   calendar c
       LEFT OUTER JOIN (
         SELECT cust,
                added_date,
                COUNT(DISTINCT bill_no) AS bill_count
         FROM   table_a
         GROUP BY
                cust,
                added_date
       ) a
       PARTITION BY (a.cust)
       ON (c.day = a.added_date)

db<>fiddle here

Upvotes: 1

Related Questions