Подсчет количества записей на основе даты последнего обновления null

#sql #subquery #greatest-n-per-group #snowflake-cloud-data-platform #window-functions

#sql #подзапрос #наибольшее число записей на группу #snowflake-cloud-data-platform #окно-функции

Вопрос:

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

 id    work_item      title         last_updated_date  
1     task1          ProjectA      2020-03-25 20:20:01.111
2     task2          ProjectA      2020-03-25 20:20:01.111
3     task3                        2020-03-25 20:20:01.111
4     task4          ProjectB      2020-03-25 20:20:01.111
5     task5          ProjectC      2020-03-25 20:20:01.111

Как вы можете видеть, мне нужно посмотреть, какие рабочие элементы не имеют проекта. Если бы я посмотрел историю для ‘task1’, я бы увидел что-то вроде следующего:

 select work_item, last_updated_date, project
from table
where work_item = 'task1'

Это приведет к:

 work_item      last_updated_date          title
task1          2020-03-25 20:20:01.111    ProjectA
task1          2020-03-17 20:20:01.111    NULL
task1          2020-03-12 20:20:01.111    NULL

Исходя из этого, я вижу, что task1 получил название, присвоенное 2020-03-25. Что мне нужно знать, так это то, скольким рабочим элементам во всем наборе данных не присвоен элемент. Поэтому я хочу проверить все записи, используя дату последнего обновления, и проверить последнюю last_updated_date, чтобы узнать, равен ли заголовок null. Я попробовал следующее, но, по-моему, я неправильно использую MAX и / или group by? Я возвращаю записи с присвоенными заголовками, что заставляет меня думать, что он проверяет МАКСИМАЛЬНОЕ значение всего столбца last_updated_date, а не каждой записи внутри.

 select id, title, MAX(last_updated_date) as "latest_timestamp"
FROM table
WHERE title is null
group by id, title

Я хотел бы видеть, что только task3 отображается как нуждающийся в присвоении заголовка.

1. Я вижу, что вы получили приемлемый ответ, но вы также можете проверить функцию окна last_value() в Snowflake. Это позволило бы полностью избежать подзапроса: docs.snowflake.com/en/sql-reference/functions/last_value.html

Ответ №1:

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

 select count(*)
from mytable t
where 
    last_updated_date = (
        select max(t1.last_updated_date) 
        from mytable t1 
        where t1.work_item = t.work_item
    )
    and title is null

Вы также можете использовать оконные функции:

 select count(*)
from (
    select t.*, 
        row_number() over(partition by work_item order by last_updated_date desc) rn
    from mytable t
) t
where rn = 1 and title is null