Как правильно собрать данные из БД?

Question

[Malkolm]

Короче условия такие - есть таблицы (естественно со стандартными полями для связи):
1. Список статусов
2. список заявок (среди которых есть поле в которое записан текущий статус)
3. история - каждый раз при изменении статуса записывается дата/время и новый статус

задача:
надо выбрать из всего этого многообразия информацию о том сколько у меня было заявок в том или ином статусе, например за период в 30 дней с разбивкой по дням.

Если я сделаю выборку например вот так:

select
    "STATUS_ID",
    count(*) as CNT,
    to_char("DATE",'YYYY-MM-DD') as DatePartChar
from ams_cs_request_history
where "STATUS_ID" in (17,18,20,21,22,23)
and "DATE" > '2022-08-01'::date
and "DATE" < '2022-09-01'::date

group by "STATUS_ID", DatePartChar
limit 100;

то я смогу получить информацию только о том сколько заявок у меня в этот статус перешло, но не будет включать в себя сколько оставалось с предыдущего временного отрезка или вообще за пределами периода выборки.
Например при такой выборке, если заявка создана 2022-07-30, и перешла скажем в 17 статус на момент начала выборки (2022-08-01), а например 2022-08-03 перешла на 18 статус, то в счетчик попадет 18 статус, а 17 вроде как останется за бортом.
Помогите подобрать алгоритм выборки (можно и в 2-3 отдельных запроса), чтобы получить информацию с полной картиной.
Заранее благодарю))

Comments

[Дмитрий]

Не совсем понятно что вы хотите. Если вы хотите данные по какому то временному окну, то зачем вам данные за пределами временного окна? если вы хотите данные без учета времени - зачем тогда ограничивать?

[Malkolm]

По факту наверное было бы правильнее тогда говорить что надо надо получить на каждый день сколько заявок выбранных статусов оставалось на конец дня и так на каждый день в рамках выбранного интервала.

[Malkolm]

вот только я не могу делать по отдельному запросу на каждый день выборки, потому, что для ситуации выборки для полугодия получится слишком много запросов....

[Дмитрий]

Malkolm,
1. лучше нажимать ответить - что бы мое имя встало в сообщение, что бы свалилось уведомление - случайно заметил что количество комментариев увеличилось.
2. опять не понятно. вы и получаете сколько каких статусов было на каждый день. вопрос в том что если в рамках одного дня статус поменялся - то будет и в том и в том count статусе - но это решаемо.
3. если вы хотите выбрать все изменения в определенный период и к ним подключить и данные из других времен, ну сделайте cte

with temp as (
    SELECT id from ams_cs_request_history
where "STATUS_ID" in (17,18,20,21,22,23)
and "DATE" > '2022-08-01'::date
and "DATE" < '2022-09-01'::date
) select
    "STATUS_ID",
    count(*) as CNT,
    to_char("DATE",'YYYY-MM-DD') as DatePartChar
from temp
where "STATUS_ID" in (17,18,20,21,22,23) and id in (select id from temp)

group by "STATUS_ID", DatePartChar
limit 100;

[Malkolm]

Дмитрий,
1. ок, мерси
2. тут в худшем случае можно будет пожертвовать точностью, но по факту лучше в итоге иметь кол-во на конец дня вне зависимости от того стал он в этом статусе или и днем ранее был в нем же
3. я по сути хочу кол-во с группировкой по статусам, за каждый фрагмент времени (в данном случае это 1 день) на момент окончания этого фрагмента и чтобы фрагменты были в рамках общего интервала. Опять же на примере 1 недели - за пн, вт, ср и т.д.
Типа такого:

пример

