db2 使用SQL查找月份范围内的“活跃”日平均值

eiee3dmh 于 6个月前发布在 DB2

关注(0)|答案(1)|浏览(69)

我有一个包含以下列的表：

ID       |  START_DATE   |  END_DATE
1001     |  02/02/2023   |  03/05/2023
1001     |  03/07/2023   |  11/05/2023
1002     |  03/05/2023   |  10/20/2023
1003     |  03/07/2023   |  04/07/2023
1004     |  04/07/2023   |  04/17/2023
1005     |  04/18/2023   |  05/19/2023
1006     |  05/19/2023   |  06/14/2023
1007     |  05/24/2023   |  06/28/2023
1008     |  10/31/2022   |  12/01/2022
1008     |  12/03/2022   |  10/07/2023
1009     |  12/01/2022   |  03/22/2023
1010     |  04/03/2023   |  05/04/2023
1011     |  05/04/2023   |  05/23/2023
1012     |  05/23/2023   |  10/02/2023
1013     |  06/15/2023   |  07/21/2023
1014     |  01/27/2023   |  02/07/2023
1015     |  05/02/2023   |  05/18/2023
1016     |  05/18/2023   |  06/18/2023
1017     |  06/18/2023   |  07/27/2023
1018     |  05/01/2023   |  09/30/2023
1019     |  11/22/2022   |  02/22/2023
1020     |  02/02/2022   |  05/04/2023

字符串
对于一个12个月的时间段（可以包括任何12个月的时间段），我需要每个月的所有日平均值的摘要。
最终产品看起来像这样：

Month   Daily Ave.
Oct-22   37.6
Nov-22   37.9 
Dec-22   24.1 
Jan-23   26.5 
Feb-23   24.0 
Mar-23   35.8 
Apr-23   35.2 
May-23   31.8 
Jun-23   26.5 
Jul-23   20.1 
Aug-23   23.0 
Sep-23   33.2

型
此表包含在两个日期之间处于“活动”状态的ID。
由于一个ID在多个时间段内处于“活动”状态，此表还包含重复的ID。
应该只有一个唯一的ID是“活跃”的任何给定的一天，但我猜可能有坏的数据，以占有重叠的“活跃”天。如果是这样，只有ID应该只计算一次，每天最多。
要确定每月的日平均值，首先在每月的每一天计算所有“活动”ID的日计数，然后将这些日计数相加，再除以该月的天数。
这需要在范围内的12个月内重复进行。
如果你手动计算的话，大概是...
1.检查所有日期范围与2022年10月1日匹配的ID，然后记录该计数。
1.在本月的所有日子重复上述步骤：10/2，10/3，10/4等。
1.将上述步骤每天的所有总数相加，然后除以该月的天数。
1.对范围内的每个月重复上述每个步骤。
其他说明：

月份是日历月份（例如10/1/2022到10/31/2022）-月份从不是滚动月份（例如11/10/2022到12/10/2022）-任何ID的“活动”开始日期可以追溯到几年前，因此您无法将开始日期与所请求的可报告范围内使用的月份进行关联。
通过SQL（特别是DB2风格的SQL）的解决方案可能是什么？
我创建了一个复杂的方法，通过SQL导出到Excel，并为每天，每月和所有ID创建公式数组，无论是否被欺骗，在另一个选项卡中以类似的方式重复这一过程，只针对唯一ID的列表，以说明具有多个“活动”的ID的重复列表。日期范围。这很麻烦，而且容易出现人为错误。

db2

来源：https://stackoverflow.com/questions/77398399/using-sql-to-find-the-active-daily-average-in-a-range-of-months

1条答案

按热度按时间

5lhxktic1#

您需要一个日期表，或者下面的CTE来生成所需的日期范围，然后将您的数据连接到该日期。这将允许您从开始日期到结束日期传播ID，然后使用count（distinct...）从最终结果中删除重复。

WITH dates (dt) AS (
    SELECT DATE('2022-02-01') AS dt
    FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT dt + 1 DAYS
    FROM dates
    WHERE dt < '2023-09-30'
)
SELECT 
    YEAR(d.dt) AS year
  , MONTH(d.dt) AS month
  , COUNT(DISTINCT t.ID) cd_tid
  , COUNT(DISTINCT d.dt) cd_ddt
  , (COUNT(DISTINCT t.ID) * 1.0) / (COUNT(DISTINCT d.dt) * 1.0) AS Daily_Ave
FROM dates d
LEFT JOIN ActiveIDs t ON d.dt BETWEEN t.START_DATE AND t.END_DATE
GROUP BY 
    YEAR(d.dt)
  , MONTH(d.dt)
ORDER BY 
    YEAR(d.dt)
  , MONTH(d.dt)

字符串
| 年|月|CD_TID| CD_DDT|每日_AVE|
| --|--|--|--|--|
| 2022 | 2 | 1 | 28 |电话：035714285714285714|
| 2022 | 3 | 1 | 31 |电话：032258064516129032|
| 2022 | 4 | 1 | 30 |0.03333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333|
| 2022 | 5 | 1 | 31 |电话：032258064516129032|
| 2022 | 6 | 1 | 30 |0.03333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333|
| 2022 | 7 | 1 | 31 |电话：032258064516129032|
| 2022 | 8 | 1 | 31 |电话：032258064516129032|
| 2022 | 9 | 1 | 30 |0.03333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333|
| 2022 | 10 | 2 | 31 |0.064516129032258064|
| 2022 | 11 | 3 | 30 |0.100000000000000000|
| 2022 | 12 | 4 | 31 |129032258064516129|
| 2023 | 1 | 5 | 31 |电话：01290322580645161传真：01290322580645161|
| 2023 | 2 | 6 | 28 |214285714285714285|
| 2023 | 3 | 6 | 31 |0.193548387096774193|
| 2023 | 4 | 8 | 30 |0.26666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666|
| 2023 | 5 | 13 | 31 |0.419354838709677419|
| 2023 | 6 | 10 | 30 |0.33333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333|
| 2023 | 7 | 7 | 31 |电话：025 - 806451612903225|
| 2023 | 8 | 5 | 31 |电话：01290322580645161传真：01290322580645161|
| 2023 | 9 | 5 | 30 |0.16666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666|
注：你可以创建一个日历表，而不是每次都使用递归CTE，这也会因为被索引而受益。
另请参见https://dbfiddle.uk/WoLWqDxW上的工作查询

赞(0）回复(0）举报 7个月前

我来回答

db2 使用SQL查找月份范围内的“活跃”日平均值

1条答案

相关问题

热门标签

最新问答