Найти количество сущностей на последний день месяца

Question

Всем привет!

Что изначально имеется.

Есть таблица Метод эксплуатации(method_exploit), в котором хранится список определённых способов эксплуатации:

Id	title	code
1	Метод 1	method1
2	Метод 2	method2
3	Метод 3	method3
4	Метод 4	method4
5	Метод 5	method5
6	Метод 6	method6
7	Метод 7	method7

Также ещё есть и таблица Журнал эксплуатации(journal), которая имеет следующий вид:

Id	entity_id	date	method_id
1	1	2021-09-28 12:00:00	2
2	1	2021-09-28 13:10:00	1
3	1	2021-09-30 14:10:00	2
4	2	2021-09-24 14:10:00	1
5	2	2021-09-25 15:10:00	3
6	3	2021-09-27 15:10:00	7
7	3	2021-09-28 15:10:00	1

то есть в этой таблице у нас находится журнал, в котором отмечается какой метод эксплуатации(method_id) был у той или иной сущности с идентификатором entity_id, начиная с определённого момента(date).

Что мне нужно:

я сейчас пишу хранимую функцию, которая на входа принимает 1 параметр:

дату, то есть ПОСЛЕДНИЙ ДЕНЬ определённого месяца;

мне нужно найти КОЛИЧЕСТВО СУЩНОСТЕЙ, у которых на ПОСЛЕДНИЙ ДЕНЬ определённого месяца переданного в качестве параметра значение способа эксплуатации равно 1 или 2. То есть method_id = 1 или method_id = 2.

В приведённом выше примере НА ПОСЛЕДНИЙ ДЕНЬ СЕНТЯБРЯ количество таких сущностей будет равняться 2. Это сущности с entity_id = 1 и entity_id = 3. Так как у entity_id = 1 последнее значение метода эксплуатации за дату '2021-09-30 14:10:00' то есть самую последнюю дату для этой сущности в сентябре равно 2. У entity_id = 3 последнее значение метода эксплуатации за дату '2021-09-28 15:10:00' то есть самую последнюю дату для этой сущности в сентябре равно 1. А вот у entity_id = 2 последнее значение метода эксплуатации за дату '2021-09-25 15:10:00' то есть самую последнюю дату для этой сущности в сентябре равно 3, поэтому сущность с entity_id = 2 не подходит под условие.

В итоге количество сущностей, удовлетворяющих условию поиска = 2. Как мне построить SQL запрос в PostgreSQL, чтобы в запросе посчитать количество таких сущностей?

http://sqlfiddle.com/#!15/c8150 - вот тут я накидал демо-данные. Может кто-нибудь помочь сформулировать запрос?

Answer 1

    SELECT 
  j.entity_id, 
  to_char(MAX(j.date), 'MONTH YYYY') as last_date,
  (
    SELECT t1.method_id
    FROM journal t1
    WHERE j.entity_id = t1.entity_id AND MAX(j.date) = t1.date
  ) as last_method_id
FROM journal j
WHERE TO_CHAR(j.date, 'MONTH YYYY') = TO_CHAR(TO_DATE('2021-09-30', 'YYYY-MM-DD'), 'MONTH YYYY')
GROUP BY j.entity_id
HAVING (
    SELECT t1.method_id
    FROM journal t1
    WHERE j.entity_id = t1.entity_id AND MAX(j.date) = t1.date
  ) IN (1,2)

Answer 2

SELECT COUNT(*) FROM (
  SELECT MAX(date), entity_id 
  FROM journal j
  WHERE j.method_id IN (1,2) AND TO_CHAR(j.date, 'MONTH YYYY') = TO_CHAR(TO_DATE('2021-09-30', 'YYYY-MM-DD'), 'MONTH YYYY')
  GROUP BY entity_id 
  HAVING MAX(date) = (SELECT MAX(date) FROM journal as t1 WHERE t1.entity_id = j.entity_id AND TO_CHAR(t1.date, 'MONTH YYYY') = TO_CHAR(TO_DATE('2021-09-30', 'YYYY-MM-DD'), 'MONTH YYYY'))
) t;

БЛОГ НА HUSL

Найти количество сущностей на последний день месяца

Ответы (2 шт):