По примеру кода не могу понять, это или пример, что можно temp использовать не вдаваясь в детали полей (потому, что в таком примере во втором селекте DATE и STATUS_ID не существуют, т.к. в temp выбирается только id) или я просто чего-то не понимаю(((

[Дмитрий]

Malkolm,
3. Ну по факту вы это и получаете. День статус - количество. Вы уж тогда с данными приведите
4. Мы в темп выбираем все записи которые попадают в ваше окно. Из них берем id того что меняет статус, и по этим айди выбираем все смены статусов без окна. Тогда получится что если 17 статус был получен до этого окна - он все равно выберется.

[Malkolm]

Дмитрий, 4 ну говорил же что не будет работать. Вбил все в IDE с подключением к БД, она сразу подсвечивает ошибки

а все потому, что основная выборка делается из temp (172 строка на скрине), а в него выбирается только ID, потому в 169, 173 и 175 строках подсвечивается как ошибка.
Я по сути понял задумку. Но вот вопрос а может проще разделить на 2 запроса? 1й выберет все изменения только в рамках периода, а 2й то, что было до него?
Только мне кажется тут будет куча проблем (далее мои предположения и размышления), потому, что надо будет выбрать по всем заявкам последнее изменение статуса перед отчетным периодом и отсеять те из них, которые не с одним из отчетных статусов. Вот только вроде бы это не сильно поможет, потому, что надо как то еще отфильтровать для 2-го и далее интервала те из них, которые в первом интервале поменяли статус.....
Короче та еще морока. Но так или иначе спасибо за помощь. Наверное для 2-го подсчета я воспользуюсь другими методами или просто буду считать только изменения.

[Дмитрий]

Malkolm, ну да я опечатлся

with temp as (
    SELECT id from ams_cs_request_history
where "STATUS_ID" in (17,18,20,21,22,23)
and "DATE" > '2022-08-01'::date
and "DATE" < '2022-09-01'::date
group by id
) select
    "STATUS_ID",
    count(*) as CNT,
    to_char("DATE",'YYYY-MM-DD') as DatePartChar
from ams_cs_request_history
where "STATUS_ID" in (17,18,20,21,22,23) and id in (select id from temp)

group by "STATUS_ID", DatePartChar
limit 100;

Но вот вопрос а может проще разделить на 2 запроса? 1й выберет все изменения только в рамках периода, а 2й то, что было до него?

Ровно это и происходит в этом запросе.

[Дмитрий]

Malkolm, В след раз загоняйте данные и структуру таблиц куда нибудь сюда и ответов будет больше и ошибок в них будет меньше

[Malkolm]

Дмитрий,

Ровно это и происходит в этом запросе.

да как это происходит то?
в темпе мы ограничиваем выборку по дате и по статусу, а в селекте снова фильтруем по тем же точно статусам И id из темпа. Т.к. условие И, то мы по сути еще раз отфильтровываем темп, а не объединяем его с чем либо еще. Т.е. это фильтрация в 2 шага. Может я ошибаюсь, но получается надо условия с датой переместить в селект, и условие ставить не и, а ИЛИ??

[Malkolm]

Malkolm, не, так тоже получается шлак, потому, что записи из темпа приходят получается с датой за пределами выборки.

[Дмитрий]

Malkolm, мы в темпе должны выбрать записи которые менялись в течении данного промежутка времени. Только выбирать надо естественно не ID записи - а ID того что меняло статус. То есть что у вам там меняет статус - заявка. После чего мы еще раз идем в таблицу истории и говорим ей - выбери все записи у которых заявка айди такая то. И мы получим данные.
Так как никакую структуру таблиц вы не привели - я использовал назвал заявку id - как просто id, думая что вы поймете по контексту запроса.

[Malkolm]

Дмитрий, а вот теперь понял (просто обычно id - это id самой строки, а если в строке есть id связанной записи, то обычно это <сущность>_id, поэтому я и не понял)

В этом случае получается, что мы считаем с группировками количество записей с выбранными статусами по тем заказам, которые в рамках заданного интервала этот статус получили. В этом случае в выборку попадут и записи смены статуса за пределами диапазона выборки, но с нужным статусом и которые при этом в рамках диапазона выборки хоть раз получили один из требуемых статусов.

Короче я возьму выборку с подсчетом изменений в течении часа + оказывается для другой статистики собирается информация раз в час о том, сколько заявок в каком статусе есть в конкретный момент времени. Если их просуммировать, тогда как раз получится нужная цифра - сколько было + сколько за час добавилось.
Хоть вопрос решить и не получилось в том контексте, который я изначально планировал, но реально благодарю за активную попытку помочь.

P.S.: а запрос в текущем контексте все равно не получается такой как надо, т.к. при выборке появляется много записей из предшествующего диапазона и они также группируются - т.е. если я делаю выборку за 8 месяц, то в итоге в выводе есть записи за 7й, и становится не понятно как и к какой записи из результатов 8 месяца их суммировать(((

[Дмитрий]

а запрос в текущем контексте все равно не получается такой как надо

Дык я до сих пор не понимаю что именно вы хотите. И судя потому что отвечаю только я, не я один не понимаю

[Malkolm]

Дмитрий, нууу тогда конкретный пример 1й единственной заявки:

пример

Реальное движение:
1.08 10:00 - создана
1.08 11:00 - статус 17
1.08 12:00 - статус 18
2.08 14:00 - статус 20
2.08 15:00 - статус 21
3.08 16:00 - статус 22
Делаем запрос на даты с 2.08 по 9.08 - 7 дней
Должен получиться такой результат
2.08 | статус 18 | 1шт - тут 1 потому, что в начале дня была в этом статусе, т.е. 1 осталась еще с 1.08
2.08 | статус 20 | 1шт - тут 1 потому, что за 2.08 заявка вошла в этот статус
2.08 | статус 21 | 1шт - тут 1 потому, что за 2.08 заявка вошла в этот статус
3.08 | статус 21 | 1шт - тут 1 потому, что в начале дня была в этом статусе
3.08 | статус 22 | 1шт - тут 1 потому, что за 3.08 заявка вошла в этот статус
При этом в результате за 2.08 17 статуса быть не должно, потому, что заявка в течении 2.08 никогда в этом статусе не была, в отличие от 18 статуса