SQL-запрос для подсчета элементов в диапазоне дат?

Начнем с того, что на данный момент я знаю ровно столько, чтобы навлечь на себя неприятности. Я работаю над написанием запроса для системы AS/400. У меня есть файл, который содержит дату начала и дату окончания. Что я хочу сделать, так это создать запрос, который может подсчитать, сколько позиций было открыто для диапазона дат. (для моего приложения open определяется как: DATE > START_DATE AND (DATE <= END_DATE OR END_DATE = '0')). Я могу заставить это работать для ручной установки «ДАТА», но я пытаюсь вернуться назад и исторически извлечь эту информацию. У меня проблемы с запуском этого запроса по списку дат. Я хочу ввести исторический интервал (скажем, CURRENT_DATE - 5) и вывести:

Date             Open
20200206         30,000
20200207         31,000
20200210         29,675
20200211         31,375
20200212         32,000

Что я использую для запроса «один день за раз»:

SELECT
        COUNT(*) 
    FROM
        MYFILE
    WHERE 
        START_DATE < '20200210' 
    AND (
            END_DATE >= '20200210' 
        OR  END_DATE = '0'
        )

Это прекрасно работает, но отнимает много времени. Отсюда я понятия не имею, как перейти к желаемому результату... или даже если это возможно... но для меня это ускорит процесс. Заранее спасибо.

P.S. любая помощь приветствуется, если вы знаете, как это сделать в mySQL и т. д., пожалуйста, поделитесь. Я могу взять это и работать с ним, чтобы заставить его работать конкретно для AS/400. Вот как я пришел к тому, где я нахожусь сейчас.

*************** Изменить для пояснений 1. Текущий код Предоставленный код текущего дня — это все, что у меня есть. Я не знаю, с чего начать то, о чем я прошу. 2. Пример данных:

Line Item       START_DATE        END_DATE
1               20200206          20200210
2               20200207          20200210
3               20200207          20200207
  1. Ожидаемый результат:
DATE           COUNT
20200205       0
20200206       0
20200207       1
20200210       2
20200211       0
  1. СУБД Я обновил тег, включив в него DB2. 5. Я не верю, что GROUP BY будет работать.
    В основном я ищу, чтобы это вычислялось итеративным способом. Исходя из моего фона python/java, это имеет для меня смысл. Я не ищу все, что было открыто 5 дней назад, я хочу начать 5 дней назад и подсчитать, сколько позиций было открыто каждый из этих 5 дней. Если это действительно возможно с GROUP, пожалуйста, уточните и покажите мне ошибку моего пути. Тем временем я начну возиться с группировкой для этого.

sql date db2 datefilter
person Theweaverman    schedule 12.02.2020    source источник
comment
Привет, добро пожаловать в Stack Overflow. Пожалуйста, прочитайте stackoverflow.com/help/minimal-reproducible-example и stackoverflow.com/help/how-to-ask. В этом примере нам нужно увидеть ваш текущий код, и данные примера имеют решающее значение. Кроме того, очень полезно видеть, что ваш код производит по сравнению с ожидаемым результатом. См.: meta.stackoverflow.com/questions/271055/. также, что DBMS вы используете? Пожалуйста, отметьте свой вопрос этим (я думаю, вы упомянули mySQL)   -  person wundermahn    schedule 12.02.2020
comment
Если я не ошибаюсь, AS/400 - это DB2 от IBM? Не могли бы вы обновить тег вопроса с помощью DB2, если это так?   -  person Igor    schedule 12.02.2020

Ответы (4)


arrow_upward
0
arrow_downward

Ниже приведен запрос, возвращающий последовательные даты между заданной датой и заданной датой — 5 дней. Конечно, вы можете использовать CURRENT DATE вместо этой константы.

WITH T (DT) AS 
(
VALUES DATE('2020-02-13') - 5 DAYS
  UNION ALL
SELECT DT + 1 DAY
FROM T 
WHERE DT < DATE('2020-02-13')
)
SELECT DT FROM T;

Результат:

|DT        |
|----------|
|2020-02-08|
|2020-02-09|
|2020-02-10|
|2020-02-11|
|2020-02-12|
|2020-02-13|

Дальнейшее использование такой "виртуальной таблицы" очевидно: вы можете LEFT JOIN ее с вашей базовой таблицей на B.START_DATE < DEC(TO_CHAR(T.DT, 'YYYYMMDD'), 8) AND DEC(TO_CHAR(T.DT, 'YYYYMMDD'), 8) <= B.END_DATE сгруппировать результат по T.DT.

person Mark Barinstein    schedule 13.02.2020
comment
ВЫБЕРИТЕ M.MY_DAY,COUNT(*) FROM (ВЫБЕРИТЕ DISTINCT(START_DATE) AS MY_DAY FROM MYFILE WHERE START_DATE ›= REPLACE(CHAR(CURRENT DATE - 5 DAY, ISO),'-','') ORDER BY START_DATE) AS M LEFT JOIN MYFILE P ON M.MY_DAY › P.START_DATE AND (M.MY_DAY ‹= P.END_DATE OR P.END_DATE = '0') GROUP BY M.MY_DAY - person Theweaverman; 13.02.2020
comment
Это был последний запрос. Спасибо за вашу помощь, я понятия не имел, что присоединение к условиям можно использовать таким образом. Я проголосовал за вас, но я слишком новичок, чтобы мои голоса могли быть учтены. - person Theweaverman; 13.02.2020

arrow_upward
0
arrow_downward

Пожалуйста, попробуйте это:

SELECT COUNT(*) FROM MYFILE WHERE (START_DATE < '20200205' AND (END_DATE >= '20200210' OR END_DATE = '0')) GROUP BY DATE_FORMAT(START_DATE , '%Y%m%d')
person Denis Kuratovich    schedule 12.02.2020

arrow_upward
0
arrow_downward

Звучит как простое использование оператора group by, но вы не указываете, по какому столбцу вы группируете, а указываете только дату, я предполагаю, что START_DATE

Я не знаю AS/400 SQL, поэтому буду немного абстрактен и предположу, что CURRENT_DATE() возвращает текущую дату в вашем регионе. РСУБД различаются в этом отношении.

Учитывая эти предположения, что-то вроде следующего должно помочь:


SELECT
        START_DATE as DATE,
        COUNT(*) as OPEN
    FROM
        MYFILE
    WHERE 
        START_DATE >= (CURRENT_DATE() - 5)
    AND (
            END_DATE >= (CURRENT_DATE() - 5)
        OR  END_DATE = '0'
        )
    GROUP BY
        START_DATE
person Richard Crossley    schedule 12.02.2020

arrow_upward
0
arrow_downward

Разверните данные и агрегируйте их с помощью оконных функций. Вот идея:

with se as (
      select dte, sum(ins) as ins, sum(outs) as outs
      from ((select start_date as dte, count(*) as ins, 0 as outs
             from t
            ) union all
            (select end_date, 0, count(*) as outs
             from t
            )
           )
select dte, ins, outs,
       (sum(sum(ins)) over (order by dte) -
        sum(sum(outs)) over (order by dte)
       )
from se
order by dte;

Это суммирует все запуски в данный день и вычитает количество окончаний.

Заметки:

  • Если вы хотите фильтровать по дате, поместите логику фильтрации во внешний запрос.
  • Это предполагает, что все даты действительны. Если какие-то даты особенные, вам нужно учесть это в order by.
  • Это НЕ учитывает кого-то в день их отъезда. Если вы хотите их посчитать, просто добавьте 1 день к end_date в подзапросе.
person Gordon Linoff    schedule 12.02.2020