在两个日期之间添加行 Presto [英] Add rows between two dates Presto

问题描述

我有一个包含 3 列的表 - start、end 和 emp_num.我想生成一个新表，其中包含每个员工的这些日期之间的所有日期.需要使用 Presto.

I have a table that has 3 columns- start, end and emp_num. I want to generate a new table which has all dates between these dates for every employee. Need to use Presto.

我参考了这个链接 - 在 Presto 中的开始日期和结束日期之间的表格中插入日期

I refered this link - inserting dates into a table between a start and end date in Presto

尝试通过创建序列来使用 unnest 函数，但是，我不知道如何通过从另一个表的两列中提取日期来创建序列.

Tried using unnest function by creating sequence but , I don't know how do I create sequence by pulling dates from two columns in another table.

select unnest(seq) as t(days)
from (select sequence(start, end, interval '1' day) as seq 
      from table1)

这是表格和预期格式

Table 1:
start       |  end         | emp_num 
2018/01/01  |   2018/01/05 | 1
2019/02/01  |   2019/02/05 | 2


Expected:
start          | emp_num 
2018/01/01     | 1
2018/01/02     | 1
2018/01/03     | 1
2018/01/04     | 1
2018/01/05     | 1
2019/02/01     | 2
2019/01/02     | 2
2019/02/03     | 2
2019/02/04     | 2
2019/02/05     | 2

推荐答案

这里是一个查询，可以为您的用例完成工作.

Here is a query that might get the job done for your use case.

逻辑是使用Presto sequence() 函数 生成广泛的日期范围(从 2000 年到 2018 年底，您可以根据需要进行调整)，可以与表格连接以生成输出.

The logic is to use Presto sequence() function to generate a wide date range (since year 2000 to end of 2018, you can adapt that as needed), that can be joined with the table to generate the output.

select dt.x, emp_num
from 
    ( select x from unnest(sequence(date '2000-01-01', date '2018-01-31')) t(x) ) dt
    inner join table1 ta on dt.x >= ta.start and dt.x <= ta.end

然而，正如 JNevill 所评论的，创建日历表比每次查询运行时都动态生成它会更有效.

However, as commented JNevill, it would be more efficient to create a calendar table rather than generating it on the fly every time the query runs.

它应该是一个简单的:

create table calendar as
    select x from unnest(sequence(date '1970-01-01', date '2099-01-01')) t(x);

然后您的查询将变为:

select dt.x, emp_num
from 
    calendar dt
    inner join table1 ta on dt.x >= ta.start and dt.x <= ta.end

PS:由于在野外缺少用于 Presto 的 DB Fiddles，我无法测试查询(@PiotrFindeisen - 如果你碰巧读到这个 ​​- Presto fiddle 会很高兴！).

PS : due to the lack of DB Fiddles for Presto in the wild, I could not test the queries (@PiotrFindeisen - if you happen to read this - a Presto fiddle would be nice to have !).

这篇关于在两个日期之间添加行 Presto的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！