Блог

Как можно получить — когда было последнее изменение (по дате) — в этой таблице:

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

Повторное значение должно быть:

Попробуйте вот это:

 with mytable as (
  select 1 as id, date '2021-01-01' as date, 0 as value union all
  select 1, date '2021-01-02', 10 union all
  select 1, date '2021-01-03', 15 union all
  select 1, date '2021-01-04', 25 union all
  select 1, date '2021-01-05', 25 union all
  select 1, date '2021-01-06', 25
)
select id, array_agg(struct(date, value) order by last_change_date desc limit 1)[offset(0)].*
from (
  select *, if(value != lag(value) over (partition by id order by date), date, null) as last_change_date
  from mytable
)
group by id
 

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

посмотрите, что это дает вам:

  SELECT MAX(date) OVER (PARTITION BY(value)) AS lastChange 

 FROM Table

 WHERE id = 1
 
 

Следующий запрос и воспроизводимый пример на db-fiddle работает. Я также включил некоторые дополнительные записи тестов.

 CREATE TABLE my_data (
  `id` INTEGER,
  `date` date,
  `value` INTEGER
);

INSERT INTO my_data
  (`id`, `date`, `value`)
VALUES
  ('1', '01.01.2021', '0.0'),
  ('1', '02.01.2021', '10.0'),
  ('1', '03.01.2021', '15.0'),
  ('1', '04.01.2021', '25.0'),
  ('1', '05.01.2021', '25.0'),
  ('1', '06.01.2021', '25.0'),
  ('2', '05.01.2021', '25.0'),
  ('2', '06.01.2021', '23.0'),
  ('3', '03.01.2021', '15.0'),
  ('3', '04.01.2021', '25.0'),
  ('3', '05.01.2021', '17.0'),
  ('3', '06.01.2021', '17.0');
 

Запрос № 1

 SELECT 
    id,
    date,
    value
FROM (
    SELECT
        *,
        row_number() over (partition by id order by date desc)  as id_rank
    FROM (
        SELECT
            id,
            m1.date,
            m1.value,
            rank() over (partition by id,m1.value order by date asc)  as id_value_rank,
            CASE
                WHEN (m1.date = (max(m1.date) over (partition by id,m1.value ))) THEN 1
        ELSE 0
    END AS is_max_date_for_group,
    CASE
        WHEN (m1.date = (max(m1.date) over (partition by id ))) THEN 1
        ELSE 0
    END AS is_max_date_for_id                                 
from 
    my_data m1
) m2
WHERE (m2.is_max_date_for_group = m2.is_max_date_for_id and is_max_date_for_group <> 0 and id_value_rank=1) or (id_value_rank=1 and is_max_date_for_id=0)
) t
              where t.id_rank=1
                         order by id, date, value;
 

Просмотр на скрипке БД

Я на самом деле нахожу, что самый простой метод-перечислить строки по идентификатору/дате и по идентификатору/дате/значению в порядке убывания. Они одинаковы для последней группы … а остальное-агрегация:

 select id, min(date), value
from (select t.*,
             row_number() over (partition by id order by date desc) as seqnum,
             row_number() over (partition by id, value order by date desc) as seqnum_2
      from t
     ) t
where seqnum = seqnum_2
group by id;
 

Если вы используете lag() , я бы рекомендовал использовать qualify для повышения производительности:

 select t.*
from (select t.*
      from t
      qualify lag(value) over (partition by id order by date) <> value or
              lag(value) over (partition by id order by date) is null
     ) t
qualify row_number() over (partition by id order by date desc) = 1;
 

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