主页 / dba / 问题 / 258424
Accepted
VGJ
Asked: 2020-01-30 05:37:51 +0800 CST

从仅包含开始日期和分区的列表中计算开始日期和结束日期

  • 772

我目前正在使用具有以下表结构的数据集

create or replace TABLE FACT_TBL_PRODUCTSUPPLIER (
    DATEID NUMBER(18,0) NOT NULL,
    PRODUCTNUMBER NUMBER(38,0) NOT NULL,
    SUPPLIERNUMBER NUMBER(38,0) NOT NULL
);

示例数据集：

    DATEID        PRODUCTNUMBER SUPPLIERNUMBER
    2019-07-06    555           1234
    2019-07-06    343           545
    2019-07-10    555           8888
    2019-07-16    555           1234
    2019-08-01    555           1234
    2019-09-15    555           6666

我试图将产品编号与供应商编号保持联系，直到产品被另一家供应商接管。当产品确实被接管时，我应该再为产品添加 30 天，以便它保持链接。

预期输出：

    DATEID       END_DATEID    PRODUCTNUMBER SUPPLIERNUMBER
    2019-07-06   2019-08-07              555           1234
    2019-07-06   2020-02-06              343           545
    2019-07-10   2019-08-13              555           8888
    2019-07-16   2019-08-29              555           1234
    2019-08-01   2019-10-13              555           1234
    2019-09-15   2020-02-06              555           6666

到目前为止，我已经尝试了以下选项：

select td.dateid, 
LEAD(DATEADD(day, +28, dateid)) OVER(ORDER BY dateid) AS EndDate,
productnumber, ps.suppliernumber,
from FACT_TBL_PRODUCTSUPPLIER PS 

select
    distinct dateid,
    coalesce(
        dateid,
        lag(dateid, 1) over(
            partition by
                productnumber,
                suppliernumber
            order by
                dateid asc
        )
    ) as dateid,
    dateadd(
        'day',
        28,
        coalesce(
            lead(dateid) over(
                partition by
                    productnumber,
                    suppliernumber
                order by
                    dateid asc
            ),
            current_date()
        )
    ) end_dateid,
    productnumber,
    suppliernumber
from
    FACT_TBL_PRODUCTSUPPLIER;

如果没有人接管产品编号，则已分配产品的当前供应商应该能够看到它。

我想做的是在有人接管之前应该向供应商展示产品。当有人接管时，结束日期将额外显示 28 天。如果没有人接管特定产品，则供应商应该能够无限期地看到它，例如当前日期。

t-sql snowflake

1 个回答

  1. Best Answer

    为了确保我的回答正确，我将重申要求。结果集应显示供应商控制产品的第一个日期 (DATEID) 和另一个供应商控制产品时产品可见的最后日期 (ENDDATEID)。

    ENDDATEID 应在下一个供应商控制产品的日期之前增加 28 天。如果没有新的供应商控制，ENDDATEID 应显示当前日期 + 28 天。

    如果我正确地说明了这一点，您可以使用 LEAD 和 CTE 来返回您正在寻找的结果集。下面的代码和这里的工作示例：

    WITH CTE AS (
SELECT DATEID
  ,LEAD(DATEID, 1, GETDATE()) OVER (PARTITION BY PRODUCTNUMBER ORDER BY DATEID) AS TAKEOVERDATE
  ,PRODUCTNUMBER
  ,SUPPLIERNUMBER
FROM FACT_TBL_PRODUCTSUPPLIER
)

SELECT DATEID
  ,DATEADD(DAY, (DATEDIFF(DAY, DATEID, TAKEOVERDATE))+28, DATEID) AS ENDDATEID
  ,PRODUCTNUMBER
  ,SUPPLIERNUMBER
FROM CTE

    输出：

    DATEID      ENDDATEID   PRODUCTNUMBER   SUPPLIERNUMBER
----------------------------------------------------
2019-07-06  2020-02-27  343             545
2019-07-06  2019-08-07  555             1234
2019-07-10  2019-08-13  555             8888
2019-07-16  2019-08-29  555             1234
2019-08-01  2019-10-13  555             1234
2019-09-15  2020-02-27  555             6666

    在 LEAD 语法中，我们按 PRODUCTNUMBER 对数据进行分区，以确保我们只获得与该行所代表的产品相关的日期。然后我们按顺序获取下一个 DATEID（在 LEAD 语法中定义为 DATEID 升序）。如果不存在下一个日期值，我们将获得当前日期。这为我们提供了产品被接管的日期（如果尚未被接管，则为当前日期）。

    从这里开始，我们可以在 CTE 之外使用 DATEADD 将 28 天添加到“TAKEOVERDATE”列，以生成所需的 ENDDATEID 值。

    注意：此语法适用于 SQL Server。您没有说明 RDBMS，但标记了问题 T-SQL，所以我假设 SQL Server。

    • 1

相关问题