As shown on below image, I have two tables;
- Customer : with about 4 million records; and 3 columns
[Customer] : Customer ID
[Date] : Creation date
[Num_Days] : Number of working days to calculate the [next_wkday] in the query result.
- Calendar : with all calendar days; and 3 columns
[ref_date] : dates
[civil_util] : when (1) -> workday; when (0) -> non working day
I need to get the query_result with the three columns of Customer Table and a calculated date [next_wkday], representing the number of working days [Num_Days] after each customer Creation date, jumping the zeros (non working days) in the [civil_util] column.
I've created the query below to calculate the [next_wkday] using the Lead() function. But is not a solution because the offset parameter must be a constant, and we need to use the [Num_Days] value for each Creation Date:
select *, Lead (to_date(ref_date),5) OVER (ORDER BY to_date(ref_date)) AS next_wkday, datediff(Lead (to_date(ref_date),5) OVER (ORDER BY to_date(ref_date)),ref_date) as days_diff from calendar where ref_date >= to_date(now()) and civil_util = 1 limit 1
Basicaly I need to solve two problems: 1 - Must be a query to perform the calculation Because I don't have the profile to create functions in the database.
2 - I need to design a query that achive the Query result, shown in the image, that will able to join both, the Customer table and the calculation query.
So, I need to find another solution. And that solution must work in two engines:
- Impala version : impalad version 2.12.0-cdh5.16.2
- Oracle 11g
I need to reinforce, for performance puposes, that the Customer table has 4 billion records approximatly.
Can anyone help please? My best regards