主页 / dba / 问题 / 302478
Accepted
Waffles
Asked: 2021-11-12 06:59:24 +0800 CST

从 2 个表中计算日期里程碑之间的日期条目

  • 772

我在表 1 上有一组随机日期的里程碑，在表 2 上有一组随机日期的项目（见下文）。

表格1

里程碑 里程碑日期
一个 2021 年 1 月 1 日
乙 2021 年 3 月 15 日
C 2021 年 5 月 27 日
D 2021 年 9 月 9 日

表2（该表有数百个条目，不同日期跨越全年）

物品编号 项目创建日期
1 2021 年 1 月 17 日
2 2021 年 1 月 18 日
3 2021 年 1 月 25 日
4 2021 年 2 月 12 日
... ...

我需要计算表 2 中日期介于每个里程碑之间的条目数（见下文）。

里程碑 项目
一个 24
乙 31
C 44
D 37

我目前的查询是：

SELECT
    tbl2.Milestone
    ,COUNT(DISTINCT tbl1.ItemID) 'Items'
FROM
    tbl1
        FULL JOIN tbl2
        ON tbl1.CreateDate = tbl2.MilestoneDate
GROUP BY
    tbl2.Milestone,tbl2.MilestoneDate
ORDER BY
    tbl2.MilestoneDate
里程碑 项目计数
无效的 136

我尝试了许多配置来解决这个问题，包括仅使用日期而不是里程碑名称、删除 ORDER BY 以及完全不同的编码方法。解决此问题的最佳方法是什么？注意：我不能使用任何一个表中的键，因为它们会导致跟踪完全不同的东西。我不是这个数据库的 dba，所以我不能自己调整表。

目前，我必须通过指定每个日期范围并运行查询，然后是另一个日期范围并运行（有几十个里程碑不断添加新的里程碑）来手动提取每个值。

count group-by

2 个回答

  1. 试试这个，让我知道你得到了什么：

    SELECT
    tbl2.Milestone,
        (SELECT COUNT(DISTINCT tbl1.ItemID)
         FROM tbl1,
         WHERE tbl1.CreateDate >= tbl2.MilestoneDate AND
               tbl1.CreateDate < LEAD(tbl2.MilestoneDate) OVER (ORDER BY tbl2.MilestoneDate))
FROM
    tbl1
    tbl2
WHERE
    'tbl1.<column> = tbl2.<column> (see below)'
    tbl1.CreateDate >= MIN(tbl2.MilestoneDate) 'AND
    tbl1.CreateDate < <insert max date, if desired>'
GROUP BY
    tbl2.Milestone,tbl2.MilestoneDate
ORDER BY
    tbl2.MilestoneDate

    此外，您的完全加入将排除 CreateDate 不等于 MilestoneDate 的任何 ItemID（除非我整天盯着代码完全失去理智！）。两个表中是否有任何列匹配并且总是相同的？（例如：两个表都有一个应该与每一行匹配的“customerID”字段 - 如果是这样，请将其添加到您的 WHERE 子句中）您需要来自 tbl1 的任何数据，还是只需要项目计数？

    如果上面的方法不起作用，我接下来会试试这个：

    SELECT
    tbl2.Milestone,
    COUNT(DISTINCT tbl1.ItemID)
FROM
    tbl2
        JOIN tbl1
        ON tbl1.CreateDate >= tbl2.MilestoneDate AND
           tbl1.CreateDate < LEAD(tbl2.MilestoneDate) OVER (ORDER BY tbl2.MilestoneDate))
GROUP BY
    tbl2.MilestoneDate, tbl2.Milestone, COUNT(DISTINCT tbl1.ItemID)
ORDER BY
    tbl2.MilestoneDate

    其他一些有用的参考资料：

    获取下一行（用于查找下一个里程碑日期）

    SELECT 子句中带有 COUNT 的子查询

    • 0
  2. Best Answer

    我能够完成这项工作。该方法相当于按日期对里程碑表进行排序，然后拉出一个表格，其中列出了每个里程碑和日期以及前一行中的里程碑和日期。这允许您在现在每行中的 2 个日期之间提取具有创建日期的 ItemID（这是作为 JOIN 的一部分完成的）。

    这种方法还使得在查询的不同阶段插入各种过滤条件变得非常容易。例如，如果您想限制在数据中填充哪些里程碑，您可以将 WHERE 语句添加到最内层或中间子查询。然后，您可以使用最外层的查询过滤 tbl1。

    SELECT
    A.PreviousMilestone 'Milestone'
    ,A.PreviousMilestoneDate 'Milestone Date'
    ,COUNT(DISTINCT tbl1.ItemID) 'Total'
FROM    
    (
        SELECT
            currentmilestone 'Milestone'
            ,milestonedate 'MilestoneDate'
            ,lastmilestone 'PreviousMilestone'
            ,lastmilestonedate 'PreviousMilestoneDate'
        FROM
            (
                SELECT
                    tbl2.MilestoneDate AS milestonedate
                    ,tbl2.Milestone AS currentmilestone
                    ,LAG(tbl2.Milestone) OVER (ORDER BY tbl2.MilestoneDate) AS lastmilestone
                    ,LAG(tbl2.MilestoneDate) OVER (ORDER BY tbl2.MilestoneDate) AS lastmilestonedate
                FROM
                    tbl2
            ) as M
        WHERE
            --filter criteria here
    ) AS A
        JOIN tbl1
        ON tbl1.CreateDate >= A.PreviousMilestoneDate and tbl1.CreateDate < A.MilestoneDate
WHERE
    --filter criteria here
GROUP BY
    A.PreviousMilestone
    ,A.PreviousMilestoneDate
    • 0

相关问题