Как отфильтровать записи по ближайшей дате?

Question

[nurzhannogerbek]

Здравствуйте! Помогите пожалуйста разобраться.

У меня есть следующая модель данных.

models.py:

class Securities(models.Model):
    section = models.ForeignKey(Section)
    characteristic = models.ForeignKey(Characteristics)
    bool_value = models.NullBooleanField()
    change_date = models.DateTimeField()
    real_change_date = models.DateTimeField()

У примеру в таблице есть такие записи:

SECTION  | CHARACTERISTIC | CHANGE_DATE | BOOL_VALUE
   A     |       8328     |  15.02.2018 |      1
   A     |       8328     |  02.09.2018 |      0
   B     |       8328     |  02.09.2018 |      1
   C     |       8328     |  02.09.2018 |      1
   C     |       8328     |  20.09.2018 |      0

Я пытаюсь отфильтровать подобную таблицу:

SECTION  | CHARACTERISTIC | CHANGE_DATE | BOOL_VALUE
   C     |       8328     |  20.09.2018 |      0
   B     |       8328     |  02.09.2018 |      1
   A     |       8328     |  02.09.2018 |      0

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

Я попытался сделать следующее:

securities = Securities.objects.filter(characteristic=8328).order_by('-change_date').values_list('section').distinct()

Данный код возвращается 36 записей, и записи все равно дублируются. То есть есть две записи A. Хотя нужно оставить одну. При этом когда я пишу print(securities.count()) мне пишет количество 33. Что вообще происходит. Как решить данную проблему, друзья?

Comments

[Freeman]

Здесь нужен запрос/подзапрос с group by и having. Как выразить его в Django, не знаю.

[nurzhannogerbek]

Vapaamies, здравствуйте! А нативный SQL запрос сможете пожалуйста для примера прислать? Я попробовал бы в Django ORM это как-то обыграть тогда.

Answer 1

[Ник В]

select distinct on (section)
  section,
  characteristic,
  change_date,
  bool_value
from securities
where characteristic = 8328
group by id, section
order by section desc, change_date desc

section | characteristic | change_date | bool_value
---------+----------------+----------------------------+------------
c | 8328 | 2018-09-20 12:28:00.701+00 | f
b | 8328 | 2018-09-02 12:28:00.701+00 | t
a | 8328 | 2018-09-02 12:27:41.907+00 | f
(3 rows)

ну и соответсвенно если тебе нужны только секции

select distinct on (section)
  section
from accounts_securities
where characteristic = 8328
group by id, section
order by section desc, change_date desc

ps. Блин ток щас заметил что у тебя в тегах Oracle, я под постгрисом проверял.

Comments

[nurzhannogerbek]

Здравствуйте! Спасибо за ваш ответ. Есть предположение, что в Oracle не будет работать distinct on. Я до этого пробывал в Django в метод distinct() передавать поля. Была такая ошибка:

DISTINCT ON fields is not supported by this database backend.

К слову встретил на форуме обсуждение по этому поводу.

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

views.py:

sql_result = SecuritiesParams.objects.raw("""
       здесь первый запрос
    """)
    for p in sql_result:
        print(p)

Ошибка такая: DatabaseError: ORA-00936: missing expression

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

[nurzhannogerbek]

Очень странно, прописал в запросе все прописными буквами, теперь ругается так: ORA-00979: not a GROUP BY expression

[Ник В]

NogerbekNurzhan К сожалению я тут вам помочь не могу, так как с Ораклом не работал и синтаксис его не знаю.
https://www.techonthenet.com/oracle/errors/ora00936.php
попробуй все поля перечислить.

[ponaehal]

ORA-00979: not a GROUP BY expression
не в ту степь пошли...

Answer 2

[ponaehal]

По ощущениям, спасет коррелированный подзапрос. Как то так:
SELECT * FROM securities t
WHERE t.change_date IN (SELECT Max(t1.change_date) FROM securities t1 WHERE t.section=t1.section)
Если будет долго работать, то подозреваю, что можно придумать что-нибудь более производительное с использованием аналитических функций...

Comments

[nurzhannogerbek]

Здравствуйте! Спасибо за ответ. А вы уверены в этом запросе? Я просто протестил его и он выдает не то. К примеру у меня 36 записей и повторение есть лишь у трех, то есть должно было быть 33 записи. В этот запрос возвращает всего 10 записей.

[ponaehal]

Добрый день! конечно не уверен.... может быть так будет правильнее:
SELECT * FROM securities t
WHERE t.change_date IN (SELECT Max(t1.change_date) FROM securities t1 WHERE t.characteristic=t1.characteristic)
Я же не знаю что у вас означают данные в том или ином столбце.... Вы не дали описания данных, дали только описание структуры...

Answer 3

[Максим Y]

select
  section
 ,characteristic
 ,change_date
 ,bool_value
  from (
    select
      section
     ,characteristic
     ,change_date
     ,bool_value
     ,row_number() over (partition by section, characteristic order by change_date desc) as rn
  )
 where rn = 1

Сорри что поздно, но пусть здесь будет.