如何在sql oracle中计算从日期起15秒间隔内的平均消息数？

我有三张table看起来有点像这样。
人员表
| 识别号|人|
| - ------|- ------|
| 1个|名称1|
| 第二章|名称2|
| 三个|名称3|
类型到人员表
| 质量标准|个人ID|
| - ------|- ------|
| 三个|1个|
| 七|第二章|
| 九|三个|
请求日期表
| 识别号|质量标准|日期|
| - ------|- ------|- ------|
| 1个|三个|2020年2月2日|
| 第二章|三个|2020年2月2日|
| 三个|三个|2020年2月2日|
| 四个|七|2020年2月2日|
| 五个|七|2020年2月2日|
我需要计算平均每15秒向每个人发送多少请求，然后按小时分组，结果应该是这样的。
| 人|小时周期|间隔内的请求计数|
| - ------|- ------|- ------|
| 名称1|十五岁到十六岁|二、八三|
| 名称1|从10到11|0.05|
| 名称2|十五岁到十六岁|三、三十九|
我一开始是这样的，但现在我完全卡住了。

SELECT PersonTable.Perosn AS person, 
    EXTRACT(HOUR FROM CAST(RequestDateTable.rDATE AS TIMESTAMP)) AS hourPeriod,
    COUNT(EXTRACT(SECOND FROM CAST(RequestDateTable.rDATE AS TIMESTAMP))) AS "requests in interval count"
FROM   PersonTable
JOIN TypeToPersonTable ON PersonTable.ID = TypeToPersonTable.PersonID
JOIN RequestDateTable ON RequestDateTable.SPECID = TypeToPersonTable.SPECID
GROUP BY PersonTable.Person, RequestDateTable.rDATE

任何帮助都将不胜感激。

这是一个有点不清楚，但似乎你需要分段小时为15秒的间隔。（60分 * 4）15秒的间隔。然后，我猜，您要对一小时内的请求进行计数，并计算每小时内每个总分段数的平均请求数（240）或在一小时内完成请求的每个段数。希望这能帮助您解决问题。
样本数据：

WITH
    persons (ID, PERSON) AS
        (   Select 1, 'Name 1' From Dual Union All
            Select 2, 'Name 2' From Dual Union All
            Select 3, 'Name 3' From Dual 
        ),
    types_to_persons (SPEC_ID, PERSON_ID) AS
        (   Select 3, 1 From Dual Union All
            Select 7, 2 From Dual Union All
            Select 9, 3 From Dual 
        ),
    request_dates (ID, SPEC_ID, R_DATE) AS
        (   Select 1, 3, To_Date('02-FEB-2020 15:00:00', 'dd-MON-yyyy hh24:mi:ss') + (LEVEL * 3) * (LEVEL + 1) * (1 / 86400) From Dual Connect By Level <= 20 Union All
      Select 2, 3, To_Date('02-FEB-2020 15:00:00', 'dd-MON-yyyy hh24:mi:ss') + (LEVEL * 3) * (LEVEL + 2) * (1 / 86400) From Dual Connect By Level <= 21 Union All
      Select 3, 3, To_Date('02-FEB-2020 15:45:00', 'dd-MON-yyyy hh24:mi:ss') + (LEVEL * 13) * (LEVEL + 2) * (1 / 86400) From Dual Connect By Level <= 22 Union All
      Select 4, 7, To_Date('02-FEB-2020 15:00:00', 'dd-MON-yyyy hh24:mi:ss') + (LEVEL * 13) * (LEVEL + 2) * (1 / 86400) From Dual Connect By Level <= 23 Union All
      Select 5, 7, To_Date('02-FEB-2020 15:00:00', 'dd-MON-yyyy hh24:mi:ss') + (LEVEL * 3) * (LEVEL + 2) * (1 / 86400) From Dual Connect By Level <= 24 Union All
      Select 5, 7, To_Date('02-FEB-2020 15:30:00', 'dd-MON-yyyy hh24:mi:ss') + (LEVEL * 3) * (LEVEL + 3) * (1 / 86400) From Dual Connect By Level <= 25 Union All
      Select 6, 9, To_Date('02-FEB-2020 15:00:00', 'dd-MON-yyyy hh24:mi:ss') + (LEVEL * 13) * (LEVEL + 1) * (1 / 86400) From Dual Connect By Level <= 30 
        ),

样本数据中有165个request_dates，应该为聚合准备数据，因此我创建了一个cte（命名为segments），cte从R_DATE中提取时间元素，并计算每小时内15秒的段数。

