Блог

Главная - Вопросы по программированию - Запрос Oracle, получите количество записей по часам

Запрос Oracle, получите количество записей по часам

#sql #oracle12c #date-format

Вопрос:

Я пытаюсь подсчитать количество транзакций за каждый час. Обычно это прямой запрос, к сожалению, столбец метки времени, с которым мне приходится работать, — это не метка времени, а varchar2! Независимо от того, что я пытаюсь, я получаю либо «неверный месяц», либо «неверный номер», в зависимости от используемого формата.

Метка времени выглядит так: 2021-08-08 00:00:52:632

Я также выполнил следующее, чтобы получить формат NLS:

 SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';

и получить

 DD-MON-RRRR.

Это последнее, что я попробовал среди дюжины других (я прокомментировал «группу по», чтобы просто показать эту чертову штуку).

 select to_char(reqts,'mm/dd/yyyy hh24') DATE_HR
--, count(*)
from idcreqresplog
where logdate > trunc(SYSDATE -2)
and logtypeid in (2,4)
--group by to_char(reqts,'mm/dd/yyyy hh24');

Также

 select to_char(reqts, 'yyyy-mm-dd hh24:mi:ss.fff' )
--, count(*) 
FROM 
reqresplog 
WHERE 
logdate > trunc(SYSDATE -2) ;
--group by to_date(reqts, 'yyyy-mm-dd HH4');

Я не в своем уме и нуждаюсь в некоторой помощи.

Комментарии:

1. Если вы сопоставили некоторые примеры данных, которые были в этом столбце VARCHAR2, то только некоторые значения будут признаны «допустимыми». Остальные (те, которых вы не видели, не знаете, что они существуют,…) не будут, и Oracle выдаст ошибку для таких значений. Вот что происходит, когда дата хранится в виде строки, так как сама база данных не запрещает пользователям вводить даты как «LI/TT/LEFO OT», поэтому, как только вы примените к ней маску формата MM/DD/ГГГГ HH24, вы получите ошибку. Поэтому никогда не храните даты в виде строк.

2. Вы пробовали преобразовать свою VARCHAR2 метку времени в метку времени с помощью TO_TIMESTAMP функции ( Документация Oracle )?

3. По мнению @Littlefoot, если вы все еще получаете ошибку, вам, возможно, потребуется добавить REGEXP_LIKE в предложение where, чтобы гарантировать, что возвращаются только допустимые записи.

4. @Littlefoot Я наследую эту базу данных и ее дизайн, так что пока мне приходится с ней жить.

5. @Del Я перепробовал все под солнцем, чтобы преобразовать, и ничего не работает, скорее всего, из-за того, что сказал Литтлфур. Есть ли способ найти несоответствующие записи?

Ответ №1:

Предполагая, что ваш столбец всегда находится в формате 2021-08-08 00:00:52:63 , затем сгруппируйте подстроку до 13-го символа:

 SELECT SUBSTR(reqts, 1, 13) AS date_hr,
       count(*)
FROM   idcreqresplog
WHERE  logdate > trunc(SYSDATE -2)
AND    logtypeid in (2,4)
GROUP BY
       SUBSTR(reqts, 1, 13);

Если вы действительно хотите преобразовать в дату, то, начиная с Oracle 12.2, вы можете использовать TO_TIMESTAMP(string_value DEFAULT NULL ON CONVERSION ERROR, 'YYYY-MM-DD HH24:MI:SS:FF') :

 SELECT TRUNC(
         TO_TIMESTAMP(
           reqts DEFAULT NULL ON CONVERSION ERROR,
           'YYYY-MM-DD HH24:MI:SS:FF'
         ),
         'HH'
       ) AS date_hr,
       COUNT(*)
FROM   idcreqresplog
WHERE  logdate > trunc(SYSDATE -2)
AND    logtypeid in (2,4)
GROUP BY
       TRUNC(
         TO_TIMESTAMP(
           reqts DEFAULT NULL ON CONVERSION ERROR,
           'YYYY-MM-DD HH24:MI:SS:FF'
         ),
         'HH'
       )

бд<>скрипка <>здесь

Ответ №2:

Предполагая, как предположил Литтлфут, что некоторые из ваших данных являются плохими, вы можете использовать встроенную функцию WITH для удаления ваших плохих данных. Возьмем следующий пример:

 WITH FUNCTION get_timestamp
(
  p_sTimeString VARCHAR2
)
RETURN TIMESTAMP
IS
BEGIN
  RETURN TO_TIMESTAMP(p_sTimeString, 'YYYY-MM-DD HH24:MI:SS.FF3');
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END;
SELECT TO_CHAR(s.hour, 'YYYY-MM-DD HH24') AS HOUR, COUNT(*) AS ROW_COUNT
FROM (SELECT TRUNC(get_timestamp(td.time), 'HH24') AS HOUR,
             td.amount
      FROM test_data td) s
WHERE s.hour IS NOT NULL
GROUP BY s.hour
ORDER BY s.hour;

Вот DBFiddle, показывающий, что это работает для некоторых хороших и плохих данных (Ссылка).

Что делает запрос, так это использует встроенную функцию для вызова TO_TIMESTAMP функции. Затем он просто улавливает любую ошибку и возвращает NULL . Это избавит вас от того, что ваши неверные данные испортят ваш запрос. После этого запрос будет примерно таким же, как вы пытались сделать ранее. Я усекаю метку времени до часа во внутреннем запросе, а затем использую ее для группировки во внешнем запросе (только используя строки, в которых нет NULL меток времени…это значит, что они не ошиблись)

Метки: ,