亚马逊红移或雅典娜/presto工作日计算之间的2天

Question

我有一个日历表，但我不确定如何使用亚马逊红移或亚马逊雅典娜(presto)获得工作日减法工作。我看过一些提到使用日历的帖子，但不能让它与红移或雅典娜一起工作。任何帮助都会很感激。这是我目前得到的。这是两个不同的表之间的减法，我需要在工作日。谢谢你

table1.createddate (start date)
table2.createddate (end date)
cal.date (cal table, column 'date' is what i can inner join with)
cal.is_week_end is either yes or no on this table.

SELECT table1.id,
table1.createddate,
table2.NAME,
table2.createddate,
Datediff(day, table1.createddate, table2.createddate) AS age1
FROM   table2
LEFT JOIN table1
ON table2.id = table1.id2;

Answer 1

如果表具有与之匹配的id，则可以使用相关子查询。我认为这将工作:

select t1.*, t2.*,     -- whatever columns you want
(select sum( (c.is_weekend = 'no')::int )
from calendar c
where c.date >= t1.createddate and
c.date <= t2.createddate
) as num_nonweekend_days
from table1 t1 join
table2 t2
on t1.id = t2.id;

如果关联子句有问题(有时会发生)，您还可以使用:

select t1.id,
count(c.date) as num_nonweekend_days
from table1 t1 join
table2 t2
on t1.id = t2.id left join
calendar c
on c.date >= t1.createddate and
c.date <= t2.createddate and
c.is_week_end = 'no'
group by t1.id;

如果需要额外的列，则需要将它们包含在select和group by中。