segments AS
    ( Select  
            p.ID, p.PERSON, t.SPEC_ID, 
            TRUNC(r.R_DATE) "R_DATE",
            To_Char(r.R_DATE, 'hh24:mi:ss') "R_TIME",
            --
            To_Number(To_Char(r.R_DATE, 'hh24')) "R_HOUR",
            To_Number(To_Char(r.R_DATE, 'mi')) "R_MINUTE",
            To_Number(To_Char(r.R_DATE, 'ss')) "R_SECOND",
            --
            (To_Number(To_Char(r.R_DATE, 'mi')) * 4) + CASE WHEN To_Number(To_Char(r.R_DATE, 'ss')) <= 15 THEN 1
                                                            WHEN To_Number(To_Char(r.R_DATE, 'ss')) <= 30 THEN 2 
                                                            WHEN To_Number(To_Char(r.R_DATE, 'ss')) <= 45 THEN 3
                                                       ELSE 4 
                                                       END "HOUR_SEGMENT",
            --
            Min(EXTRACT(Hour From CAST(r.R_DATE as TIMESTAMP))) OVER(Partition By p.PERSON, R_DATE) "HOUR_START",
            Max(EXTRACT(Hour From CAST(r.R_DATE as TIMESTAMP))) OVER(Partition By p.PERSON, R_DATE) + 1 "HOUR_END"
      From        persons p
      Inner Join  types_to_persons t ON(p.ID = t.PERSON_ID)
      Inner Join  request_dates r ON(r.SPEC_ID = t.SPEC_ID)
      Order By    p.PERSON
    )
-- the result has 165 rows like below
-- segments cte result:
        ID PERSON    SPEC_ID R_DATE    R_TIME       R_HOUR   R_MINUTE   R_SECOND HOUR_SEGMENT HOUR_START   HOUR_END
---------- ------ ---------- --------- -------- ---------- ---------- ---------- ------------ ---------- ----------
         1 Name 1          3 02-FEB-20 15:00:06         15          0          6            1         15         16 
         1 Name 1          3 02-FEB-20 15:00:09         15          0          9            1         15         16 
         1 Name 1          3 02-FEB-20 15:00:18         15          0         18            2         15         16 
         1 Name 1          3 02-FEB-20 15:00:24         15          0         24            2         15         16 
         1 Name 1          3 02-FEB-20 15:00:36         15          0         36            3         15         16
... ... ...
         3 Name 3          9 02-FEB-20 17:43:48         17         43         48          176         17         18 
         3 Name 3          9 02-FEB-20 17:55:56         17         55         56          224         17         18 
         3 Name 3          9 02-FEB-20 18:08:30         18          8         30           34         18         19 
         3 Name 3          9 02-FEB-20 18:21:30         18         21         30           86         18         19

现在，您可以使用上面的分段数据获得不同的计算/聚合。
主SQL

Select    PERSON, R_DATE, 
          LPAD(HOUR_START, 2, '0') || '-' || LPAD(HOUR_END, 2, '0') "HOUR_RANGE",
          R_HOUR,
          Count(*) "REQUESTS", Count(DISTINCT HOUR_SEGMENT) "R_SEGMENTS", 
          Round(Count(*) / Count(DISTINCT HOUR_SEGMENT), 2) "AVG_PER_R_SEGMENT",
          60 * 4 "TOTAL_SEGMENTS",
          Round(Count(*) / 240, 2) "AVG_PER_HOUR_SEGMENTED"
From      segments
Group By  PERSON, R_DATE, R_HOUR, LPAD(HOUR_START, 2, '0') || '-' || LPAD(HOUR_END, 2, '0')
Order By  PERSON, R_DATE, R_HOUR, LPAD(HOUR_START, 2, '0') || '-' || LPAD(HOUR_END, 2, '0')

R e s u l t :
PERSON R_DATE    HOUR_RANGE     R_HOUR   REQUESTS R_SEGMENTS AVG_PER_R_SEGMENT TOTAL_SEGMENTS AVG_PER_HOUR_SEGMENTED
------ --------- ---------- ---------- ---------- ---------- ----------------- -------------- ----------------------
Name 1 02-FEB-20 15-16              15         48         44              1.09            240                     .2 
Name 1 02-FEB-20 16-17              16         10         10                 1            240                    .04 
Name 1 02-FEB-20 17-18              17          5          5                 1            240                    .02 
Name 2 02-FEB-20 15-16              15         62         56              1.11            240                    .26 
Name 2 02-FEB-20 16-17              16          9          8              1.13            240                    .04 
Name 2 02-FEB-20 17-18              17          1          1                 1            240                      0 
Name 3 02-FEB-20 15-16              15         16         16                 1            240                    .07 
Name 3 02-FEB-20 16-17              16          7          7                 1            240                    .03 
Name 3 02-FEB-20 17-18              17          5          5                 1            240                    .02 
Name 3 02-FEB-20 18-19              18          2          2                 1            240                    .01

如何在sql oracle中计算从日期起15秒间隔内的平均消息数？

1条答案

相关问题

热门标签

最新问答