python - 查找两个日期之间的日期范围并重复列

Question

    A                       B               C
0   10062-35551-49007-45097 8/31/2014 0:00  233
1   10062-35551-49007-45097 10/31/2014 0:00 14
2   10062-35551-49007-45097 12/31/2014 0:00 22

我想要输出，因为 B 和 C 的日期范围之间的所有月份都应在该日期范围内重复，例如:

    A                       B               C
0   10062-35551-49007-45097 8/31/2014 0:00  233
1   10062-35551-49007-45097 9/30/2014 0:00  233
2   10062-35551-49007-45097 10/31/2014 0:00 14
3   10062-35551-49007-45097 11/30/2014 0:00 14
4   10062-35551-49007-45097 12/31/2014 0:00 22

Answer 1

解决此问题的典型方法是首先创建一个引用表来保存月末日期列表。虽然一开始这似乎需要付出一些额外的努力，但这样做的优点是使查询编写起来更加简单(并且很大程度上独立于 RDBMS)。

假设我们创建表 all_dates 并存储 2014 年的所有月末日期:

create table all_dates (dt date not null primary key);
insert into all_dates values('2014-01-31');
insert into all_dates values('2014-02-28');
insert into all_dates values('2014-03-31');
insert into all_dates values('2014-04-30');
insert into all_dates values('2014-05-31');
insert into all_dates values('2014-06-30');
insert into all_dates values('2014-07-31');
insert into all_dates values('2014-08-31');
insert into all_dates values('2014-09-30');
insert into all_dates values('2014-10-31');
insert into all_dates values('2014-11-30');
insert into all_dates values('2014-12-31');

现在我们可以编写一个查询:

• 根据原始表格 B 列中的最小值和最大值选择相关的日期范围
• 使用 NOT EXISTS 条件将每个月末日期连接到原始表中的相关记录

SQL:

SELECT t.A, d.dt B, t.C
FROM 
    all_dates d
    INNER JOIN (
        SELECT MIN(B) minb, MAX(B) maxb FROM mytable
    ) trange  
        ON d.dt >= trange.minb AND d.dt <= trange.maxb 
    INNER JOIN mytable t
        ON t.B <= d.dt
        AND NOT EXISTS (
            SELECT 1 
            FROM mytable t1
            WHERE t1.B <= d.dt AND t1.B > t.B
        )

这个 demo on db-fiddle 与您的示例数据返回:

| A                       | B           | C   |
| ----------------------- | ----------- | --- |
| 10062-35551-49007-45097 | 2014-08-31  | 233 |
| 10062-35551-49007-45097 | 2014-09-30  | 233 |
| 10062-35551-49007-45097 | 2014-10-31  | 14  |
| 10062-35551-49007-45097 | 2014-11-30  | 14  |
| 10062-35551-49007-45097 | 2014-12-31  | 22  |

PS:由于你没有说明你使用的是哪种RDBMS，所以我选择了MySQL；您可能需要根据实际用例调整创建 all_tables 引用表的代码(但最终查询很可能保持不变)。

<小时/>

奖励:这是一个查询，可以正确处理 A 列中存在多个不同值的用例:

SELECT t.A, d.dt B, t.C
FROM 
    all_dates d
    INNER JOIN (
        SELECT A, MIN(B) minb, MAX(B) maxb 
        FROM mytable
        GROUP BY A
    ) trange  
        ON d.dt >= trange.minb AND d.dt <= trange.maxb 
    INNER JOIN mytable t
        ON trange.A = t.A
        AND t.B <= d.dt
        AND NOT EXISTS (
            SELECT 1 
            FROM mytable t1
            WHERE 
                t1.A = t.A 
                AND t1.B <= d.dt 
                AND t1.B > t.B
        )

<强> Demo on DB Fiddle