Функция окна и CTE, использованные для задачи “1661. Среднее время обработки на машину” на LeetCode, но вызывает неизвестную ошибку

Вопрос или проблема

WITH table_partition_by_machine_id AS 
(
    SELECT 
        *,
        DENSE_RANK() OVER (PARTITION BY machine_id 
                           ORDER BY Process_id, activity_type DESC) AS  arrange_data 
    FROM Activity
),
-- создание столбца для нахождения разницы
previous_time_stamp AS
(
    SELECT 
        *,
        LAG(timestamp, 1) OVER (PARTITION BY machine_id) AS start_timestamp
    FROM 
        table_partition_by_machine_id
),
-- нахождение общего времени каждого процесса на машине
process_time AS 
(
    SELECT 
        *,
        timestamp - start_timestamp AS process_time
    FROM    
        previous_time_stamp
    WHERE 
        activity_type="end"
)
-- нахождение среднего времени всех процессов каждого устройства
SELECT 
    *,
    AVG(process_time) OVER (PARTITION BY machine_id)  processing_time
FROM    
    process_time

SELECT 
    *,
    AVG(process_time) OVER (PARTITION BY machine_id)  processing_time
FROM
    process_time,

SELECT 
    machine_id,
    AVG(process_time) OVER (PARTITION BY machine_id)  processing_time
FROM    
    process_time,

---

SELECT 
    machine_id,
    AVG(process_time) processing_time
FROM    
    process_time,
GROUP BY 
    machine_id

Ответ или решение

Конечно! Давайте подробно разберем, как реализовать решение для задачи "1661. Среднее время процесса на машине" с использованием оконных функций и CTE (Common Table Expressions) в SQL.

Задача

Вам необходимо вычислить среднее время обработки (processing time) для каждого machine_id на основе данных, хранящихся в таблице Activity, которая включает в себя такие поля, как machine_id, process_id, activity_type и timestamp.

Подход

Мы использовали три CTE для обработки данных:

1. table_partition_by_machine_id: Этот CTE позволяет нам порядково распределить записи для каждой машины с использованием функции DENSE_RANK() по process_id и activity_type.

2. previous_time_stamp: Здесь мы используем функцию LAG(), чтобы получить предыдущую метку времени (timestamp) для каждой машины, тем самым позволяя нам вычислить длительности процессов.

3. process_time: В этом CTE мы вычисляем process_time для каждой записи, когда activity_type равен "end", то есть, рассчитываем разницу между timestamp и start_timestamp.

Ниже находится полностью реализованный SQL-запрос:

WITH table_partition_by_machine_id AS 
(
    SELECT 
        *,
        DENSE_RANK() OVER (PARTITION BY machine_id 
                           ORDER BY process_id, activity_type DESC) AS arrange_data 
    FROM Activity
),
-- Создание колонки для поиска разницы
previous_time_stamp AS
(
    SELECT 
        *,
        LAG(timestamp, 1) OVER (PARTITION BY machine_id ORDER BY timestamp) AS start_timestamp
    FROM 
        table_partition_by_machine_id
),
-- Вычисление времени каждого процесса для машины
process_time AS 
(
    SELECT 
        *,
        timestamp - start_timestamp AS process_time
    FROM    
        previous_time_stamp
    WHERE 
        activity_type = 'end'
)
-- Вычисление среднего времени для процессов каждой машины
SELECT 
    machine_id,
    AVG(process_time) AS processing_time
FROM    
    process_time
GROUP BY 
    machine_id

Объяснение

1. DENSE_RANK(): позволяет нам разбивать записи на группы в зависимости от machine_id и сортирует их по process_id и activity_type.

2. Функция LAG(): обеспечивает возможность получения предыдущего значения timestamp в пределах группы, чтобы затем можно было рассчитывать разницу времени. Это позволяет нам узнать, сколько времени длился процесс.

3. Фильтрация по activity_type: Мы отбираем только те записи, которые имеют activity_type равный "end", поскольку именно это указывает на завершение процесса.

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

Заключение

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