How can I create a subquery or function

gg1234 · February 2020

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.

marcothesane · February 2020

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.

Arumugaguru_M · February 2020

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;

betinajessen · October 2022

performance wise which is 2023 calendar better subquery or scalar function ?

VValdar · October 2022

Classic aggregate query is also to consider:

  select i.id
       , i.start_date
       , i.end_date
       , sum(c.holiday) 
    from item          as i
    join calendar_date as c  on c."date" >= i.start_date
                            and c."date" <= i.end_date
group by i.id
       , i.start_date
       , i.end_date
order by i.id asc;

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.

How can I create a subquery or function

Answers

Leave a Comment