The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
How can I create a subquery or function

On Vertica, how Can I create a user defined function for find a total holiday days between StartDate and EndDate.
for example:
I have two tables 1.table "calendar_day" to keep a calendar days
| date | holiday |
| 1 Jan 20 | 1 |
| 2 Jan 20 | 0 |
| 3 Jan 20 | 0 |
*1 = Holiday 0 <> Holiday
table "ITEM" to keep a data detail include Start and end date for each item.
| ITEM | StartDate | EndDate |
| 1 | 1 Jan 20 | 3 Jan |
| 2 | 1 Jan 20 | 5 Jan |
. . .
So, I would like to find how many holiday days between StartDate and EndDate for each item record.
like a
select sum(holiday) from calendar_day where date between StartDate and EndDate
how can I create a subquery or function to get a result.
Thank you very much.
Tagged:
0
Answers
You can't create a function including a SELECT.
UDx-s, written in C++, Python, Java or R, don't allow cursors as of yet.
And you can't CREATE OR REPLACE FUNCTION .... BEGIN ... SELECT .. RETURN .. END; - You can't put a SELECT into the body of a SQL based function.
Do you need help to create a fullselect returning COUNT(*) of the days in a calendar that are holidays? That would be the workaround. Store that somewhere central, and copy-paste it into all queries needing it - as a Common Table Expression.
Hi,
You can try with Analytic queries and cross join to get the results.
create local temp table calendar_date ("date" date, holiday int) on commit preserve rows;
create local temp table item(id int,start_date date, end_date date) on commit preserve rows;
INSERT INTO calendar_date values('2019-10-01',0);
INSERT INTO calendar_date values('2019-10-02',0);
INSERT INTO calendar_date values('2019-10-03',0);
INSERT INTO calendar_date values('2019-10-04',0);
INSERT INTO calendar_date values('2019-10-05',0);
INSERT INTO calendar_date values('2019-10-06',0);
INSERT INTO calendar_date values('2019-10-07',0);
INSERT INTO calendar_date values('2019-10-08',0);
INSERT INTO calendar_date values('2019-10-09',1);
INSERT INTO calendar_date values('2019-10-10',0);
INSERT INTO calendar_date values('2019-10-11',0);
INSERT INTO calendar_date values('2019-10-12',0);
INSERT INTO calendar_date values('2019-10-13',0);
INSERT INTO calendar_date values('2019-10-14',0);
INSERT INTO calendar_date values('2019-10-15',0);
INSERT INTO calendar_date values('2019-10-16',0);
INSERT INTO calendar_date values('2019-10-17',0);
INSERT INTO calendar_date values('2019-10-18',1);
INSERT INTO calendar_date values('2019-10-19',0);
INSERT INTO calendar_date values('2019-10-20',0);
INSERT INTO calendar_date values('2019-10-21',0);
INSERT INTO calendar_date values('2019-10-22',0);
INSERT INTO calendar_date values('2019-10-23',0);
INSERT INTO calendar_date values('2019-10-24',0);
INSERT INTO calendar_date values('2019-10-25',0);
INSERT INTO calendar_date values('2019-10-26',0);
INSERT INTO calendar_date values('2019-10-27',1);
INSERT INTO calendar_date values('2019-10-28',0);
INSERT INTO calendar_date values('2019-10-29',0);
INSERT INTO calendar_date values('2019-10-30',0);
INSERT INTO calendar_date values('2019-10-31',0);
INSERT INTO calendar_date values('2019-11-01',0);
INSERT INTO calendar_date values('2019-11-02',0);
INSERT INTO calendar_date values('2019-11-03',0);
INSERT INTO calendar_date values('2019-11-04',0);
INSERT INTO calendar_date values('2019-11-05',0);
INSERT INTO calendar_date values('2019-11-06',0);
INSERT INTO calendar_date values('2019-11-07',0);
INSERT INTO calendar_date values('2019-11-08',0);
INSERT INTO calendar_date values('2019-11-09',1);
INSERT INTO calendar_date values('2019-11-10',0);
INSERT INTO calendar_date values('2019-11-11',0);
INSERT INTO calendar_date values('2019-11-12',0);
INSERT INTO calendar_date values('2019-11-13',0);
INSERT INTO calendar_date values('2019-11-14',0);
INSERT INTO calendar_date values('2019-11-15',0);
INSERT INTO calendar_date values('2019-11-16',0);
INSERT INTO calendar_date values('2019-11-17',0);
INSERT INTO calendar_date values('2019-11-18',1);
INSERT INTO calendar_date values('2019-11-19',0);
INSERT INTO calendar_date values('2019-11-20',0);
INSERT INTO calendar_date values('2019-11-21',0);
INSERT INTO calendar_date values('2019-11-22',0);
INSERT INTO calendar_date values('2019-11-23',0);
INSERT INTO calendar_date values('2019-11-24',0);
INSERT INTO calendar_date values('2019-11-25',0);
INSERT INTO calendar_date values('2019-11-26',0);
INSERT INTO calendar_date values('2019-11-27',1);
INSERT INTO calendar_date values('2019-11-28',0);
INSERT INTO calendar_date values('2019-11-29',0);
INSERT INTO calendar_date values('2019-11-30',0);
INSERT INTO item
values (1,'2019-10-01','2019-10-20');
INSERT INTO item
values (2,'2019-10-01','2019-10-31');
INSERT INTO item
values (3,'2019-10-01','2019-10-03');
INSERT INTO item
values (4,'2019-10-01','2019-11-30');
INSERT INTO item
values (5,'2019-10-01','2019-11-10');
select distinct id,start_date,end_date,holiday from
(
select id,start_date,end_date,sum(holiday) over(partition by id) as holiday from item cross join calendar_date
where end_date >= date
order by 1,2
)A;
performance wise which is 2023 calendar better subquery or scalar function ?
Classic aggregate query is also to consider:
There is room for optimization if the number of couple (start_date, end_date) has lots of duplicates.
Oh, just noticed it's an old topic.