oracle - 按 15 分钟间隔对数据集进行分组

Question

我正在尝试为我们的内部公司系统建立平均 session 量。问题是范围大约向后三个月，我想按 15 分钟间隔对这整组大数据进行分组，因此输出将如下所示:

interval      sess_avg
12:00-12:15   300
12:15-12:30   350
etc..

我的查询是:

    SELECT
        TO_CHAR(sess_start_date,'YYYY-MM-DD HH24:MI:SS') time_start,
        COUNT (sess_id) how_many
                FROM
        t.sessions,
        t.users
            WHERE
        t.users.user_id         =t.sessions.sess_user_id
        AND user_type     !='X'
        AND sess_start_date>=TRUNC(ADD_MONTHS(SYSDATE,-12))
GROUP BY
        TO_CHAR(sess_start_date,'YYYY-MM-DD HH24:MI:SS')

问题是我不知道如何开始。请帮忙。

Answer 1

如上所述in a previous answer ，您可以使用 SSSSS 格式模型来操作日期值的时间部分。在这里应用该方法，然后您可以按调整后的值进行查询和分组:

SELECT
    TO_CHAR(TRUNC(sess_start_date)
            + FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)/96,
        'YYYY-MM-DD HH24:MI:SS') time_start,
    COUNT (sess_id) how_many
FROM
    t.sessions,
    t.users
WHERE
    t.users.user_id = t.sessions.sess_user_id
    AND user_type !='X'
    AND sess_start_date>=TRUNC(ADD_MONTHS(SYSDATE,-12))
GROUP BY
    TRUNC(sess_start_date) + FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)/96;

如果您想要不引用日期的时间段，您可以这样做:

SELECT
    TO_CHAR(TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)*900,
      'SSSSS'),'HH24:MI') time_start,
    COUNT (sess_id) how_many
FROM
    t.sessions,
    t.users
WHERE
    t.users.user_id = t.sessions.sess_user_id
    AND user_type !='X'
    AND sess_start_date>=TRUNC(ADD_MONTHS(SYSDATE,-12))
GROUP BY
    TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)*900, 'SSSSS');

要获取所有天的同一时间段内的平均计数，您可以将它们与内联 View 结合起来:

SELECT
    TO_CHAR(period_start, 'HH24:MI') as period_start,
    AVG (how_many) avg_how_many
FROM (
  SELECT
      TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)*900,
          'SSSSS') period_start,
      TO_CHAR(TRUNC(sess_start_date)
              + FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)/96,
          'YYYY-MM-DD HH24:MI:SS') time_start,
      COUNT (sess_id) how_many
  FROM
      t.sessions,
      t.users
  WHERE
      t.users.user_id = t.sessions.sess_user_id
      AND user_type !='X'
      AND sess_start_date>=TRUNC(ADD_MONTHS(SYSDATE,-12))
  GROUP BY
      TO_DATE(FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)*900, 'SSSSS'),
      TRUNC(sess_start_date) + FLOOR(TO_NUMBER(TO_CHAR(sess_start_date, 'SSSSS'))/900)/96
)
GROUP BY
    period_start
ORDER BY
    period_start;